Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Report Designer 12 month rolling chart to show all months

  1. #1
    Join Date
    Dec 2009
    Posts
    12

    Default Report Designer 12 month rolling chart to show all months

    I would like to create a 12 month rolling stacked bar chart where all the months are displayed whether or not there is any data for one of those months.

    For the data I am tracking the number of complaints per month grouped by Product Line. So for each month I have a stacked bar showing the # of complaints for each product line and the whole bar represents the total # of complaints for that month. I have the SQL setup and the chart is working correctly for the months that have data. What I want now is if a month has no data I still want the month to show up, so it is easy to see that a month had no complaints.

    Any ideas on how to do this with pentaho report designer?

  2. #2
    pstoellberger Guest

    Default

    thats a query issue, not a report designer issue

  3. #3
    Join Date
    Dec 2009
    Posts
    12

    Default

    How do I do it then with sql?

  4. #4
    Join Date
    Mar 2009
    Posts
    30

    Default Use an alias and an outer join

    if your base table contains all the combination of months and product groups you want to display, you can alias that table, otherwise you'll have to use another one. In any case, your 'left' part of the outer join needs to return all months/product groups. For the 'right' part of the table you can use your existing query and join on product group & month. The outer join makes sure that all combination of month/product group are returned, whether a value for any of the measures is found or not. Again, this is not really a PRD issue, just basic SQL. Many sources for this on the internet, just google for 'left outer join'

    good luck!

  5. #5
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    If you use a XY-Chart with a TimeseriesCollector then missing dates will be left empty, but the space for them will be reserved. Take the XY-Bar-Chart sample from PRD and change the query to:

    Code:
    SELECT
         PRODUCTS.PRODUCTLINE,
         ORDERFACT.ORDERDATE,
         sum(ORDERFACT.TOTALPRICE) AS SALES,
         sum(ORDERFACT.QUANTITYORDERED) AS VOLUME
    FROM
         PRODUCTS INNER JOIN ORDERFACT ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
    WHERE 
    ORDERFACT.ORDERDATE >= ${startDate} 
         AND ORDERFACT.ORDERDATE <= ${stopDate}
         AND 
         (ORDERFACT.ORDERDATE < '2003-04-01' 
      OR 
         ORDERFACT.ORDERDATE > '2003-08-01')
         
    GROUP BY
         PRODUCTS.PRODUCTLINE,
         ORDERFACT.ORDERDATE
    ORDER BY
         PRODUCTS.PRODUCTLINE ASC,
         ORDERFACT.ORDERDATE ASC
    so that we generate some gaps. Run it and you'll see that you get the x-axis entries for the missing months, but no data in it. No need for SQL magic here (that would be needed if you use a categorical-chart (bar-chart instead of XY-bar chart etc)).
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  6. #6
    Join Date
    Dec 2009
    Posts
    12

    Default

    I was able to get all the months to display using sql. Are there any good tutorials on reporting with x/y bar chart with TimeSeries collector. I messed around with it a little and got it to display, but couldn't get it to display for a set period.

    For the 12 months I just created 12 sql statements (one for each month) with unions, then left joined on that. I also had to do a trick to display the product line for the blank months since the bar chart will not chart null values.

    Here is the SQL if anyone is interested.

    --SQL Statement for 12 month rolling
    --the_months: Defines the last 12 months
    -- needed to display months that have 0's
    with the_months as (
    select a_month from (
    select to_char(to_date(sysdate, 'DD-MON-YY'), 'yyyy-MM') as a_month from dual month_0 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-1), 'yyyy-MM') as a_month from dual month_1 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-2), 'yyyy-MM') as a_month from dual month_2 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-3), 'yyyy-MM') as a_month from dual month_3 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-4), 'yyyy-MM') as a_month from dual month_4 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-5), 'yyyy-MM') as a_month from dual month_5 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-6), 'yyyy-MM') as a_month from dual month_6 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-7), 'yyyy-MM') as a_month from dual month_7 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-8), 'yyyy-MM') as a_month from dual month_8 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-9), 'yyyy-MM') as a_month from dual month_9 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-10), 'yyyy-MM') as a_month from dual month_10 union all
    select to_char(add_months(to_date(sysdate, 'DD-MON-YY'),-11), 'yyyy-MM') as a_month from dual month_11)
    ),

    --with statement for all product lines (prod_line_data)
    --with statement for actual summarydata (actual_data)... then

    --START OF SQL STATEMENT
    select
    the_months.a_month,
    --IF Product Line is null (meaning no data for the displayed month)
    --then display one of the product lines that had data in one of the months
    --else display the product line
    decode(actual_data.product_line, null,
    (select min(prod_line_data.product_line) as min_prod_line
    from prod_line_data inner join actual_data on prod_line_data.product_line = actual_data.product_line),
    actual_data.product_line) as product_line,
    decode(actual_data.new_ptcs, null, 0, actual_data.new_ptcs) new_ptcs
    from
    --Left outer join on the months to display all months
    the_months left outer join actual_data on the_months.a_month = actual_data.reporting_month
    order by a_month, product_line

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.