SSRS 2012 CTP Shared Datasets in SQL Azure

In preparation for my SQLRally Presentation in Dallas in May 2012, I’ve been investigating SQL Azure, using the SQL Server Data Tools to create SQL Server Reporting Services 2012 reports. One issue you might encounter is that your report runs perfectly well in SSDT, but does not run when it’s been deployed to SQL Azure. You might get the following error message:

  • The report server cannot process the report or shared dataset. The shared data source ” for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference) 

The resolution for this issue is that Shared Dataset don’t seem to work properly, at the time of writing. The workaround is straightforward – don’t use a Shared Dataset. Here are some pointers:

 – Make sure your data source is pointing to your SQL Azure Reporting Services Report Server URL. Test your connection works, and that it is using the correct login.

 – When you create your report, don’t use a Shared DataSet. Instead, once you’ve set up your Data Source, go straight to creating your Report by right-clicking on the Reports folder and selecting the option ‘Add new report’.  

You can then deploy the report to Azure, and it should work fine.

As an aside, you could try your report out on a mobile device. If you need any instructions, just take a look here.

I hope that helps! Any questions, please leave a comment.

Tiny correction to MSDN Blog – adding report server content types to a Sharepoint Library

I spotted a small error in MSDN article entitled Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) It’s just a tiny error, but small enough that someone looking for it, might run into problems. The second half of the document should read:

To add report server content types

1.Open the library for which you want to add Reporting Services content types.

2.On the Library Tools ribbon tabs, click the Library tab.

3.On the Settings ribbon group, click Library Settings.

4.Under Content Types, click Add from existing site content types.

5.In the Select Content Types section, in Select site content types from, click the arrow to select Reporting Services. << This should actually say ‘SQL Server Reporting Services Content Types. Please see the image below:

SQL Server Reporting Services types
6.In the Available Site Content Types list, click Report Builder, and then click Add to move the selected content type to the Content types to add list.

7.To add Report Model and Report Data Source content types, repeat steps 5 and 6.

8.When you finish selecting all of the content types that you want to add, click OK.

I hope that helps someone out.

SQLPass 24hop Review: Slicers in Reporting Services

Twice a year, SQLPass put on a stellar free ’24 hour hop’ which is aimed at engaging with SQL Server fans all over the world. Basically, SQL experts from all over the world give an hour of their time in order to present you, the SQL Server viewing public, with some SQL goodness, advice and tips and tricks to make your lives easier. Ever since I have got involved with the SQL Server community in the UK and the US, I have learned so much from my peers. I can say that SQLPass and SQLBits have both enriched my life so much in terms of my skills, and also introducing me to some wonderful friends that I’ve made my home in the community. For that, I am grateful. The 24 hop is one of the ways in which we can learn more about SQL!

The SQLPass 24 hop sessions, given in Fall 2011, have been designed to give you a flavour of the precons that will be provisioned as a part of SQLPass Summit.  The 24hop sessions are given by world-class SQL experts. This fall, I listened to a total of 5 different sessions and learned a lot! 
I’d like to thank the following people for giving up their time to educate the wider SQL Community (and me!): Denny Cherry, Stacia Misner, Paul Turley, Rob Farley, Simon Sabin and Peter Myers for their excellent sessions and for giving up their time to help people in the community by provisioning free training. If you are interested, I’d recommend that you take the time to look at the SQLPass preconference training for each of these sessions since you’d be trained by the ‘best of the best’. 

In this blog, I wanted to call out Simon’s Reporting Services session since I thought that his session was particularly outstanding. Simon’s webinar focused on provisioning slicers for Reporting Services. Yes, you read that right! Slicers are available in Excel and in Project Crescent. Every time I show slicers to an Excel user for the first time, the customer is usually impressed by their simplicity and ease of configuration. It’s also possible to ‘theme’ slicers so that they match the rest of the dashboard elements. They also increase usability because they are consistent with Schneiderman’s Visual Information Seeking Mantra: ‘overview, filter and zoom’ methodology with respect to data navigators ‘surfing’ their way through the data.

