D-pk
asked on
Crystal Report 11 -- Cross Tab format and SQL connect by function
I have a cross-tab with 24 months of data. (Please see the attached file). But sometimes the SQL doesn't return 24 months of data, because there is no data for those months. In our example the Sep 2010 and Oct 2010 (201009 & 201010) doesnt have data. But I want those columns in the report with just 0s as shown in the attachment. i tried using the following SQL, but still not able to acheive the one I want.
The ?DATE_ENTERED is a parameter. If for example today's date is given (11-16-2010), I have a formula[maximum(LastFullMo nth)] which makes the date to previous months last date (10-31-2010). So the date range would be from oct 2010 and goes back 24 months nov 2008.
I tried playing with the connect by level number sometimes it goes backward or forward not the right one.
When I run just the second part of the union all, I get the desired result ie., from 11/01/2008 thru 10/01/2010, but when I use it together its not giving the desired result, maybe I dont understand that function fully.
My next question is, can we change the label of the summary row, that is, GRAND TOTAL instead of just TOTAL. I have highlighted in red in the attachment.
Thoughts?
Thanks!
CROSS-TAB-FORMAT.xls
The ?DATE_ENTERED is a parameter. If for example today's date is given (11-16-2010), I have a formula[maximum(LastFullMo
I tried playing with the connect by level number sometimes it goes backward or forward not the right one.
When I run just the second part of the union all, I get the desired result ie., from 11/01/2008 thru 10/01/2010, but when I use it together its not giving the desired result, maybe I dont understand that function fully.
select
trunc(s_date, 'MM') as SDATE,
trunc(s_date, 'MM') as SDATE,
SALARY
FROM TABLE_A,
TABLE_B
.....
WHERE
.....
s_date BETWEEN TRUNC(ADD_MONTHS(TRUNC({?DATE_ENTERED},'MM'),-23)) AND {?DATE_ENTERED}
p_date BETWEEN TRUNC(ADD_MONTHS(TRUNC({?DATE_ENTERED},'MM'),-23)) AND {?DATE_ENTERED}
UNION ALL
-- This is to get those months which aren't returned
SELECT
trunc({?DATE_ENTERED}, 'mm') + INTERVAL '-1' MONTH * ROWNUM SDATE,
trunc({?DATE_ENTERED}, 'mm') + INTERVAL '-1' MONTH * ROWNUM PDATE,
0 AS salary
FROM dual
CONNECT BY LEVEL <=24
My next question is, can we change the label of the summary row, that is, GRAND TOTAL instead of just TOTAL. I have highlighted in red in the attachment.
Thoughts?
Thanks!
CROSS-TAB-FORMAT.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER