Friday, December 19, 2014

Session 19 :Caching SSRS Reports
AnandFriday, December 19, 2014 0 comments

Session 19 :Caching SSRS Reports

Caching SSRS Reports

In this article, I will show you how to use caching for SSRS reports. But before that go through below lines which will help to understand the concept of caching in sql server reporting services.
A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period,
A report server can cache a copy of a processed report and return that copy when a user opens the report. To a user, the only evidence available to indicate the report is a cached copy is the date and time that the report ran. If the date or time is not current and the report is not a snapshot, the report was retrieved from cache.
Caching can shorten the time required to retrieve a report if the report is large or accessed frequently. If the server is rebooted, all cached instances are reinstated when the Report Server Web service comes back online.
Caching is a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed.
To enable caching for a report, go through below steps :
1. First of all open Internet Explorer and go to Report Manager URL which is something like below:

Your internet explorer tab looks like below :

2. Click on your SSRS project. In my case it is Start SSRS. So now it will show you list of reports which are deployed on your report server.

3. Now click on down arrow on the report which you want to subscribe and select Manage as shown in below screen shot.

4. Then select Processing Options from left pane and you will see screen shown in below screen shot. In that screen, you have three different choice.
Either you can set time in minutes

or you can defined a schedule as shown below.

5. Then click on Apply button. So we have done with Caching SSRS Reports.
Main purpose of Caching SSRS Reports is to improve the performance. To see the effect of Caching, execute following query.

SELECT  ITEMPATH,USERNAME,PARAMETERS,TIMESTART,TIMEEND,TIMEDATARETRIEVAL,

        TIMEPROCESSING,TIMERENDERING,SOURCE,BYTECOUNT,[ROWCOUNT]

FROM    EXECUTIONLOG3

Here you can see that it will not take even 1 second to retrieve data. Similarly you can see time difference in TIMESTART & TIMEEND.
6. You can also create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report. A refresh plan includes a schedule and the option to specify or override values for parameters.
To create a Cache Refresh Plan go to Cache Refresh Options and click on New Cache Refresh Plan.

7. In that screen give details as shown in below screen. Then click on OK button.

you can also check the history of Cache Refresh Plan.

Congratulations! We successfully completed use of Caching SSRS Reports

Session 18 : SSRS Drill Through Report
Anand 1 comments

Session 18 : SSRS Drill Through Report

Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

This post is about the scenario when click on any values of Parent Report you want to Go to child Report. You can also pass clicked value of parent Report to filter child Report.

let us take example of Adventure work Database (AdventureworkDW2008)

we will take two table DimProduct category and DimProductSubCategory

First we will create two Report one with table DimProduct category and another Report with DimProductSubCategory

Parent Report
Create Dataset with SQL

SQL -Select * from DimProduct category

Screenshot

Child Report

Create Dataset with SQL

select * from DimProductsubCategory

screenshot

above we created just two simple report now in click of Parent Report-Product Category Key we want to navigate to child Report i.e Simple Drill through Report or Hyperlink to a Report.

for this go to Parent Report (Product Category Report)

Select Product CategoryKey column
Textbox Properties
Action
GO to Report
Specify a Report-Select your child Report
Child Report
ok

Screenshot

Preview the Parent Report

click on any Product Category field it will open the Child Report

Now we want to Pass value from parent to Child report.

so this is the final Part where clicking on the ProductiCategoryd will filter the child Report i.e will show data of only clicked Product category Id in the child Report .

for example clicking on Product categoryid -1 on Parent Report will display only product categoryid -1 data in child Report

For this what we will do we will pass the Product categoryid from Parent Report to child Report to filter the child Report

In order to achieve this

Add a Parameter to child Report

just change the child report dataset query to

select * from DimProductsubCategory where ProductCategoryKey =@ProductCategoryKey

click ok

This will automatically add a Parameter to child report

Now go to Parent Report
Select Product CategoryKey column
Textbox Properties
Action
use parameter to run the Report
In Name write the name of child report parameter( the name should be same as Child Report Parameter
in value dropdown add Productcategory key

preview the Parent Report

click on any Product category key in my case i am clicking on category 4 in Parent Report it will open the child report with only data having Product category key 4 in child report..

if you don't want to show the parameter in child Report.
Make it hidden by Right click Product category Parameter parameter properties-visibility-hidden.

Screenshot of child Report with category 4 Data.

Session 17: SSRS Subreports
Anand 1 comments

Session 17: SSRS Subreports

SSRS Subreports are extremely helpful, when we need to embed multiple reports in a single report.
The main report serves as a container for multiple sub-reports.
SSRS gives us complete control over what parameters to be passed to the sub report.
Many times this is useful in matrix reports too.

Lets look at an example of a sub-report.

We are going to create a main report with 1 parameter.
As soon as someone selects a parameter, relevant data is displayed + the sub report is also filtered and displayed in the main report itself.

Step 1: Create a main report (Steps to create a basic report can be found in the previous tutorials)
I have the following dataset:

SELECT [TerritoryID]
,[Name]
,[CountryRegionCode]
,[Group]
,[SalesYTD]
,[SalesLastYear]
,[CostYTD]
,[CostLastYear]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks].[Sales].[SalesTerritory]

