SSRS #52 – Setting Dynamic Default Parameters in MDX Dataset

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).

Goal

There are two things I’d like to change for the Year Number parameter.

1. Remove the value 0 (zero) from the available list.
2. 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.

On the Report Parameter Properties window, go to the Default Values tab. Check “Specify values”. Then click Add, and the function button .

In the Expression editor, type this:

="[DimDate].[Year Number].&["+CSTR(Year(Today))+"]"

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.

SSRS #51 – Using Parameters in SSRS – Cascading Parameter (2)

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.

Goal

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.

SSRS #50 – Using Parameters in SSRS (1)

One of the readers asked me this question in Ask Sherry Li

Question

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:

1. 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.
2. Available Values – example: pre-populate a dropdown list with all the supervisors
3. 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
4. Multi-value – example: to see productivity of several employees at the same time, users can select multiple employees from a pre-populated dropdown list
5. 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.
6. Using parameters to dynamically change report item properties – example: use a report view parameter to dynamically show data at aggregation or detail level
7. Using parameters in Drill Through and Sub reports

I’ll blog about the Steps to Create Cascading Parameters in the next post.

SSRS #49 – Reporting Services 2008 and SharePoint 2010 Integration

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.

Overview of Reporting Services and SharePoint Technology Integration

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.

Sounds exciting?

SSRS #48 – Reporting Services Data Cache in Development

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.

Issue

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”.

Productivity Reporting #3 – Back fill transactional data or not? Slowly Changing Dimension (SCD)?

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.     

SQL #47 – Too many in-line comments totally destroy code readability

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!

SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube

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. 

SSAS #20 – When Analysis Services meet Integration Services

I recently designed a cube in Analysis Services from scratch. After some brief research on all the different ways to process Analysis Services objects, some of the questions I had finally have a clear answer.

If you have being working on Microsoft’s BI platform, you would know that SSAS and SSIS actually meet quite often. Did you design SSIS packages for fact table ETL and dimension ETL? Yes, you did.

The real integration points between SSIS and SSAS, though, involve SSAS cubes, where we need SSIS to process the database sources into the OLAP cube structure.

A big portion of SSAS integration with SSIS involves processing data and managing measure group partitions. In SSIS, Microsoft has provided us with some out-of-box basic cube processing capabilities, and also tools for handling more complicated situations. I’ll show you how I used the out-of-box basic tasks in SSIS to process my cube.

Before I jump into the details, I want to ask you two questions. One, what are some of the basic objects in SSAS? Two, what are the main types of SSAS objects that must be processed? I’ll blog about these two questions later.

3 Out-of-Box methods to process SSAS Objects in SSIS

1. Using the control flow object Analysis Services Processing Task

To me, this is the most straightforward approach. This is the method I used. I’ll show you how I used it in this post. You will see the drawbacks of this approach from the example I will show you.

2. Using the control flow object Analysis Services Execute DDL Task     

This object’s functionality goes beyond just Data Definition Language (DDL). It can run an SSAS XMLA script, which can not only run DDL, but also query and process. Because XMLA script can be modified in SSIS package before it is executed, this object is actually very powerful in terms of processing SSAS objects dynamically (without drawbacks of hard-coding the SSAS objects).

3. Two data flow destinations: Dimension processing destination and Partition processing destination

These two objects allow data directly from the pipeline to be pushed into a dimension or a partition. This is the only method where data is pushed directly to SSA. The above two methods essentially tell SSA to start pulling data from a data source      

Use control flow object Analysis Services Processing Task to process SSAS Objects

It’s pretty straightforward with this approach. I’ll blog about it in my next post.

SSRS #47 – Manage subscriptions dynamically

In my previous post, SSRS #43 – Problem with undocumented SQL procedure AddEvent when kicking off a Timed Subscription, I mentioned that the report link from the Subscription is not a valid link. Our architecture team informed me that using undocumented procedure AddEvent probably is not the best way to kick start a Reporting Services Subscription. The recommendation is to use the exposed web services provided by the reporting Services within my SSIS package to kick start the Subscription.

Well so far I couldn’t get it to work because I couldn’t even get the ReportingService class to work in either a Script Task or the Web Service Task. There are many articles talking about how to get the ReportingService class reference to work in a SSIS package. This one seems to have a very detailed instruction on how to make it work, Consuming the Reporting Services web service inside SSIS. Unfortunately so far it’s not working for me because I could not find the wsdl.exe file locally

According to this article on MSDN, Web Services Description Language Tool (Wsdl.exe), the file wsdl.exe is part of the.NET Framework 2.0 Software Development Kit. Once installed, the tool (wsdl.exe) can be found in the folder: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. Since I don’t have the SDK Kit installed, no solution for me so far. I don’t want to install it right now either, since this is not on my priority list.

My priority regarding managing Subscriptions on Reporting Services

What is on my priority list:

1) Continue to use the AddEvent procedure to kick start the Subscription, but do not hard code the SubscriptionID. This should be easy to achieve. See below.

2) I need to switch from Subscription to Data Driven Subscription.

Reasons for the above priority

If you have created a Timed Subscription on Reporting Services, you must have noticed how “easy” it is to select values for your report parameters. You must have also noticed a few times when the automatic emails did not fire off. You scratched your head, and couldn’t figure out why. Later you will find out that it’s because the values you picked on the Subscription are no longer valid, or you have added more report parameters, or have deleted a few parameters. In another word, the Subscription you have created is no longer valid.

Retrieve SubscriptionID Dynamically

I need to do the first thing on the list if I am going to continue to use the Subscription, instead of the Data Driven Subscription, so at least I have a valid SubscriptionID if I changed something on the Subscription.

This is a relatively easy task. I would create two Execute SQL Tasks, and a package variable varSubscriptionID.

     

The variable varSubscriptionID will be populated in the first Execute SQL Tasks with a query like this:

In the second Execute SQL Tasks , I will use this to kick start the Subscription by passing the variable varSubscriptionID to it (represented by the question mark).

Data Driven Subscription

There are many articles about why we need to use Data Driven Subscription VS. just the plan Subscription extension on Reporting Services. Most bloggers have done a great job. I don’t need to report them here. To me, I need to use it because I do not want to hard code the values for my report parameters. I found out what would happen if the values are no longer valid. I also want to be able to customize the subscriptions for different business groups. The idea is the same as my first priority, I want to manage the subscriptions in a dynamic way.

I will blog about the Data Driven Subscription in the future.