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.

Monitoring SSRS Reports and Subscriptions

Interpreting data in ReportServer database

Observations from SSRS 2008 R2

Subscriptions

table dbo.Subscriptions.

InactiveFlags

There’s no way to disable a subscription from SSRS UI though articles suggest setting InactiveFlags to 1 will achieve this.

  • 0 : schedule is active.
  • 8 : “One or more report parameters have an invalid parameter value.” (text displaed in subscription editor)
  • 16 : “An unkown parameter was found.” (text displayed in subscription editor).  i.e. a parameter name was set that no longer exists in the report specification.

Schedules

table dbo.Schedule

Column Description
 DaysOfWeek  Bitmap.
Sun = 1 … Sat = 64
All days = 127
 WeeksInterval  1 for Daily
 Month Bitmap for months
 MonthlyWeek  nth week of month
or Null
 Recurrence Type 1 : Once
2 : Hourly
3 : [not yet achieved]
4 : Day / Week
6 : Month

Re-triggering Scheduled Reports

This is the code that appears in the report-initiating Job.

EXEC ReportServer.dbo.AddEvent@EventType=‘TimedSubscription’,@EventData=‘<SubscriptionID>’

Values for EventType:

  • Timed Subscription : individually scheduled subscription for  a single report.
  • SharedSchedule : this job triggers a shared schedule.