In my previous blog post, SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube, I blogged the out-of-box features in SSRS to create a report with parameters accessing an Analysis Services database.
5 report parameters were automatically created.
When I preview the report, the Year Number shows the following available values, which include 0 (zero).
There are two things I’d like to change for the Year Number parameter.
- Remove the value 0 (zero) from the available list.
- Set a dynamic default value to the current year.
- Here is what I’d like to achieve;
Step 1 – Show Hidden Datasets
In my BIDS, the default is to hide all the MDX datasets that are automatically created. To show all the MDS datasets, right-click on the Analysis Services data source, and select Show Hidden Datasets (make sure the check mark is showing).
Now, all 5 MDX datasets are showing under the Analysis Services data source.
Step 2 – Add a Filter function for Year Number.
In order to remove the 0 from the list, I will need to use the Filter function.
To save some typing, I copied the automatically created MDX query to SSMS, and modified it with a Filter function.
This is the final MDX query with a Filter function.
In the Query Designer, I pasted my new MDX query, and tested it.
Step 3 – Set a dynamic default value to parameter Year Number
I’ll achieve this with Reporting Services expressions. However, with MDX datasets, I’ll need to use a combination of regular RS expressions and MDX expressions.
First, get to the Report Parameter Properties window.
In the Expression editor, type this:
You will need to replace [DimDate] with your own date/time dimension. [Year Number] is the one of the attributes in [DimDate]. You will need to replace it with your own too.
However, the Today function, the Year function, and the CSTR function are all regular RS functions.
You are done. Now preview your report, you will see that value 0 is removed from the available list, and 2011 is provided as the default value for parameter Year Number.
In the previous blog post, SSRS #50 – Using Parameters in SSRS (1), I listed 7 different ways you can use report parameters. They are also the skills that report developers need to master in order to develop interactive reports.
In this blog, I’ll show you some simple steps to use cascading parameters.
You want to allow users to select a supervisor from the first dropdown list, then be able to see the employees who report to the selected supervisor in the second dropdown list.
The end result looks like this. Note that the second dropdown list is dimmed initially. Once the selection is made from the first dropdown list, the second dropdown list will be selectable.
The screen shots are from BIDS 2008, which will be slightly different from BIDS 2005. The concept and the steps are the same though.
Step 1 – Create two datasets: One for supervisors, one for team members
In my example, they are DATASET_SUP and DATASET_UW. I have other datasets in the screen shots, but they are irrelevant for the purpose of this post.
Examine the properties for the DATASET_SUP, you can see that the SQL query just simply retrieves the unique supervisor names and their IDs (within the selected date range).
Examine the properties for the DATASET_UW, you can see that the SQL query now has a where clause WHERE SUPCCRID IN (@SUP_CCRID).
The at sign @ represents a report parameter. At this point, do not worry about if parameter @SUP_CCRID has been created or not. If not, BIDS will automatically create one for you and you can modify its properties later.
Note1: make sure you have both the ID and the name fields in your queries for both the supervisors and the team members. See my blog post, SSRS #46 – A case against using character field in multi-value parameters, for the reasons why you want to use ID field to pass between queries or stored procedures.
Note 2: the where clause WHERE SUPCCRID IN (@SUP_CCRID) is the trick that makes the cascading happen.
Note 3: as observant as you are, you must have noticed that I didn’t write my where clause as this:
WHERE SUPCCRID = @SUP_CCRID
With WHERE SUPCCRID IN (@SUP_CCRID) , I am allowing multiple values of SUP_CCRID passing to my query, instead of a single value.
Step 2 – Create two parameters: one for supervisors, one for team members
In my example, @SUP_CCRID is the parameter that allows users to select a supervisor from a list, and @CCRID is the parameter that allows users to select team members from a list.
Let’s examine their properties.
For parameter @SUP_CCRID, this is what you will need to do on the Available Values tab:
- 1) you need to get values from a dataset. Select DATASET_SUP from the dropdown.
- 2) Select the ID field for the Value field.
- 3) Select the name field for the Label field.
For parameter @CCRID, you will do the same as for @SUP_CCRID.
- 1) you need to get values from a dataset. Select DATASET_UW from the dropdown.
- 2) Select the ID field for the Value field.
- 3) Select the name field for the Label field.
You are done. Preview your report, you will see the result as shown in the first screen shot in this post.
One of the readers asked me this question in Ask Sherry Li
Dependent parameter in SSRS
How do I create a dependent parameter in SSRS? For example, select a Supervisor in the first drop down and then the Employees for that Supervisor appear in the second drop down.
Master Skills of Manipulating Report Parameters
Before I answer the question, I’d like to draw your attentions to skills report developers need to master.
Building interactive SQL Server Reporting Services reports not only require report developers to master skills of using parameters, but also manipulating report parameters in many different ways. Here are some of the ways we can manipulate the report parameters:
- Cascading parameters – example: users select a supervisor from the dropdown, then the employees who report to the selected supervisor will appear in the second dropdown.
- Available Values – example: pre-populate a dropdown list with all the supervisors
- Default Values – example: the Start Date and End Date parameter will be pre-populated with the Beginning of the Month, and the current date, respectively
- Multi-value – example: to see productivity of several employees at the same time, users can select multiple employees from a pre-populated dropdown list
- Custom code in SQL stored procedures – Stored procedures allow the ultimate flexibility to use report parameters. One example is to use SQL code to split comma separated list into table values.
- Using parameters to dynamically change report item properties – example: use a report view parameter to dynamically show data at aggregation or detail level
- Using parameters in Drill Through and Sub reports
- I’ll blog about the Steps to Create Cascading Parameters in the next post.
I am no SQL Server architect. Normally I don’t blog about topics like this. I volunteered recently, however, to create an end-to-end solution on the company’s new Microsoft 2008 BI platform.
After I migrated almost everything from the 2005 platform to 2008 platform, and ready to deploy my Reporting Services reports, I was told that we do not have the Reporting Services 2008 ready yet.
I was also told that reports developed in BIDS 2008 will not be deployable on Reporting Services 2005. As stubborn as I was, I tried anyway.
Reports developed in BIDS 2008 will not be deployable on Reporting Services 2005
The message is very clear.
The report definition is not valid. Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ which cannot be upgraded. (rsInvalidReportDefinition)
Getting SharePoint 2010 and Reporting Services 2008 Integrated
The next thing I was told is that the architecture team is getting SharePoint 2010 and RS 2008 to work together. Although it’s not my job to make the integration work, I am interested in this topic from a developer’s point of view.
I searched on Internet, and found many information on this topic. Most of them are tips on how to configure both the SharePoint 2010 and RS 2008 to work together in the integration mode.
I, however, am interested in knowing how the integration will change how I publish and manage my Reporting Services reports and models.
Unfortunately, I didn’t find many examples. But this article on MSDN, although short, helped me to understand the topology of the integration. I can “almost” imagine what’s like to publish and manage my reports on SharePoint site.
From this diagram, I can see the components that will work together to make the integration happen. It also showed that report viewing, publishing and other report management activities will now be on SharePoint site.
Report items and properties will be stored in SharePoint content databases. This will allow us to publish reports to SharePoint libraries and secure them using the same permission levels and authentication provider that controls access to other business documents hosted on the SharePoint site.
The report server, however, will continue to provide all data processing, rendering, and delivery. It also supports all scheduled report processing for snapshots and report history.
If you have developed a few SSRS reports in BIDs, you must have noticed that BIDs uses a data cache to speed up your development work. Your design-preview-design-preview cycle is fast enough that you probably have never complained . Not yet.
One of my reporting Services reports is still in development phase. After receiving a Success email notification from my ETL job in the morning, I opened up the BIDs, and tried to preview my report. I was expecting data from yesterday, but I didn’t see it.
I am using BIDs 2008. The SSRS report is accessing an Analysis Services database using MDX. I checked my fact table which has data from yesterday. I know my cube processing was successful. Browsing the cube in SQL Management Studio also showed data from yesterday. However, in BIDs, when previewing the cube, I didn’t see my data from yesterday.
A Quick Solution
I understand the data cache feature. So I didn’t panic. right click on the dataset, select Query.
On the Query Designer, click the link to execute the query.
This will pull fresh data from the back-end data source. Now I see data from yesterday on Preview.
Another Even Quicker Solution
After I published this post the first time, Mark W, a regular reader, pointed out that the Refresh button is an even quicker way to retrieve the most recent data from the back-end data source. Thanks Mark!
Reasons for this blog
There are three reasons for this post. One is for the benefit of new SSRS report developers who might be unaware of the data cache feature in BIDs.
Second I am still not sue if BIDS 2008 behaves differently from BIDS 2005 in terms of data cache, or it behaves differently when accessing a cube.
Third reason is to give you a link to this blog post Disable Reporting Services Data Cache in Development. In this blog, the author pointed out that you can permanently disable the data cache feature in BIDs. I personally will not do this, because I really like the data cache feature.
In case somebody wants to give it a try, here is what the author suggested.
Find the config file that controls the Report Designer in BIDS. For SQL Server 2008 SSRS, this should be:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
Then change CacheDataForPreview to “false”.
Fact tables that involve employee productivity (which drives the Incentive program) are mostly transactional. The word transactional is not exactly accurate in our world. The granularity in many of data elements is per day, meaning that certain data elements will only store the last value from each day, rather than every changed values (transactional) throughout the day.
This “per day” approach also coincides with our batch ETL process, which is also per day.
However, business has constantly asked us to back fill those per-day transactional data with data that were entered many days later. Here are some scenarios:
1. Managers have forgotten to assign team members to a task until the tasks have been completed.
2. Team members have forgotten to check certain indicators until days after the decisions have been made.
As a developer, we are always happy to comply with business requirements, with a few occasional exceptions. I think this should be one of the exceptions.
Here is the reason. If the transactional data can be updated many days later, imagine what would happen to the record counts. Your record counts (for certain measures) on 5/20 will be slightly different when you count it again one 5/25, if you have back filled the transactional data between 5/20 and 5/25.
Does this issue sound familiar to you? What about the famous Slowly Changing Dimension (SCD) issue in star schema ETL process? Do they have anything in common?
I think this is the same issue as the SCD issue. I need to confess that when I read about the SCD issues, it really didn’t make a lot of sense to me until I have a real business case.
A real business case also helped me to realize that there is no right or wrong solution for the SCD issue, and the solution largely depends on what you can work out with the business.
I have been wanting to blog about this for awhile. SQL code readability is not a topic SQL developers like to talk about. In my 10+ years of working career, I only had this topic with two co-workers. Both of them told me that I write very “readable” SQL code. One of them said he would not use the coding style to judge a SQL developer. Another co-worker told me that he actually installed a SQL code beautifying software to make other developers’ SQL code readable, before he even attempted to modify the code.
Commenting is always welcome in SQL code. Single line comments, multi-line comments, in-line comments are always good to see when you work on other developers’ SQL code, until you realize that not only the comments itself have no readability, but also the messy comments totally destroy the readability of the code.
I find myself recently not only spending time to beautify the SQL code, but also to beautify the comments, especially the in-line comments.
Multi-line comments in the header portion of the procedure
It’s my preference to have multi-line comments in the header portion of the procedure (beginning of the procedure) .
I also place multi-line comments in my SSIS packages by adding them as Annotation. The annotation is certainly not a sophisticated text editor. So make sure you type up your multi-line comments in a SQL/Text/Word editor, and format it with appropriate indents, then paste it to the Annotation in the SSIS package.
Inside the procedure body, I do not use in-line comments
Inside the procedure body, I absolutely do not use in-line comments. Instead I logically divide my code into sections and paragraphs, similar to sections and paragraphs in a book, where paragraph performs only one task, and section is the collection of small tasks.
For Sections, I’d use comments like this:
For paragraphs, I only use single line comments.
One more note before I close this post. When you design your SSIS package, and run into problems in your SQL query in an OLE DB Source in a data flow, check if you have In-line comments embedded in the query. Removing them might help to solve your problem.
Let’s work together to make our SQL code readable and beautiful with a consistent style!