7 Replies Latest reply on Apr 1, 2015 11:01 PM by orioncrack

    Dynamic Title for SQL Report?

    sw@rec

      Hello,

      I'm using an uptime report that I pulled from the forums here.  (APM Availability Report - Business Hours) Three, in fact because the maintenance windows are different for each of the three reports.  But by using the Union statement I'm able to get them all on one piece of paper.  I would like to include the date range as the title and haven't figured it out.  If you include them in the select you get thousands of rows which distorts the data.

       

      This is the data I would like to include as the title:

           dbo.Date(YEAR(GETDATE()),MONTH(GETDATE())-1,1)

           dbo.Date(YEAR(GETDATE()),MONTH(GETDATE()),1))

       

      The basic outline of the report is below:

       

      Thanks,

      SW

       

      (

      SELECT 

      FROM

      WHERE

      AND 

      GROUP BY

      )

      UNION ALL

      (

      SELECT 

      FROM

      WHERE

      AND 

      GROUP BY

      )

      UNION ALL

      (

      SELECT 

      FROM

      WHERE

      AND 

      GROUP BY

      )

      ORDER BY 1 ASC, 2 ASC;

        • Re: Dynamic Title for SQL Report?
          zackm

          can you post the full query you are using?

            • Re: Dynamic Title for SQL Report?
              sw@rec

              Hello - This is the full query:

              I have three classes of service so three different AND statements so three different SELECT statements (I'm sure there's a better way but this works).  I'd like to have a title like the WHERE statement so that everyone knows what we're looking at on the report.  But I believe the UNION command requires that all of the columns are the same across the UNION statements so adding another UNION block seems to be out.

              Thanks,

              SW

               

              (

              SELECT  TOP 10000 Nodes.Caption AS NodeName,

              APM_AlertsAndReportsData.ApplicationName AS Application_Name,

              AVG(APMAA.PercentAvailability) AS AVERAGE_of_ApplicationAvailability

               

              FROM

              (Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId))  INNER JOIN APM_ApplicationAvailability APMAA ON (APM_AlertsAndReportsData.ApplicationId = APMAA.ApplicationID)

               

              WHERE

              ( APMAA.DateTime BETWEEN dbo.Date(YEAR(GETDATE()),MONTH(GETDATE())-1,1) AND dbo.Date(YEAR(GETDATE()),MONTH(GETDATE()),1))

               

              AND 

              (

                (Nodes.UnManaged = 0) AND

                (Nodes.Caption = 'cap1') AND

                ((DatePart(Hour,APMAA.DateTime) > 3) OR

                (DatePart(Hour,APMAA.DateTime) > 3))

              AND

                (APM_AlertsAndReportsData.ApplicationName = 'type1')

              )

               

              GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName

               

              )

              UNION ALL

              (

               

              SELECT  TOP 10000 Nodes.Caption AS NodeName,

              APM_AlertsAndReportsData.ApplicationName AS Application_Name,

              AVG(APMAA.PercentAvailability) AS AVERAGE_of_ApplicationAvailability

               

              FROM

              (Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId))  INNER JOIN APM_ApplicationAvailability APMAA ON (APM_AlertsAndReportsData.ApplicationId = APMAA.ApplicationID)

               

              WHERE

              ( APMAA.DateTime BETWEEN dbo.Date(YEAR(GETDATE()),MONTH(GETDATE())-1,1) AND dbo.Date(YEAR(GETDATE()),MONTH(GETDATE()),1))

               

              AND 

              (

                (Nodes.UnManaged = 0) AND

                (Nodes.Caption = 'cap1') AND

                ((DatePart(Hour,APMAA.DateTime) < 6) OR

                (DatePart(Hour,APMAA.DateTime) > 11))

              )

               

              AND 

               

                (APM_AlertsAndReportsData.ApplicationName <> 'type1')

               

              GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName

               

              )

              UNION ALL

              (

               

              SELECT  TOP 10000 Nodes.Caption AS NodeName,

              APM_AlertsAndReportsData.ApplicationName AS Application_Name,

              AVG(APMAA.PercentAvailability) AS AVERAGE_of_ApplicationAvailability

               

              FROM

              (Nodes INNER JOIN APM_AlertsAndReportsData ON (Nodes.NodeID = APM_AlertsAndReportsData.NodeId))  INNER JOIN APM_ApplicationAvailability APMAA ON (APM_AlertsAndReportsData.ApplicationId = APMAA.ApplicationID)

               

              WHERE

              ( APMAA.DateTime BETWEEN dbo.Date(YEAR(GETDATE()),MONTH(GETDATE())-1,1) AND dbo.Date(YEAR(GETDATE()),MONTH(GETDATE()),1))

               

              AND 

              (

                (Nodes.UnManaged = 0) AND

                (Nodes.Caption = 'cap2') AND

                ((DatePart(Hour,APMAA.DateTime) > 6) AND

                (DatePart(Hour,APMAA.DateTime) < 22))

              )

               

              GROUP BY Nodes.Caption, APM_AlertsAndReportsData.ApplicationName

              )

               

              ORDER BY 1 ASC, 2 ASC;

            • Re: Dynamic Title for SQL Report?
              LadaVarga

              Hello

               

              You can use ${fromtime} ${totime} and ${fromtimeUTC} ${totimeUTC} macros in SQL datasource. After that u can pick Time frame and you will see that in your report/custom table resource.

               

              reports-2-2-000720.png

              reports-2-2-000718.png

              reports-2-2-000719.png

              • Re: Dynamic Title for SQL Report?
                sw@rec

                Nuts, we're on 5.5.  I think the web report's not available to us yet.