Jonathon

Jonathon Eve-O'Connor's Blog

SQL PASS Summit - Day 3

Today was the final day of the pass summit. The schedule was a little different in the morning with no keynote speech. Instead I opted to see Jerimiah Peschka’s session ‘Dynamic SQL: Build Fast, Flexible Queries’. This was an interesting talk, which covered the security aspects of dynamic SQL and how to avoid things such as SQL injection and also how to ensure that queries generated from dynamic SQL ran quickly and avoided issues such as ensuring plan reuse on each execution. Whilst I very rarely find a myself situation in which Dynamic SQL is essential I found this an interesting talk and I am sure that when the need does arise it will have been very handy.

Following this I attended a Microsoft lab on Azure Machine Learning (AML) ‘Authoring a hand-written digit recognizer in Microsoft Azure Machine Learning Studio’. This was a hands on session where computers and a set of exercises and sample data are supplied. Essentially the lesson consisted of providing a model with vector images of numbers, training the model to recognise the numbers and then passing a new set of number vector images to the model to see it identify them (> 85%) correctly. The session was interesting and gave me an overview of using AML Studio which was great. My main issue was that the data was already provided in vector format (csv files which were imported), whilst I can see time constraints mean that the conversion from image to vector during the class would have been difficult it would have been very interesting to have seen a few of the examples of the images so that we could see exactly what it was that was being identified and how different the numbers were as this would give an illustration of how clever the algorithms are.

I finished the conference with Brain Larson’s talk entitled ‘Effective Reporting through SSRS Advanced Authoring Features’. As you may know SSRS has not been updated for some time now and with the current focus on Power View I wasn’t sure how much I would benefit from this and what the plans are for this tool in the future. I was quite surprised therefore when the room filled up and overflowed so that there were quite a crowd of people in the room who had to stand in order to watch it. The talk showed some interesting uses of SSRS - images embedded in the background of reports, adding strip lines and markers and an example of creating an interactive report which allowed users to set their preferences for running other reports. The examples given were very good as was the delivery, my only issue here is that without new features added to SSRS (for example ability to do an asynchronous refresh of some report elements) I am not sure what the uptake of SSRS is going to be.

All in all I have had a fantastic and informative time here. I leave you with some pictures, taken earlier in the week :)

20141031_113229  20141030_132631

Maintaining Hierarchy State On Report Refresh

I recently had a request from a client who wanted to display use a parent child hierarchy to navigate their report.

The issue with this however is when you click the hierarchy the report refreshes, the data is updated in the charts, but the hierarchy collapses to its original state as below. This wasn’t suitable for the clients needs as it was a large hierarchy with several levels of depth.

image image

What was required was to maintain the state of the hierarchy so it would stay expanded during report refresh.

After a little thought I came up with the following solution:

1. Setup a standard report with an indented hierarchy with expandable sections. This has been covered in many places so I’ll only give a brief overview, for this example report I have used adventure works multidimensional model with the following MDX query as the source for the hierarchy data:-

image

Add the fields in a matrix like this:

image 

Right click the Organizations text box, select ‘Textbox Properties’ and in the Alignment tab set the Left padding property to the following value (change the number to adjust indentation):

image

Following this you need to setup groupings in order for the expanding hierarchy. Add a grouping on the UniqueName property

image

On the visibility section set the toggle property to HIDE (ignore the expression for now -) and set the "’Display can be toggled by this report item” property as per the below.

image

In the advanced section set the recursive parent property to [Organisations.ParentUniqueName] and this should give a working report with an expandable hierarchy.

 image

2. Add a hidden parameter to the report called OrganisationLevel. Set the default value to the top of the hierarchy.

image

Add another column to the matrix, add text to it for the link.

image

Select the text and right-click the selected text navigate to properties and place an action to go to original report and pass the OrganisationLevel parameter with a value of [Organisations.UniqueName]

 image

3. Add a dataset dsHierarchyVisibleMembers with the below MDX query – this query uses the OrganisationLevel parameter to work out which level in the hierarchy the user has clicked on and the entities at the same level which we can then use to control the state of the expanding hierarchy. 

 image

Now set the visibility property on the row grouping to the following value:

image

The last step is to set the hierarchy collapsed/expanded state, which we using the following formula for the ‘InitialToggleState’ property of the text box to the following.

image

Once these steps are completed clicking one of the elements in the report will pass the parameter, but the hierarchy state is maintained between clicks. Clicking View on ‘Northeast Division’ the left report displays the following in the right – note the chart has been updated but that the hierarchy expansion state remains as it was before the user clicked.

image image

That’s all for now, hope this is of use.

