For this month’s T-SQL Tuesday I thought I’d go with something useful that I’ve been meaning to blog about for weeks now. While listening to the PowerScripting Podcast a few month’s back I heard about a product that sounded really cool for simple data visualization and quickly showing people just how powerful PowerShell is. Now to get this to work using all the code on the screen you’ll at least have to download the trial version of of the Power Gadgets however even without the method I describe is important if you’re new to this type of reporting (desktop gadgets).
Turns out this little piece of software had even more packed into it than I was hoping for. I needed a piece of software that could allow me to pipe data to it to display it in a desktop gadget as a list.
Before I show you how to setup this quick-and-easy tool to create desktop gadgets I want to issue a warning against setting up this type of thing directly against your production database; read all the way through or don’t blame me when you bring your production environment down.
What you want to do (at the very least) is to have all of the gadgets that you deploy read from a central location where they only have to scoop up the results of the query that you’re trying to report on, and not 400 gadgets running the query itself. An easy way to accomplish this would be to run the query in an Agent Job against your production environment (assuming that you don’t have a live reporting copy) and deposit those results in a table. Let’s see if we can step through an example with the AdventureWorks database.
If this below were your query to show your sales for today…
SELECT SUM(SOD.OrderQty) AS ‘OrderQty‘, s.StateProvinceCode
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product P
ON SOD.ProductID = P.ProductID
JOIN Person.Address A
ON SOH.BillToAddressID = A.AddressID
JOIN Person.StateProvince S
ON A.StateProvinceID = S.StateProvinceID
WHERE SOH.OrderDate = ’2001-07-01 00:00:00.000′ –<—This would get GETDATE() or something
GROUP BY S.StateProvinceCode
…you would really want to create an Agent Job to load that into a table and select off of the table. For simplicity we’ll leave out the job and just use the queries.
/* <Do this part only one time>*/
USE [AdventureWorks]
GO
CREATE SCHEMA [Report] AUTHORIZATION [dbo]
GO
CREATE TABLE [Report].[TodaysSalesOrderByState](
[OrderQty] [int] NULL,
[StateProvinceCode] [nchar](3) NOT NULL,
[LastLoadTime] DATETIME NOT NULL CONSTRAINT [DF_LastLoadTime] DEFAULT GETDATE()
) ON [PRIMARY]
/* </Do this part only one time>*/
Then this…
/* <This is the part that would eventually go into an agent job, along with a delete statement before it that was in the same transaction>*/
INSERT INTO Report.TodaysSalesOrderByState (
[OrderQty],
[StateProvinceCode])
SELECT SUM(SOD.OrderQty) AS ‘OrderQty‘, s.StateProvinceCode
FROM Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product P
ON SOD.ProductID = P.ProductID
JOIN Person.Address A
ON SOH.BillToAddressID = A.AddressID
JOIN Person.StateProvince S
ON A.StateProvinceID = S.StateProvinceID
WHERE SOH.OrderDate = ’2001-07-01 00:00:00.000′ –<—This would get GETDATE() or something
GROUP BY S.StateProvinceCode
/* </This is the part that would eventually go into an agent job>*/
Now you’ll want to create a stored proc that will be used to call the report data. It will also make creating the report nice and clean.
CREATE PROCEDURE [dbo].[rp_MapThat]
AS
SELECT [OrderQty]
,[StateProvinceCode] AS ‘State’
FROM [AdventureWorks].[Report].[TodaysSalesOrderByState]
GO
After that all you have to do is to go ahead and create the gadget using this little script:
#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100
#add the Gadgets Snapin
add-pssnapin PowerGadgets
invoke-sqlcmd -query "EXEC dbo.rp_MapThat" -database AdventureWorks -serverinstance WIN7\KILIMANJARO | out-map -values OrderQty -label State -title "CountByState" -refresh 0:1:0
And now you’ve got a gadget prototype. I’m sure you can figure out how to turn it into an operational gadget for your company.
). At the end of the day life is about priorities and I’ve finally figured mine out. So until my daughter stops letting me drop her off at school, play taxi-cab for her and her friends, and generally allows me to be seen with her in public… well, I’m not going to have a lot of free time to do much else 
