Drilldown Matrix Reports

This is how it looked:

Lets add some child groupings to this report, and explore some visibility toggling features.

Step 1: Right click on the Territory field and add a child group:

Step 2: Choose 'CountryRegionCode' to group by:

Step 3: Your design view would look something like this:

Step 4: Hit preview.

Congratulations - your matrix report is ready.

Lets proceed to see some visibility toggling features

Step 5: Click on the row groups - 'CountryRegionRegion' and select the group properties:

Step 6: In the visibility pane, check 'Hide' for 'When the report is initially run'
Also, check the 'display can be toggled by this report item' for Territory

Step 7: Select OK and hit preview.
You should be able to toggle the report.

SSRS Expressions

Lets look at a very simple example.

Step 1: Create a report server project and connect to Adventure Works Database.

Step 2: Add the SalesOrderDetail table with a couple of columns to the Report.

Step 3: In the toolbox, add the table control to the report.

Step 4: Add 3 columns to the report - SalesOrderID, SalesOrderDetailID, OrderQty, LineTotal.

Step 5: Now lets write some expressions. We will display anything in LineTotal cell as Green, if the number is above 2000, else we will display in Red.
Right click on the cell , and select text-box properties.

Step 6: Next to the colr dropdown, there is an Fx button.
Write the below expression.

Step 7 : Click OK and then on the Preview button.
You should be getting the final output as displayed below.

Step 8: Now lets write some expressions to manipulate some data.
Right click on the OrderQuantity Cell and click on expressions.
Enter the below expression and click OK.

Step 9: Click on the preview tab, you should get the below output.

I have demonstrated a very simple use of expressions. However , you can write very complex expressions depending on your business needs. For instance, you can write expressions, to calculate the standard deviation of so and so , and manipulate results accordingly.

Creating an SSRS parameterized report

Creating an SSRS parameterized report

Step 1: Modify the query in the dataset, to make PostalCode as a parameter

Step 2: Once you click OK, go to the parameters folder in "Report Data" - you will find that @postalcode automatically appears in the "Parameters" Fields.

Step 3: Lets explore more about the parameter. Double click on @postalcode.

This is place, where you configure, how your parameter should behave - should it allow blank values or null values etc.

You can configure the visibility, data-types, default values etc.

Lets leave it as it is for now.

Step 4: Click preview, and enter in a postal code (91370 for example)

Note: Observe that the parameter appears as a text box here.

If you want pre-configured values (in a dropdown), double click on the parameter in "ReportData" --> Parameters (here, @postalcode) and click "Available Values"/"Default value" and click "Get values from a query". You can choose your dataset and corresponding column. Create multiple datasets to suffice your parameter value requirements.

Creating your first SSRS report.

Lets follow a step by step process to create your first Business Intelligence Report.

Step 1: Open Business Intelligence Development Studio (BIDS) (Start --> Microsoft SQL server 2008 --> SQL server Business Intelligence studio

Step 2: Click File --> New project --> Report Server Project Wizard. Give any name and location.

Step 3: Once you click OK, you will be presented with the following screen.

Step 4 : Click Next. Now configure your connection string. I have chosen the type as 'Microsoft SQL server'

Step 5 : In this step, you need to enter the query for the data, that you need the SSRS report to be populated with. You can also use the query builder.