Skip to Content
0

Crystal Reports loop

Dec 21, 2017 at 05:57 PM

70

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Abhilash Kumar
Dec 22, 2017 at 03:57 PM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

You are the man!! Thank you!!!

0
Abhilash Kumar
Dec 22, 2017 at 08:55 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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)
0
Abhilash Kumar
Dec 22, 2017 at 01:33 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Kenshin Himura Dec 22, 2017 at 01:55 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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

0
0
Abhilash Kumar
Dec 22, 2017 at 03:02 PM
0

Hi Kenshin,

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

-Abhilash

Show 2 Share
10 |10000 characters needed characters left characters exceeded
0

I sincerely appreciate your help!

0