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

Report Sales
Picture 1 – Report Sales

Select Create Linked Report from appeared menu.

Create Linked Report
Picture 2 – Create Linked Report

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

 Linked Report
Picture 3 – Linked Report

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

Linked report
Picture 4 – linked report

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.

Sales Report linked report
Picture 7 – Sales Report linked report

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

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.

SSRS advanced configuration

In this post we take a quick overview on Reporting Services configuration.

There are few configuration sections of Reporting Services based on what part of functionality you would like to control. Here are briefly described fundamental configuration settings with short description.

    • Reporting Services Configuration manager
      • Configuration tool for basic settings of Report Server (Report Service URL, Report Manger URL, Reporting Services state, etc…)
    • Connect to Reporting Services via Management Studio
      • Setting of Execution log expiration, Reporting Services Report Manager text etc.
    • Reporting Services Installation directory
        • Report manager directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager
          • rsmgrpolicy.config
            • Report Manager policy configuration file.
        • Report Server installation directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
          • rsreportserver.config – Stores configuration settings for feature areas of the Report Server service
            • orting Services URL, Report Manager URL, Extension Configuration (e.a. csv extension delimiter), new Reporting Services Extensions adding, etc.
              <Extension Name="Report Server Email" Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider"> <MaxRetries>3</MaxRetries> <SecondsBeforeRetry>900</SecondsBeforeRetry> <Configuration> 
              <RSEmailDPConfiguration> <SMTPServer>smtpserver.cz</SMTPServer> <SMTPServerPort> </SMTPServerPort> <SMTPAccountName> </SMTPAccountName> <SMTPConnectionTimeout> </SMTPConnectionTimeout> <SMTPServerPickupDirectory> </SMTPServerPickupDirectory> <SMTPUseSSL> </SMTPUseSSL> <SendUsing>2</SendUsing> <SMTPAuthenticate> </SMTPAuthenticate> <From>DWHReports@email.cz</From> <EmbeddedRenderFormats> <RenderingExtension>MHTML</RenderingExtension> </EmbeddedRenderFormats> <PrivilegedUserRenderFormats> </PrivilegedUserRenderFormats> <ExcludedRenderFormats> <RenderingExtension>HTMLOWC</RenderingExtension> <RenderingExtension>NULL</RenderingExtension> <RenderingExtension>RGDI</RenderingExtension> </ExcludedRenderFormats> <SendEmailToUserAlias>True</SendEmailToUserAlias> <DefaultHostName> </DefaultHostName> <PermittedHosts> </PermittedHosts> </RSEmailDPConfiguration> </Configuration> </Extension>
        • web.config – ASP.NET configuration
          • here you can for example change max limit for file upload for example
          <httpRuntime  maxRequestLength="100000" executionTimeout="9000" requestValidationMode="2.0" />
        • rssrvpolicy.config
          • Stores the code access security policies for the server extensions.
            • To set security to extension library
        <CodeGroup class="UnionCodeGroup" version="1" Name="SSRSCustomReportExtension" Description="" PermissionSetName="FullTrust"><IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQL2008\Reporting Services\ReportServer\bin\SSRSCustomReportExtension.dll"/>

        You can find more info on MSDN web site https://bit.ly/2ZkDZ2l.