Cross-tab percent - 1st row/Sum of the following rows

[aka45]

How can I insert a percent in a cross-tab with sum of standard labor by period divided by sum of other fields by the same period as below? Do I need to do a Manual Cross tab?
Jan Feb
Standard Labor 105,044 105,044
Actual Direct Labor 114,004 114,004
Actual Overtime 5,954 5954
PTO 14 14
Other 417 417
Temporaries 10,359 10359
Total 25,703 25703
%((Standard labor/(sum of other rows) 80% 80%

Please suggest me some Ideas. Thanks and I appreciate your help

Thanks, AK

 

[aka45]

I am using CR 10

Thanks,
AK

 

[lbass]

Please tell us whether you have a row field in the crosstab or whether your display above is of multiple summaries. Also explain where you are getting that total, which appears to have nothing to do with the numbers above it. What is your summary field if there is only one?

-LB

 

[aka45]

Hi lbass,

I have GL Account Numbers, Netactivity, Accounting period in the database. Account numbers are grouped togather to form the rows of the crosstab below like Standar labor, Actual Direct labor etc. Accounting Period like Jan, feb etc represent the Columns of the cross tab. sum of Netactivity in each Accounting period is shown as the summary under each column. I need to calculate the percentage of Standard labor over actuals

Jan Feb
Standard Labor 105,044 105,044
Actual Direct Labor -114,004 -114,004
Actual Overtime -5,954 -5954
PTO -14 -14
Other -417 -417
Temporaries -10,359 -10359
Total -25,703 -25703
%((Standard labor/(sum of Actual labor) 80% 80%

Please let me know if you have questions.

Thank you for your help

AK

 

[lbass]

First create a formula {@0) to use as a place holder in the crosstab:

whilereadingrecords;
0

Add this as a second summary in the crosstab. Then in preview mode, suppress this summary in the inner cell, so that it only appears in the total row. Grab the borders of the suppressed field and minimize the height.

Next, still in preview mode, select an inner cell->right click ->format field->common->suppress->x+2 and enter:

whileprintingrecords;
numbervar std;
numbervar other;

if gridrowcolumnvalue("@Acct") = "Standard Labor" then (
std := currentfieldvalue;
other := 0);
if gridrowcolumnvalue("@Acct") <> "Standard Labor" then
other := other + currentfieldvalue;
false

The above assumes that your row formula is called {@Acct}. Whatever your formula name is, remove the brackets and place in quotes to use in the gridrowcolumnvalue function.

Then select the column total field foro {@0}->format field->common->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar other;
numbervar std;
totext(abs(std % other),2)+"%" //2 allows 2 decimals

It looks like you want the percentage to always be positive, so I added the abs() function.

-LB

 

[aka45]

Thank you for your reply LB. It worked. Just added a condition statement to check on Zero's before the % calculation.

AK

 

[pwenke]

I have a similar situation. I am trying to add a Net Revenue Margin percentage to a cross tab. Here is what I want:

REVENUE Jan Feb
xxxx-xx Revenue1 1,000 1,000
xxxx-xx Revenue2 2,000 2,000
xxxx-xx Revenue3 3,000 3,000
xxxx-xx Revenue4 4,000 4,000
xxxx-xx Revenue5 5,000 5,000
Total Revenue 15,000.00 15,000.00

ROYALTIES
xxxx-xx Royalties1 100.00 100.00
xxxx-xx Royalties2 200.00 200.00
xxxx-xx Royalties3 300.00 300.00
xxxx-xx Royalties4 400.00 400.00
xxxx-xx Royalties5 500.00 500.00
Total Royalties 1,500.00 1,500.00

NET
Net Revenue 13,500.00 13,500.00
Net Revenue Margin 90% 90%

Please note that Revenue, Royalties, and Nets are in 3 different subreports. Net, which I'm concerned with here, uses @Revenue-@Royalties to calculate Net Revenue. Net Revenue Margin is then just Net Revenue / Revenue, but I'm having trouble getting this percentage into the cross tab. I'm using CR 8.5; any help is greatly appreciated.

Thanks!
PW

 

[lbass]

I don't think you can use an inserted crosstab in this instance. You would have to do this manually, using the shared variables in conditional formulas.

-LB