Develop a simple AX 2009 SSRS Report with a Multi Value parameter

Well this is my first blog article and I hope it will help add value to the online knowledge-base of AX !!!!! 

So here it goes  ..  

Like Single value parameter report in AX 2009 SSRS , multi value parameter reports can be developed. There is a property named Multi-value. If the value is set to true then the report becomes multi valued.

Note: Once the parameter is set to “multi value” it can’t be made nullable or blank simultaneously.

So the real problem arises when user ask for an option to choose data in a multi value parameter and at the same time can leave it as blank. Out of the box SSRS 2008 does not provide this option. The workaround is to pass values as default value. So the user will not provide value explicitly but the system will automatically consider all the available data.

Let’s start with a dummy report development:( keeping in mind that creation of a new report project is known)

1. Create a new report.

2. Now add a dataset with data source type as Query. (See image below) Data source type can be Business Logic also.

3. Create a new parameter like in the image below. Thus we have created a new parameter with “Values” based on AccountNum column from the dataset (ForMultiValueParmDS).

4. Set the multi value property as True.

5. Provide a default value as shown below to the same parameter “AccountNum”.

6. After all these steps build and save the report to AOD.

7. Lets Preview the design. (Hope you have added the dataset in the auto design else just drag the dataset created to the design node from which you want to display data.)

8. The way you create filter is a bit different for multi value parameters. See below

9. Now lets preview the report and see how does it come.

10. Values in the parameter Account Num is being displayed as a list of values with check boxes with it. Also see that it has got (select all) option. It is coming like this because default value property fro the parameter is set. When the report is deployed and viewed in EP the parameters are rendered as Drop-down consisting of a list of values and each of them prefixed with a check box. Also the “Select All” option is available there.

Now the user has flexibility if he/she wants to explicitly provide values or not. It is similar to what we have in MS Office Excel Macros.

Please provide your feedback on this article.