16 Replies Latest reply on Mar 5, 2010 1:38 PM by Ballzo

    Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky

    Ballzo

      So I sent the default report Daily Node Availability - Last Month to my boss and he liked it but wanted me to limit it to just the devices that were not 100%. So I modify the report and just added a filter Availability is less than 100. Doing this for some reason changes the results the average availability % in the report. I saw this problem posted on another tread so I am trying to enter some of my own sql queries from that thread to see if they work and find another problem. I cannot edit or add any sql code in the report writer at all. Any ideas on either issue????

       


      Ballzo

      Sacramento, CA

        • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
          njoylif

          make a copy of the report
          open the report in notepad/wordpad/etc and edit the query directly

          OR

          copy the SQL from the original query and recreate as custom SQL report

          • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
            kweise

            Ballzo,

            I just ran into the same issue the other day when I was creating a new report based on the default Daily Node Availability report.  I'm not much of a SQL writer, but I'd be willing to try the queries from the other thread too.  Can you post a link to the other thead?

            Thanks!

            • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
              kweise

              I think I figured it out.  You'll have to create a custom SQL report instead of trying to use the report wizard.  To do that, just open the default report, go to the Report header in Report Writer and select Show SQL.  That will add a SQL tab to the report in the Report Designer view.  Go to that tab, copy the SQL query and then create a new report.  In the new report window, select Advanced SQL.  Then paste the code you copied from the default report into the SQL tab in the new report design mode window.

              When you add the filter Availability less than 100, you aren't filtering on the average availability.  The WHERE clause becomes the following:

              WHERE
              ( DateTime BETWEEN 40208 AND 40235.9999884259 )
               AND 
              (
                (ResponseTime.Availability < 100)
              )

              In order to filter on the average availability, you have to add as HAVING statement under the GROUP BY clause.  You can't use the average of availability in the WHERE clause because the average calculation occurs after the WHERE conditions are evaluated.  If you add the following after the GROUP BY clause and before the ORDER BY clause, you should get the results you're looking for:

              GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
              Nodes.NodeID, Nodes.Caption, Nodes.MachineType, Nodes.IP_Address

              HAVING AVG(ResponseTime.Availability)<100

              ORDER BY SummaryMonth ASC, 6 DESC

              Hope this helps!

                • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
                  Ballzo

                  I think I understand why the values are not coming out correctly on my modified report and would like to try and change it but still when I do the show SQL option and go to the SQL tab, I am unable to make any changes to the query. The same thing when I try to create a new report and enter my own query, I cannot type or paste anything into the window.

                  Is there any chance that I can maybe change the report without modifying the sql query manually and worry about that problem later?

                   

                  This is what it looks like now in case you are wondering.

                   

                  SELECT  TOP 10000 CONVERT(DateTime,
                  LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
                  101) AS SummaryMonth,
                  Nodes.NodeID AS NodeID,
                  Nodes.VendorIcon AS Vendor_Icon,
                  Nodes.Caption AS NodeName,
                  Nodes.IP_Address AS IP_Address,
                  AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

                  FROM
                  Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)


                  WHERE
                  ( DateTime BETWEEN 40208 AND 40235.9999884259 )
                   AND 
                  (
                    (Nodes.Vendor <> 'Cisco') AND
                    (Nodes.Caption <> 'TSSD000') AND
                    (DailyNodeAvailability.Availability < 100)
                  )


                  GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
                  Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address


                  ORDER BY SummaryMonth ASC, 4 ASC

                    • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
                      kweise

                      That's correct.  On the SQL tab on the modified report, you won't be able to modify the SQL query.  The only way you'll be able to change the SQL query directly is to copy the SQL code from  your report and create a new report.  When report writer asks you what type of report you want to create, select Advanced SQL.  Paste the SQL query from your modified report into the SQL tab in the new, Advanced SQL report and you'll be able to edit it in that report.

                        • Re: Two Issues - 1 with modifying report and 2 with attempted solution to modify query no worky
                          Ballzo

                          OK, I have the sql editing figured out. Thank you but now still no luck with the query. It looks like this and is still returning different values than the default report that displays all devices. What do I need to change?

                           

                           

                          SELECT  TOP 10000 CONVERT(DateTime,
                          LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
                          101) AS SummaryMonth,
                          Nodes.NodeID AS NodeID,
                          Nodes.VendorIcon AS Vendor_Icon,
                          Nodes.Caption AS NodeName,
                          Nodes.IP_Address AS IP_Address,
                          AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability

                          FROM
                          Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)


                          WHERE
                          ( DateTime BETWEEN 40208 AND 40235.9999884259 )
                           AND
                          (
                            (Nodes.Vendor <> 'Cisco') AND
                            (Nodes.Caption <> 'TSSD000') AND
                            (DailyNodeAvailability.Availability < 100)
                          )


                          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
                          Nodes.NodeID, Nodes.VendorIcon, Nodes.Caption, Nodes.IP_Address


                          ORDER BY SummaryMonth ASC, 4 ASC