Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Getting Started with SparkLines in ReportBuilder 3

First you’re going to need the download and install SQL Server 2008 R2 November CTP.  My step by step guide on doing that is here (which stil works with the November CTP)


Now you’re going to need to have a copy of the adventure works database  from Codeplex I have used the version designed for use with SQL Server 2008 R2.  You’ll need to enable FileStream support in SQL Server configuration manger if you use this one…


image


(right click on the instance to bring up this properties page).


You should now be able to go to  Report Manager which is the web portal that comes with reporting services and should be at http://myserver/reports and will look like this..


image


 


except that yours will have no content.


From the toolbar in the portal you’ll need to select New Data Source to make a connection to get the data from..


image


I have not followed best practice here because I have used a very privileged account, but this is just a demo!


I have also created a folder called data sources to keep all of these in, but this is optional.


Now we can start to use Report Builder 3, by selecting that from the toolbar..


image


 


and this in turn launches a wizard.  One of the new things in Reporting Services in SQL Server 2008 R2 is the ability to share data sets as well as data sources, and I am going to do that as part of this walkthrough be selecting the create shared dataset option I’ve highlighted.


I can now choose which columns I want to have in my dataset..


image


But be careful if you include calendar and sales in your query as I have done as the friendly query designer in Report Builder will try and join these table on three fields.. so just use the one join i.e. cut out the stuff in red..


SELECT
  DimProduct.EnglishProductName
  ,DimProductSubcategory.EnglishProductSubcategoryName
  ,DimProductCategory.EnglishProductCategoryName
  ,DimTime.CalendarYear
  ,DimTime.CalendarQuarter
  ,DimTime.EnglishMonthName
  ,DimReseller.ResellerName
  ,FactResellerSales.SalesAmount
  ,FactResellerSales.OrderQuantity
FROM
  DimReseller
  INNER JOIN FactResellerSales
    ON DimReseller.ResellerKey = FactResellerSales.ResellerKey
  INNER JOIN DimTime
    ON DimTime.TimeKey = FactResellerSales.OrderDateKey AND DimTime.TimeKey = FactResellerSales.DueDateKey AND DimTime.TimeKey = FactResellerSales.ShipDateKey
  INNER JOIN DimProduct
    ON DimProduct.ProductKey = FactResellerSales.ProductKey
  INNER JOIN DimProductSubcategory
    ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
  INNER JOIN DimProductCategory
    ON DimProductCategory.ProductCategoryKey = DimProductSubcategory.ProductCategoryKey


Run the query (the exclamation mark) to check it’s returning meaningful data. Now you can save this be selecting the disk tool (just like in any office 2007 product), and you ‘ll be asked to give it a name so call it ResellerSales.  


This can now be used in a new report so from the the Report Builder icon (top left – again like office) select New  bring back the startup wizard again.  This time select new Table or Matrix Wizard..


image


Browse to find the DataSet you just made..


image


and click next to bring up the layout..


image


click on the image above to make it larger so you can see how to arrange your data exactly as I have. Click Next when you have


image


go with the defaults here and for the next screen (the theme)..


image


Click finish and you should see the layout you have created in the design view of RB3..


image


To sharpen this up you’ll want to


highlight all the numbers and set there format to C2 or N2 (currency or a number to 2 decimal places)..


widen all the columns


and rename the product columns to Category, Sub Category and Product..


image


If you are already familiar with Report Builder we can now start the new stuff by adding SparkLines to this..


First create a new column by selecting the product column right clicking and add column –> right like this..


image


Now select the insert ribbon and you’ll see the new SaprkLines..


image


Click on it and then click on the lightest blue cell in the new column which will bring up the SparkLines Wizard


image


I’m going to choose this one..


image


and I’m simply going to click OK. You should see a little preview in the cell you selected ..


image 


But before it will work you need to tell it what data to use so click on it..


image


and click on the plus signs to add the fields you want to use. I have selected OrderQuantity for the values and Calendar Year for the Category Groups.


Now for the clever bit. highlight that cell and select copy and then paste it to the three rows below..


image


No you can run it..


image


Notice that as it expands each level of the hierarchy has a SparkLine for the quantity sold each year.


I have uploaded the report and the shared dataset to Skydrive  for you, but you’ll have to create your own datasource to the adventure works database to use them.