In the first post in this series, I explained how to drill from a mobile report to a paginated report that contained parameters populated from a SQL Server database query. This was pretty simple and straightforward. What if the data in the parameter of the paginated report was populated from a SQL Server Analysis Services (SSAS) model? Does the drill URL change? It absolutely does. Let me show you how.
To get started I created a mobile and paginated report. The paginated report includes SSAS parameters. Then I followed these steps:
1. Open the Mobile Report Publisher and create a mobile report, similar to what is seen in the following image:
2. Then identify and configure a visual that supports drill through.
3. Click the visual and notice in the bottom properties section there is a button labeled Drillthrough.
4. Click that button and select Custom URL from the list of available choices.
5. The Set Drillthrough URL window will appear.
6. Before you proceed, you need to understand how to pass a parameter via a URL to SSRS. If you are not familiar with this, go here: Pass a Report Parameter Within a URL, to get more details.
7. The fastest way to start building the URL navigate to http://<servername>/ReportServer and browse to the location of your report, replace <server name> with your server name. Right-click the link for the report and select Copy shortcut. This is the starting point for the URL.
8. Paste it into the Enter a URL to go to when this visualization is clicked text box on the Set drillthrough URL screen. Remove rs:Command=Render from the URL.
9. Next you will need to add parameters. Go to the report in SQL Server Data Tools (SSDT) and find the name of the parameter(s).
10. In this particular paginated report, there are two parameters.
11. One is the year, which will be populated from the Selection List. So, return to the URL and add &<name of parameter>=, replacing <name of parameter> with your report parameter names, to the end of the existing URL.
12. Click {{SelectionList.SelectedItem }} from the Available parameters list.
13. The other is Term. Term will get its value from a Bar Chart. On the Set drillthrough URL add another &<name of parameter>= and click CategoryChart.SelectedDataPoint
14. You will get a preview of the URL in the textbox below.
15. Finally, click the button labeled Apply.
16. Preview the report and click one of the bars on the bar chart. What happens?
17. The report appears but does not execute because the parameters are not set. Why not?
Well, after inspecting the URL (http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fHigher+Education+Solution%2fReports%2fAnnual+Enrollment+Details&DateSchoolYear=2007&Term=Spring), it passed the values as expected. What is the problem? Remember, the parameters are populated from and SSAS model, so that means we need to send the values formatted as such. This format is:
[TableName].[Attribute].&[Value]
No problem, just build that string as part of the URL. Guess what, that doesn’t work either. What you need to do encode certain characters in the URL. For example, to pass year it needs to look like this [Date].[School Year].&[{{SelectionList.SelectedItem}}].
18. Prior to setting that as a parameter in the URL the open bracket ([), closed bracket (]) and ampersand (&) must be encoded, which transforms the value to this: %5BDate%5D.%5BSchool+Year%5D.%26%5B{{ SelectionList.SelectedItem }}%5D&D. That’s pretty simple. Don’t worry if you don’t know the URL encoded values, this site (https://www.w3schools.com/tags/ref_urlencode.asp) actually will convert them for you. Once that is done the URL will resemble the following:
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fHigher+Education+Solution%2fReports%2fAnnual+Enrollment+Details&DateSchoolYear=%5BDate%5D.%5BSchool+Year%5D.%26%5B{{ SelectionList.SelectedItem }}%5D&DateTerm=%5BDate%5D.%5BTerm%5D.%26%5B{{ CategoryChart.SelectedDataPoint }}%5D
Notice the bolded text, that is how the parameters should be formatted. Kind of tricky, but it works.