Where CountryRegionCode = @CRC

Step 2: Create a parameter called CRC.

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

Step 4: Now lets add a sub-report and pass in the same parameter to it.
Drag and drop a sub-report control from the toolbox.

Step 5: Now click on the sub-report properties.

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

Step 7:
Choose the parameters tab, and configure the parameters

Step 8: click OK and hit preview.
You should be able to see the main and the sub-report.

Session 16: Drilldown Matrix Reports
Anand 0 comments

Session 16: Drilldown Matrix Reports

In the last tutorial, we saw how to create a matrix report.
Matrix reports are very useful, and it allows total flexibility too.
For instance we could easily convert a matrix report to tabular.

In this tutorial, lets see how to add the drill down feature to an existing matrix report.
Lets take the same matrix report, which we created in the last tutorial.

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.

Session 15 : SSRS Matrix Reports
Anand 0 comments

   Session 15 : SSRS Matrix Reports

Tablix is a new feature introduced in SSRS 2008.
Tablix combines the features of a tabular report and cross tab features.
A tablix report, display the report data in rows and columns, and allows us to organize the data in aggregated groups.
It also allows us to add drill down features to get into details of a report.
Tablix = Table + Matrix.
So essentially it allows to add pivot like features to our SSRS reports.

Lets take an example.

Step 1. Create a blank report
Step 2. Add a connection to the Adventure works database
Step 3. Create a dataset with the following query:

SELECT  Sales.SalesTerritory.Name as Region,
        Sales.SalesTerritory.CountryRegionCode,
        Sales.SalesTerritory.[Group] as Territory,
        Year(Sales.SalesOrderHeader.DueDate) as Year,
        Month(Sales.SalesOrderHeader.DueDate) as Month,
        Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID

Step 4: Drag and drop the Matrix control from the ToolBox.

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.
So lets drag and drop the TotalDue field to the 'Data' area in the report.

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

Go ahead and add some currency formatting to the data.

Lets look at groupings and drilldowns in the next tutorial.

Lesson 14: SSRS Subscriptions - Email Based
Anand 1 comments

Lesson 14: SSRS Subscriptions - Email Based

Subscriptions are delivery mechanisms for delivering a report to an user.
Subscriptions allow us to schedule a report, to be run and delivered in many formats (Excel/PDF etc) as an email or to be dropped in a file share at any pre-defined time.

There are mainly 2 types of subscriptions:
1.Standard
2.Data-Driven Subscription.

It also has 2 delivery mechanisms:
1.Email based
2.File Share based.

Lets discuss the email based subscription.
This demo is based on SSRS 2008 R2
Step 1:After I navigate to my ReportServer page, I should be seeing something like this:

Step 2: Select the report, and click subscribe.

Step 3: if the credentials are not stored, you will get the following error message.

Navigate again to the report and click manage:

Goto the data sources tab, and enter the credentials:

Click on apply and then click on subscriptions tab.
You should be seeing a screen something like this:

Click on New subscription.

you should be presented with a screen like this:

Select delivered by: Email
Type in To, CC, BCC
Select a report rendering format.
Select a schedule.
Click OK

The report should be available in your inbox, at your specified time.

Session 13: Using SSRS Web Services
Anand 0 comments

Session 13: Using SSRS Web Services

SSRS exposes all functionality of reporting services through Web service.
Its bascially a XML web service and has a SOAP API.
It provides 2 endpoints:
1. Report execution
2. Report Management

Mainly there are 2 ways to use the SSRS web services:
1. Using Microsoft.NET Framework
2. Using RS.exe

Lets look at an example to use SSRS web services using Microsoft C#.

Step 1:
Create a Visual studio 2010 console application (you can use any Visual Studio version)
I am calling the application MyFirstSSRSWebService.

Step 2:
Next click add service reference:

Step 3:
Click advanced--> add web reference -- url - http://localhost/reportserver/reportservice2005.asmx
(replace localhost with your report server name).
Then click add reference

Step 4:
You should be seeing the SSRS webservice that you just added, in the solution explorer:

Step 5 :
I have created a report called MyFirstReport in my local, and given it a small description.

Step 6:
Now lets try to programmatically display this report name and the description

Copy paste the following c# code:

using System;
using MyFirstSSRSWebService.myPC;

namespace MyFirstSSRSWebService
{
class Program
{
static void Main(string[] args)
{
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://localhost/reportserver_SSRSexpress/reportservice2005.asmx";

Property name = new Property();
name.Name = "Name";

Property description = new Property();
description.Name = "Description";

Property[] properties = new Property[2];
properties[0] = name;
properties[1] = description;

try
{
Property[] returnProperties = rs.GetProperties(
"/HelloWorld/MyFirstReport", properties);

foreach (Property p in returnProperties)
{
Console.WriteLine(p.Name + ": " + p.Value);
}
Console.ReadKey();
}

catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadKey();
}
}
}
}

Step 7: Run the code, you should be seeing the following output

Likewise, I would encourage you to explore other methods of this web service as well.
One common activity people do, is to programmatically render a report in excel/pdf etc

Friday, December 19, 2014