Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

Topic 1: Creating your first SSRS 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
...


Step 3: Once you click OK, you will be presented with the following screen
...
Now configure your connection string
...
You
can also use the query builder
...


Step 8: If everything looks good, click on Next

Step 9 : Click Next

Step 10: Add the fields, as shown in the below screenshot
...


Step 13 : Choose a deployment location
...


Step 15 : You can preview the report in Business Intelligence Development Studio
...


Lesson 2: Making your first SSRS report (non-wizard) method
This time, we will not be using the wizard to create our report, but we will make use of the
toolbox, and report designer to create our report
...
This allows more flexibility to your report development
...

Step 2: configure the data sources and datasets
...

Set the connection string to point to your new adventure works database
...

This is basically the place, where you will configure your query that returns some data
Select the data source, that you just created and type in a query
...


After everything is done, click ok
Step 5: Now from the toolbox, drag and drop a table control
...


Lesson 3: Creating an SSRS parameterized report
This lesson, should give you an idea, as to how to control the data in the reports using
parameters
...

We are going to expose the postal code as a parameter
...

Step 1: Modify the query in the daaset, to make Postal Code 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
...

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


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

Note: Observe that the parameter appears as a text box here
...
You can choose your dataset and corresponding column
...


Lesson 4: SSRS Expressions
Expressions can be used for a number of things
...

Hence, you can write expressions to manipulate the data, or change the properties of cells
...

Basically you would want to write expressions, if you need to manipulate properties/data at
runtime

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


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


Step 5: Now let’s write some expressions
...

Right click on the cell, and select text-box properties
...

Write the below expression
...

You should be getting the final output as displayed below
...

Right click on the Order Quantity Cell and click on expressions
...


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

To put it simply, imagine you have a report which gives you sales amount for each of the
continent
...

Next, you might want to drill down further to see the sales amount for each state in a country
...
and so on
...
Fact Internet Sales
2
...
DimSalesTerritory
4
...
DimCustomer
...
CalendarYear AS [Year]
,D
...
EnglishMonthName AS [Month]
,D
...
EnglishProductName AS [ProductName]
,C
...
SalesTerritoryRegion AS [SalesRegion]
,ST
...
SalesOrderNumber AS [OrderNumber]
,A
...
ProductKey = A
...
CustomerKey = A
...
DateKey = A
...
SalesTerritoryKey = A
...

Let’s use this data in our report and group the data
...

Step 1: Make a report, with a table control by dragging and dropping Customer Name, Order
Number and Sales Amount
...


Step 2:
Now let’s Add a product name, and put the customer Name, order number and Sales Amount
under it
...
Your report should look something like this
...

Year--Quarter--Month--Date--Sales Region--Sales Country--Product Name-Details
your design view should look something like this:

And your output should look something like this:

Lesson 6: SSRS Matrix Reports
Tablix is a new feature introduced in SSRS 2008
...

A tablix report, displays the report data in rows and columns, and allows us to organize the data
in aggregated groups
...

Tablix = Table + Matrix
...


Step 1
...
Add a connection to the Adventure works database
Step 3
...
SalesTerritory
...
SalesTerritory
...
SalesTerritory
...
SalesOrderHeader
...
SalesOrderHeader
...
SalesOrderHeader
...
SalesTerritory
INNER JOIN Sales
...
SalesTerritory
...
SalesOrderHeader
...


Step 5: Drag and drop the territory to the rows and Year to the column:

Step 6: ON clicking Preview, you should be able to get something like this:

Step 7: However the report, does not have any aggregated data
...


Step 8: On clicking preview, you should be able to see the aggregated report as below:

Lesson 7: Drilldown Matrix Reports
In the last tutorial, we saw how to create a matrix report
...

For instance we could easily convert a matrix report to tabular
...

Consider the same matrix report, which we created in the last tutorial
...

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

You should be able to toggle the report
...

The main report serves as a container for multiple sub-reports
...

Many times this is useful in matrix reports too
...

As soon as someone selects a parameter, relevant data is displayed + the sub report is also
filtered and displayed in the main report itself
...
[Sales]
...

Step 3: Your Main report would look something like this
...

Drag and drop a sub-report control from the toolbox
...


Step 6: Lets us the previous tablix report that we created as our sub-report
...

You should be able to see the main and the sub-report
...

If you have a document map in your report, it will appear in the left most pane
...

It is similar to the table of contents
...


Step 1: Create a blank report by connecting to Adventure works database
...
SalesTerritory
...
SalesTerritory
...
SalesTerritory
...
SalesOrderHeader
...
SalesOrderHeader
...
SalesOrderHeader
...
SalesTerritory
INNER JOIN Sales
...
SalesTerritory
...
SalesOrderHeader
...
SalesTerritory
...


Please observe the Row groups
...

You can hide one of the territory columns (the non-group one) in the report
...

Go ahead and choose Territory in the Document Maps Property:

Now hit preview, and you should be able to see the follow SSRS 2008 R2 report with a
document map:

Select any one Territory in the document map and the report will jump to the appropriate page

---