Word Cloud Reports in SSRS

Whilst SSRS does not have an inbuilt utility for building Word Cloud reports (sometimes also called tag clouds) this can be achieved with a little HTML as per the sample below.

WordCloud

Essentially the concept is that you generate a HTML string which allows you to control the sizes and colours the of the text which you can then render in a Tablix or Text Box.

There are many ways of achieving this, the method that I will describe dynamically sizes the text depending on the results generated from the query, you will need to decide if this meets your needs and adapt to your requirements as appropriate but the central concepts will be the same. For this example I have used the AdventureWorksDW database, available for download from: http://msftdbprodsamples.codeplex.com/releases/view/55330

1. The first step is to construct a query in the dataset that will return the html string in the required format. To produce this report I used the query below. As noted in the comments in the query, you can amend the parameters to control the text sizing and base size.

DECLARE @max INT
DECLARE @min INT
DECLARE @base INT = 6 --This sets the base text size.
DECLARE @scalefactor INT = 60 --This controls amount of size increase.
DECLARE @multiplier FLOAT
DECLARE @divisor FLOAT

CREATE TABLE #tmpProductOrders
     (Product VARCHAR(250)
     ,Orders INT 
     )

INSERT INTO #tmpProductOrders
SELECT
       PSC.EnglishProductSubcategoryName AS Product
      ,SUM(FI.OrderQuantity) Orders
FROM [dbo].[FactInternetSales] FI
INNER JOIN [dbo].[DimProduct] P
        ON P.ProductKey = FI.ProductKey
INNER JOIN [dbo].[DimProductSubcategory] AS PSC
        ON PSC.ProductSubcategoryKey = P.ProductSubcategoryKey
GROUP BY PSC.EnglishProductSubcategoryName

SELECT @max=MAX(Orders), @min=MIN(Orders) FROM #tmpProductOrders
SELECT @divisor = CAST((@max-@min) AS FLOAT);
SELECT @divisor=IIF(@divisor=0.00, 1.00, @divisor)
SELECT @multiplier = (@scalefactor-@base)/@divisor

SELECT
          REPLACE(REPLACE(
                  (SELECT '<span style=font-size:' + CAST(u.Fontsize AS VARCHAR(10)) + 'pt;>' + u.Product + '</span><span> </span>'
                   FROM
                      (
                       SELECT
                              Product
                             ,Orders
                             ,@base + ((@max-(@max-(Orders-@min)))*@multiplier) AS Fontsize
                       FROM
                               (
                                SELECT
                                          Product
                                         ,Orders
                                FROM #tmpProductOrders
                               ) sh
                       ) u
                    ORDER BY NEWID() --This provides a random ordering
                   FOR XML PATH('') )
             , '&lt;','<'),'&gt;','>'
           ) AS KeywordCloud

DROP TABLE #tmpProductOrders

2. Once you have the query which produces the HTML in the required format the next step is to place this into a Text Box or Tablix within the report. For this example I used a Text Box and expression to populate the data. It is important to note here that unlike most browsers the HTML rendering engine in SSRS is very sensitive and you need to be careful as slight errors in syntax or use of unrecognised tags will stop the html from rendering.

image

3. The final step is to change the properties so that the render format is set to HTML

image

One can also use the HTML to change the colour or other properties of the individual words as required. I hope you find this useful!

Adding Strip Lines to SSRS Reports

Once in awhile you happen upon a really useful feature in SSRS that you were unaware of. For me strip lines are definitely one of these features that you don’t see that much mention of but which can add valuable insight to a report.

Below is a simple example of a scatter chart with a horizontal and vertical strip line each marking the average values of their axis. 

image

In order to add strip lines to the report you need to do the following:

1. Select the chart axis to which you wish to add the strip line and go to the properties window. In this there is an option marked ‘StripLines’. When you click in the cell you can open up the Strip Line Collection editor.

image image

 

2. Click the ‘Add’ button in the strip line editor (note: you can add multiple strip lines to each axis if required).

3. In the option marked ‘IntervalOffset’ specify the required value for the Strip Line, you can either specify a fixed value or use an expression from your Dataset. If you require the line to repeat over fixed values you can fill in the properties for ‘Interval’.

image

4. You then need to set the display properties for the strip lines under appearance in order for them to be visible. To produce the example strip lines in this post I have set the ‘BorderColour’ to black and the ‘BorderStyle’ to dashed.

image

 

 

5. If you wish to add text after the strip line on the graph – for example to illustrate that values after the strip line are above average as shown below then the settings are under the title section.

image image

That’s it for now, hope that you’ve found this helpful:)