9/30/2015

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!

9/29/2015

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!

9/28/2015

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:


 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!

9/24/2015

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!