It is very common when designing reports that you may need to define a multi-select report parameter in order to give the users some filter options and generate the report based on the user-selected values. You may also want to display those selected values on the Excel report. This can easily be achieved with OfficeWriter by creating an SSRS formula that uses the required report parameter and inserting that formula into the report template through our OfficeWriter Designer add-in in Excel, as described in the following steps:
-
Open the RDL file using the Open Report button in our add-in
-
From the Insert Formula drop-down, select Build New Formula
-
Select the report parameter that you want in the report and click Insert
-
The formula gets inserted automatically in the following form: =Parameters! <parameter>.Value .You will need to manually edit this formula and use the Join() function in SSRS, which will allow you to display all the selected values in a comma-delimited list, and then click OK: =Join(Parameters!<parameter>.Value,”, “)
-
This newly-created formula will appear in the “Insert Formula” drop-down. All you need to do now is to select the cell where you want the values to be displayed and then insert the formula by selecting it from the drop-down. The formula will be inserted in the form of %%=RS_FORMULA.Formula1. (In the formula name it saysFormula1because in this example it is the first formula we created and inserted to the template)
After you are done with the SSRS formula, you can save this RDL using the Save As button in our add-in and then publish it to the report server. After that, in the Report Manager, once you run the report by selecting all the values for the report parameter and export it using our renderer extension Excel designed by OfficeWriter, in the generated XLSX file you will see that the selected values are displayed as a comma-delimited list in cell B6:
Voilà! It is done!