Basically, Simon used VB and SSRS to produce the slicers in the report. For SSRS people who’d like to know more about what .Net can offer them, this is an excellent route towards learning some .net whilst enhancing report usability in line with the best data visualisation thinking as advised by gurus like Schneiderman. What I especially liked is that Simon paid attention to the .Net requirement from the SSRS writers’ perspective, and was careful to call out any potential pitfalls or mistakes that the SSRS report writer might make. 

The end result was great to see, and Simon produced the report, which you can see on his blog, on less than an hour. It seems to me that his SSRS precon would be especially interesting since you’d obtain lots of useful practical advice, packed into a one-day event, that would really make a difference to writing SSRS reports.

As a business intelligence specialist, I believe firmly that accurate and useful reporting can drive a business from data towards business intelligence and customer intelligence; listening to the stories that the data is telling you. If you can’t ‘hear’ the data because the reports are poor or don’t have the user in mind, then you are still not using the data properly – even if you have lots of it. Quantity of data is not quality of data – it has to be clean and well-presented before it can be used to support the enterprise.

It seems to me that an investment in report writing is fundamentally an investment in business. SSRS precon and training like Simon’s session offers a real ‘value-add’ to the business long term, by supporting the provisioning of reports by report writers to the decision-makers who need the reports to drive the business forward. SSRS can be left behind a bit, in the fanfare over Excel Services, and of course, Project Crescent. Despite the new technologies and new ‘self-service’ business intelligence outlook, there is always a role for straight reporting in running a business knowledgeably and accurately, based on the data. 

If you’re interested in the SQLPass pre-cons, then please do head over to the site and take a look! If you decide to go, please do let me know by tweeting me and hopefully I’ll see you at SQLPass Summit!

Data Visualisation and SQL Server Blethering in July

In the Scots language, a ‘blether’ is someone who likes the sound of their own voice. I will be speaking at four events in July, so I’m sure I qualify as a blether! In case anyone’s interested, here are the details of the events:

I will be speaking at the Tableau UK User Group on 5th July. Stephen Few is presenting, and he’s the best presenter I have ever seen (and I’ve attended lots of presentations). I’ve volunteered to go straight after Stephen which is brave since he’ll have set the standard for presentations extremely high, and I won’t compare! To Register, visit our Tableau User Group site.

I am speaking at a SQLPass Performance Webinar on the visualisation of the data contained in the Microsoft Management Data Warehouse using SQL Server Reporting Services 2008 R2. This takes place on July 5th, after the Tableau event, and the registration details can be found here. This event sponsored by Confio

I will be speaking on Data Visualisation and SQL Server Reporting Services at the South Wales SQL Server UG July 14th Cardiff. To get more details, and to register, please visit the SQL Server User Group site.

On 21st July, I am also speaking at the Manchester SQL Server User Group but the details aren’t up on the site yet. The topic will be the data visualisation of the data in the Management Data Warehouse again, and the link to the site is here

Wish me luck!
Jen x

How can reading a report be like reading a comic? Trellis Charts

How can reading a report be like reading a comic? Comics have a particular accepted structure, where each panel tells its own story, but the whole number of panels together tell a complete story. In a comic, the individual panels combine together to form a narrative. If you are interested in reading more about the structure of comics, then I recommend that you read ‘Understanding Comics: The Invisible Art’ by Scott McCloud. I first saw the idea of relating reporting to comics in the brilliant Dataspora blog, and I was interested to relate this concept to SQL Server Reporting Services. The purpose of this blog will show you how easy it is to create a Trellis report in reporting services, followed by some data visualisation principles and some thoughts on interpreting the data, and finally some references for further reading. 

I have been presenting this material at some of the SQL Server User Groups in the UK, and thought it might be useful to give the information out in blog format too. 

