Sunday, December 21, 2014

Session 24: SSRS report execution and performance enhancements
AnandSunday, December 21, 2014 2 comments

Session 24: SSRS report execution and performance enhancements

SSRS 2008 R2 allows us to execute reports in 3 modes:

1. On Demand.
2. From Cache
3. From Snapshots

On demand:
This is normal approach that we follow, by hitting a report server URL. Each time a report is run, data is returned from the database server and rendered to the report.
This approach ensures that our report is update and fresh.
The downside of this approach is that, if n users open up this report on their browsers, queries on the report are executed n times.
Thus this approach at times might slow down the server.

Cache
One of the performance enhancements techniques is to cache a report when it is initially run.
This means that if another user requests for the report, the same report is served to the user from the cache
This avoids people querying the database server from each report rendering.
To make sure that people do not receive too much stale data, we can set a time to invalidate a cache.
This is a good performance enhancement technique for slow running reports.

Snapshots:
Report snapshots are created at a particular schedule for certain parameters.
Please note that parameters cannot be changes on snapshot reports.
SSRS 2008 R2 allows to schedule the snapshot creation times.
Users can directly render a report from a snapshot. However please note that not all reports can have snapshots, especially the ones that prompt users for credentials.

Session 23 : SSRS 2008 R2 Document maps

A document map in an SSRS report, provides pointers/links to certain report items in your report.
If you have a document map in your report, it will appear in the left most pane. Clicking on any of the links, in the document pane, jumps the users directly to the report item.
It is similar to the table of contents.
Please note that clicking on the document map links, refreshes the report.

Lets look at an example for a document map.

Step 1: Create a blank report by connecting to Adventure works database.
I have used the following query to create my dataset:

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
Order by Sales.SalesTerritory.CountryRegionCode

Your design view after creating the report should look something like this.

Please observe the Row groups.
Adding Territory as a group is necessary here, else SSRS will repeat the Territory values in the document map.
You can hide one of the territory column (the non-group one) in the report.

Next, goto the Territory column, and select the properties.
Go ahead and choose Territory in the DocumentMaps Property:

Now hit preview, and you should be ale 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.

Session 22 : SSRS Charts and Graphs
Anand 0 comments

Session 22 : SSRS Charts and Graphs

SSRS 2008R2 provides a rich set of visualization tools. Some of them are charts / graphs. These are available in 3D formats to add a rich effects to your reports.

SSRS charts and graphs helps to summarize the data in visual format. It enables to represent very large datasets as aggregated information available at a glance.
SSRS 2008 R2 also added sparklines to the visualization tools.
In addition, SSRS 2008 R2 also supports guages/Databasrs/Sparkline/Indicators.

One of the most exciting features of SSRS being, usage of Maps.
It allows an awesome representation of Geagraphic data using maps.

Lets look at some basic graphs and shapes.

Step 1:
As usual, lets create a blank report, connecting to adventure works database with dataset:

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 2:
Now lets drag and drop a chart control from the toolbox.
As soon as you do that , you get a pop-up, displaying various image shapes for the chart control.

Step 3: These are the set of shapes provided by SSRS for visualization purposes.
Let's choose the first shape.
You should be getting something like this on your scree:

Step 4: This is a blank chart,with no data in it.
You can click on the headings/text in the axis and change accordingly.
Alternatively , you can also write SSRS expressions to change text/descriptions dynamically.
Please refer my previous tutorial, on SSRS expressions.

Step 5: In the above example, i have changed the chart title to 'My First Chart'
Now drag and drop TotalDue to the summation of values, CountryRegionCode to the CategoryGroups and year to the series Groups.
Something like this...

Step 6: Hit Preview, and you be getting something like this:

Step 7: Try these steps, with a number of other shapes and graphs/Sparklines/Indicators.

Lesson 18: Managing SSRS Security

Session 21 : Managing SSRS Security

As soon as you install SSRS 2008 R2, in your server and hit http:///reports, by default the the system administrators get access to the report manager.

But if you wish to let other people see the reports that you deploy, you need to explicitly provide access to these reports.
Providing access to reports is very simple and can be done at an item level( report level) or a folder level.

Lets look at a very simple example.
I have installed a SSRS 2008 R2 report server, on my local machine and have created a folder called Sandbox, in which i have added a report called Hello World.

By Default all admins in the system can access this report.
Lets say, we add more reports to this folder and we need to grant access to these reports.
This can be done, by clicking on Folder Settings and then clicking on security

Click on New role assignment.
This is the place where you define your role assignments.

If you need to grant all users of your domain access, then you may need to add 'Domain\DomainUSERS' and assign appropriate roles.

There are mainly 5 types of roles, that you can assign to an user or a group.
These roles are:
a. Browser
b. Content Manager
c. My Reports
d. Publisher
e. Report Builder.

Descriptions for each of these roles are provided in the UI itself.

If you wish to add just a particular user of your domain for your report, type the username and select the appropriate role for the same.

Click OK, and your security is set.

Session 20 : Different ways of Deploying Reports

