Jump to content
Software FX Community
PSaul

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...