In the sphere of reporting, it is also possible to construct a panel of reports in order to form a narrative of what the data is telling the data consumer. The term for a panel of reports is a trellis chart (Cleveland, 1997) is a ‘chart of charts’ – it is a multi-panel of small, similar charts. The design of a trellis is hallmarked by the arrangement of the small charts in a lattice or grid fashion, whereby the charts are arranged into rows, columns or even pages.

Each chart in the trellis is similar; for example, all the charts in the trellis should be the same style, for example, scatterplot, bar charts and so on. However, each chart differs in terms of the category variable that is displayed. This means that the trellis structure can help the data consumer to understand the interactions between the variables that produce the result.

Trellis charts are useful for displaying data that is multivariate and/or has large data sets.  If a chart has a lot of bars and lines, then it can become difficult for the user to understand. By splitting the data out by category, Trellis charts can help the data consumer to understand the structure of the data by removing the need for 3D representation, or by having too many bars and lines.  Here is an example of a trellis chart. If you can’t see it properly, then please click here to go to flickr:


The above trellis chart shows the BP Statistical Review of World Energy 2010, and shows the expected resources for oil production in Thousand Million gallons per day. I have provided a cut down version of the CSV file here, for your reference. 

This blog will explain how to create the above trellis chart using SQL Server Reporting Services 2008 R2. The reason we use this version of SQL Server is because it has the Matrix component, as we will see as we progress through this blog.

First things first: a data source! We will need a table that is populated with data, and a stored procedure to extract the data. I have given you samples below.

1.      Create and Populate the Table
If you need a script to create a table, here is one for you:

