Use parameters in reports

You use parameters in reports to control the data retrieved by prompting the user for a value or a set of values when the user runs the report. The dataset query retrieves only the data that is requested by the user. You can also add hidden and special parameters in the reports that do not prompt the user for input, but can be used for operations such as data filtering and dynamic drill-through.

Adding parameters

You can add parameters to a report to define a report’s individual parameters, pass information through a query, or provide access to user settings, such as CRM_CurrencySymbol and CRM_CurrencyPositivePattern parameters.

The <ReportParameter> is an element in the report definition (RDL) file that is used to describe an individual parameter in the report. The <QueryParameter> contains information about an individual parameter that is passed to the data source as part of a query. The following XML code taken from the Account Summary report's RDL file demonstrates how to use the ReportParameter and QueryParameter parameters.

<ReportParameter Name="CRM_FilteredAccount">  
  <DataType>String</DataType>  
    <Nullable>true</Nullable>  
    <DefaultValue>  
      <Values>  
         <Value>select * from FilteredAccount</Value>  
      </Values>  
    </DefaultValue>  
    <AllowBlank>true</AllowBlank>  
    <Prompt>CRM_FilteredAccount</Prompt>  
</ReportParameter>  

<Query>  
   <rd:UseGenericDesigner>true</rd:UseGenericDesigner>  
   <CommandText>declare @sql as nVarchar(max)  
      set @sql = '  
      SELECT top 10 CAST(accountid as nvarchar(100)) as AccountID,  
      name, '''' as None  
      FROM (' + @FilteredAccount + ') as fa'  
      exec(@sql)  
   </CommandText>  
   <QueryParameters>  
      <QueryParameter Name="@FilteredAccount">  
         <Value>=Parameters!FilteredAccount.Value</Value>  
      </QueryParameter>  
   </QueryParameters>  
   <DataSourceName>CRM</DataSourceName>  
</Query>  

The following examples show how to use the QueryParameter and ReportParameter parameters in a Fetch based report.

<ReportParameter Name="FilteredAccount">  
      <DataType>String</DataType>  
      <Prompt>Filtered Account</Prompt>  
            <DefaultValue>  
                  <Values>  
                        <Value>  
                              <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  
                              <entity name="account">  
                              <all-attributes/>  
                              </entity>  
                              </fetch>  
                        </Value>  
                  </Values>  
            </DefaultValue>  
    </ReportParameter>  

<Query>  
        <DataSourceName>DataSource1</DataSourceName>  
        <CommandText>  
                  <fetch>   
                  <entity name="account" enableprefiltering="true" prefilterparametername="FilteredAccount">  
                  <attribute name="accountid" />  
                  <attribute name="name" />  
                  </entity>  
                  </fetch>  
            </CommandText>  
        <QueryParameters>  
          <QueryParameter Name="FilteredAccount">  
            <Value>=Parameters!FilteredAccount.Value</Value>  
          </QueryParameter>  
        </QueryParameters>  
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>  
      </Query>  

Hidden parameters

The Report Designer in Visual Studio has built-in support for hidden parameters. In addition, you can hide parameters by adding a CRM_ prefix to the parameter name in a report. By default, the parameters with a CRM_ prefix are hidden when the report is published. When you run the report, you aren’t prompted to enter parameter values for the hidden parameters.

Special parameters

The following table shows the special hidden parameters that you can use in your reports.

You must create all parameters in a report before you can refer to them. The values of these special parameters are filled in by Customer Engagement (on-premises) when you run the report.

Additional format parameters

The following table contains additional parameters that you can use in the reports. Among them are parameters that provide access to the user Number settings information. You can use these values to format and display the numeric values. These parameters are similar to values specified in the NumberFormatInfo Class. Use these parameters in custom reports to format the data according to the user settings.

See also

Reporting and Analytics Guide
Publish reports