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]

@Dököll

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 :

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}
 

Now, Place this formula in GrandTotal Section...

Regards
Veena

[lotus18]

@QVeen72
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]

@QVeen72
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, :

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}
 
 

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 GroupFooterSection 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:

[Dököll]

Sorry Rey Sean!

I hadn't forgotten you, it's just been a crazy week... Certainly happy that you have some company:-) Will be asking my colleagues to make it quicker response, of course only if V or others hadn't nailed it for ya...

In a bit!

Dököll

[lotus18]

@Dököll
Still hoping :-(


Rey Sean

[QVeen72]

Hi,

I guess, Formula is Fine, Just Change the Sorting Order of the report..
Sort First by Lec and then by Lab...
Also post your formula here...

Regards
Veena

[lotus18]

@QVeen72
I changed my sql statement from

SQLString = "Select Distinct * From PrintSchedule Where Courses.Title='" & frmPrintOption.cboCourses.Text & _
            "' And Semester='" & frmPrintOption.cboSemester.Text & "' Order By SubjectCode"
 

to this (Sorted First by Lec and then by Lab)

SQLString = "Select * From PrintSchedule Where Courses.Title='" & frmPrintOption.cboCourses.Text & _
            "' And Semester='" & frmPrintOption.cboSemester.Text & "' Order By Lec, Lab ASC"

When I tried to the sort the lec and lab the value of @GrandTotal now is Php0.0 (previous value is Php3.0 as seen on post #15)

Here's the formula:

@GrandTotal (Formula Field)

//Taken From Veena
currencyvar MyLec;
If RecordNumber =1 Then 
    MyLec := {PrintSchedule.Lec}
Else
    If {PrintSchedule.Lec} <>Previous ({PrintSchedule.Lec}) And {PrintSchedule.Lab} <> Previous ({PrintSchedule.Lab}) Then
   MyLec := MyLec +{PrintSchedule.Lec}

#total_lec(Running Total Field)

• • Summary
• Field to summarize : PrintSchedule.Lec
• Type of summary: sum
  • Evaluate
• On change of field : PrintSchedule.SubjectCode
• On change of group : Group#1: PrintSchedule.SectionUnit - A

#total_lab(Running Total Field)

• • Summary
• Field to summarize : PrintSchedule.Lab
• Type of summary: sum
  • Evaluate
• On change of field : PrintSchedule.SubjectCode
• On change of group : Group#1: PrintSchedule.SectionUnit - A

Rey Sean

[Dököll]

Rey Sean, Do forgive me, a load of stuff happened... just bad timing, trust me I do have a number of CR experts near me and I would get you the answer you need even if I could not do it myself... thing is, I am entrusted to finish an application in Java and have been practicing and reading all about it...

Even if I am late in providing you a cure, I will still post what I find out, or at least tell you the smoke has finally cleared and that I'm on it.

As said I don't normally work with CR, it would have helped... makes it hard to go into it there at will. I am also an army of one at work:-)

Sorry Pal!