Report Caching and Report snapshot in SSRS

Caching and Snapshot of the report:

Caching is a copy of the last executed report. It is not a persisted copy; it has a lifetime (like caching for 30 minutes). It is stored on the ReportServerTempDB database. You can have only one "instance" per report (if you have parameters, you will have one per combination of parameter)

Snapshot is a persisted copy of the report. It is stored for good on the report database. You can have as many as you want. You can configure for example to save a snapshot of a report per day, so if you want to see how was your data 3 months ago, you just access the snapshot of that day.

Caching in detail: SSRS lets you enable caching for the report, in which case SSRS will maintain (store in ReportServerTempDB database) a copy of the processed report (along with data) in intermediate format so that if the same report request comes again, the stored copy can be rendered in the desired format and served. This improvement in subsequent report processing can be evident especially in cases where the report is quite large and accessed frequently.

Please note, different combinations of parameters force SSRS to store a new cache and require more storage space for ReportServerTempDB database. Also the reports served from cache will consistently show the same data (even if the data has changed since the last query) until the cache is refreshed. A cached report can be set to expire after a specified time interval or after a specific date and time. After expiration, a cached report is replaced with a newer version when the user selects the report again.

Caching for a report can be enabled only if the report uses the data sources with stored credentials; in other words caching cannot be enabled for a report if it prompts users for credentials or uses Windows Integrated authentication.

Snapshot in details: A report snapshot is a report that contains layout information and data that is retrieved at a specific point in time. A report snapshot is usually created and refreshed on a schedule, allowing you to time exactly when report and data processing will occur. If a report is based on queries that take a long time to run, or on queries that use data from a data source that you prefer no one access during certain hours, you should run the report as a snapshot. A report snapshot is stored in the intermediary form in the ReportServer database, where it is subsequently retrieved when a user or subscription requests the report. When a report snapshot is updated, it is overwritten with a new instance. The report server does not save previous versions of a report snapshot unless you specifically set options to add it to report history. We cannot create a snapshot for a report that prompts users for credentials or uses Windows integrated security to get data for the report.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

SSRS Subscriptions

In SSRS, we can create subscriptions to generate a report snapshot on some defined scheduled time and parameters and delivered to the intended users through the report server.

In SSRS there are basically two types of subscriptions:

·         Standard subscriptions

·         Data-driven subscription.

A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.

A data driven subscription is dynamic. In Data Driven subscription we can get subscription information at run time from database by querying an external data source that provides values used to specify a recipient, report parameters, or rendered format. Below are some advantage points over the standard subscription:

·         Distribute a report through subscription to a dynamic list of recipients: For example, if we have a list of recipients in a table of database which is not fixed (it can be varied from time to time), then we can use this list by querying it from database.

·         Provide report parameter value dynamically at run time: For example, if we have a date parameter where we need to provide a calculated value for date at run time then we can write a SQL query to get calculated date value at run time for this parameter.
·         Vary report output formats and delivery options for each report delivery.

Below are the steps to create a standard subscription on the report manager:

Step 1: To create a static subscription (non data driven subscription) we need to provide static values for recipient, report parameters, or rendered format so click on New Subscription tab on manage page:

Step 2: The New Subscription option will take you to standard subscription design page where you need to provide the email list, rendered format of file (i.e. Excel, CSV and PDF etc.), subject line and comments under the report delivery options:

 

 

Step3:  In Subscription processing options, you need to select any one option for schedule the trigger time of the subscription:

·         When the scheduled report run is complete: in this option you need to provide a schedule to run this subscription by using daily, weekly, monthly or once option available in the list:

·         On a shared schedule: In this option we need to provide a shared schedule which we have created to use in multiple subscription:

More on Shared Schedule go through this link: Shared Schedule in SSRS report manager

Step3:  In the Report Parameter Values option, you need to provide static values for parameters and after putting parameters information click on OK button and now standard subscription is ready to trigger.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

Shared Schedule in SSRS

Shared Schedule in SSRS is a mechanism to create a schedule for a particular date or time so that it can be used among multiple subscriptions for their execution in SSRS report manager.

So advantages for creating a shared schedule are:

Easy to use: we can share it in multiple subscriptions directly from On a shared schedule option without creating a manually for each one.

Easy to update: for example we have many subscriptions which are scheduled for 7 AM daily and based on the light saving we need to change their trigger time from 7 AM to 8 AM so for this you need to update each subscription manually one by one. So for this if we have used a shared schedule in all subscriptions then we need to just update that shared schedule only and all subscriptions will be executed with updated time which are using that shared schedule.

To create a shared schedule, first you must have permission on report manager and on the report manager go to the top ribbon where you will find Site Settings tab as shown in below given screen shot:

 

After clicking on Site settings option it will open a page where we have a Schedules option which is used to create a shared schedule.

