Skip to Content

Crystal Reports loop

Thanks for the help I received from here (great people we have here!), I was able to complete my report that performs a couple of calculations based on year parameters with like 5 years in between. But now, my boss tells me the parameters have to be whatever; the number of years in between the begin year and the end years could be 5, 9, or 12, and the calculations need to be able to handle it.

Now I'm thinking I have to incorporate some kind of a loop here (right?) I'm thinking about "While Do" loop, but how do I make the loop stop AFTER the calculations are done with the end year?...

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Dec 22, 2017 at 03:57 PM

    Hi Kenshin,

    Attached is the updated report.

    I updated:

    1. The Insertion formula so that it dynamically inserts calculated columns at required intervals and works with any number of years

    2. The Calculation formula for both columns so that it dynamically 'looks' at previous year columns for subtraction and percentage calculation.

    https://drive.google.com/file/d/1616lgk5lHt7Euxl3p95tHuX-a9t0P_S2/view?usp=sharing

    I hope this helps.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 22, 2017 at 08:55 AM

    Hi Kenshin,

    Yes, you'd need a loop.

    Could you please attach a screenshot of the Crosstab and an example of the required calculation?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • (To get the difference)
      GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(Totext( (tonumber({?year1}) + 1 ), 0, "" )), CurrentSummaryIndex) 
      - GridValueAt(CurrentRowIndex, GetColumnPathIndexOf({?year1}), CurrentSummaryIndex)
      
      (To get the percentage: this code is in Format Field because the user wants a decimal point)
      if GridRowColumnValue("Command.fiscal_year") = "%CHG1"
      then
      (
        if GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) =0
           then '0'
        else
        (
           totext(
                  (( GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( Totext( (tonumber({?year1}) + 1 ), 0, "" ) ), CurrentSummaryIndex)
                  -
                  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex) )
                  /
                  GridValueAt(CurrentRowIndex, GetColumnPathIndexOf( {?year1} ), CurrentSummaryIndex))
                  *100.0
                  ,1) 
        )
      )
      else
      if GridRowColumnValue("Command.fiscal_year") = "%CHG2"
      .....

      Here is a screen shot of my crosstab. There are two parameters - a begin year and an end year - that user selects. For this example, a user selected 2013 as a begin year and 2015 as an end year. The calculations obviously would have to end after the end year.

      Thanks!!!

      diff-and.png (3.7 kB)
  • Dec 22, 2017 at 01:33 PM

    Hi Kenshin,

    I assume you've manually inserted Calculated columns after 2014 and 2015?

    You'd also need a dynamic column insertion formula!

    Would you be able to send me the .rpt 'with saved data'? It is much easier working on the report than trying to reproduce the datastructure.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 22, 2017 at 01:55 PM

    Of course, but how do I send the report to you here? It won't allow me to attach the report.

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 22, 2017 at 03:02 PM

    Hi Kenshin,

    Upload to google drive/dropbox or any other location and post the link here.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded