Skip to Content
avatar image
Former Member

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

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)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

11 Answers

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

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 23, 2017 at 11:49 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 23, 2017 at 12:36 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 23, 2017 at 01:03 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 23, 2017 at 01:52 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 24, 2017 at 11:07 AM

    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)
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 24, 2017 at 11:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 24, 2017 at 01:50 PM

    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)
    Add comment
    10|10000 characters needed characters exceeded

  • Feb 24, 2017 at 02:08 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 27, 2017 at 06:45 AM

    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?).

    Add comment
    10|10000 characters needed characters exceeded