DX Unified Infrastructure Management

  • 1.  iReports SQL question

    Posted Jul 06, 2011 09:16 PM

    I'm working on report within the iReporter for use within the unified reporter.  What I'd like to do is run a query and have it pull back the details, which seems to be easy to do with the datasets.  However, I'd like to associate the returned value to an image.  The example is a query that returns the percent of availability and based on that value returned it either displays a red circle or a green circle.

     

    Has anyone done something similar to this in the designer?

     

    Thanks,

     

    Dave



  • 2.  Re: iReports SQL question

    Posted Jul 06, 2011 09:57 PM

    Hi Dave--I have done something similar, but instead of associating an image, I used a graph.  The % available is green and the % unavailable is red.  Attached is the JRXML and a PDF sample.
     
    Hope that helps.
     
    Cash

     

     



  • 3.  Re: iReports SQL question

    Posted Jul 06, 2011 10:07 PM

    That actually helps a lot.  Do you have an example of a chart that may have multiple inputs?  For example something showing a response time over the last 7 days?

     

    Thanks,

     

    Dave



  • 4.  Re: iReports SQL question

    Posted Jul 06, 2011 11:23 PM

    You mean a timeseries graph?  The attached shows CDM for past 14 days but the query can be tweaked to any timeframe.

     

     



  • 5.  Re: iReports SQL question

    Posted Jul 06, 2011 11:25 PM
    Also, those datasets can be pointed to any QoS metric.

    Cheers
    Cash


  • 6.  Re: iReports SQL question

    Posted Jul 07, 2011 03:40 PM

    Using the ones you've linked I was able to figure out the data population so that has been a massive help.  Linked is what I am trying to go for within the reports.  The image is something I have running in the UMP as a dashboard, but I'd like to make it a static report as it runs a lot of SQL in the background.  The following SQL is used to collect the data:

     

    SELECT 
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 1
    Group by c.period_begin) as '1',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 2
    Group by c.period_begin) as '2',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 3
    Group by c.period_begin) as '3',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 4
    Group by c.period_begin) as '4',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 5
    Group by c.period_begin) as '5',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 6
    Group by c.period_begin) as '6',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 7
    Group by c.period_begin) as '7',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 8
    Group by c.period_begin) as '8',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 9
    Group by c.period_begin) as '9',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 10
    Group by c.period_begin) as '10',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 11
    Group by c.period_begin) as '11',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 12
    Group by c.period_begin) as '12',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 13
    Group by c.period_begin) as '13',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 14
    Group by c.period_begin) as '14',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 15
    Group by c.period_begin) as '15',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 16
    Group by c.period_begin) as '16',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 17
    Group by c.period_begin) as '17',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 18
    Group by c.period_begin) as '18',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 19
    Group by c.period_begin) as '19',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 20
    Group by c.period_begin) as '20',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 21
    Group by c.period_begin) as '21',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 22
    Group by c.period_begin) as '22',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 23
    Group by c.period_begin) as '23',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 24
    Group by c.period_begin) as '24',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 25
    Group by c.period_begin) as '25',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 26
    Group by c.period_begin) as '26',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 27
    Group by c.period_begin) as '27',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 28
    Group by c.period_begin) as '28',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 29
    Group by c.period_begin) as '29',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 30
    Group by c.period_begin) as '30',
    (select CAST(ROUND(AVG(c.percentage),2,1) as Decimal (18,2))
    from S_SLO_DEFINITION d, H_SLO_COMPLIANCE c
    where d.slo_id = c.slo_id
    and d.slo_id = 835
    and (MONTH(c.period_begin) = MONTH(GETDATE())-1) AND (YEAR(c.period_begin) = YEAR(GETDATE()))
    and DAY(c.period_begin) = 31
    Group by c.period_begin) as '31'

     We have a lot of these that run on a single page.  Right now I'm just trying to find the best way to make a similar view in the iReporter.



  • 7.  Re: iReports SQL question

    Posted Jul 07, 2011 05:40 PM

    I think I was able to do what I wanted, sort of.  I took the Elipse tool and created three with backgrounds.  Then in the "Print When Expression" field I did the following:

     

    Green Background:

    $F{13}.intValue()>=90

     

    Red Background:

    $F{13}.intValue()<90

     

    Grey Background:

    $F{13}==null

     

    I then stacked them on each other.  My only worry now is if I add a second query to the report will that also be using "$F{#}".