SSRS 2008 R2 June Webinar Details

Today I gave a presentation on how to create some awesome reports using the Sparkline, Map and Data Bar (all R2 features).  I also discussed some of the different areas you could use expressions and showed some different examples. 

I am really excited that I had so many people join us today for Training on the T’s.  First I want to share the TSQL script that I used for the my Sparkline report as well as the expressions used for that specific report.

Queries (AdventureWorksDW2008R2):

USE [AdventureWorksDW2008R2]

GO

/****** Object:  StoredProcedure [dbo].[usp_GetSalesByDay]    Script Date: 06/30/2011 23:01:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_GetSalesByDay]

/*********************************

Return a listing of sales by day

Sample Executions:

     usp_GetSalesByDay

*********************************/

AS

SELECT

     d.[CalendarYear] AS [Year]

     , d.[CalendarQuarter] AS [Quarter]

     , d.[MonthNumberOfYear] AS [Month]

     , d.[DayNumberOfMonth] AS [Day]

     , CONVERT(VARCHAR(12),d.[FullDateAlternateKey],101) AS [OrderDate]

     , SUM(f.[SalesAmount]) AS [SalesAmtForDay]

FROM

     [AdventureWorksDW2008R2].[dbo].[DimDate] d

     JOIN [AdventureWorksDW2008R2].[dbo].[FactInternetSales] f ON d.DateKey = f.OrderDateKey

     JOIN DimCustomer AS DC ON f.CustomerKey = DC.CustomerKey 

     JOIN DimGeography AS DG ON DC.GeographyKey = DG.GeographyKey

WHERE

     DG.CountryRegionCode = 'US'

GROUP BY

     d.[CalendarYear]

     , d.[CalendarQuarter]

     , d.[MonthNumberOfYear]

     , d.[DayNumberOfMonth]

     , CONVERT(VARCHAR(12),d.[FullDateAlternateKey],101)

ORDER BY

     d.[CalendarYear]

     , d.[MonthNumberOfYear]

     , d.[DayNumberOfMonth]

Here are the expressions that I used:

The expression MonthName is going to return the Abbreviated Month instead of the numerical value that represented the Month.  Remember, if you put False instead of True it will bring back the full month name.  I placed this expression in the field where I brought back the month number.

=MonthName(Fields!Month.Value,True)

Add ToolTip expression on Yr column RC on Year (text box properties):

  ="View Customer Sales for " + cstr(Fields!Year.Value)

We created 2 variables that we referenced in our expressions:

YearGoal – 1000000

MonthGoal – 200000

Right click on Year Sparkline, go to Series Properties / Go To FILL  / Fill Style is GRADIENT

Click FX for 1^st Color:  =IIF(ReportItems!SalesAmtForYear.Value>=Variables!YearGoal.Value,"Green","Red")

Secondary Color should be NO Color (Default)

Gradient Style:  Top to Bottom

Right click on Month Sparkline, go to Series Properties / Go To FILL / Fill Style is GRADIENT

Click FX for 1^st Color:  =IIF(ReportItems!SalesAmtForMonth.Value>=Variables!MonthGoal.Value,"Green","Red")

Secondary Color should be NO Color (Default)

Gradient Style:  Top to Bottom

For my second report, I created a report using the Map object.  Here is the query that I used:

USE [AdventureWorksDW2008R2]

GO

/****** Object:  StoredProcedure [dbo].[GetCustomerSalesDetailsWebinar]    Script Date: 06/30/2011 23:44:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Proc [dbo].[GetCustomerSalesDetailsWebinar]

(

@CalendarYr smallint = 2007

)

AS

Select

     DD.CalendarYear

     , SUM(SalesAmount) as YrlySalesAmt

     , SUM(OrderQuantity) as YrlyOrderQty

     --, DD.EnglishMonthName

     , DG.StateProvinceName

From DimDate as DD

     Inner Join FactInternetSales as FIS

     On DD.DateKey = FIS.OrderDateKey

     Inner Join DimCustomer as DC

     On FIS.CustomerKey = DC.CustomerKey

     Inner Join DimGeography as DG

     On DC.GeographyKey = DG.GeographyKey

Where DD.CalendarYear = @CalendarYr

     and DG.EnglishCountryRegionName = 'United States'

Group By

     DD.CalendarYear

 , DG.StateProvinceName

We create a report that located the states that our customers were in based on the year that the purchases came.  Here are some of the expressions I used in this report:

Map Title expression:  

=Parameters!CalendarYr.Value & " Customer Sales By States"

Map Polygon Properties

Create tooltip expression:  = FormatCurrency(Fields!YrlySalesAmt.Value)

Pull States / Orders / Sales columns in your table:

Add column to the right (Call it Avg Monthly Sales Amt) and create an expression

=Sum(Fields!YrlySalesAmt.Value) / 12

Click on Row Details add Expression in Fill Color:

=iif(RowNumber(Nothing) Mod 2,"AliceBlue","White")

Here is the link to watch it:

 http://pragmaticworks.com/Resources/webinars/Default.aspx

Here is the link to watch it:

 http://pragmaticworks.com/Resources/webinars/Default.aspx