SSRS: How to get a distinct list of values from an input parameter of field

Today I was building a SSRS report which should display the unique values of a column/field in the header of the report (still with me?).

Basically I had 3 report parameters which query the SSAS cube for values where ‘Measure1’ exists or > 1. Turns out that the list of customers which have at least one record in the cube, was quite large. Now the business asked me to list all the customer names which were selected in the report (via the input parameter), but this will fill up a few pages…

Another idea was to look at the MDX query result set and try to get a distinct list of values.

I stumbled upon this blog post which has a VB (?) function that splits a list in values and then checks for duplicate values (by ignoring them).
With an extra line of code (“System.Array.Sort(SpStr)“) I was able to sort the list of values also.


Here’s an example:
ssrs-distinct-list-of-values-from-list-parameter-values-example











The expression I used for this example:
="Distinct values: "+Code.RemoveDups("5,5,1,2,2,3,3,3,4,4,4,4,5,5,5,5,5")


Getting practical:
In my report, I’ve made a new internal @ListOfCustomers parameter which selects a single column/fields from your main dataset (result set) which you want to display in the header of the report. NOTE: Make sure to check the ‘Allow multiple values‘-checkbox and for your Available/Default-values, only select the CustomerName-field you’ll be using.

Now, for the textbox in the header of the report you may want to use an expression like:
=Code.RemoveDups(JOIN(Parameters!ListOfCustomers.Label, ", "))


The code I used:

Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()

Dim SpStr
SpStr = Split(items ,",")
System.Array.Sort(SpStr)

For i As Integer=0 To Ubound(Spstr)
If Not noDups.Contains(SpStr(i).Trim()) Then
noDups.Add(SpStr(i).Trim())
End If
Next

Dim uniqueItems As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueItems)

Return String.Join(",", uniqueItems)
End Function



Copy/Paste this into the Code-section of your Report properties window:
ssrs-distinct-list-of-values-from-list-parameter-values-code








SQL Server, SSIS and Biml data types

Cathrine Wilhelmsen wrote a blog post with a mapping table (or cheat sheet) with the data types of SQL Server (DB), SSIS and Biml:
(you’re able to sort the columns on Cathrine’s post, just click on the link above)


The table below shows a simplified mapping between SQL Server, SSIS (SQL Server Integration Services) and Biml (Business Intelligence Markup Language) data types. The current version of the table does not include all type systems, all possible mappings or all data types, but is instead meant as a quick reference while developing and learning Biml. It was mainly made as a cheat sheet for myself, but I hope other new Biml developers will find it useful.


biml-data-types








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








SSIS: Extract package information (e.g. DataFlowTaskName, TaskDescription, PrecedenceConstraint, etc.)

Found this great blog post from Saravanan:
http://sqlmall.wordpress.com/2014/05/29/extract-ssis-2012-package-data/

He describes a way to search through the XML coding of your SSIS packages, to for example, look for a certain DataFlow which is called ‘RAW_DWH_Main‘.

Step 1) Enable FILESTREAM via SQL Server Configuration Manager

http://sqlserverscribbles.com/2013/07/09/how-to-enable-and-configure-filestream-in-sql-server-2008-2012/
http://msdn.microsoft.com/en-us/library/cc645923.aspx

Step 2) Create the FileTable (and a new database)
NOTE: Make sure to fill in an existing path for the directory.


EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

CREATE DATABASE PackageDefinition
ON PRIMARY
(Name = PackageDefinition,
FILENAME = 'E:\FileTableDB\PackageDefinition.mdf'),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = Package,
FILENAME='E:\FileTableDB\PackageDefinition')
LOG ON (Name = PackageDefinitionLog,
FILENAME = 'E:\FileTableDB\PackageDefinitionLog.ldf')
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'PackageDefinition');
GO

USE PackageDefinition;
CREATE SCHEMA FT
GO

CREATE TABLE FT.PackageDefinition AS FileTable
WITH
(FileTable_Directory = 'PackageDirectory');
GO

Here’s your new FileTable:

Search-in-ssis-packages-with-sql-filetable-structure


Explore the directory and copy/paste your SSIS packages here

Search-in-ssis-packages-with-sql-filetable-explore-directory


Now copy (or perhaps move/relocate) your TFS to this directory.

Almost done…run the query!

;WITH XMLNAMESPACES
(
'www.microsoft.com/SqlServer/Dts' AS DTS
),

