SSRS – Page 3 – Reporting Services

August 7, 2019August 7, 2019

SSRS report caching

There are several techniques to optimize report processing. One of the solutions is to enable report caching. When you enable this feature in SSRS report manager (as described below) the data are cached to the Reporting Services Temporary database (see queries below) Reporting services database. There are few possibilities of how to set the caching strategy, from planned approach to the solution with expiration cache. But, be aware that in case the user changes report parameters, the stored cache will not be used and there will be new data cache created. Keep it in mind when you set the caching strategy.

Go to the report manager.

Go to report Properties click on right arrow right to the report name and select properties form menu.

Go to Processing Options section. And set Cache a temporary copy of the report. Expire copy of report after a number of minutes: and put time you would like to hold the cache in database.

When you choose scheduled strategy. Expire copy of report on the following schedule or Render this report from a report snapshot, schedule has to be set. You have to fill the data on picture bellow. Be aware the SQL agent has to be running to use scheduling feature.

After setting property for Report Execution Cache new record is added to execution cache. You can see in database that there is time when cache will expire. After this time the record disappear from the database. (not immediately but after few seconds minutes asynchronous mechanism delete it ).

SELECT * 
FROM ReportServer.dbo.[Catalog] c
JOIN [ReportServerTempDB].dbo.ExecutionCache ec ON ec.ReportID=c.ItemID

Change report parameter.

You can see that you record what inserted into Report Execution cache.

August 7, 2019August 7, 2019

SSRS linked report

Linked reports are very nice feature of Reporting Services, I found it very uses full since it gives you possibility to make many reports from your base report by changing parameters default values. It like Windows shortcut when you create child report from your parent report. This child, linked report does not have its own definition. Definition is taken from parent report, but you can set different parameters and it gives you lots of possibilities for implementation scenarios.

You can make for example different naming convention and structure in your reporting services repository when you can prepare different organisation structure for reports based on its usage or you can develop reports with predefined parameters based on user needs. In organisation structure each user could have different requirements for final view of report.

Linked Reports are created in SSRS Report Manager.

Goal

Create Linked report to Sales Report with default Region code, Australia.

Go to Reporting Services Report Manager to path where is situated report you use like base report. Click on arrow next to the right to the Report name. You can use one of report we created in previous post SSRS Cascading Parameters

Select Create Linked Report from appeared menu.

Name Linked Report in next window. You can fill Description too.

By confirming previous step, you can see Linked Report in Reporting Services Manager added. See picture bellow.

Go to linked report properties, click on right arrow on Linked Report name, select manage and go to parameters.

Linked report - parameters — Picture 5 – Linked report – parameters

Set default value for the Linked Report parameter RegionCode. You can see that if we would like to set default parameter value for GetProvince parameter, we are not allowed since it has default value defined by Query. IN case we would like to use linked reports, we should think about parameters in advance.

Linked report - parameter default — Picture 6 – Linked report – parameter default

See Linked Report on picture bellow.

Be careful when changing definition of base report. Some changes like changing parameters can invalidate Linked Report and you have to create it again.

August 3, 2019August 7, 2019

Reporting services database

Reporting Service databases are used to store objects definitions and their metadata. There are placed two databases – Reporting Services Database and Reporting Services Temporary database. Each instance of reporting services in native mode should have defined these two databases in configuration manager. See post Reporting Services, Basic configuration tips .

Reporting Services database

Here is stored information regarding reporting services repository like reports, schedules, data sources users etc. Bellow few tables you may interested in.

Reporting services temporary database

Here are stored data used for caching reports

The reporting services database structure is more than clear, based on name of tables. Even so I mention two tables you may be interested in. But as Microsoft states you shouldn’t use your custom queries directly against reporting services database. You should use API to get info you need, except of execution log you can access directly from database. See Microsoft web site for more info https://bit.ly/2Jxn9J6.

Catalog table – report server items – reports and theirs pats for example
- here you can find Report Definitions too. But these data are informative only. Do not expect that you if you change report definition here it changes on Report Server too. For such a purpose you should use Reporting Web Services endpoints or Reporting Services Extensions or hack Chunk and Segment tables where are stored the data reporting services work with in binary form
ExecutionLogStorage – information regarding repots rendering, processing and data retrieving.
- Here are few views pre-pared from Microsoft team to audit reporting services data
- Dbo.ExecutionLog, dbo.ExecutionLog2, dbo.ExecutionLog3

In next few posts I would like to show you work how you can work with Reporting Service tables since it is good tool how to monitor performance of reports and auditing users access to report serves. Stay in touch.