Now Click on New Schedule option to create a new schedule where we need to provide schedule name and schedule details as I have created a Weekly Refresh for Monday only as:

This shared schedule “Weekly Refresh” we can use in any subscription where we need to trigger our subscription for each Monday only.

If you have any thoughts or suggestion, feel free to post in the below comment section.
Thanks!

Data Driven Subscriptions in SSRS

Below are the following steps to create a Data driven subscription in SSRS through SSRS report manager:

Step 1:  Go to the Report Manager and do right click on report to go on manage option of the report and click on Subscriptions option under the properties:

Step 2: Click on New Data driven Subscription tab for creating a data driven subscription or if we want a standard subscription than click on New Subscription:

Click here for Differences between Data Driven and Standard Subscriptions

Step 3: In Data driven subscription specify the description of the subscription, email as recipient format if you want to deliver rendered file through the email or if you want to share rendered file through a shared location than you can use “Windows File Share” option:

There are two options available for implementing a Data Source (database connection):

·         Shared Data Source

·         Specify for this Subscription only

 Shared Data Source is a Data source which we have created on report manager to connect to a particular database server so for creating a Shared data source click on below link:

 Create a Shared Data Source on Report Manager

 Here we have existing Data sources available for reporting so we are going to use Tran1 data source under the Datasets folder:

Step 4: If we select second option “Specify for this subscription only” than we need to provide connection string and credentials as:

Step 5: After the selecting data source we need to go on next page where we need to provide a query and click on Next, suppose we want to generate email list from database table then we can use that SQL query to fetch email list here as I have added a SQL script to get year value for my parameter’s value:

On the next page, If we choose the delivery method to Windows File Share, the fields on below screenshot will change to let you specify the file name, share location, credential to use to copy the file at that location, overwrite options, etc.

Step 6: Provide the information about recipient email and rendered format of the report and Subject (select false option for include Link option) and click on Next:

Step 7: On this page provide parameter’s information and click on next as I have one parameter here named “Year” so I have use second option to populate value from SQL query and you can provide a static value also for this parameter:

Step 8: On the next page we need to schedule this subscription by following options:

-         On a schedule created for this subscription: In this option we can schedule our subscription for Hourly, Weekly, Daily or Monthly as:

-         On A Shared Schedule: in this option we need to provide a shared schedule which we can create to use in multiple subscriptions as in below screen shot I have selected Weekly Refresh for Monday morning shared schedule:

To deep dive about Shared Schedule, please go through this link: Shared Schedule in SSRS report manager

Step 9: Finally click on next and your subscription is ready to trigger based on scheduled time.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

SSRS Shared Data Soure

A shared data source in SSRS defines a connection to an external data source. With a shared data source, you can create and maintain the settings for the data source connection separately from the reports, models, and data-driven subscriptions that use the data source. So it can be used by any report on the report manager for rendering data and subscriptions.

In BIDS, under the project we can define the Shared Data source to use it in multiple reports as:

Navigate down the project folder in the solution explorer, we have “Shared Data Sources” folder, do right click to add new data source and on the editor, provide details as name and connection string as:

Open Report Manager, and navigate to the folder in which you want to create a data source. In the toolbar, Click on “New Data Source” option and you must have content manager permissions to create a shared Data source on report manager and on data source editor page, we have to provide Name, description, Connection string (Syntax of the connection string is specific to data provider or we can say a connection string for a Database Server) and at the last you need to provide authentication ways to access database server which you have mentioned in the connection string as:

 

For Data Source Types, which are supported by the Report manager, different types of data source are available, you can choose from the given list as: if our data source is SQL Server then select Microsoft SQL Server or OLE DB. If our data source is SQL Server Analysis Service then select Microsoft SQL Server Analysis Services.

For Connection Properties option, we have to specify the Data Source (Provider), Server Name (or Instance Name), Login credentials and Database Name.

For Connect Using option: we have to mention the Credentials which are required for connecting the Reports with mentioned database server in Connection string
·         Credentials supplied by the user running the report: When the report is connecting to data source, it will prompt the given text (Type or enter a user name and password to access the data source). If you check mark, Use as Windows credentials when connecting to the data source option then reporting server will pass the current user windows credentials to external server instead of prompting otherwise, it will prompt for credentials.
·         Credentials stored securely in the report server: If you select this option, Please specify the user name and password to log into data source and these credentials will be stored inside the report. This option is very important for Report Subscriptions.
·         Windows Integrated Security: It will pass the current user windows credentials to external server for connect to the data source. For now, We are using this option
·         Credentials are not required: Report will not use any credentials to connect with data source. For instance, If we are accessing file in local file system then we can select this option. However, try to avoid this option.

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!



 

Count all Null values from all columns for each row of the table

