How to define a history chart in crystal reports .net (2008)?

Question

Hi,

I want to display a Bar Chart in a Report that shows the sum of a measure grouped by month for the last 24 month. The months that do not have any tuples do not show up in the graph. I do not want that. I want exactly 24 groups/bars that are 0 if there are no tuples. What is the best way to do this?

      thanks

Answer 1

--oracle-specific syntax
with MONTHS as (

  --current month
  SELECT  TO_DATE(to_char(sysdate,'mm') || '/1/' || to_char(sysdate,'yyyy'),'mm/dd/yyyy') date_field, 0 measure_value
  FROM    DUAL

  UNION 

  --prior month
  SELECT  add_months( TO_DATE(TO_CHAR(SYSDATE,'mm') || '/1/' || TO_CHAR(SYSDATE,'yyyy'),'mm/dd/yyyy'),-1) date_field, 0 measure_value
  FROM    DUAL

  --continue for remaining 22 months
),

DATA AS (

  SELECT date_field, COUNT(measure_field) measure_value
  FROM   table
  GROUP BY date_field

)

SELECT *
from data

UNION

SELECT  *
FROM    MONTHS m
WHERE   NOT EXISTS (
    SELECT 1
    FROM    data d
    where   m.date_field=d.date_field
)

It would make sense for you to create a user-defined function that would return a table of date values, rather than all of the UNION statements.

Use this query in the report's Command object.