Using Images as links in SSRS

In SSRS you can create drill-through links to other reports from textboxes. But if you really want to jazz things up you could instead add an Image and make it clickable. One catch is that you cannot associate an Image to a dataset to pass down values. How do we overcome this you ask?

There are two methods we can use:
1) If you used parameters in your report, you can easily add these as hyperlink parameters.

2) The trickier one is non-parameter values. To pass down a value from a dataset you need to have the
value available somewhere on your report. If your value may be different for each row, you will need to display it somewhere on your reports’ result set. Then on the Image, Action tab add another parameter and give it a name.

Image Properties and Parameters

For the expression value you would enter =ReportItems!Textbox126.value (or whatever the field name may be on your table)

Image Properties and Parameters

Advertisements

Dynamic MDX

I recently wrote some dynamic MDX queries for my SSRS reports. I didn’t have much trouble until I ran into a StrToMember function that needed a double quote at the beginning and end. Hmm.. how to overcome that. Turns out you can use some VB .Net characters.

Here is a list of some common ones.
chr(39) = single quote
chr(34) = double quote
chr(10) = new line
chr(13) = carriage return

so for my example I had the following:
STRTOMEMBER("[Time Periods].[Fiscal Month Desc].&["+@FromMonth+", "+@FromYear+"]")

After I did some converting, I came up with this:
STRTOMEMBER(" & chr(39) & "[Time Periods].[Fiscal Month Desc].&[" & Parameters!FromMonth.Value & ", " & Parameters!FromYear.Value & "]" & chr(39) & ")

SSRS Divide by Zero

In SSRS you often have calculated fields that do division. To avoid having divide by zero errors, you may need to use IIF statements in the expression:
=IIF(Fields!column2.Value = 0, 0, Fields!column1.Value/Fields!column2.Value)
which means if the denominator is 0, show 0, otherwise show the calculated value.

Now for reasons unknown this only works if you are using integers. If you are using decimal values, then you will need to do some more work. The best approach I’ve seen is here, where Robert Bruckner provides a nice solution.

You would go to Report -> Report Properties -> Code and insert the following:

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Now you can do the following in your expression:
=IIF(Fields!column2.Value = 0, 0, Code.Divide(Fields!column1.Value, Fields!column2.Value))

SSRS Formatting

SSRS has some standard formatting options predefined, yet they seemed to have left out a couple of commonly used ones.
1. Formatting currency with no cents. To do this, in the column expression field put in =FormatCurrency(Fields!column1.Value, 0). The 0 means we want no decimal places to be shown.
2. Show a number with commas, no decimal places, and a zero if the value is zero. One way to do this is to set the formatting properties to #,### but if the numbers value is zero it will show nothing. So we would want to use #,##0 to show a zero if neccesary.

Failed Job Steps That Didn’t Notify An Operator

I often use jobs that have numerous steps. One example would be a job that has some initial prep work, multiple steps that run similar code on separate databases for multiple stores, and then finally some cleanup steps. I break the job down to individual steps per store so that if one of the steps fails, they all don’t fail. To do this, go to the advanced tab of the job step properties and set the ‘On failure action:‘ to ‘Go to the next step’. This allows the job to continue processing if there is an error on one of the steps. However, now it raises the question of how do we know if a step failed within a job? Unfortunately Microsoft doesn’t have built in failure notification for steps like they do with the overall job. You can view the job’s history and see if a step failed by the yellow icon, but that is not practical to check every day especially if you have multiple jobs setup this way. A better solution is to use the code below which shows jobs that recently had failed steps and did not notify an operator. It can be handy to setup in a SSRS report to keep an eye on all of your jobs that had failed steps.

-- FAILED JOB STEPS THAT DIDN'T NOTIFY AN OPERATOR VIA EMAIL
USE msdb
GO

DECLARE @DateStringToday VARCHAR(8);
DECLARE @DateStringYesterday VARCHAR(8);

SET @DateStringToday = convert(varchar, getdate(), 112);
SET @DateStringYesterday = convert(varchar, getdate()-1, 112);

SELECT
job_name = sj.name,
sj.enabled,
sjh.step_id,
sjh.step_name,
sjh.sql_message_id,
sjh.sql_severity,
sjh.message,
sjh.run_status,
sjh.run_date,
sjh.run_time,
sjh.run_duration,
operator_emailed = so.name

FROM msdb.dbo.sysjobhistory as sjh
INNER JOIN msdb.dbo.sysjobs_view sj ON sj.job_id = sjh.job_id
LEFT OUTER JOIN msdb.dbo.sysoperators so ON (sjh.operator_id_emailed = so.id)

WHERE sjh.run_status = 0
AND sjh.run_date IN(@DateStringToday, @DateStringYesterday) -- show today and yesterday
AND sj.enabled = 1 -- make sure it's enabled
AND sj.category_id != '101' -- remove SSRS report process jobs
AND so.name IS NULL -- show jobs that didn't already email an operator

ORDER BY sjh.run_date DESC, sjh.run_time DESC

SSRS export to piped CSV

Reporting Services has a comma delimited export option. But what if you need to export a pipe delimited? The code below show’s you how to do this. Edit your rsreportserver.config file which can be found somewhere like C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. (your MSSQL.3 may vary depending on where Reporting Services was installed)

Add the following code to the end of the Render section:
<Render>

<Extension Name=”PIPE” Type=”Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,
Microsoft.ReportingServices.CsvRendering”>
  <OverrideNames>
      <Name Language=”en-US”>CSV (pipe delimited)</Name>
  </OverrideNames>
  <Configuration>
      <DeviceInfo>
          <FieldDelimiter>|</FieldDelimiter>
          <NoHeader>True</NoHeader>
      </DeviceInfo>
  </Configuration>
</Extension>


</Render>

In my case I didn’t want any table headers. You can look over the list of available options here:
http://msdn.microsoft.com/en-us/library/ms155365.aspx
If you want your file extension to be .txt instead of .csv then add this line in the DeviceInfo section: <Extension>txt</Extension>

Now you need to restart SSRS. In my experiences just restarting the service doesn’t force it to look at the config file for changes. I have to go into Reporting Services Configuration Manager and make a change within there for it to re-read the config file. I usually go to Email Settings and change the SMTP Server, hit apply, change it back, and hit apply again. Now click on Server Status, Stop, Start. Open up your report via the Reporting Services web page and you should now have a CSV (pipe delimited) export option!

SSRS Failed Subscription Notifications

Reporting Services can email reports to end users on a regular basis without allowing them access to the web portal. This gives you a little more control over the report and can make it easier for the end user. One problem is that with numerous reports scheduled with numerous parameters to numerous recipients it can be difficult to know if/when a report failed to be sent. It looks bad when the VP keeps asking why he didn’t get his report today.

SSRS subscriptions are very picky. If you have one invalid email address in a subscription with multiple recipients, the whole subscription fails and nobody gets the report. Likewise, if one of your report parameters changed the report will not be sent. Surprisingly there isn’t an out of the box solution to see what reports failed to run.

Here is a query I wrote that checks SSRS subscriptions and returns any failed reports. Use the database you setup for Reporting Services. (default is ReportServer)

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
FROM Subscriptions AS S
LEFT OUTER JOIN [Catalog] AS C
ON C.ItemID = S.Report_OID
WHERE LEFT (S.LastStatus, 12) != ‘Mail sent to’
AND LEFT (S.LastStatus, 12) != ‘New Subscrip’

The first time I ran this we had 4 reports that had failed and we weren’t aware of it! I’ve set this up as an SSRS report and view it daily, and have it emailed to me so I know all my reports were sent.