PackageDefinition
AS
( /*Query the file content([file_stream]) from filetable*/
SELECT *
,FileTableRootPath() AS RootPath
,file_stream.GetFileNamespacePath() as FilePath
,path_locator.GetLevel() AS PathLevel
,PackageData = CAST(CAST([file_stream] AS VARBINARY(MAX)) AS XML )
FROM [FT].[PackageDefinition]
WHERE file_type = 'dtsx' --SSIS Packages
--AND Name LIKE '%RAW_DWH_Main%' --FileName
AND file_stream.GetFileNamespacePath() LIKE '\PackageDirectory\DWH.SSIS\%' --Project folder
AND path_locator.GetLevel() = 2

)

SELECT r.*
FROM
(
SELECT
RootPath
,FilePath
,PathLevel
,PackageName = Exe.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,CreationDate = Exe.pkg.value ('@DTS:CreationDate' ,'DATETIME2(0)')
,CreatorName = Exe.pkg.value ('@DTS:CreatorName' ,'VARCHAR(250)')
,DataFlowTaskName = Task.pkg.value ('@DTS:Description' ,'VARCHAR(250)')
,TaskDescription = Task.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,PrecedenceConstraint = '['+REPLACE((pc.pkg.value ('@DTS:From','VARCHAR(250)')),'Package\','')+'] ' +'---->' + '['+REPLACE((pc.pkg.value ('@DTS:To' ,'VARCHAR(250)')),'Package\','') +']'
,refId = Con.pkg.value ('@DTS:refId' ,'VARCHAR(250)')
,ConnectionProvider = Con.pkg.value ('@DTS:CreationName' ,'VARCHAR(250)')
,ConnectionManagerName = Con.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ConnectionString = Par.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ParameterName = CM.pkg.value ('@DTS:ConnectionString' ,'VARCHAR(250)')
,ParameterValue = Par.pkg.value ('.' ,'VARCHAR(250)')
,VariableName = Var.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,VariableValue = Var.pkg.value ('.' ,'VARCHAR(250)')
,PackageData
FROM PackageDefinition
OUTER APPLY PackageData.nodes('/DTS:Executable') AS Exe(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:Executables/DTS:Executable') AS Task(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PackageParameters/DTS:PackageParameter') AS Par(pkg) --Will only return data while using the 'Package Deployment'-model I guess.
OUTER APPLY Exe.pkg.nodes('DTS:Variables/DTS:Variable') AS Var(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager') AS CM(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager') AS Con(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PrecedenceConstraints/DTS:PrecedenceConstraint') AS pc(pkg)
) AS r
WHERE r.DataFlowTaskName LIKE '%MyDataFlow%'
ORDER BY FilePath



The results:

Search-in-ssis-packages-with-sql-filetable-xml-query-results


Important note:
The query doesn’t return any nested tasks, you may need to use a LIKE-operator for that:
WHERE CAST(PackageData AS NVARCHAR(MAX)) LIKE '%%'

If you have a solution for this, please let me know or post the solution in the comments. Thanks!


More info about FileTables:
http://blog.tallan.com/2012/01/01/its-a-file-system-its-a-database-table-its-sql-server-denali-filetable/

How-filetables-work-more-info-sql-server








SSRS image with transparent background

ssrs-image-with-transparant-background-example

What?
Sounds easy but actually if you use an image which has transparent areas, ie. has one color which will be transparent, the transparent pixels will be colored in with the page background color.

Why?
I want an image to display per row as a status marker for 3 different types of results: Success, Failure, Unknown. My images are circles with the background being transparent. I want the first column to display an image based on the status result.

If I simply insert an image, the report would use the background color of the report.

How?
The issue exists if your image has transparent areas but displays the report background color instead:

1) Right-click on the cell you want to put the image in.

2) Insert > Rectangle

3) Set the Rectangle Fill color (I’m using an expression based on the status) to output: Green (for Success), Red (for Fail), Orange (for Unknown).

4) Right-click on the rectangle and select Insert > Image

5) Edit the Image Properties to specify the image to use.

The image can be an expression as well (I embed these and then refer to them in the expression by name). For example:
=IIF(Fields!SummaryStatus.Value="Success", "status_ok", IIF(Fields!SummaryStatus.Value="Unknown", "status_unknown", "status_fail"))

ssrs-image-with-transparant-background



Thanks to Joël Lipman.



Here’s another cool example on how to use transparency within a SSRS report:
http://cm-bloggers.blogspot.nl/2010/10/using-transparent-background-in.html