Get-Sales | Out-Map = T-SQL Tuesday #005 – Reporting

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.

image

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.

 

 

LiveJournal Tags:

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

4 Responses

  1. 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.

  2. 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.

    1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner