Some SSRS reports have a large number of parameters
I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.
Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.
A bug in SSRS 2008
Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.
I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.
So I had to say that this is a bug in SSRS 2008.
Remove the middle parameter to work around the bug
To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).
The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.
Longer version of the solution
The following is the longer version of the solution.
My Original Suggestion
- Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
- Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
- The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
- Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
- Good news: this solution worked in SSRS 2012 in my test.
Here are two screen shots from our failed solution.
New Solution That Worked
- Remove the middle parameter entirely.
- Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
- The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
- Voila. It works.
- Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.
Here are a few screen shots from our successful solution.
The nested IIF statement is now coded in the parameter expression.
I worked on a SSIS package a few months back to retrieve data from a SSAS cube with MDX queries. A co-worker recently is developing a SSIS package to perform a similar task, but couldn’t get the SSIS package to work. The data flow was lost somewhere and the package could never finish the data loading.
This is what it will look like in the Data Flow task. The OLE DB Source will stay yellow forever.
I couldn’t remember right away how my SSIS package was configured to make the data flow work. I had to find my old SSIS package and open it to refresh my memory.
Need to set Format=Tabular in the Data Link Extended Properties
After choosing the OLE DB Provider for Analysis Services 10.0 as the provider for the connection manager, we need to go to the All tab to set Format=Tabular in the Data Link Extended Properties.
There are many blogs on this little “secret” developers discovered. Some claimed that this is a fix for a bug in SSIS 2005 SP1. I had to do this in both SSIS 2005 and 2008 to make it work. So I am not sure if it is caused by a bug or a designed “feature” in SSIS.
Neither can I find more information about what this property value means. You are welcome to share if you have more insight on this.
The happy green color is what you will see once you set Format=Tabular in the Data Link Extended Properties.
A couple of notes:
1. About the data type. Whenever you try to access the column information, you will get a “nice” warning from SSIS that data type DT_WSTR (nvarchar) will be used instead. Again if you have more insight on this, you are welcome to share.
2. I used a Data Conversion transformation to convert the DT_WSTR to DT_STR or DT_NUMERIC accordingly.
I wasn’t sure if I should put this blog under SSRS or SSAS category. SSAS category won by default because I do not have many SSAS posts.
In my last SSAS #20 post, I blogged about how to use the out-of-box Analysis Services processing tasks in SSIS to process dimensions, partitions, and measure groups.
In this blog post, I’ll show you the out-of-box features in SSRS you can use to create a report that accesses your cube.
I know I am skipping a lot of steps, such as how I designed the star schema for my data mart, how I designed my SSIS package to load data into the data mart, and how I created and deployed my Analysis Services database.
Analysis Services database – asLMRUWDashboard
For the purpose of this blog post, I’ll just show you my final product, asLMRUWDashboard, an Analysis Services database. I have 5 dimensions, and two of them have user defined hierarchies. It’s a very simple and small database. Even with 5 dimensions, the size of the AS database is less than 1 MB.
5 Parameters in the final SSRS report
My final SSRS report will have 5 parameters. The first 2 will be from my Data dimension, and other 3 are from the Site dimension, the Treatment dimension, and the Review Type dimension. I am ignoring the Lien dimension. I am also ignoring the attribute hierarchies for now.
Data in a Matrix
I need my data to display the date dimension horizontally, and other dimensions vertically. I will need a matrix to do this.
Step 1 – Create a shared data source for Analysis Services database asLMRUWDashboard
I am using BIDS 2008. The steps should be the same or similar in BIDS 2005. (I am down playing the difference between BIDS 2005 and 2008. But the truth is you will never want to use BIDS 2005 again if you have ever put your hands on BIDS 2008, especially when it comes to using Matrix.)
This is a straightforward step. Make sure you choose Analysis Services as the Type of the source.
Step 2 – Create a Data Source to use the above shared data source
In the Report Data tab, click New and select Data Source…
Make sure you select the data source you just created.
Now you should have a data source created that points to the AS.
Step 3– Create a Dataset to use the above shared data source
Right click the data source and select Add Dataset…
In the Dataset Properties window, ignore the query for now. It’s my final MDX query. I didn’t hand write it. Instead, click the Query Designer button.
What shows up is actually the cube browser, which is the same cube browser you have seen in SQL Server Management Studio and in Analysis Services project in BIDS.
To create parameters for your report, just simply drag and drop dimensions/attributes onto the top portion of the Query designer, and make sure you check Parameter. The Query Designer will automatically create parameters and default datasets for each check mark.
Then start to drag and drop the measure(s), and dimensions into the bottom portion. The bottom portion is only showing data in a tabular format. do not worry about this yet. You will have opportunity to put your data in a matrix later.
Step 4 – Examine the Parameters that have been created.
Parameters and parameter values are automatically created.
Step 5: Create a matrix with three row groups, and one column group.
The three Row Groups have parent/child relationships. The Column group will be from the Date.
Preview your report. You will see the report parameters. Start to create and format the sub totals in the matrix. You will have a very impressive report.
Hope this blog post will motivate you to create your first cube and first SSRS report to access a cube.