How to add an Option Set as a parameter in a SSRS Report

In this post I will go through how you can create a SSRS Report parameter which contains Option Set values.

To get all the values of your Option set, you will need to use the FetchXML below:

<fetch>
 <entity name="stringmap" >
 <attribute name="attributevalue" />
 <attribute name="value" />
 <filter type="and" >
 <condition attribute="objecttypecodename" operator="eq" value="account" />
 <condition attribute="attributename" operator="eq" value="new_country" />
 </filter>
 </entity>
</fetch>

• objecttypecodename = Which entity your Option Set is under
• attributename = What your Option Set is called

This FetchXML will return these results:

I uses the FetchXML Builder from the XRMToolBox if you are wondering how I tested this.

You will next need to add this FetchXML into a new Dataset as displayed below:

Next you will need to create a new Parameter. You may wish to tick or untick ‘Allow multiple values’ depending on what your requirement is.

Within the ‘Available Values’ Tab. You will tick the value ‘Get values from a query’ and then set the other values like so:

• Dataset –  The Dataset which includes the Option Set FetchXML
• Value field – ‘attributevalueValue’, this field includes the number value of the Option Set
• Label field – ‘value’, this is what will be displayed to the user when they run the report and choose which Option Set value they wish to filter by

Next, you will need to add another dataset you wish to bring back to show on your report. In my example I wish to bring back Account details which belong to ‘Argentina’ or ‘Brazil’. I created the FetchXML below:

<fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0" >
 <entity name="account" >
 <attribute name="name" />
 <attribute name="address1_city" />
 <attribute name="primarycontactid" />
 <attribute name="telephone1" />
 <attribute name="new_country" />
 <attribute name="accountid" />
 <order descending="false" attribute="name" />
 <filter>
 <condition attribute="new_country" operator="in" value="@Country"/>
 </filter>
 </entity>
</fetch>

You then add this FetchXML into a new Dataset:

As you can see. I have added the filter:

 <filter>
 <condition attribute="new_country" operator="in" value="@Country"/>
 </filter>

The @Country is pointing at the parameter we created earlier and which your Dataset will filter by in run time. The operator=”in” is needed if you wish to choose multiple options.

I run the report and I have chosen the Country values of Argentina and Brazil. The results only return Accounts which have the Option Set values of those Countries I specified.

I hope that this post has been helpful. You could also use the stringmap FetchXML for other development purposes as well.