Category Archives: Reporting Services Tricks

SSRS Speaks ! New Feature in Reporting Services 2014

SSRSNewFeaturesByVersionFullAn old colleague recently asked… “What’s new in Reporting Services 2014 bro?”.   Asides from official support for Chrome Browsers, Reporting Services 2014 remains unchanged feature wise compared to 2012.

It appears focus is predominantly on PowerBI and the acquisition of Datazen.  The following chart illustrates Approximate number of outstanding cool new features by version since Reporting Services 2005.  Of course there were a few discontinued or depreciated items along the way too…

So is this it for the trusty reporting tool ?? Can we show Report Builder some hipster love please MS ??? ;(  Read about the Reporting Services 2014 New Feature here https://msdn.microsoft.com/en-us/library/ms170438(v=sql.120).aspx.

SSRS Migration: Data Sources Supported in Reporting Services 2012/2014

Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.

First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).

Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 http://msdn.microsoft.com/en-us/library/ms159219.aspx. However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances http://support.microsoft.com/kb/2796721/en-nz.

Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 http://msdn.microsoft.com/en-us/library/ms143509.aspx. They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.

Finally, the full migration process is outlined here step by step http://msdn.microsoft.com/en-us/library/ms143724.aspx. There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.

SSRS: Driven to the Brink with the Data Driven Subscriptions Error

Clam Chowder….but first, you attempt to create a Data Driven subscription.  You have setup your SMTP settings for your Reporting Services instance, you have setup your encryption key for storing sensitive data and you have configured stored credentials for all data sources used in the report.

Alas you get the following error when creating your Data Driven subscription.

DataDriven

If you have checked all of the above and you think you might be going crazy, then it is likely you have made a reference to the User!UserID Report Collection within your report somewhere.  You will need to get rid of it, or use a workaround that I stumbled across http://prologika.com/CS/blogs/blog/archive/2011/03/28/data-driven-subscriptions-and-row-level-security.aspx (read through this link a little because it has more detail than my blog) but the example below simplifies the expression a little.

Use the following code snippet in any expression where you need to see the User running the report.


=Code.ClamChowder()

And make sure you have the following function set out in the Code section of your Report.


Public Function ClamChowder() as String

return Report.User!UserID.ToLower()

End Function

You gotta love Clam Chowder with sour dough.

 

MDX: How to Pass a SSRS Parameter Label into a MDX Parameter

Ok Computer, Sing… us a song …

You have to pass a text string from your Reporting Services parameter (Label) into a MDX dataset as a parameter. An example MDX dataset is outlined here …


SELECT Measures.[some stuff]

ON 0,

FROM [Your Model]

WHERE

(

StrToSet("[Dimension].[Attribute].&[" + @YourString + "]")

)

DAX: NAAN is good…NAN is not…use DIVIDE

Just a minute while I reinvent myself …

Measure [ExampleWithoutDivide] divides one measure by another in your Tabular cube…and when you run a query in Management studio you get -1.#IND values …

Screen Shot 2014-06-18 at 1.31.29 pm

And in your Reporting Services dataset you get NaN values …

Screen Shot 2014-06-18 at 1.32.51 pm

Use DIVIDE in your MDX calculation as illustrated in the [ExampleWithDivide] measure above…

ExampleWithDivide:=DIVIDE([Measure1],[Measure2])

Oe Use an alternative result option to return a constant numeric value instead …

Screen Shot 2014-06-18 at 1.50.04 pm

ExampleWithDivide:=DIVIDE([Measure1],[Measure2],1)

See you in places
You’ll be upstairs…
And I’ll be there too…

SSRS: Creating Report Labels for Multi Select Parameter Selections

You want to display the selected multi select parameter values within a label in your report.   Add a textbox to your report, then use the following expression in your textbox to display your multi select parameter selections :

Your label will display “All Selected” – when all parameter values have been selected…

“Multiple Selected” – When more than one value has been selected but not all…

and the actual parameter label will be displayed of only one selection is made.

="For Parameter:   " &
IIF(COUNTROWS("YourDataset")=Parameters!YourParameter.Count,
"All Selected",
IIF(Parameters!YourParameter.Count>1,"Multiple Selected",Parameters!YourParameter.Label(0)))

SSRS: Email Report Subscriptions and the Greyed Out To Field Fiasco

You are creating a report subscription to Email, then you notice the To field is greyed out and you are unable to edit.   We assume you have setup your Reporting Services SMTP successfully because the Email option was available when you created your subscription.
Locate your RSReportServer.config file on the server where Reporting Services is installed.   Take a backup of this file and keep the backup in a safe place.    Now edit the RSReportServer.config file and search for the tag SendEmailToUserAlias – change the value from True to False so this line should look as follows.
<SendEmailToUserAlias>False</SendEmailToUserAlias>
Once edited, save the RSReportServer.config file.  There is no need to restart your Reporting Service service.  Try creating your report subscription to Email again.    The To field should now be unlocked and editable.