BIRT Pie Chart, Cascading Parameters, & SQL Formatting

Question

I have a report that displays a pie chart based on the below query:

SELECT 
    COUNT(COURTESY_CHECK.CHECK_NUMBER) AS SUM, 
    'FORWARDED' AS STATUS
FROM
    COURTESY_CHECK
    INNER JOIN CHECK_FORWARD 
    ON COURTESY_CHECK.ID = CHECK_FORWARD.COURTESY_CHECK_ID
WHERE 
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?


UNION ALL

SELECT 
    COUNT(COURTESY_CHECK.CHECK_NUMBER) AS SUM, 
    'RETURNED' AS STATUS
FROM 
    COURTESY_CHECK
    INNER JOIN CHECK_RETURN 
    ON COURTESY_CHECK.ID = CHECK_RETURN.COURTESY_CHECK_ID
WHERE 
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?

UNION ALL 

SELECT  
    COUNT(COURTESY_CHECK.CHECK_NUMBER) AS SUM,
    'IPAC' AS STATUS 
FROM 
    COURTESY_CHECK
    INNER JOIN CHECK_DEPOSIT 
        ON COURTESY_CHECK.ID = CHECK_DEPOSIT.COURTESY_CHECK_ID
WHERE 
    COURTESY_CHECK.CHECK_STATUS = 'CLOSED' AND
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?

UNION ALL

SELECT 
    COUNT(COURTESY_CHECK.CHECK_NUMBER) AS SUM, 
    'PENDING' AS STATUS
FROM 
    COURTESY_CHECK
WHERE 
    COURTESY_CHECK.CHECK_STATUS = 'PENDING' AND
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?

UNION ALL 

SELECT 
    COUNT(COURTESY_CHECK.CHECK_NUMBER) AS SUM, 
    'DEPOSITED' AS STATUS
FROM 
    COURTESY_CHECK
WHERE 
    COURTESY_CHECK.CHECK_STATUS = 'DEPOSITED' AND
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?

My issue is with this part:

WHERE 
    to_char(COURTESY_CHECK.DATE_RECEIVED, 'FMMonth') = ? AND 
    EXTRACT (YEAR FROM COURTESY_CHECK.DATE_RECEIVED) = ?

The Month and Year parameters are based on a cascading parameter that is dynamically created from the following query:

SELECT  

 DISTINCT to_char(COURTESY_CHECK.DATE_CREATED, 'Month') AS MN, 
 EXTRACT (YEAR FROM COURTESY_CHECK.DATE_CREATED) AS YR 

FROM COURTESY_CHECK

I've discovered that using 'Month' pulls in the month name and also trailing spaces, while 'FMMonth' truncates the month name and removes the spaces.

I've tried using 'Month' for both queries and see an empty white space where the pie chart should be. Using 'FMMonth' for both queries doesn't give me a pie chart (empty or otherwise) at all. Using 'FMMonth' for one or the other doesn't work either.

I'm using to_char, because that's how I'm displaying the month name in the cascading parameter drop-down menu. I'd prefer the users of the report see the month name and not the month number.

I'm at a lost as to why the pie chart isn't appearing.

Answer 1

You should use month number in the cascading parameter and in the query, and take advantage of the "displayText" property to show the month name in the dropdown menu. This would solve any kind of issue due to char conversion, see the screenshot below taken from this example. In your case the query of the cascading parameter should be changed to display both month IDs and Names:

SELECT  
 EXTRACT (MONTH FROM COURTESY_CHECK.DATE_CREATED) MN_ID,
 DISTINCT to_char(COURTESY_CHECK.DATE_CREATED, 'Month') AS MN_NAME, 
 EXTRACT (YEAR FROM COURTESY_CHECK.DATE_CREATED) AS YR 
FROM COURTESY_CHECK

In the sample below, the department level is displaying DEPARTMENT_NAME but it actually makes use of DEPARTMENT_ID as parameter value, which is an integer. You can address both value and displaytext of the month parameter in the report body with these expressions (assuming you named it "month"):

params["month"].value;
params["month"].displayText;