Shared Variable being increased by 1 for no reason 1

[glthornton]

Hi, I'm using CR XI accessing a SQL server DB. I just designed a Committed Visit Note report for our clinic. In this report, I've got the Appt Date, Note Commit Date (from a subreport), Patient Name, # of Days (Datediff between Appt Date and Note Commit Date) and a few other fields.

The problem that I'm experiencing is a formula field: 'Total Commit Visits' that is placed in both the Details b Section and Report Footer Section do not always return the same ending value. The formula for this field is:

Shared NumberVar TotCommVisits;
IF {@CommitDT}= CDate('01/01/1900')
THEN TotCommVisits
ELSE TotCommVisits:=TotCommVisits+1;

The above code works very well and as designed. But if you look at the attachment link (

http://www.driveway.com/u8v1g9d5u1)

you'll be able to see more of what I am describing. In the Report Footer Section, you can see that the value for this field was increased by 1 even though there are no additional entries. When I run the report for other date ranges, it matches perfectly. But what I just thought of, was that this only happens when the report indicates that all entries have a a commit date. If for some reason that not all entries have a commit date, then it works just fine. Does anyone have any ideas or suggestions?

Kind regards,
Glenn

 

[beltmanjr]

So, this I have seen often, but I dont have a live sample handy to help you more effectively.

What happens is that your equation is re-evaluated in the report footer and hence adds +1.

For the report footer create a new formula simply reading

Shared NumberVar TotCommVisits;

Remove the old formula from the footer and things should be well

 

[glthornton]

Thanks Beltmanjr!!!
You and I were on the same wave length when your response came in. I tried your exact suggestion and for some weird reason it kept incrementing by 1 again. So I re-used the new variable and put in some conditional coding.

IF {#Total Visits}={@Total Commit Visits}-1
THEN {@Total Commit Visits}-1
ELSE {@Total Commit Visits}

And now everything works in the proper way that I need it to. I've checked all my other calculation fields and they are now coming out perfect. Thank you so much for you quick response, I greatly appreciate it. Kind regards, Glenn

 

[beltmanjr]

Ok. I could only try this with one record in a blank report. However, my formula worked well there...

Are you sure you dont have the orginal formula double somewhere?

I know that this is such a pain!!!

 

[glthornton]

Hey, I totally understand. I do not have the formula duplicated anywhere else. Glenn

 

[beltmanjr]

I just dupliced your situation, but am unable to get it to add that last one if I make the separate 2nd formula...

Weird stuff...

Your could try to adjust the original formula to

Shared NumberVar TotCommVisits;
IF {@CommitDT}= CDate('01/01/1900')
THEN TotCommVisits:=TotCommVisits
ELSE TotCommVisits:=TotCommVisits+1

I hate it when things dont work!

 

[glthornton]

Shazam!! That one finally worked!!

What I did was take the code you provided me and put that in the formula that is located in the Details Section. Then I removed the old code from the formula that was located in the Report Footer Section and replaced it with "Shared NumberVar TotCommVisits;" in there. And now everything seems to be working properly. I do hate it when quirky things like this happen, but I appreciate your persistance on this to finding a much more appropriate work around. Kind regards, Glenn

 

[beltmanjr]

Super!!!