SSRS Linked Reports

Is it worth the effort?

  • Advantages
    • Can encapsulate a set of defaults. Pre-configurations of a very general report, for example.
  • Disadvantages
    • Links between reports take users to the master source folder.
    • (!) Dynamic parameter defaults don’t survive linking.  Instead the value of those defaults at the time the linked report is created will be saved as literal values.

It is useful to keep “master” reports (.rdl) files in a restricted area and present reports as linked reports for users to browse.  The linked report mechanism allows distinct parameter defaults to be set.

There are some challenges:

Permissions

Permissions are those of the folder that is linked from.

If the master report contains a subreport, the subreport will be referenced from its original location.  Permissions are not transferred by the linking mechanism to the subreport.  Hence it is necessary to give the user View report permissions (only?) on the subreport.  Do this with a new ‘View report’ user role.

Open Report Action

Linked Reports are also run from their original location.   (Proof:  create a set of reports with Open Report actions established between them.  Create a link from another folder to one only of these reports.  Open the linked report and click the Open Report action object:  the click-through will work, and the new report is being served from the master location.

SSRS Notes

Display All Values on a Graph’s Axis

Set Interval in Axis Options section to 1. Auto is normally too sparse for my liking on date axes.  Setting an Interval expression disables auto-settings although the layout will still be adjusted automatically (staggered horizontal, level horizontal, rotated).

Expression for auto-adjusting axis Interval.  This example is for a date axis and uses the report parameters to establish the number of dates to be shown.  Assuming no more than 30 values should be shown on the axis.

= Ceiling(DateDiff(DateInterval.Day, Parameters!StartDate.Value, Parameters!EndDate.Value) / 30)

Conditional Formatting of Graph Lines

For example, to make today’s line stand out against previous days’:

SSRS-HighlightedGraphTrace

Use these expressions

Border

= IIF(Fields!IsTodayFlag.Value = 1, “0.15cm”, “0.05cm”)

Although the default is expressed as “1pt” it seems necessary to use another unit such as cm.

Line Style

= IIF(Fields!IsTodayFlag.Value = 1, “Solid”, “Dotted”)

Fill Color

= IIF(Fields!IsTodayFlag.Value = 1, “DarkBlue”, “Automatic”)

A side effect of this is that the graph lines will become invisible in design view.  Additionally this warning is generated:

Warning 1 [rsInvalidColor] The value of the Color property for the chart ‘Chart1’ is “Automatic”, which is not a valid

Custom User Roles

  • Browser with Subscriptions    “Everything that a browser can do plus subscribe.”
    • View reports, View resources, View folders, Manage individual subscriptions, Manage all subscriptions.
  • View Reports  Use this to grant users ability to view (not browse to) reports.  Use for linked reports that refrence subreports in restricted foldrs.

Disable Actions for Export

Disable click-throughs when reports are rendered for transmission to third parties.

For the Go To Report action, specify this expression for the report name:

=IIF(Globals!RenderFormat.IsInteractive = true, “../OtherProject/ReportName”, false)

Also use expressions to change format to blue, underlined and add tooltip:

=IIF(Globals!RenderFormat.IsInteractive, “Underline”, “Default”)

IsInteractive includes HTML presentation, so that might include the MHTML version that can be included in the body of an email.

Sql Server Service Broker (SSSB)

SSSB Endpoint Authentication Certificate

Don’t forget to give this a long lifetime (if security policy allows) by using EXPIRY_DATE, otherwise the certificate expires after a year.

CREATE CERTIFICATE [BrokerCertificateDec15]
  WITH SUBJECT = N'certificate_subject',
       EXPIRY_DATE = N'2025-12-31';

Expiry of the certificate causes the following log entries with source ‘Logon’:

Service Broker login attempt failed with error: ‘Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.’.  [CLIENT: …

Check the contents of sys.certificates (from master)  for the expired culprit.

Solution

  • Create a new certificate, e.g. using the code above. Hence the date in the name.
  • Alter the broker endpoint to use the new certificate:
    ALTER ENDPOINT [Broker] 
    FOR SERVICE_BROKER
    (
    	AUTHENTICATION = CERTIFICATE [BrokerCertificateDec15]
    )

     

  • This should leave all other endpoint settings unchanged.  Note that the endpoint name is variable.
  • If successful the following log entries appear:
    • The Service Broker protocol transport has stopped listening for connections.
    • Server is listening on [ ….[IP] <ipv4> 4022].
    • The Service Broker protocol transport is now listening for connections.
  • At this stage the broker should be running correctly again.