How to have Radial Gauges pull values from SQL query (innergauge, maxscale, etc)

[PSaul]

Hello,

I'm trying to make some Radial Gauges in PowerGadgets that pull data from a SQL database. I'm using the PowerGadgets Creator create PGF files that connect to SQL and for 1 value it's simple enough. Works fine. But I am attempting to do something more complex: I would like to create a gauge that shows our Quarterly Sales, with the Quarterly Sales Goal as the max value, with an InnerGuage with the Monthly Sales and Monthly Sales Goal as max.

It's a sort of two part question:

1) I would like to pull in not just the mainvalue from the SQL query (the needle) but also the mainscale_max (the maximum value for the dial). Right now the Gauge just uses the first returned value from SQL. How do I make it use the 2nd returned value for the mainscale_max value?

2) Once I get that going, or even I have to forgo that and manually adjust the max value every quarter in the PGF file: How do I set up an InnerGauge that pulls from SQL? (when ever I add an innergauge it's just a static dial)

I have been looking at the documentation and QuickStartGuide. I managed to do something very close to what I want using PowerShell, I combined 2 examples and came up with this:

get-wmiobject Win32_PageFileUsage | select CurrentUsage,AllocatedBaseSize,PeakUsage | out-gauge -MainValue {$_.CurrentUsage} -MainScale_Max {$_.AllocatedBaseSize} -refresh 0:0:1 -InnerGauges_Add Radial -InnerGauges_0_RadialGauge_Value {$_.PeakUsage} -InnerGauges_0_Layout_Alignment BottomCenter -InnerGauges_0_Size 0.8

It queries WMI, returns 3 values, uses the 1st for the main needle, the 2nd for the main dial's max value and the 3rd for the innergauge. (I assume I could probably return a 4th and do the innergauge's max from that).

Then I used the Ctrl+C and Ctrl+V trick from:

 and made a PGF file that works like the above PowerShell command.

How can I make that work using straight SQL? Looking in the PGF file (which is XML) I see this in the Window section at the end:

  <Data>PowerGadgets.Commands.PowershellSource</Data>

  <Data.PrevCommands>get-wmiobject Win32_PageFileUsage | select Name,CurrentUsage,AllocatedBaseSize,PeakUsage</Data.PrevCommands>

  <Data.CurrentLocation>FileSystem::C:\</Data.CurrentLocation>

  <Data.SnapIns>PowerGadgets</Data.SnapIns>

  <Data.PropertyCount>3</Data.PropertyCount>

  <Data.Property0>mainvalue</Data.Property0>

  <Data.Property0ScriptBlock>$_.CurrentUsage</Data.Property0ScriptBlock>

  <Data.Property1>mainscale_max</Data.Property1>

  <Data.Property1ScriptBlock>$_.AllocatedBaseSize</Data.Property1ScriptBlock>

  <Data.Property2>innergauges_0_radialgauge_value</Data.Property2>

  <Data.Property2ScriptBlock>$_.PeakUsage</Data.Property2ScriptBlock>

Is there a reference for the PGF/XML properties? If I could use Data.Property0SQL or something to assign to the various mainvalue, mainscale_max, innergauge, etc properties then it would work just fine.

I suppose I could construct a PowerShell script that generates the query, but it would really just be a single invoke-sql command, so it seems pointless cumbersome.

Seems like it can be done or should be doable, has anyone done something like this? Does anyone have a reference for the various Data.Property values for SQL in the PGF file?

Any help appreciated!

--Saul

[JuanC]

Unfortunately in PowerGadgets 1.0 (current version) you can only populate other properties such as mainscale_max or an innergauge property from data when using PowerShell. The only work around at the moment would be very similar to your experiment, e.g.

- invoke-sql ... | out-gauge

- Use Copy-Paste trick to create a PGF

Note that this PGF can be later edited in the creator to adjust the visual appearance, the drawback is that PowerShell needs to be installed in the machine where the PGF will be viewed.

We will support populating other properties directly from SQL in our next version but I am not sure if we will expose UI to achieve this. For now if you are using Powershell as the data source you can manually edit the PGF

Data.PropertyCount: Number of extra properties that should be populated from a column in the data

Data.PropertyX: Name of the property to modify, note that _ is used for subproperties

Data.PropertyXScriptBlock: Powershell script block, typically $_.ColumnName although you could also write any valid powershell expression.

Hope this helps.

JuanC