Crystal Reports loop

[former_member548403]

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

[abhilash_kumar · Accepted solution]

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

[former_member548403 · Accepted solution]

You are the man!! Thank you!!!

[abhilash_kumar]

Hi Kenshin,

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

-Abhilash

[former_member548403]

https://drive.google.com/open?id=1Xg6uwfgTRXm1IxRC6u7aklPzuzejUSwm

[former_member548403]

I sincerely appreciate your help!

[former_member548403]

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

[abhilash_kumar]

Change the extension of the report from .rpt to .txt and then attach.

If that doesn't work, upload to google drive/dropbox or any other location and post the link here.

-Abhilash

[former_member548403]

https://drive.google.com/open?id=1Xg6uwfgTRXm1IxRC6u7aklPzuzejUSwm

[abhilash_kumar]

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

[abhilash_kumar]

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

[former_member548403]

(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!!!