Today one of my friend asked me to count null values from all columns of a table for each row so first question which I asked to him: are we confirmed about the number of columns we have in the table? He said what script we need to write if we are not confirmed about the numbers of columns of the table or if we are confirmed about the number of columns so for this I have created a table where I have some user details as:

UserId	UserName	CreatedDate	CreatedBy	ModifiedDate	ModifiedBy
1249	Jhon.K	1/13/2015	Sunil	NULL	NULL
1302	Jenifer.Goldberg	11/11/2014		NULL	NULL
2260	Raju.Singh	1/13/2015	Avanish	NULL	NULL
8746	Avanish.Tomar	NULL	Sunil	NULL	NULL
15305	Sonu.Sharma	11/11/2014	Sammy	NULL	NULL
18222	Prateek.Borsi	NULL	Jonny	NULL	NULL
19228	Kavita.Sharma	11/17/2014	NULL	NULL	NULL
19583	Erica.James	NULL	Avanish	NULL	NULL
22698	Alina.Pointing	1/13/2015	Avanish	NULL	NULL
28463	James.Jhon	1/13/2015	Sunil	NULL	NULL

For first scenario: if we know columns of the table than we can use below script to get total count of nulls for each record as:

Select UserID,UserName,CreatedDate,CreatedBy,ModifiedDate, ModifiedBy,

 Case  When UserId is Null Then 1 else 0 end +

 Case  When UserName is Null Then 1 else 0 end +

 Case  When CreatedBy is Null Then 1 else 0 end +

 Case  When CreatedDate is Null Then 1 else 0 end +

 Case  When ModifiedBy is Null Then 1 else 0 end +

 Case  When ModifiedDate is Null Then 1 else 0 end

 as TotalNulls

From Users

For second scenario where we are not confirmed about the numbers of columns we need to fetch columns name from INFORMATION_SCHEMA.COLUMNS table and than we can design a dynamic query to create a case statement for each column to identify null value and at the last we can concatenate this case statement with select statement to get result for each row, as in below SQL script I have fetched all columns from INFORMATION_SCHEMA.COLUMNS table with row number for looping on each column to generate case statement and in the last I have concatenate case statement which is stored in @CaseVal variable with select statement to get total count of all nulls for each row so we need to provide only table name in the @TableName variable and this code will automatically identify the columns for counting nulls for each row:
DECLARE @TableName Nvarchar(100)

SET @TableName='Users'  --->> Enter table Name

IF OBJECT_ID('tempdb.[dbo].[#TT]') IS NOT NULL DROP TABLE [dbo].[#TT]

IF OBJECT_ID('tempdb.[dbo].[#DB]') IS NOT NULL DROP TABLE [dbo].[#DB]

SELECT TABLE_NAME, COLUMN_NAME

, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY COLUMN_NAME) as RowNo into #TT

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

DECLARE @CaseVal varchar(Max)

SET @CaseVal=' '

Declare @ColumnName varchar(max)

Declare @i int

Set @i=1

While(@i<=(Select Max(RowNo ) From #TT))

Begin

       set @ColumnName=(Select COLUMN_NAME from #TT where RowNo= @i)

       If(@i<(Select Max(RowNo ) From #TT))

       Begin

       Set @CaseVal=@CaseVal+

       'Case  When '+@ColumnName+' is Null Then 1 else 0 end +'     

       END

       If(@i=(Select Max(RowNo ) From #TT))

       BEGIN

       Set @CaseVal=@CaseVal+

       'Case  When '+@ColumnName+' is Null Then 1 else 0 end '      

       END

       Set @i=@i+1

              End

Print 'Select *, '+@CaseVal+ ' as TotalNulls from '+@TableName

               Exec ('Select *, '+@CaseVal+ ' as TotalNulls from '+@TableName)

Bothe scripts result will be as:

UserId	UserName	CreatedDate	CreatedBy	ModifiedDate	ModifiedBy	TotalNulls
1249	Jhon.K	1/13/2015	Sunil	NULL	NULL	2
1302	Jenifer.Goldberg	11/11/2014	NULL	NULL	NULL	3
2260	Raju.Singh	1/13/2015	Avanish	NULL	NULL	2
8746	Avanish.Tomar	NULL	Sunil	NULL	NULL	3
15305	Sonu.Sharma	11/11/2014	Sammy	NULL	NULL	2
18222	Prateek.Borsi	NULL	Jonny	NULL	NULL	3
19228	Kavita.Sharma	11/17/2014	NULL	NULL	NULL	3
19583	Erica.James	NULL	Avanish	NULL	NULL	3
22698	Alina.Pointing	1/13/2015	Avanish	NULL	NULL	2
28463	James.Jhon	1/13/2015	Sunil	1/18/2015	NULL	1

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!