posted 7/1/2011 by SMcDonald - Views: [1730]
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
SET QUOTED_IDENTIFIER ON
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
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 1st 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 1st Color: =IIF(ReportItems!SalesAmtForMonth.Value>=Variables!MonthGoal.Value,"Green","Red")
For my second report, I created a report using the Map object. Here is the query that I used:
/****** Object: StoredProcedure [dbo].[GetCustomerSalesDetailsWebinar] Script Date: 06/30/2011 23:44:19 ******/
ALTER Proc [dbo].[GetCustomerSalesDetailsWebinar]
(
@CalendarYr smallint = 2007
)
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
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
Enjoyed the presentation. From Template creation to Report Creation to Sparkline to Linked Reports to Maps. Lots of details in between. Our Reporting Services team watched the demo together in a conference room. We think its time to upgrade from 2005 to 2008 R2 this summer.
Hi sherri
i attend your class. it was awesome . i really like it