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.
4 Responses
There are several code plugins for livewriter, but every one I’ve tried has been terrible. I’ll give that method a shot and see how it works on the different sites.
The best method I’ve found before this was to use tags and then use server plugins to have that format the Sql code. That’s kindof a pain though, as you need to enter HTML mode, manually enter tags, paste from SSMS and then you’re at the mercy of plugin coloring, which leaves a lot to be desired.
Is that a wordpress plugin that you use for your code? I’m still looking for something that handles SQL well and that coloring is by far the best I’ve seen so far.
Ha! It’s actually Word.
What I do is copy the code out from SSMS 2008 R2 > Paste it into a MS Word Document > then I copy it from Word to Windows Live Writer as HTML (not thinned HTML). I have found this to be the best process for me. Copying straight from SSMS to Live Writer doesn’t seem to work right. I sometimes use the Simple-Talk Prettifier but only when blogging remotely.
There is a code plug-in for Windows Live Writer as well; I’ll see if I can find a link to that.