1. Background

This article describes how to deploy the reports in different ways using SQL Server Reporting Services: Following are the different ways in which we can deploy the report:

Report Deployment from BIDS/Report Builder/SQL Server Data Tools.
Report Deployment from Report Manager.
RS command prompt utility
Backup and Restore of Report Server DB.

2. Prerequisite

Reporting services should be configured in the instance.
SQL Server Reporting services should run.
Reports that need to deploy should run in the BIDS/ Report Builder/ SQL Data Tools without any issue.
Report Server and Report Manager URL should work.

3. Steps to deploy the report:

a) Report Deployment from BIDS/Report Builder/SQL Server Data Tools.

Below is the sample report named “EmployeeReport” with shared dataset and data source in BIDS. Report is working fine in BIDS. Below is the screenshot.

I. Right click on the project top node named “MyProjectSolution” and click on Build:

Build succeeded :

II. Right click on project top node named “MyProjectSolution” and select Property:

III. It will open the property window. Here we can set the deployment property for the report. Below are the property need to set for report deployment:

TargetDataSourceFolder : MyDataSource
TargetDatasetFolder : MyDataSets
TargetReportFolder : MyReports
TargetReportPartFolder : MyReportPartFolder
TargetServerURL : http://vishal-pc/reportserver
TargetServerVersion : SQL Server 2008 R2 or later
OverwriteDatasources : Based on your requirement
OverwriteDatasets : Based on your requirement

Below is the screenshot showing deployment configuration settings:

Click ok to save the settings.

IV. Go to the report server URL to check whether Report server URL is working fine or not: http://vishal-pc/reportserver :

Report server url is working fine and there are no reports available.

V. Now go to Reporting solution, right click on project top node and click on deploy:

So Report has been deployed successfully. See the below screenshot from BIDS:

VI. To verify whether report deployed successfully or not. Go to Report server URL and run the report.

Here we are able to see the deployed objects:

VII. Go to My Reports folder and run the report:

Report is working fine. See the below screenshot:

Note : Deployment steps for Report builder/ SQL Data Tools is same as BIDS.

b) Report Deployment from Report Manager

Report deployment from Report Manager means uploading the Reports, Data Sources and Datasets from file system to Report Server DB by using Report Manager.

I. Below are the report items that we will deploy by using Report Manager:

II. Go to Report Manager URL e.g (http://servername/reports) and check whether these report items are there or not:

There are no report items in the Report Manager.

III. Create the folder for Datasource, Dataset and Report in report manager. For creating folder click on new folder:

Provide the folder name and description and click ok.

It will create the folder for Datasource. Create the folder for Dataset and Report also.

IV. Below is the screenshot of all the folders created in Report Manager:

V. Go to DataSource folder and click on New Data Source:

It will open the page for creating data souce. Provide the Data source name, connection string and credential type and click Ok.:

Now we can see data source has been created.

VI. Now go to DataSet folder and click on Upload File:

Provide the path of Dataset and clcik Ok:

It will create the dataset. Below is the screenshot:

VII. Repeat the VI for Report folder and upload the rdl file. Below screehsot showing datasource, dataset and report both uploaded successfully.

VIII. Now go to the report folder, click on drop button on report and click on Manage to see whether report is mapped to correct dataset or not:

It will open the property for the report. Click on “Shared Dataset” and select the correct dataset for report.

Click Ok. It will map the “Reseller” dataset to the report.

IX. Go to DataSets folder and select the Dataset “Reseller” and click on Drop button and select Manage:

X. It will open the Dataset property. Click on DataSource and select the dataset for the dataset:

Click Ok. It will map the dataset to datasource.

XI. Go to the report folder and run the report.

Report is working successfully.

c) Backup and Restore of Report Server DB: It is one of the simplest way of deploying all the reports from one environment to another environment. This way is useful when we are deploying report first time in any environment in which there are no existing reports.

I. Below is source environment where all the reports are available(http://vishal-pc/reports)::

Below is the report that is available in this environment :

II. Below is the target environment where no report items are available(http://sqlcircuit/reports):

III. Take the Backup of ReportServer DB from the source system:

IV. So we have taken the backup of source Report server DB in G:\ReportDeployment folder. Now restore it to the target SQL server instance.

Note: SQL Server Reporting Services should configured properly in the target instance

V. Now go to Report Manager URL and check whether all the report items came or not. (http://sqlcircuit/reports)

VI. Run the report and see whether you are able to run the report or not:

Report is working fine.

d) Report Deployment using RS utility

RS.exe is a command prompt utility that Processes script you provide in an input file (.rss). We use this utility to automate report server deployment and administration tasks.

RSS file: The .rss file contain the code to read the report item file from the local system, upload the file to report server and set data source. We can write .rss file using Visual Basi.net.

Below is the syntax for RS.exe

rs –i c:\ReportDepolyment\RSDeploy.rss -s http://localhost/reportserver

4. Conclusion

By using the above steps, we can deploy the SSRS reports

Sunday, December 21, 2014