Dynamically change table columns 1

[johnhugh]

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}

 

[PeterDimitrov]

Use a cross-table. You will need also to reshape the data to have all dates in one column.
Create a view or a command like this:

SELECT (Date for Month1) as ValueDate, Month1 FROM Table WHERE ...
UNION
SELECT (Date for Month2) , Month2 FROM Table WHERE ...
...
SELECT (Date for MonthN) , Month12 FROM Table WHERE ...

Then use this view to create a cross-tab

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[SkipVought]

hi,

The problem is that you are using a table that generated from a crosstab REPORT, rather than the table(s) that THAT REPORT used to generate THAT summary.

Which is another way of saying what Peter also stated, "You will need also to reshape the data to have all dates in one column."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnhugh]

Thanks I've used an unpivot to reshape my data. Got it working now.