SSRS : experimenting with sparklines

Hi,

Today a post about sparklines in SQL Server Reporting Services. As reading in the book ""SSRS recipes" from Paul Turley and Robert Bruckner, sparklines are invented by Edward Tufte. Sparklines are "small word sized graphics that are embedded in a context of words, numbers and images". I used the following query to experiment with. You can download this from the WROX site.

SELECT
    SUM(t.ExtendedAmountSum) AS ExtendedAmountSum
    , t.EnglishProductCategoryName
    , t.CalendarYear
    , t.MonthNumberOfYear
FROM
(
    SELECT
        SUM(FactInternetSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactInternetSales
    INNER JOIN
          DimProduct ON FactInternetSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
          DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
          DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
          DimDate ON FactInternetSales.OrderDateKey = DimDate.DateKey
    GROUP BY   
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    UNION ALL
    SELECT
        SUM(FactResellerSales.ExtendedAmount) AS ExtendedAmountSum
        , DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
    FROM
        FactResellerSales
    INNER JOIN
          DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey
    INNER JOIN
          DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
    INNER JOIN
          DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey
    INNER JOIN
          DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
    GROUP BY   
        DimProductCategory.EnglishProductCategoryName
        , DimDate.CalendarYear
        , DimDate.MonthNumberOfYear
) t
GROUP BY   
        t.EnglishProductCategoryName
        , t.CalendarYear
        , t.MonthNumberOfYear
ORDER BY
    CalendarYear, EnglishProductCategoryName

1. And i started building the following report. Insert a table into the report body and delete the original row group and associated rows. Drag the calendar year and english productcategory into the row groups window. Drag the field extendedAmountSum on the data fields region.

Running the report will show this:

Now drag a sparklines component from the toolbox on the report body. Setup the fields calendaryears and the Salesamount for the sparklines.

And drag the sparkline into the tabel and run the report and you will get something like this:

If you change the sparkline type to "smooth area" and you get this:

 Change the chart type to pie and you get this:

I've some difficulty understanding what's length of the period of a sparkline. With a marker points you get a marker for every row for  grouped records. For instance for 2005 and accessoires there are 6 months, 6 records and 6 markerpoints. Playing with background colors and markerpoints and you get this:

So that's it with playing with sparklines.

Greetz,

Hennie