CREATE TABLE [dbo].[WorldRegionBPProvedReserves](
       [Year] [float] NULL,
       [Region] [nvarchar](255) NULL,
       [Country] [nvarchar](255) NULL,
       [Thousand Million Barrels] [float] NULL

You can import the CSV file into the table using the SSIS wizard in SSMS, just to make life easier for you.

2.      Extract the data using a Stored Procedure

Once the data has been imported, you’ll need a stored procedure to extract the data from the database. Here is a sample stored procedure for you:

/****** Object:  StoredProcedure [dbo].[usp_MV_WorldRegionBPProvedReserves]    ******/
CREATE PROCEDURE [dbo].[usp_MV_WorldRegionBPProvedReserves]
–exec [dbo].[usp_MV_WorldRegionBPProvedReserves]
      ,CAST([Thousand Million Barrels] AS INT) AS [Thousand Million Barrels]
  FROM [SQLBits].[dbo].[WorldRegionBPProvedReserves]
 Country IN
‘Canada’,‘Iran’,‘Kazakhstan’,‘Kuwait’,‘Mexico’,‘Other Europe & Eurasia’,‘Russia’,‘Saudi Arabia’,‘United States of America’

Once you’ve created the stored procedure, you should test that it works so far by executing it:

EXEC [dbo].[usp_MV_WorldRegionBPProvedReserves]

Et Voila! You should see the data. If you don’t see the data, then you will need to resolve this issue before you try to move forward.

3.      Create a new Project, Data Source and Dataset

Once you are confident that the stored procedure is returning data, it is time to create a new project, with a corresponding data source and a dataset. If you need information on how to do this, then please refer to the Microsoft tutorials for clear explanations on how to create a new project, data sources and datasets.
Once these have been created, add a new report to the project, and give it an appropriate name.

4.      Using the Matrix Component to display and group

Use the Matrix component; simply drag it from the toolbox, and pop it onto the canvas. I like to name my components, so I am going to call this one Matrix_WorldRegionBPProvedReserves.
Take the chart component, and drag it to the matrix cell. Select a 2D Bar Chart.
Then, the bar chart will need to be populated with data. We will display the data by region and by year.

The grouping section of the SSRS report should appear as follows:

1. Grouping of Rows and Columns

In other words, the rows should be grouped by years, and the columns should be grouped by columns.

5.      Populating each chart with data
The next step is to populate each chart with data. For the values, we want to display the number of barrels measured in thousand million units. For the category grouping, we want to select ‘Country’. Here is a sample picture:

2. Populating the chart with data

6.      Data Visualisation – making the trellis consistent

Remove the legend from each chart – it gives the data more room to ‘breathe’, and is unnecessary repetition. If it helps, make it clear in the title, or create a new legend outside of the individual charts. Removing the unnecessary repetition means that the data/ink ratio is maximised, since unnecessary redundant information is removed.

Remove the chart title from each chart – again, this is unnecessary repetition. The chart title takes up space, and it can be easily placed outside the trellis structure.

Remove the axis title – again, this is repeat information. In this case, it is obvious that the axis is a country, and there is no need to add it in.

The Axis scale needs to be made consistent – if not, it will confuse the users. If this is done automatically, it will vary from chart to chart. However, it is important that the axis sizes all match correctly, or the user will get incorrect information. To adjust the axis size, right click on the axis and set the maximum value to be the same for all charts.

7.      Interpreting the Report

One of my favourite quotes from ‘Now you See it’ by Stephen Few is that ‘comparison is the beating heart of analysis’. This is one of my mantras for data visualisation. It’s clear from the trellis example that, if the charts were all different sizes, it would be very difficult to compare. However, by ensuring that the X-axis of the individual charts all match, then it is comparison is facilitated. Put another way, if the X-axis for each chart does not match, then the data navigator may misinterpret the data.

When we preview the report, it should appear as follows. If you can’t see it properly, then please click here to go to flickr:


The data is interesting. If you look at the data for Mexico, for example, then you can see that the expected resources have decreased from 1989 to 2009. Since the trellis chart allows us to compare up and down easily, we can see changes over time. We can also see a similar trend for ‘Other Europe and Eurasia’, which is a combination of the resources for some smaller European countries. 

We can also compare ‘across’ the table as well as ‘down’ the table. For example, Russia, Iran and Canada have increased their expected resources from 1989 to 2009.

This chart could be further improved by adding in colour to show the maximum value, for example, or by adding in more countries to look for further patterns. To summarise, the trellis chart can be a useful narrative for displaying data that is multidimensional in nature, and means that 3D is not necessary. By using the analogy of reading a comic, it is hoped that the trellis chart can be more easily understood and introduced to people who may not be familiar with the concept.

Trellis Display from Bell LabsVisualizing Data by William S. Cleveland (1993)Envisioning Information by Edward R. Tufte (1990). This provides a clear explanation of the ‘Small Multiples’ concept, as well as the ‘data ink’ ratio.

SQLBits: Follow-Up from Data Visualisation Sessions

I recently helped out at SQLBits by delivering a pre-conference day on Data Visualisation in SQL Server Reporting Services, a shorter one-hour session on Data Visualisation on Reporting Services, spoke briefly at two SQLBits sponsor sessions for Attunity ( blog | twitter ), and a ten-minute Lightning Talk on 3D graphs. So, I’ve been a busy girl, but it was great fun. Before we start, I’d like to thank the SQLBits Committee members for their support and hard work on putting on an outstanding SQL Server event for the community.
The purpose of this blog is to clear up some of the general questions that I received, provide a pointer to my slides, and to provide a forum for anyone to directly ask questions or provide comments or feedback on my sessions. 
Data Visualisation Precon and Abbreviated Version
For the Data Visualisation sessions, I had a great crowd at each session who asked some brilliant questions. More detailed questions will be answered over the course of my next blogs, since they require more detail than I’ve been able to provide here. In the meantime, you can download my slides from the SlideShare website. 
Every Friday at 1pm EST – that’s 6pm for us here in the UK – Howard Dresner ( blog | twitter ) and the BIWisdom team use Twitter in order to chair a discussion on business intelligence topics. The team throw out a question every Friday, and then the Business Intelligence twitterati join in. For example, the session on 15th April 2011 focused on SaaS and its future in Business Intelligence. For example, is it at tipping point? If not, when is the tipping point reached?
If you’re interested in joining in, or just lurking on the conversation, the hashtag is #BIWisdom. For me, this is the highlight of my Twitter week and I learn so much from my peers since the conversation is extremely thought-provoking.
This week, the main contributors were Howard Dresner ( site | Twitter ) and the BI Wisdom ( Twitter ) team, along with Flying Binary (site | twitter ), and Warren Hart (site | twitter).
Book References
I thought it worthwhile noting some useful book references to support my commentary on data visualisation. I hope that these links are useful as a starting point for people who would like more information.
Stephen Few References
Any book by Stephen Few is an excellent, informative discussion on data visualisation. I usually recommend ‘Now You See It‘ because I believe it is well-written and instructive. Best of all, it’s one of these books where you learn something, without realising that you’re learning. Stephen Few seems to have the knack for writing in such a way that the reader is intaking the information, without realising that they are learning. For me, that’s the art of a true teacher and it is my ambition to be able to write in the same way. 
I’d also recommend his blog for a great read. 
Edward Tufte References
Tufte’s book ‘The Visual Display of Quantitative Information‘ is another one of these books where the reader learns by stealth, reading and not realising that they are taking in complex information. This book covers good and bad examples of design, and cogently argues the case for good visualisation. 
Nathan Yau References
I love the Flowing Data site, and Nathan Yau’s book is being released in the UK in the near future. His book is entitled ‘Visualise This: The Flowing Data Guide to Design, Visualization and Statistics‘. I will be obtaining my copy as soon as it is released in July 2011!
SQLBits Feedback
I’m obsessed with my SQLBits speaker feedback; I like to visualise it, analyse it, and then work out what the data is telling me so I can improve for next time. So far, I’ve found kind comments on my Lightning Talk on ‘3D or not to 3D’ Data Visualisation, from Luke Merrett ( blog | twitter ), who summarised it nicely; I was particularly glad of this because it told me that I managed to get the main point across in the tight 10-minute slot.

I hope that helps!

SSRS Red-Yellow-Green Indicators: An Alternative Approach?

The release of SQL Server Reporting Services 2008 R2 was accompanied with much fanfare regarding the new gauges that were available. This blog will look at different ways of implementing KPIs that do not require the gauges or indicators that are available. The reason for this is that, by default, the SSRS Indicator gauges do not always follow the best principles for data visualisation as expressed by experts such as Stephen Few or Edward Tufte. As a note, this blog was part of a SQLPass webinar given by the current author for the ‘Women In Technology’ SQLPass 24 hop webinar series, given in March 2011.

In order to be clear, in SSRS, Indicators are minimal gauges that display the state of a single data value. It is intended that this information is taken ‘at-a-glance’. The icons that represent indicators and their states are simple, for example, traffic light indicators. They are used to display trends, states, ratings or conditions. In the SQL Server Performance Dashboard example we will look at, the Indicator is used to display ‘Wait Time’ for a SQL Server.

Before we dive into the implementation in SSRS, let’s take a look at the Indicator gauges. Here is an example of the indicators that are available:

A. Default KPI Indicators

Figure 1 Default KPI Indicators in SSRS

In terms of data visualisation, these traffic lights could be improved by taking on board the following points:
The Red-Yellow-Green colour system is not good for people that are colour blind. Approximately 12% of men, and 1% of women, are colour-blind (Few, 2008). Stephen Few’s rule number 8 specifies that ‘To guarantee that most people who are colour-blind can distinguish groups of data that are color-coded, avoid using a combination of red and green in the same display’.  However, for people who are short-sighted with very strong glasses, it is possible that they can experience a strong effect in that off-axis viewing of objects away from the centre of the lens. Specifically, this can result in prisms, and the colours separate. This is known as chromatic aberration, and is the glasses wearer experiences a color fringing affect around strongly contrasting colours, which can be distracting for the eyeglass wearer. It may well be worth pointing out that near-sighted contact lenses wearers do not experience chromatic aberration because the contact lens moves with the cornea, thereby eliminating the chromatic effect.

Given that Red-Yellow-Green is a problematic combination, what can we do to resolve this issue? Stephen Few (2008) recommends using red and blue instead; this is perceptually distinct enough to carry the message of the data visualisation, whilst reducing the impact on colour-blind and very near-sighted individuals.

The current example will use the SQL Server Performance Dashboards to display indicator materials, and will only show Red if there is an issue; otherwise, it will not show anything. Instead of using the default Indicator gauges, it was decided to keep the visual representation as simple as possible. Thus, a small red square is used.  Since the red square is only present if there is an issue, this can be viewed as reducing the chartjunk on the page since only essential items are shown. Further, by keeping the display to a minimum, the data/ink ratio is reduced. 
In this article, we will improve one of the SQL Server Performance reports to show more visual information. The original Historical Waits Report looks like this:

Historical Waits Default Report

Figure 2 Original Historical Waits report

One thing we can do to improve this report is to use colour in order to convey a message about the information. From the above report, we can see that the top ‘% Wait Time’ is set at 76.93%. It is possible to use some colour in order to convey the highest wait time, and this is the purpose of the current article.
For this article, the following is assumed:

The SQL Server Performance Dashboards have been downloaded from Microsoft Download Centre
The SQL Server Performance Dashboards script has executed successfully: in order to do this, the column ‘cpu_ticks_in_ms’ needs to be changed to ‘ms_ticks’. Intellisense will help you to identify the incorrect column.
1.      Import the SQL Server Performance Dashboards into a SQL Server Project.
2.      Open the ‘Historical Waits.rdl’ report and navigate to the column entitled ‘% Wait Time’. This is outlined in the blue box in the following diagram:

2.  KPI Text Box to change

Figure 3 Sample Text Box for amendmen

3. Right-click the box and select ‘Text Box Properties’. We will need to do two things: we will change the value of the textbox from the expression to a ‘o’. Then, we will change font to WingDings in order to obtain a nice ‘o’ shape using the ‘o’ that we typed in first.

In order to change the value of the Textbox to a ‘o’,  type ‘o’ in the Values box for the Text Box properties. This can be seen in the following diagram:

3. KPI Value o

Figure 4 Changing the value of the Textbox
4. Now it is time to change the font to Wingdings. This will give us a simple square, rather than a trumpeting arrow as per the SSRS Indicator gauges. This can be seen in the following image:

4. KPI Wingdings font change

Figure 5 Change Font to WingDings
      Once this has been done, click ‘Ok’.
5. Now it is time to update the colour of the square indicator in order to reflect the status of the Wait Time. This rule specifies that, if the wait time is greater than or equal to 70%, then the textbox show a red square; otherwise, nothing is displayed. In order to ensure nothing is displayed for values that are less than 70%, the square is set to white; this means it will be invisible.
In order to update the colour, we will copy and paste the following formula into the ‘Color’ box of the ‘Properties’ window. If the ‘Properties’ window is not showing, click on the textbox, and then choose the ‘F4’ button. This will reveal a dialog box as follows:
5. Color Property change
Figure 6 KPI Textbox Properties Window
6.      Choose the ‘Color’ item. Clear the contents of the expression editor window, and copy and paste the following in its place. When this is done, click OK.

(Sum(Fields!wait_time.Value) / sum(Fields!wait_time.Value, “DM_OS_WAIT_STATS”))
>= 0.7, “Red”,
(Sum(Fields!wait_time.Value) / sum(Fields!wait_time.Value, “DM_OS_WAIT_STATS”))
< 0.7,
  Save and preview the report. The report should appear as follows:
6. Completed Report
Figure 7 Completed Report
It is now clear that, instead of percentages, there is a small indicator which specifies that the ‘Other’ Wait Category exceeds a specified criterion.
Stephen Few (2008). Practical Rules for using Colour in Charts. Perceptual Edge, February 2008.