15 Replies Latest reply on May 17, 2017 3:08 PM by msarkar

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

    ouberlord

      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?

        • Re: Unable to add data series for a report's SQL-driven custom chart
          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

            • Re: Unable to add data series for a report's SQL-driven custom chart
              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?

                • Re: Unable to add data series for a report's SQL-driven custom chart
                  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.

                    • Re: Unable to add data series for a report's SQL-driven custom chart
                      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.