cancel
Showing results for 
Search instead for 
Did you mean: 

Calculating a Number in a Cross-Tab

Former Member
0 Kudos

Not sure the best way to explain this one, but I was helped to create a cross-tab that used an array to calculate the number of incentives and summarize the result in a cross-tab.  Now I'm looking to take this number and multiple it by another column in a table {UnitCost} to calculate the total value of the incentives used for the report.


The previous issue was here:

For instance, if the units costs are as follows:

Double Fandango = 10

Fandango = 5

Gift Card $10 = 10

Movie Ticket = 5

I would like to cross tab to look like the attached picture.  Would this be possible?

The array formula is:

whileprintingrecords;

stringvar d:= {OBI_EquipmentMaster.Description};

numbervar p:= {DriveProjectionAndCollectedTotals.ProcedureProjection};

numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};

stringvar array ad;

numbervar array ap;

numbervar array ape;

numbervar c2:= 1;

numbervar n:=0;

// check to see if the description has been added to the string array

// if not, add it plus add the initial projection value to the number arrays

if not (d in ad) then

    (

        numbervar c:= c + 1;

        redim preserve ad[c]; ad[c]:= d;

        redim preserve ap[c]; ap[c]:= p;

        redim preserve ape[c]; ape[c]:= pe;

    )

else

// if the description is already in the array, find its position

// then add the new projection as a running total to the appropriate number array values

(

    while c2 <= count(ad) do

        (

            if d = ad[c2] then (n := c2; exit while);

            c2 := c2 + 1

        );

    ap[n]:= ap[n] + p;

    ape[n]:= ape[n] + pe;

);

// grand running totals

numbervar gp:= gp + p;

numbervar gpe:= gpe + pe;

I thought it would be a start creating a new number variable:

numbervar i:= {OBI_EquipmentMaster.UnitCost}

But I'm not sure I understand the rest of the formula nor how to insert a new column in the cross-tab.  Any suggestions on how to proceed?

Thanks,

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

hi Trey,

since you're using the Display String in your first summary you need to assign the displayed value to a variable. i.e. change the Display String formula to

whileprintingrecords;

stringvar t:= GridRowColumnValue ("rpt_EquipmentMaster.Description");

stringvar array ad;

numbervar array ap;

numbervar c3:= 1;

numbervar n2;

while c3 <= count(ad) do

    (

        if t = ad[c3] then (n2 := c3; exit while);

        c3 := c3 + 1

    );

numbervar apn:= ap[n2];

totext(apn,0)

once that is done, you now have a variable (apn) that is available on the row level for your summaries.

once you've added the unit cost field as a Maximum type summary to your cross-tab as the second summary, right click on that summary and go to the display string for unit cost maximum. enter in this syntax:

whileprintingrecords;

numbervar apn;

numbervar apnrt:= apnrt + apn*currentfieldvalue;

totext(currentfieldvalue * apn,0)

note that you're referencing the variable apn which is used in the output of the previous summary. also note that you're creating another running total named apnrt which runs in the background of the cross-tab. you'll need this running total for the grand total in the cross-tab.

now right click on the grand total for the maximum unit cost summary and go to the display string for this value. enter in this syntax.

whileprintingrecords;

numbervar apnrt;

totext(apnrt, 0)

Former Member
0 Kudos

Hey Jamie,

I promise I'm not being obtuse intentionally.

When you say I need to assign the displayed value to a variable (change the display string to a formula), I assumed I should replace the previous formula text with this:

whileprintingrecords;

stringvar t:= GridRowColumnValue ("rpt_EquipmentMaster.Description");

stringvar array ad;

numbervar array ap;

numbervar c3:= 1;

numbervar n2;

while c3 <= count(ad) do

    (

        if t = ad[c3] then (n2 := c3; exit while);

        c3 := c3 + 1

    );

numbervar apn:= ap[n2];

totext(apn,0);

numbervar p:= {DriveProjectionAndCollectedTotals.ProcedureProjection};

numbervar pe:= {DriveProjectionAndCollectedTotals.ProceduresPerformed};

numbervar i:= {OBI_EquipmentMaster.UnitCost};

numbervar array ape;

numbervar c2:= 1;

numbervar n:=0;

But when I check the formula, I get:

This function can only be used within the context of a value grid.

In reference to GridRowColumnValue ("rpt_EquipmentMaster.Description");

Any suggestions?

JWiseman
Active Contributor
0 Kudos

hi Trey,

the array builder formula doesn't need to be changed...only the Display String properties of your new Cross-tab Summary.

after you create your new Maximum summary in the cross-tab on the unit cost, right click on it and choose Format > Common tab > Display String. then follow the steps above.

-jamie

Former Member
0 Kudos

Thanks Jamie,

Understand now and I believe I've got it. One last question and I think it will be complete.

In the display field for the total cost, you had me put this in the Display String:

whileprintingrecords;

numbervar apn;

numbervar apnrt:= apnrt + apn*currentfieldvalue;

totext(currentfieldvalue * apn,2)

And it works, but since this is a currency field I changed the ,0 to ,2 so it will display two decimal places.  But I try to insert a "$" in front of the currentfieldvalue with:

totext("$"&currentfieldvalue * apn,2)

or

toext "$"&(currentfieldvalue * apn,2)

And nothing seems to work.  Any suggestions on that?

JWiseman
Active Contributor
0 Kudos

you can change the output of display string to pretty much anything you want..it's a very handy feature.

for the currency symbol one way is

"$" +  totext(currentfieldvalue * apn,0)

Former Member
0 Kudos

Awesome, you're the best!

Now I think I might have to re-ask on older thread about the array but this one should be closed out.

Thanks again!

Answers (0)