Saturday January 20th 2018

Categories

Insider

Archives

sqlRecently I came across lot consulting working automating crosstab reports in Crystal Report. Crystal Reports has wizard for generating cross tab report, which is very limited in usage. We cannot customize these wizard generated crosstabs. Issue involved creating a crosstab which show months in columns in first half of the page and then shows growth in the second half in numbers and percentage in third half of the report. This report is not possible in Wizard generated crosstab report.

Advanced SQL case statement and dateadd function was utilized to create these reports. Anaexample of that code is below.
,sum(Case when date_format(b.dateCol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 0 MONTH) ,’%Y-%m-01′) then b.paid_AMNT else 0 end) as Month3
,sum(Case when date_format(b.dateCol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 1 MONTH) ,’%Y-%m-01′) then b.Paid_AMNT else 0 end) as Month2
,sum(Case when date_format(b.datecol,’%Y-%m-01′) = date_format( DATE_ADD(date({?enddate}), INTERVAL – 2 MONTH) ,’%Y-%m-01′) then b.PaidD_AMNT else 0 end) as Month1
From bills b
where
(dateCol between
date(date_format( DATE_ADD(date({?endDate}),INTERVAL -3 MONTH) ,’%Y-%m-01′) )
and LAST_DAY(date({?endDate}))
;