Creating a Drill-down report in SSRS

In this article, let us start to create a Drill-down report, another common type of report, using SSRS 2012.

I will be using the same Parameterized report that I created in one of my earlier articles (Go to Creating a parameterized report in SSRS using mdx ). You can see that we have 4 columns in this report – Category, Subcategory, Product and Internet Sales Amount. When we execute the report in its current form we see all the column values by default.

But consider a scenario where the requirement is that only the Category values (with sub-totals at Category Level) should be visible when the report is initially rendered. If the user wanted to view the next level of details, like viewing the report at the Sub category or product level, then he should be able to do it easily.

The Drill-down report is what we need in this scenario.

1. Open report

Step 1: The first step is to add a Parent Group at the Category level. Right-click the details group (in the Row Groups section as shown in the screenshot) and select Add Group > Parent Group…

2a. Add 1st Parent Group

In the Tablix Group, select Group by: [Category] option.

2b. Add 1st Parent Group

Two things are added to the report after creating the group. Firstly, you can see an additional column also named Category (which is associated with the group we just had created). We will delete the original Category column later. The second change would be in Row Groups section. Now we can see a new group named Category above Details group indicating that the Category group is the parent group of Detail group.

Step 2: Now we need to add a second group, at the SubCategory level. Right click Details group again and select Add Group > Parent Group…

3a. Add 2nd Parent Group

Select Group by: [Subcategory] and click OK.

3b. Add 2nd Parent Group

Step 3: You can see the additional Subcategory column in the tablix and an additional Group in the Row groups section. Now we need to add the final group to the report. Right-click the Details group again and select Add Group > Parent Group…

4a. Add 3rd Parent Group

Select Product in the Group by: drop down and click OK.

4b. Add 3rd Parent Group

We can again see an additional column, Product, in the Tablix section and an additional group in the Row Groups section.

Step 4: In the tablix, select the three original columns – Category, Subcategory and Product. Right-click the header of the selected columns and select Delete Columns.

5. Delete Old Original Columns

Step 5: Modify the expression of the Internet Sales Amount which has the measure value by right-clicking and selecting Expression (as shown in the below screenshot).

6. Adjust column Width & updated expression

Replace the existing expression from =Fields!Internet_Sales_Amount.Value  TO  =SUM(Fields!Internet_Sales_Amount.Value)

7a. Update Expression - OLD

7b. Update Expression - NEW

Step 6: Now we need to add the Drill-down functionality and we do this in the groups. Since Category is the top level and we need to see this by default when the report is rendered, we do not need to make any changes to this group. However, some of the properties for the second group, Subcategory, need to be changed. Right-click the Subcategory group and select Group Properties.

8a. Add Drill Down functionality - 2nd Group

Go to the Visibility tab and change the visibility option from Show to Hide. Tick mark the ‘Display can be toggled by this report item:’ check box and select the Category1 column from the drop down list and click OK.

8b. Add Drill Down functionality - 2nd Group

Step 7: Similarly, right-click on the Product Group and select Group Properties.

9a. Add Drill Down functionality - 3rd Group

In the Visibility tab, change the visibility setting from Show to Hide.
Tick mark the ‘Display can be toggled by this report item:’ check box and select the Subcategory1 column from the drop down list and click OK.

9b. Add Drill Down functionality - 3rd Group

 

Step 8: Change the background of the three detail cells (not header cells) – Category, Subcategory and Product as shown in the below screenshot. Change the color to Grey (or any color you like using the Background color selector button.

10. Change background color for detail columns

11. Clear the Headers - Rename headers

Step 9: Also, for these three detail cells, change the BorderStyle property to None.

12. Change BorderStyle to None

Step 10: Now let’s preview the report.

13. Preview Report

We can see that when the report is rendered, it is a summarized report, summarized at the category level.

14 DrillDown Report Main Level

If we want to view more details about the report, then we need to click the + button against the category and the report will expand to show the subcategory details.

15 DrillDown Report Second Level

Expand the Subcategory to view the report at the Product level.

16 DrillDown Report Third Level

Note: Since we had changed the expression for the Internet Sales Amount to SUM(Fields!Internet_Sales_Amount.Value), we are getting the correct aggregated values.

In the next report, we will see how we can create other types of report.

Creating a Parameterized Report in SSRS using MDX

As you start working on SSRS reports, it is only imperative that you will eventually be asked to create a parameterized report at one point in time. This is because this is one of the most common type of reports as it gives the power of filtering the report based on end user preference.

For e.g., a business analyst might just want to focus on North America sales today but might want to view the sales of another region tomorrow. A static report without parameters and created just for North America sales would not work here and another report would be needed to be created to meet the Analyst’s requirements. On the other hand, creating a parameterized report will enable the Analyst to just select the region that he wants to focus on and view the report.

In this article, we will be looking into the detailed steps as to how to create a parameterized report.

Since we will be using Analysis Services as the source here, we will use MDX to query the cube. MDX is a powerful language and SSRS supports this wonderful language.

Step 1: Open BIDS (All Programs > Microsoft SQL Server 2012 > SQL Server Data Tools). In the start screen, click on New Project (or go to File > New > Project).Provide a name for the project and also browse to the location where we want the project to be created (tick the check box to create a directory for solution). Click OK.

1 Create New Project

Once the new project is created, in the Solution Explorer, we can see a Solution named SSRS_2012_Reports with three folders: Shared Data Sources, Shared Datasets and Reports.

2 Create New Project

Step 2: The first thing we need to do is to create a data source followed by a dataset. To create a Shared Data Source, right-click Shared Data Sources and select Add New Data Source.

3 Add New Shared Data Source

The Shared Data Sources Properties window opens up. Provide a Name for the shared dataset. In the Type selection box, select Microsoft SQL Server Analysis Services (since we will be querying an SSAS Cube to build our report).

Then in the connection string, click the Edit button. The Connection Properties window box opens up. Provide the server name, in my case it is the local server and I just put a (dot) and then select the AdventureWorksDW2012Multidimensional-EE database (This is the SSAS database that contains the Adventure Works cube that we will be querying). Click OK to exit the Connection Properties window.

4 Shared Data Source

Click Ok again.

5 Shared Data Source

Step 3: Now that we have a Data Source, we can go ahead and start creating our report. (A dataset is still required which we would create a little later). Right-click on Reports folder in the Solution Explorer, Select Add option and New Item.

6 create a New Report

In the Add New Item window, select Report template, provide a name for the same and click on Add to create a new blank report.

7 Parameterized Report - a

Step 4: Now, we have created a blank report with no data source (the one we initially created was a Shared Data Source with Project scope). We still need to have a Data Source that the new report can have access to. So, let’s create a new Data Source specific to the report and point it to our Shared Data Source we initially created.

In the Report Data pane (if you are not able to locate the Report Data pane, press Ctrl + Alt + D to toggle the same), right-click on Data Sources folder and select Add Data Source… that will open up the Data Source Properties window.

8 Parameterized Report - Create Data Source a

In the Data Source Properties window, provide a Name for the Data Source and select the ‘Use shared data source reference’ radio button and select the shared data source that we had initially created.

Click OK to close the window and return to the report.

9 Parameterized Report - Create Data Source b

Step 5: The next step is to add a Dataset. Right-click on Datasets folder and select ‘Add Dataset…’ to open the Dataset properties window.

10 Parameterized Report - Create Dataset a

In the Dataset properties window, provide a name for the dataset and select the ‘Use a dataset embedded in my report.’ radio button. Then select the data source that we created in the previous step and click on Query Designer button.

11 Parameterized Report - Create Dataset b

This will open up the Query Designer window that allows us to browse the Adventure Works cube. This Query Designer supports both graphical interface (Design View) and code interface (Query View). While the Design View allows us to drag and drop the required measures and dimensions and set filters, the Query View allows us to manually modify the MDX query to perform advance operations.

But note that once we switch from Design View to Query View and make even a small change, we cannot switch back to Design view. So we need to use caution here. Eventually though, we would need to switch to Query View to take advantage of the full power of MDX.

Browse to Internet Sales Amount measure (under Internet Sales measure group) and drag it to the white space. We can also right-click a measure or dimension & select Add to Query.

12 Parameterized Report - Create Dataset c

Next, let us add some dimensions.

Browse to the Product dimension and in the Product.Product attribute hierarchy, drag the Product level to the query results window.

In the below screenshot, Product.Product is an attribute hierarchy and within this hierarchy, Product (highlighted in red) is a level.

Now, let us add some filters to our dataset. There are multiple ways to add a filter. We can either browse to the hierarchy, select it and click ‘Add to Filter’, drag the hierarchy to the filters pane or use the <Select dimension>’ drop down box to select the dimension and the Hierarchy.

Let us drag and drop the Category hierarchy to the filters tab. Select Equal in the Operator column and in the Filter Expression column, select ‘All products’. Similarly, drag the Product.Subcategory, Product.Product and Date.Calendar hierarchies to the filter pane and set it to the values mentioned in the screenshot.

Note: Since we need to provide a way for users to modify the filter expression such as category, sub-category or product, we will make them as parameter by marking the Parameters check box.

Once done, click the OK button to go back to Dataset Properties.

14 Parameterized Report - Create Dataset e

Now we can see the MDX code behind our drag and drop actions and filters in the Query section of the Dataset Properties window. Click Ok to exit the Data Properties window.

15 Parameterized Report - Create Dataset f

Since we have opted to parameterize the filters, parameters and datasets are automatically created in the background (the parameter datasets cannot be initially seen under datasets).

To see these hidden parameter datasets, we need to right-click Datasets folder and select Show Hidden Datasets.

16 Parameterized Report - View Hidden Datasets  17 Parameterized Report - Hidden Datasets

18 Parameterized Report - Parameters

Step 6: Now that we have the dataset, we can go ahead and add report items to the report body to display the report. In the report body, right-click and select Insert > Table.

19 Parameterized Report - Create Table

Align the table at the top of the report and assign the columns to the dataset fields as shown below.

When we select a cell in the detail row of the table, we see a small blue and white icon. When we click this icon, a drop down list of all the dataset fields appears. We can select the required dataset field to assign for the column.

20 Parameterized Report - Assign Columns to Table

Step 7: We can apply formatting for the various report items, like background colors for the headers, font type and size, and for numeric and currency fields, we can assign the respective formatting so that they appear correctly in the final report.

We also have some pre-defined currency and number formats. E.g., For a Currency field, we can go to the Format property for the cell and set it to either C0, C1 or C2. Similarly for Number fields, we can set it to N0, N1 or N2.

21 Parameterized Report - Apply Formatting

Now, the report is ready to be previewed. When we click Preview, we (hopefully 🙂 ) see a beautifully designed report.

22 Parameterized Report - Preview

But, wait a minute. The report is not yet final!

If we observe closely, we can see that the Prompts for parameters are not so friendly.
E.g., we see labels like Product.Product for the Product slection box and Date.Calendar for the date parameter. We need to change them.

Step 8: Go back to the Design mode and in the Report Data toolbar, under parameters, open each parameter and modify the Prompt: to a more user friendly prompt.

23 Parameterized Report - Preview - Fix 1

Click Preview again. Now the report looks good. But is it perfect?

24 Parameterized Report - Preview - Post Fix

Just click on the Calendar Year prompt and see how the parameter is being populated.

25 Parameterized Report - Preview - Date Param Issue1

Does not look good. Does it? Let’s fix it.

 

Step 9: Fixing the Date parameter to accept only years:

Go back to the design view. In the Report data, under parameters, copy the Query from any other existing parameter: E.g., from ProductCategory parameter

1

Step 10: Create a new dataset as below.

2

In the Dataset Properties, provide a Name for the dataset and select the ‘Use a dataset embedded in my report’ option. Select the Data Source from the drop down box and click on Query Designer.

3

Paste the copied query into the new dataset and modify the query by replacing the hierarchies/ levels with the required ones as below.

OLD Query: The below is the old query from the ParoductCategory parameter. We need to change the text highlighted in Red.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Product].[Category].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Modified Query: The below is the modified query.

WITH 
MEMBER [Measures].[ParameterCaption] AS [Date].[Calendar Year].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[ParameterValue] AS [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME 
MEMBER [Measures].[ParameterLevel] AS [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL 
SELECT {
[Measures].[ParameterCaption], 
[Measures].[ParameterValue], 
[Measures].[ParameterLevel]} ON COLUMNS , 
[Date].[Calendar Year].ALLMEMBERS ON ROWS 
FROM [Adventure Works]

Click the exclamation mark ! to test the query results. You should see similar results for ParameterValue containing Years.

4

 

Click Ok to come out of the Query Designer.

5

Click OK and the new dataset is created.

Step 11: Now that we have our dataset ready, it is time that we make use of it.
Open the existing DateCalendar parameter.

6

In the General tab, change the Name to Year and Prompt to Select Year.

7

Next, go to the Available Values tab.

8

Change the Dataset name to LoadYear from DateCalendar.

9

Next go to the Default values tab.

10

Change the option from Specify Values to No Default value as below. Click OK.

11

Step 12: Now that we have created a new dataset and mapped that to the parameter, we can delete the Original, now unused DateCalendar dataset.

12

Now when you click preview, you can see that the correct year values appear in the drop down and the report executes properly 🙂

13

14

 

Deploying an SSIS Project (using an .ispac project deployment file)

New to SSIS 2012? You may find the below info helpful in your journey through SSIS 2012 (which has seen some major changes from earlier versions).

This is the Second in the series of articles that I plan to cover over the next couple of months on how we can deploy and execute SSIS 2012 Projects. I will try my best to churn out one article every week or two, fingers-crossed 🙂

Deploying an SSIS Project (using an .ispac project deployment file):

SSIS 2012 supports two deployment models:

  1. Project Deployment Model (newly introduced in SSIS 2012)
  2. Package Deployment Model (Pre – SSIS 2012 versions). 

The Project Deployment Model helps us to deploy our projects to the Integration Services server.

Today, we will discuss on how to deploy an SSIS Project that was created using the Project Deployment Model.

Step 1. Open the .ispac project deployment file using the SQL Services Integration Services Deployment Wizard. E.g., Open the Data Migration .ispac file as shown in the below screenshot.

SSIS-ispac file

SSIS-ispac file-OpenWith

Step 2. The Integration Services Deployment Wizard opens up to the Introduction page.
Click Next in the Integration Services Deployment Wizard.

SSIS-ISDeployment Wizard-Intro

Note: Sometimes, you may get a failed to decrypt warning during the validating Project deployment file phase. This may occur for a variety of reasons such as the user account that is used to run the package may differ from the original package author, or the user account may not have the required permissions to either make connections or to access resources outside the SSIS package, etc.

SSIS-ISDeploymentWizard-warning

 

The most appropriate method depends on the environment and the reason that the package failed.

Some of the common methods to resolve this are as below:

Method 1: Use a SQL Server Agent proxy account
Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
Method 4: Use SSIS Package configuration files
Method 5: Create a package template

More details about this can be found at: http://support.microsoft.com/kb/918760

Additional Link: http://www.databasejournal.com/features/mssql/ssis-2012-implementing-package-security-using-access-control.html

For now, I will click OK and continue the deployment.

Step 3. Select the Integration Services Project that you want to deploy. Here, since we are deploying from the ispac file, select Project deployment file as the source.

SSIS-ISDeployment Wizard-Select_Source

 

Step 4. Enter the destination server details like Server name and Path as shown in the below screenshots. Click the Browse button to select the Server name and Path. Click Next to go to the next screen.

SSIS-ISDeployment Wizard-SelectDestination

SSIS-ISDeploymentWizard-SelectDestination-Server

SSIS-ISDeploymentWizard-SelectPath

SSIS-ISDeployment Wizard-SelectDestination-Server-Path

 

Step 5. Once the destination server name and path is provided, click Next.
You will be taken to the Review screen (as in the below screenshot). Click Deploy button.

SSIS-ISDeploymentWizard-Review

 

Step 6. Upon successful deployment, you will see the below screen.

SSIS-ISDeploymentWizard-Results

 

Step 7. Go to SQL Server Management Studio and Refresh the Staging1 folder.
You can see that the Project has deployed successfully and you can view all the packages of the project.

image030      image031 image032

 

Even though we have deployed the project, we are still not ready to execute it. Because the project most likely uses some Project level parameters for which correct values need to be supplied (rather than the values used in development) and hence we still need to bind it to our required environment(s).

In the next article, we will look at Creating Environments for Customized Executions.

 

 

 

Creating SSIS Catalog

New to SSIS 2012? You may find the below info helpful in your journey through SSIS 2012 (which has seen some major changes from earlier versions).

This is the first in the series of articles that I plan to cover over the next couple of months on how we can deploy SSIS 2012 Projects and also how to execute them. I will try my best to churn out one article every week or two, fingers-crossed 🙂

This article would explain in detail about Creating the SSIS Catalog. (The next article would focus on the types of deployment in SSIS 2012 and also provide detailed steps on how to deploy an SSIS project to SQL Server).

Creating the SSIS Catalog:

Note: Use Windows Authentication (and not SQL Server Authentication as we cannot Create Catalog under SQL Server Authentication)

Step 1.  Once you are logged into SQL Server Management Studio, Connect to the Database Engine as shown below.
Provide the Server name and set the Authentication to Windows Authentication

SSIS-ObjectExplorer         SSIS-ConnectToSQLServer

 

Step 2.  Once logged in, right-click Integration Services Catalogs and select Create Catalog.

SSIS-CreateCatalog

 

Step 3.  Select ‘Enable CLR Integration’ checkbox and provide a password for the Catalog.

Note:  Make sure to keep this password at a secure place because if you migrate and move the Integration services catalog to another SQL Server Instance, you can restore the key to regain access to encrypted content.

SSIS-CreateCatalog-EnableCatalog

 

Step 4.  You can now see that a new SSISDB Catalog is created under Integration Services Catalogs.

SSIS-SSISDB

 

Step 5.  Right-Click SSISDB and select Create Folder.

 SSIS-SSISDB-CreateFolder

 

Step 6.  Provide a Folder Name in the Create Folder dialog box. Optionally, enter a Folder description.

 SSIS-SSISDB-CreateFolder-Name

 

Step 7.  Once done, you can see that a new folder by the given name is created under SSISDB.

This newly created folder has 2 sub-folders by default: Projects & Environments (which I will explain later in another article).

SSIS-SSISDB-FolderName

 That’s it. Now you have created the SSIS Catalog where you can deploy your projects.

In my next article, I will provide detailed steps to deploy your SSIS project using the newly introduced Project Deployment Model (in SSIS 2012). Till then, stay tuned… 🙂