Calculate the total sum of data in CR

[lotus18]

Hello World!


I have my sample report here


How to get the total sum of my fields that are not duplicated? The highlighted are examples of my duplicated fields (both lec and lab) but they don't have the same time.

I tried Suppress if duplicated under Format Object of the field but still it gives me a total of 19 for Lec (instead of 14) and 4 for lab (instead of 2)

[Dököll]

Heiya lotus18!

What version of CR are you using? I do not have this installed here, but I do at work and have taken a couse in CR 11. Perhaps I can salvage something for you or at least ask gurus there; unless someone else sees your post and comes to aid before I do.

Sorry for your troubles!

Dököll

[lotus18]

I don't know what version of crystal report is this. The CR I am using right now is integrated on the IDE of visual studio 2005. BTW, I also have a CR11 here installed in my pc.

Waiting for your replies. Thanks Dököll : )


Rey Sean

[Dököll]

Quite welcome, lotus18!

Questions for you; are you hoping to combine LEC and LAB to get fourteen, I'm a little unclear on that one. And if so, what are combining to get only 2 in LAB?

Will be loking at this at work, late Monday...

In a bit!

[lotus18]

Quite welcome, lotus18!

... are you hoping to combine LEC and LAB to get fourteen, I'm a little unclear on that one. And if so, what are combining to get only 2 in LAB?

No. If you are going to compute the total LEC in the report no doubt it is 19 as seen above. But my problem is, since the highlighted details are the same subjects however they don't have the same schedules, it should be treated as 1. I know its getting complicated now : )

Like for example, I have 2 ENGG MECH (see image above), they have the same no. of lec which is 5, so there total is 10. I want to treat this 2 subjects as 5 lec and not 10. So, total Lec now is 14. Same as well as with the lab. Hope you got it : )


P.S. All same subjects here have the same lec or lab




Rey Sean

[Dököll]

Hello Rey!

Hope this isn't something you need right away, I could not do anything today, working CR is not my normal routine at work, had to jump into something straight away as usual...

It does sound a bit confusing, but I'll ask about Supressing dupes and see what I can gather, okay...

Rest assured I sent the link there for quick access.

Hopefully soon!

Dököll

[QVeen72]

Hi Lotus,

Create a Running Total Formula, for the Lab Field, and Put the Condition, if both the Fields Change then add to the Total, else, Dont add..
Or Create a new Formula Field, this is For Lec :
[code=vb]
currencyvar MyLec;
If RecordNumber =1 Then MyLec := {MyTable.Lec}
Else
If {MyTable.Lec} <>Previous ({MyTable.Lec}) And {MyTable.Lab} <> Previous ({MyTable.Lab}) Then
MyLec := MyLec +{MyTable.Lec}
[/code]

Now, Place this formula in GrandTotal Section...

Regards
Veena

[lotus18]

Hi Lotus,

Create a Running Total Formula, for the Lab Field, and Put the Condition, if both the Fields Change then add to the Total, else, Dont add..
Or Create a new Formula Field, this is For Lec :
[code=vb]
currencyvar MyLec;
If RecordNumber =1 Then MyLec := {MyTable.Lec}
Else
If {MyTable.Lec} <>Previous ({MyTable.Lec}) And {MyTable.Lab} <> Previous ({MyTable.Lab}) Then
MyLec := MyLec +{MyTable.Lec}
[/code]

Now, Place this formula in GrandTotal Section...

Regards
Veena

Thanks Veena! I'll try this later at home.


Rey Sean

[lotus18]

I tried your suggestion but unfortunately it did not work. It says "The Report Application Server Failed". :'(

Luckily, after a series of trial and error, I finally got it and I hope I had really make it. This is what I did. I edit my running total fields (total_lec) and on Evaluate Option, I changed the For each record option to On Change field option and I selected SubjectCode. I also did this to total_lab.



Now, my problem is, how can I make the duplicated subject's lec and lab fields to become empty or make the text to 0? I know this sounds complicated but I want to make this way. See my sample report below.



I want to change them (the orange block) to 0 so that it is more accurate and it looks neat.


I don't think that using Suppress if Duplicated will solve this problem.


Rey Sean

[QVeen72]

Hi,

Did you try my other suggestion..? Creating Formula Field...?

Regards
Veena

[lotus18]

Hi,

Did you try my other suggestion..? Creating Formula Field...?

Regards
Veena

Thanks Veena for your reply

What do you mean by your other suggestion? I'm a little bit confused with your suggestion I tried it but it didn't work. Hope I got your suggestion right.

Is it possible to make the duplicated subjects for both lec and lab fields to become empty or set them to 0.0? Like for this example.

--------------------------------------------------

Subjects. | Lec | Lab

--------------------------------------------------

1. Subject 1 | 3.0 | 0.0 ...
2. Subject 1 | 3.0 | 0.0 ...
3. Subject 2 | 0.0 | 3.0 ...
4. Subject 3 | 3.0 | 0.0 ...
5. Subject 4 | 0.0 | 2.0 ...
6. Subject 4 | 0.0 | 2.0 ...
7. Subject 5 | 2.0 | 0.0 ...
   --------------------------------------------------
   Total| 8.0 | 5.0

I want to make the sample table above to this

--------------------------------------------------

Subjects. | Lec | Lab

--------------------------------------------------

1. Subject 1 | 3.0 | 0.0
2. Subject 1 | 0.0 | 0.0 ->Duplicate, set 0.0 for lec and 0.0 for lab
3. Subject 2 | 0.0 | 3.0
4. Subject 3 | 3.0 | 0.0
5. Subject 4 | 0.0 | 2.0
6. Subject 4 | 0.0 | 0.0 ->Duplicate, 0.0 for lec and set 0.0 for lab
7. Subject 5 | 2.0 | 0.0
   --------------------------------------------------
   Total| 8.0 | 5.0

Rey Sean

[QVeen72]

Hi,

Create a New FormulaField, for GrandTotal, :

[code=vb]
currencyvar MyLec;
If RecordNumber =1 Then
MyLec := {MyTable.Lec}
Else
If {MyTable.Lec} <>Previous ({MyTable.Lec}) And {MyTable.Lab} <> Previous ({MyTable.Lab}) Then
MyLec := MyLec +{MyTable.Lec}

[/code]

Now, Place this formula in GrandTotal Section...

Regards
Veena

[lotus18]

Hi Veena,

As you have said, I created a new Formula fields named GrandTotal and I placed your codes. After that, I putted it on the GroupFooterSect ion of my report but still it didn't work. It gives me unexpected result. By the ways, this is the sample layout of my report now .



One more thing, is it neccessary to declare MyLec variable to a currency?


Rey Sean

[QVeen72]

Hi,

Can you Just Post the PrintPreview (Not Design View..) after pasting that Formula..

Regards
Veena

[lotus18]

Here's my sample report: