SSRS: Render your reports in HTML4.0 instead of MHTML!

By default in SQL Server 2012 (Enterprise) when you create a new data-driven subscription (DDS), you’re able to select ‘HTML4.0‘ as a rendering format. But when you create a normal subscription, the option isn’t available.

SSRS 2012 HTML4 subscription

 

Here’s how you can enable the functionality of rendering in HTML4.0 format for a normal subscription. Please remove Visible=”false” for the HTML4.0 rendering extension in the RSreportserver.config file as follows:

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

To get the HTML4.0 to be included in the body of the email rather than as an attachment, look for this element in the RSreportserver.config file:

< EmbeddedRenderFormats>

And add this as an extra entry within that element:

<RenderingExtension>HTML4.0</RenderingExtension>

In my own file the whole thing now looks like this:

< EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
<RenderingExtension>HTML4.0</RenderingExtension>
</EmbeddedRenderFormats>

This works fine for me using SQL 2008 R2 and up.

Also for SQL Server 2016, the option for HTML5 is also available (same principle as above). But in SQL 2016 with HTML5 it was still send as an attachment instead of embedded. So somehow the embed-config doesn’t work properly yet?








SSRS Dashboarding: a ‘webdesign-look’

Couple of weeks ago, I shared my SSRS dashboard with the community on LinkedIn:

SSRS Dashboard shared on LinkedIn














Koos van Strien asked for the source (RDL) and restyled the dashboard with his ‘webdesign-look’. Pretty nice!

SSRS Dashboard response on LinkedIn

















Koos discusses the changes he made to my dashboard on his blog:
http://www.msbiblog.com/2016/01/28/ssrs-non-ugly-5-the-nitty-griddy-details/#more-402

Here are both dashboards in full size:

SSRS Dashboard Clint Huijbers

 

SSRS Dashboard Koos van Strien



 


 


 

SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).

But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).


What we want to achieve
So this is what we want to achieve (in red):

SSRS datetime input parameter instead of text for mdx





Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:

SSRS datetime input parameter instead of text for mdx - properties






Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.


Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:
2015-12-01

To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:

=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)



Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]

Here’s the full code expression which you can use as a parameter value (expression):

="[Dim Creation Date].[Date].&["
+CSTR(DATEPART(DateInterval.Year,CDate(Parameters!DateFrom.Value)))+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))
)
+"T00:00:00]"



Use the expression as an input value for your dataset
The final step is to use the expression:

SSRS datetime input parameter instead of text for mdx - dataset properties









Done!
That’s it! Bye bye ugly drop-down list ๐Ÿ™‚

SSRS datetime input parameter instead of text for mdx - final result











SSRS Drill-through: passing multiple values to another report (MDX list of values)

When I build large dashboards, I often fall back to basic stored procedures to combine datasets, but also to be able to manipulate parameter values (month, first week, previous week, previous year totals, etc.).
An annoying thing is that when you want to create a drill-though action to another report which uses MDX, you’ll need to magically join these values.

Well…here’s how you could achieve it.
Let’s say I have these three values (keys): XXX, YYY and ZZZ

MDX expects a prefix and a suffix (according to the dimension properties/levels).
So here’s what MDX expects:

[Dim Customer].[Customer Number].&[XXX]
[Dim Customer].[Customer Number].&[YYY]
[Dim Customer].[Customer Number].&[ZZZ]

Important: MDX expects different objects/rows, like you need to use separate rows in VS/SSDT for the default parameter values.

Your dataset returns the basic key values, first step is to join these (including the prefix and suffix):

="[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]"

The results is:

[Dim Customer].[Customer Number].&[XXX],[Dim Customer].[Customer Number].&[YYY],[Dim Customer].[Customer Number].&[ZZZ]

Which seems correct, but MDX isn’t expecting a multi-valued string (compared to SP’s).

Final thing, you’ll need to do is to split it again in order to provide a record/row per value:

=Split("[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]",",")








SSRS: Windows Authentication connection to SMTP server

In order to get SSRS to use Windows Authentication to setup a connection with your local SMTP, you’ll need to modify the rsreportserver.config.

The ‘rsreportserver.config’-file is located in: X:\Program Files\Microsoft SQL Server\MSRS1111.MSSQLSERVER\Reporting Services\ReportServer

Open the file and look for ‘SMTPAuthenticate‘:

SSRS SMTP configuration





















By default it is empty, but you’ll need to set it to ‘2‘.

More info about this property: https://msdn.microsoft.com/en-us/library/ms159155(v=sql.120).aspx#bkmk_options_local_SMTP

SMTPAuthenticate specifies how the report server connects to the remote SMTP server. The default value is 0 (or no authentication). In this case, the connection is made through Anonymous access. Depending on your domain configuration, the report server and the SMTP server may need to be members of the same domain.
To send e-mail to restricted distribution lists (for example, distribution lists that accept incoming messages only from authenticated accounts), set SMTPAuthenticate to 2.

IMPORTANT: After you’ve saved the configuration-file, don’t forget to restart the SSRS service on your server.








SSRS: NaN, Infinity or even #Error

I’ve seen many IIF’s, CASE-statements, +0.00001 additions and others to suppress or solve one of these ‘errors’:
NaN, Infinity or even #Error

Let’s start with the basics, when do these things emerge and why?
Normally, you get NaN when trying to divide 0 / 0 or Infinity when you are dividing any number by 0.

I personally use this function to overcome such obstacles:
Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
Return 0
Else
Return dividend / divisor
End If
End Function

And here’s how you should use it:
=Code.Divide(X,Y)

Other more creative solutions are:
=REPLACE(X/Y,"NaN","0")
Or:
=IIF(Y = 0, 0, X/Y)
Or:
=Switch(
X/Y = "NaN",Nothing,
X/Y = "Infinity",Nothing,
X/Y = "Infinity",Nothing
)

SSRS – Report Execution Analysis

A little while ago, I made a report to analyze the usage of SSRS report (users and rendering times).

Here’s how it works:
The vertical axis displays the total number of report executions, while the size (and (x) number) displays the total number of unique users.
And not to forget, color of the balloons is based on the average rendering time of that specific report.


Report Execution Analyses explanation

It is pretty straight forward on how to implement it.

Download
Download the SQL Stored Procedures and SSRS report RDL-file here:
ReportExecutionAnalysis.zip





Other reports
Have a look at other reports I’ve published for my readers:

SQL Agent Job Schedule Timeline report

sql-agent-job-schedule-timeline-report



SQL Agent Job Status report

sql-agent-jobs-report








SSRS Heat Map in a Table or Matrix

Today I wanted to create a Heat Map (not the geographical one, but in a matrix) and stumbled upon this blog post:
http://www.arrangeactassert.com/how-to-create-heat-maps-in-sql-server-reporting-services/

It uses the colors from green to red, but I’m more of a subtle guy who wants to use softer tones for that. So I picked my own set of pre-defined colors for the VB code to use and here is it:

ssrs-heat-map-matrix-table-code-example

Code:

Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
Dim colours As String() = New String() {"White", "WhiteSmoke", "Gainsboro", "Silver", "DarkGray"}
If textBoxValue = 0 Then
Return colours(0)
End If
If minDataSetValue = maxDataSetValue Then
Return colours(0)
End If
If textBoxValue > maxDataSetValue Then
Return colours(colours.Length - 1)
End If
Dim divider As Decimal = (maxDataSetValue - minDataSetValue + 1) / 4
Dim index As Decimal = (textBoxValue - minDataSetValue) / divider
GetHeatmapColor = colours(index)
End Function

Simply copy/paste this code into Report Properties -> Code and use this expression for your BackgroundColor of the cell in your matrix or table:


=Code.GetHeatmapColor(Sum(Fields!NumberOfUsers.Value), Min(Fields!NumberOfUsers.Value, "DataSet1"), Max(Fields!NumberOfUsers.Value, "DataSet1"))

Note: In my case I set the minimum to 8.0 to ignore very low values which will mess up the color scheme.








Data driven subscriptions cannot be created because the credentials used to run the report are not stored,the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Well SSRS is stil bugging you huh?

ssrs-data-driven-subscription-cannot-be-created-user-defined-parameter-values

There are a few things you may need to check:

1) Make sure that you’ve stored the credentials of the data source IN the report

2) Since the data-driven subscription is running without an actual user-context, the use of User!UserID is not allowed.

Start by adding this custom code to your report (under ‘Report Properties‘ -> ‘Code‘):

Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function

Now you need the find: User!UserID

And replace it by: code.UserName()

in the Code-view of the report.








How to design your SSRS or Power BI dashboard

Here’s how you should design your SSRS of Power BI dashboard (thanks to Julie Koesmarno).
More info on her blog:
http://www.mssqlgirl.com/power-bi-in-a-jiffy-composition-in-power-view.html


To my delight, she mentioned about composition, in particular Fibonacci Spiral. It reminded me of one of the fundamental things that I have learned in photography (as a hobby). So this weekend, I spent a bit of time reviewing some of the data visualisations in Power View that I have created in the past. Instead of just looking at it as just data and information, I put my โ€œamateurโ€ photographer eyes on this. I begin thinking about design, technical and most importantly business components and how to put them together.

Here are a couple of Before-And-Afters, where I have revisited the objectives and composition aspects of these data visualisations in Power View.

Before and After

Before-and-After-design-ssrs-power-bi-dashboards

Fibonacci spiral is a pretty cool thing to use / apply in composition. Use it wisely and when it works, it works really well. Not all visualisations have to fit Fibonacci spiral though ๐Ÿ™‚


Another example based on a photo:

how-it-is-done-in-photography

More tips for photography (and dashboards):
http://photo-typ.blogspot.nl/2013/08/golden-rule-of-thirds-and-fibonacci.html


Another interesting blog post by Jason Thomas (also HowTo’s):
http://www.sqljason.com/2013/05/a-sample-ssrs-dashboard-and-some-tips.html

Dashboard-design-nice