Cross tabs reports Expert Question 1

[benramz]

I have a cross tab report that produces the sum of daily appointments and sum of the units by appointment type.

This was straight forward use of the crosstab wizard. The problem is that I need to have a summary calculation on the report that is something like (Units of Appointment type 1) - (Units of Appointment type 2) = Net Gain or loss

Is there a way to add this to the totals section as a formula?
Date
Appointment type 1 - Appointments 1
Units 1
Appointment Type 2 - Appointments 1
Units 2
Total - Appointments 2
Units 3
Net Gain Loss 1 or -1

 

[lbass]

First add the unit field again, to create a third summary field. Select the inner cells, label, and row total for this new summary and suppress. Then minimize the height of these empty cells. You will now have a holder field for the difference in the total section.

Next select the units field in the inner cells->right click->format field->suppress->x+2 and add the following formula:

whileprintingrecords;
numbervar t1;
if gridrowcolumnvalue("Table.ApptType") = "Appointment Type I" then
t1 := currentfieldvalue else
t1 := t1 - currentfieldvalue;
false

Substitute the row field for "Table.ApptType". Replace the curly brackets around the {table.field} with double quotes: "table.field". Use the exact name of the row instance for "Appointment Type I".

Then select the third summary in the total area->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar t1;
totext(t1,0,"")

You can edit the text for the third summary label as needed.

PS. You should always identify your CR version--especially when the questions relate to crosstabs.

-LB

 

[benramz]

Thanks Linda, I knew I could count on a great solution from you.

I am using CR XI

Will this work for multiple of appointment types?

I have three types of appointments that are defined as a Gain and three that are defined as a loss.

 

[lbass]

I'm not following--what would the calculation be? Can you show an example?

Also, I'm assuming you are using an inserted crosstab because you have multiple instances of the date column that you need to be dynamic...

-LB

 

[benramz]

Yes, the Cross tab report is different.


1 Dynamic Column: AppointmentDate - Uses a date range

2 Rows: Resource - This is value is either "Truck" or "Warehouse"
Appointment Type - The value is "Delivery", "Re-Delivery", "Pick Up Empty", "Pick Up Full", "Access", "Dock In", "Dock Out", "Inbound Shipment", "OutBound Shipment"

Resource Truck is limited to "Delivery", "Re-Delivery", "Pick Up Empty", "Pick Up Full"
Resource Warehouse is limited to "Access", "Dock In", "Dock Out", "Inbound Shipment", "OutBound Shipment"


Summary is Number of Appointments by Type and Summary of Containers By Appointment Type.

The additional Summary: Net Gain or Loss
Formula: Units for Appointment Types ("Delivery","Dock In","Inbound Shipment") - Units for Appointment Types ("Pick Up Empty", "Dock Out","OutBound Shipment" )

The remaining appointment types ("Re-Delivery","Pick Up Full", "Access") will not affect the gain or loss of units.

Hopefully this does not make question any more confusing.

Thanks Again Linda.

 

[benramz]

Linda,

I used the formulas that you sent and modified for the appointment types.
The only issue is that I need to reset the value of T1 for each column. Any Ideas?

whileprintingrecords;
numbervar t1;
if (gridrowcolumnvalue("Command.Appointment Type") = "Delivery" or
gridrowcolumnvalue("Command.Appointment Type") = "Dock IN" or
gridrowcolumnvalue("Command.Appointment Type") = "Inbound Shipment") then
t1 := t1 + currentfieldvalue
else
if (gridrowcolumnvalue("Command.Appointment Type") = "Pick Empty" or
gridrowcolumnvalue("Command.Appointment Type") = "Dock Out" or
gridrowcolumnvalue("Command.Appointment Type") = "Outbound Shipment") then
t1 := t1 - currentfieldvalue;
false

 

[lbass]

Select the summary field where you are displaying the net gain/loss->right click->format field->border->color->background->x+2 and enter:

whileprintingrecords;
numbervar t1 := 0;
crnocolor

This formatting formula area is processed later so works as a location for a reset.

-LB

 

[benramz]

Linda,

Thank you!
This is why you are the GURU!

Looks like knowing the order of the formatting makes the difference in this case.

Sorry for the delay, was on vacation.