Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Dynamically change table columns Post Reply Post New Topic
Author Message
wibni
Newbie
Newbie


Joined: 16 Apr 2013
Online Status: Offline
Posts: 14
Quote wibni Replybullet Topic: Dynamically change table columns
     Posted: 17 Apr 2013 at 12:12am
Hi, I'm using Crystal 10.

I want to build a report which lists me values for the last 12 months from the date the report is run.
If I run the report in April 2013 it will show values back until May 2012.
This would usually not be a problem, but in the table I'm using every month is in a seperate column like shown below.

Year|Month1|Month2|Month3|...|Month12

How can I dynamically show the last 12 months from any give date?

This formula would work if the report was run in April to show me the value for March. However if I run the report in May, Month -1 would be April and I would have to show {TABLE.MONTH4} and not {TABLE.MONTH3}


IF month(DateAdd("m",-1,currentdate)) = 3  THEN {TABLE.MONTH3}

IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet Posted: 17 Apr 2013 at 5:32am
Don't think in fixed terms, but in variable terms
define
_dtc = month(CurrentDate)
_dtm01 = month(DateAdd("m",-1,CurrentDate))
_dtm02 =  month(DateAdd("m",-2,CurrentDate))

these will be headers, and today April 17th
_dtc = 4
_dtm01 = 3 as in minus 01 months
_dtm02 = 2 as in minus 02 months

then, check your table against those
Remember to SELECT for records in past year, since this in only looking at the month!

if month({table.invoicedate}) = {@_dtc}
  then
     monthc := monthc + {table.invoice}
     whatever you want to do; accumulate, etc..
if month({table.invoicedate}) = {@_dtm01}
  then
     monthm01 := monthm01 + {table.invoice}
     whatever you want to do; accumulate, etc..

and so on; you could probably also do a CASE statement

And for your report, use the _dt variable across the top, with the respective monthc or monthm variables in your report under those columns.

IP IP Logged
wibni
Newbie
Newbie


Joined: 16 Apr 2013
Online Status: Offline
Posts: 14
Quote wibni Replybullet Posted: 17 Apr 2013 at 3:33pm
Thanks Joeg. Managed to do it with an UNPIVOT function.
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum