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?








Advertisements

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