"Difference" column to cross tab report ? 2

[prasadmokashi]

Hi,

I have created cross tab report which shows sum of expenditure on each user from each department for each month in the following format

March April May June
Admin John 100 110 110 115
Peter 200 200 210 205
Finance Rita 110 110 110 110
David 200 200 210 220

What I would like to do is add one more column having difference in expenditure for last two months.
so in example above


March April May June Diff
Admin John 100 110 110 115 +5
Peter 200 200 210 205 -5
Finance Rita 110 110 110 110 0
David 200 200 210 220 +10

How can I add this additional column in cross tab report ?

Could anybody please help ?

Thank you very much.

Regards,
Prasad

 

[lbass]

I think you would have to use a manual crosstab in order to add this column. You would group on department and then on employee, and then create a formula for each month, like:

//{@May}:
if month({table.date}) = 5 then {table.amt}

//{@June}:
if month({table.date}) = 6 then {table.amt}

Then insert summaries on these formulas and suppress the details. Your difference column (to be placed in the employee group header or footer) would be:

sum({@June},{table.employee}) - sum({@May},{table.employee})

-LB

 

[prasadmokashi]

Thank you very much LB.

I think this will work.
But one problem, at any time report will have variable number of months.

I mean in March, it will have data for Jan, Feb, March.
( And I need difference between March and Feb )
In May, it will have data for Jan, Feb, March, April, May.
( And I need difference between May and April )

If I need to create formula for each month & for difference before hand, how should I cope with this ?

Thanks a lot for your response and help.

Regards,
Prasad

 

[lbass]

What determines what the last month is? Are you using a date parameter? Or is it always the last full month? Or?

-LB

 

[prasadmokashi]

I just get all the data by where clause such as "where date is between JAN-2004 to DEC-2004"

So if I run report at May end, I get the data only for 5 months.

Thanks again.

Prasad

 

[synapsevampire]

You can derive the months, and the formula that LB suggested could derive the last 2 months:


The following gives you the last month:
whileprintingrecords;

{table.date} >= cdate(year(dateadd("m",-1,currentdate)),month(dateadd("m",-1,currentdate)),1)
and
{table.date} <= (cdate(year(currentdate),month(currentdate),1)-1)

Previous month

{table.date} >= cdate(year(dateadd("m",-2,currentdate)),month(dateadd("m",-1,currentdate)),1)
and
{table.date} <= (cdate(year(dateadd("m",-1,currentdate)),month(dateadd("m",-1,currentdate)),1)-1)

And so on.

As LB suggested, state how you decide which months to display.

-k

 

[prasadmokashi]

Hi LB and K

Thanks for your help.

So if I am running the report at start of May, I have to create the 4 formulas for Jan, Feb, Mar, Apr.

If I am running the report at start of July, I have to create 6 formulas for Jan to June.

But I think I have to add the foumula at design time. right ?, so do you suggest I should add all 12 formula for 12 months in the report and after determining last month and previous month show the difference as one more column ?

Thanks,
Prasad

 

[lbass]

I think I would set it up with the difference column being the first column, and then have the next column be the last full month, followed by the previous month, etc., so that it grows to the right as you progress through the year. The record selection formula should limit the columns to the current calendar year. Otherwise, you would have blank columns for months occurring later in the year, followed by the difference formula.

You need to set up the formulas like the ones SV showed, changing the number of months being deducted from the current month in each formula.

Or, you could have a clearly labeled difference column first, followed by Jan - Dec.

-LB

 

[prasadmokashi]

Thanks LB,

Actually end users are not ready to accept such format of the report. They want months going fwd from left to right and at the end "Diff" column.

~ Prasad

 

[lbass]

Are you just letting us know that you have decided on this layout or do you have further questions?

-LB

 

[prasadmokashi]

LB,

Because of this format, I tend to add 12 formulae for 12 months and diff at the end. But as you said later months will be blank in this case.

Is there any way I can suppress those blank columns and reduce the spacing betn last month having the values and the diff column at the end ?

Thanks a lot.

Prasad

 

[lbass]

The only method I can think of is to insert separate detail sections, one for each possible number of months chosen, with the difference formula following the last column in each case. Then conditionally suppress each detail section unless the number of months matches that in the selection criteria.

-LB

 

[prasadmokashi]

Great ! I didn't know about that ability of CR.

Could you please tell me how to conditionally supress deatail sections ?

Thank you very much LB for all your help.

Prasad

 

[lbass]

Go to format->section->details->suppress->x+2 and enter something like:

datediff("m",date(2004,01,01),maximum(lastfullmonth))+1 <> 8

This would suppress the particular detail section if there were anything other than 8 months (Jan to Aug) to be displayed. So in this detail section you would place your Jan to Aug formulas, followed by difference formula.

For your Jan to Sept detail section, you would change the suppression formula to "<> 9", etc.

-LB