SSRS null parameter to return values

reporting-servicesssrs-2008

I have two reports bound to each other. On the first report when I choose a field I am lead to a second report showing only data from the row I selected in the first report. The second report is used for updating, therefore it takes in parameters. I have three text-boxes which allow a null value and a dropdown list.

First when I created the dropdown list and specified the values, and added a null value the report returned the row I selected in the first report with all the data. Now I tried to assign the values of the parameter to a database, but each time I get to this report it first asks to select a value from the dropdown and then it will display the data.

How can I add a Null value to the items retrieved from the DB so when null is selected as default then all values would be returned without any problems, and without any selection needed?

Best Answer

You would need to add a condition to your Dataset query to handle the NULL parameter. For example:

WHERE @Parameter is NULL or ColumnValue = @Parameter

Related Solutions

How to allow a null values in multi valued parameter

You can add a CRM filter parameter to your report, by adding a blank-value report parameter to your SSRS report.

The XML for the parameter would be like the following, considering you are working for example with the Account entity:

<ReportParameter Name="FilteredAccount">
   <DataType>String</DataType>
   <Nullable>true</Nullable>
   <DefaultValue>
      <Values>
         <Value></Value>
      </Values>
   </DefaultValue>
   <AllowBlank>true</AllowBlank>
   <Prompt>FilteredAccount</Prompt>
   <Hidden>true</Hidden>
</ReportParameter>

After you add this parameter, you will upload the report to CRM, and CRM will automatically create the parameter for you as SQL query (Value) and will load CRM filters. Any specific required filters could be saved to the report in CRM.

And note that this report parameter should be passed from SSRS to the stored procedure, which fetches the report filtered results.

You could also find details about CRM 2011 report pre-filtering parameter in this blog post http://carlwillis.blogspot.com/2012/01/pre-filtering-in-crm-2011-ssrs-reports.html

Sql-server – SQL variable to hold list of integers

Table variable

declare @listOfIDs table (id int);
insert @listOfIDs(id) values(1),(2),(3);    

select *
from TabA
where TabA.ID in (select id from @listOfIDs)

declare @listOfIDs varchar(1000);
SET @listOfIDs = ',1,2,3,'; --in this solution need put coma on begin and end

select *
from TabA
where charindex(',' + CAST(TabA.ID as nvarchar(20)) + ',', @listOfIDs) > 0

Best Answer

Related Solutions

How to allow a null values in multi valued parameter

Sql-server – SQL variable to hold list of integers

Related Topic