Skip to Content
0

Crystal Reports - How to calculate the difference in Cross tab and few more calculation

Feb 23, 2017 at 09:30 AM

234

avatar image
Former Member

Hello,

Am using crystal report V12.3. This is a continuation of my previous post (Crystal Reports need to find the max & minimum value for dynamic input). My initial requirement was satisfied with the Cross-tab concept but to further drill down to the requirement i need to find the difference between the values obtained in the Cross-tab concept. I have attached the image showing (sample) the calculation required.

And also How to save or get the values (output) derived from the cross tab concept? so that i can find the difference and use those value in my further calculation.query.jpg

query.jpg (50.2 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

11 Answers

avatar image
Former Member Feb 23, 2017 at 11:13 AM
0

And also Please tell let me know how to remove those default Totals (both Row and column) from the display of Cross-tab section, as I require only difference.

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 23, 2017 at 11:49 AM
0

Hi Kayalvizhi,

Are 'Minimum Qty' and 'Maximum Qty' two separate summary fields inserted in the crosstab?

To suppress the 'Total' fields, right-click the 'Total' field on the Row > Row Grand Totals > Suppress Grand Totals.

Repeat the same for the Column Totals by selecting 'Column Grand Totals' > Suppress Grand Totals.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 23, 2017 at 12:36 PM
0

Hi Abhilash,

'Minimum Qty' and 'Maximum Qty' are same field showing the Maximum & Minimum value of that particular field.

Yeah I do did that supress but still displaying the blank space in the place of "Total".

How to save or get the values (output) derived from the cross tab concept? i.e., I want to use the Maximum & Minimum value derived from this Cross-tab for other calculation. Is that possible?

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 23, 2017 at 01:03 PM
0

Did you suppress the 'Total' text field or suppress the Totals using the method I posted above? What I posted above doesn't leave behind any empty space.

Here's what you need to do for calculating the Difference:

1. Right-click the Maximum column (which I believe is the last column) > Calculated Member > Choose the option 'Select "Maximum' as first value.

2. Right-click the Minimum column > Calculated Member > Choose 'Difference of Maximum and Minimum'.

3. A new calculated column called 'Difference' should be inserted. Right-click the header cell of this column > Select Calculated Member > Edit Insertion Formula > Modify the code to:

GetColumnGroupIndexOf(CurrentColumnIndex) = 0

This will move the Difference column to the end of the crosstab.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 23, 2017 at 01:52 PM
0

Thank you. I will try this "Difference" and let you know the result.

Is there way to get (assign it to a variable or formula field) the Maximum & Minimum value derived from this Cross-tab for other calculation?

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 24, 2017 at 11:07 AM
0

24feb-1.jpgcross-expert.jpgcrystal-design.jpg

Hi Abhilash,

I have attached the output of my Crosstab, Cross-tab expert and Crystal Report design.

1. Right-click the Maximum column (which I believe is the last column) > Calculated Member > Choose the option 'Select "Maximum' as first value. - am not getting "Calculated Member option" and Maximum & Minimum are shown as Column. (Sorry to mislead by my first attachment, as that was my required output but I couldn't bring it like that). Please let me know how to find the difference between maximum and minimum with the attached design.

And Is there way to save(assign it to a variable or formula field) the Maximum & Minimum value derived from this Cross-tab for other calculation? i.e., i want to use this Maximum and Minimum value derived outside the Cross tab.


24feb-1.jpg (33.5 kB)
cross-expert.jpg (60.8 kB)
crystal-design.jpg (78.4 kB)
Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 24, 2017 at 11:55 AM
0

The steps I mentioned above will not work with the design you have.

Here's what you need to do:

1. Right-click the Minimum value under the 'Green' column > Select Embedded Summary > Insert Embedded Summary.

2. You should cells labeled 'Edit this formula'.

3. Right-click this cell > Embedded Summary > Edit Calculation Formula > Use this code:

GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1) - GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0)

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 24, 2017 at 02:08 PM
0

Sorry, the formula should be:

GridValueAt(CurrentRowIndex, CurrentColumnIndex, 0) - GridValueAt(CurrentRowIndex, CurrentColumnIndex, 1)

Also, Where would you like to display the addition of Row and Column 'Difference' columns? Outside the crosstab or within the crosstab as a new Row and Column?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 24, 2017 at 01:50 PM
0

That's great Abhilash. Difference Calculation worked perfectly but am getting a minus (-) symbol in the front. Please find the attached image.

And also i want to sum the Difference of each Column & Row to derive another value. For example in the attached, i want to Sum the Difference value under "green" column and also i need to Sum the Difference value under "Apple" row. Is that possible?

Is there way to save the value from Cross-tab? (either Embedded Summary or the Max Min value)24feb-2.jpg


24feb-2.jpg (33.8 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 27, 2017 at 06:45 AM
0

Thank you. Latest formula worked !!!

I would like to display the addition of Row and Column 'Difference' columns within the crosstab as a new Row and Column and also i need to store these values Outside the crosstab because here am trying to build a complex report with many calculations or manipulation. So I need these addition of Row and Column 'Difference' columns outside the cross tab as well (i.e., how to save or store the values calculated from Cross-tab concept?).

Share
10 |10000 characters needed characters left characters exceeded