Difference of summarized fields in crosstab 1

[ind123p]

I m using CR XI.

This is what I have to accomplish.
CP stands for Current Period
PP stands for Previous period.

CP(03/01/2005 to 03/31/2005) PP (03/01/2004 to 03/31/2004) Increase/Decrease
Hours % hours Hours % hours Hours %hours
Client1 StaffA 10 66.67% 150 75.00% -140 -93.33%
StaffB 5 33.33% 50 25.00% -45 -90.00%
Total 15 100.00% 200 100.00% -185 -92.50%

The way I plan to implement the above is as follwos.

CP will be in one inserted crosstab
PP will be in second inserted crosstab ( overlapping CP tab, hiding grids,etc)

Problem is how to implement the third tab ( Increase/decrease).
1) Will Crystal allow me to take a difference of Summarized fields ?
2) How do I calculate the % ? Is there a way to store the values in variables and then calculate the percentages/difference ?

As always any direction/help will be appreciated.

 

[lbass]

I think you should use a manual crosstab here. You can create a parameter for year and for month, and then insert groups on client and then on staff. Then create formulas like:

//{@selectedyr}:
if month({table.date} = {?Month} and
year({table.date}) = {?Year} then
{table.hours}

//{@selectedyear-1}:
if month({table.date} = {?Month} and
year({table.date}) = {?Year}-1 then
{table.hours}

Right click on these and insert summaries at both client and staff group levels and then suppress the detail sections. For the hours percents, use formulas like:

//{@selyr@} to be placed in the staff group footer:
sum({@selectedyr},{table.staff}) % {@selectedyr},{table.staff)

Repeat for each year and for the client group, create formulas using {table.client} as the group condition.

For the hours difference, use:

sum({@selectedyr},{table.staff})-(sum({@selectedyr-1},{table.staff})

...and for the percent, use:

(sum({@selectedyr},{table.staff})-(sum({@selectedyr-1},{table.staff})) % sum({@selectedyr},{table.staff})

-LB

 

[ind123p]

LB,

I will try your approach and let you know.

Thanks for your quick response.

 

[ind123p]

LB, As always your solution worked perfect.Star for you!!! I have created a manual cross tab.

My client wishes to see the Total for each staff too as shown below in the same report.

CP(03/01/2005 to 03/31/2005) PP (03/01/2004 to 03/31/2004) Increase/Decrease
Hours % hours Hours % hours Hours %hours
Client1 StaffA 10 66.67% 150 75.00% -140 -93.33%
StaffB 5 33.33% 50 25.00% -45 -90.00%
Total 15 100.00% 200 100.00% -185 -92.50%

Client2 StaffA 10 66.67% 150 75.00% -140 -93.33%
StaffB 5 33.33% 50 25.00% -45 -90.00%
Total 15 100.00% 200 100.00% -185 -92.50%
----------------------------------------------------------------------------------------
Total StaffA 20 66.67% 300 75.00% -280 -93.33%
StaffB 10 33.33% 100 25.00% -90 -90.00%
Grand Total 30 100.00% 400 100.00% -370 -92.50%



In GH1 I have client
GH2 --Staff

How do I create the total for staff (Section below the dotted line) in the same report ?

 

[lbass]

You could do this with running totals, but I think it would be simpler to save your report under another name and then insert it into the original report as a subreport in the report footer. In the subreport, delete the client group. Then your existing calculations should give you the correct results.

-LB

 

[ind123p]

Hmm.. I did not think of subreports...Looks like a good approach. I will let you know once I do it.

 

[ind123p]

LB,

Star for you. The subreport approach worked perfect.