Set property “Hide” of report parameter in Report Server 2008 R2

The problem

In SQL Server 2008 Business Intelligence Development Studio there is the navigation pane “Report Data”. Here the parameters can be configured. I have checked “Hidden” for all of the automatically filled parameters with the prefix “CRM_” and the Prompt is filled with the name of the parameter.

Here is the code snippet for the report parameter “CRM_FilterText” in the rdl file:

<ReportParameter Name=“CRM_FilterText“>

  <DataType>String</DataType>

  <Nullable>true</Nullable>

  <DefaultValue>

    <Values>

      <Value xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance“ xsi:nil=“true“ />

    </Values>

  </DefaultValue>

  <AllowBlank>true</AllowBlank>

  <Prompt>CRM_FilterText</Prompt>

  <Hidden>true</Hidden>
</ReportParameter>

 

Until SQL Server 2008 there hat been no problems during installing the reports in CRM. The report parameters hat been set correctly.
With SQL Server 2008 R2 an error occurred. Every parameter starting with “P” or with “CRM_” is not set correctly. The entry in the event viewer is .This is amazing because other parameters with other names are all stored correctly. Perhaps it is a bug in SQL Server 2008 R2 – I don’t know.
For we have more than one report to install it is not practicable after installing the reports to go to the report server and corrrect every parameter by checking “Hide” for every report. No way. I had to find a better way.

The solution

After a while I found a hint how to set the hide propety of report parameter using RS API (many thanks to Jin Chen and his answer ).
For the installation of the reports in CRM I have written a small console application using the CRM and the RS API. The RS API is referenced by webservice, the CRM API is referenced by DLLs.
I have discovered setting the property hide of the report parameter is differently when you have a report server 2008 or a report server 2008 R2.

If you have the report server 2008 you have to use SetReportDefintion. The Prompt property can have the name of the report parameter, but it can set to string.empty too.The PromptUser property is set to false.

Below is the code snippet for report server 2008: 

string reportname=“/”+organizationName+“_MSCRM”+“/4.0/{“+rptGuid+“}”;
try

{

    //Append prompt user value “false” and prompt empty value

    byte[] reportDefinition = null;

    Warning[] warnings;

    System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

    //Get report definition.

    reportDefinition = rs.GetReportDefinition(reportname);

    MemoryStream stream = new MemoryStream(reportDefinition);

    doc.Load(stream);

    //Get report parameters’ list

    XmlNodeList nodeList = doc.GetElementsByTagName(“ReportParameter”);

    //Create a new node for prompt user.

    XmlElement elem = doc.CreateElement(“PromptUser”);

    elem.InnerText = “false”;

 

    //Iterate through nodes of type report parameter

    foreach (XmlNode nodeTemp in nodeList)

    {

        if (nodeTemp.Attributes["Name"].Value.StartsWith(“CRM_”) ||

            nodeTemp.Attributes["Name"].Value.StartsWith(“P”))

        {

            bool bPromptUserExisting = false;

            //bool bPromptUserSpecified = false;

            //string promptName = nodeTemp.Attributes["Name"].Value;

            foreach (XmlNode childNode in nodeTemp.ChildNodes)

            {

                if (childNode.Name == “Prompt”)

                    childNode.InnerText = string.Empty;

 

                if (childNode.Name == “PromptUser”)

                {

                    childNode.InnerText = “false”;

                    bPromptUserExisting = true;

                }

            }

            //If node does not exist, add the promptuser node.

            if (!bPromptUserExisting)

            {

                //Add the node to the document.

                nodeTemp.AppendChild(elem);

            }

        }

    }

 

    MemoryStream streamSave = new MemoryStream();

    doc.Save(streamSave);

    reportDefinition = streamSave.ToArray();

    //Update the report definition.

    warnings = rs.SetReportDefinition(reportname, reportDefinition);

    if (warnings != null)

    {

        foreach (Warning warning in warnings)

        {

            Console.WriteLine(warning.Message);

            AppendTextToLogFile(warning.Message);

        }

    }

 

}

catch (SoapException e)

{

    Console.WriteLine(e.Detail.InnerXml.ToString());

    AppendTextToLogFile(e.Detail.InnerXml.ToString());

} 

In case of report server 2008 R2 you only can change the property using SetReportParameters. SetReportDefintion do not change anything. The Prompt property must be empty and the PromptUser property is set to true!

Below is the code snippet for report server 2008 R2:

string reportname = “/”+organizationName+“_MSCRM”+“/4.0/{“+rptGuid+“}”;
ReportParameter[] parameters = null;
string historyID = null;
bool forRendering = true;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;

parameters = rs.GetReportParameters

    (

        reportname,

        historyID,

        forRendering,

        values,

        credentials

    );

 

foreach (ReportParameter parameter in parameters)

{

    if (parameter.Name.StartsWith(“CRM_”) ||

        parameter.Name.StartsWith(“P”)

        )

    {

        //PromptUser = true AND Prompt =

        parameter.PromptUser = true;

        parameter.Prompt = string.Empty;

    }

}

rs.SetReportParameters(reportname, parameters);



Advertisement