Unable to add data series for a report's SQL-driven custom chart

Hello,

I am having issues with figuring out how to use the Custom Chart report functionality, specifically when trying to populate the chart using data taken from a SQL query. Here is the query I am using as a data source:

SELECT [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[DateTime],  'AP Name' = CASE WHEN Right([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID],'1') = '0' THEN ([SolarWindsOrion].[dbo].[CustomPollerStatus].[Status] + ' 0') ELSE ([SolarWindsOrion].[dbo].[CustomPollerStatus].[Status] + ' 1') END,  [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[Status]  FROM [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]  INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]  ON Left([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID],Len([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID]) - 2) = [SolarWindsOrion].[dbo].[CustomPollerStatus].[RowID]  WHERE [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'  ORDER BY DateTime

This gets me output that looks like the following:

DateTime AP Name Status

2013-10-09 18:35:23.417 AP-3333-3-C 0 3

2013-10-09 18:35:23.417 AP-3333-3-C 1 0

2013-10-09 18:45:23.447 AP-3333-3-C 0 0

2013-10-09 18:45:23.447 AP-3333-3-C 1 0

2013-10-09 18:55:23.443 AP-3333-3-C 0 5

2013-10-09 18:55:23.443 AP-3333-3-C 1 0

The idea is that I want to create a line on the chart for each AP name, with data points of the Status (Y-axis) plotted along their corresponding time (X-axis). The preview of the data source works, but when I edit the chart and select "Add Data Series" for the Y axis it shows no results, and oddly the "Orion Object" dropdown is set with a sole available option of "Group name".

Any ideas what I'm doing wrong?

Find more posts tagged with

custom_charts

Reports

network_monitoring

data_series

sql

network_management

report

Accepted answers

LadaVarga

Hello ouberlord,

You don't see anything in Add data series dialog?

[SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[Status] is nvarchar type. Please retype to int for example:

cast([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[Status] as int) as STATUS

After adding it. You have to pick time column and ObjectID column (it will be 'AP Name').

If you have any other question please ask.

Thanks

All comments

LadaVarga

Hello ouberlord,

You don't see anything in Add data series dialog?

[SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[Status] is nvarchar type. Please retype to int for example:

cast([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[Status] as int) as STATUS

After adding it. You have to pick time column and ObjectID column (it will be 'AP Name').

If you have any other question please ask.

Thanks

ouberlord

That certainly got me closer, but I've hit another hurdle.

After your suggested change I was able to add the newly int-converted Status column as the Y-axis data series. I then clicked "More" for that data series and selected "DateTime" for the time column. Finally, I added "AP Name" for the Object ID column since there are multiple objects in the data source.

However, when previewing the resource or viewing the report I just get the "Data is not available" message on the chart. I even tried changing the DateTime column to be the datetime variable type, but that seemed to have no effect.

Any ideas?

LadaVarga

Do you have enough data on that datasource? (try remove 'ORDER BY'' statement)

Try change sample interval to bigger or smaller.

Try add all columns in custom table and look how much data do you have.

ouberlord

Removing the ORDER BY doesn't seem to have an impact, nor does changing the sample interval. I've tried adding in other columns from the same table, and still get no data though they do show up as additional options when choosing to create a data series.

My SQL query now looks like this:

SELECT CAST([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[DateTime] AS datetime) AS DateTime,
 'AP Name' = CASE WHEN Right([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID],'1') = '0' THEN ([SolarWindsOrion].[dbo].[CustomPollerStatus].[Status] + ' (G)') ELSE ([SolarWindsOrion].[dbo].[CustomPollerStatus].[Status] + ' (A)') END,
 [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RawStatus] AS Clients
 FROM [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail]
 INNER JOIN [SolarWindsOrion].[dbo].[CustomPollerStatus]
 ON Left([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID],Len([SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[RowID]) - 2) = [SolarWindsOrion].[dbo].[CustomPollerStatus].[RowID]
 WHERE [SolarWindsOrion].[dbo].[CustomPollerStatistics_Detail].[CustomPollerAssignmentID] = '6C4E621B-A7D3-439C-8402-D692BE67743A'

What makes it odd is that the data source preview shows expected data; seemingly the top 15 lines of the results. It's just that the chart is for some reason unable to make use of it.