Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: How can I total or count each item.. Pls help T_T Post Reply Post New Topic
Author Message
bluem007
Newbie
Newbie


Joined: 18 Jan 2012
Location: Philippines
Online Status: Offline
Posts: 7
Quote bluem007 Replybullet Topic: How can I total or count each item.. Pls help T_T
    Posted: 23 Jan 2012 at 10:30pm
How can I total or count each item.

Example
Theres a group(1,2). and datafield(F1,F2,F3)

Group 1
F1     F2     F3
A       X       1
C       XX     1
Group 2
A       X       1
C       X       1

I need to display this:
Total
A X: 2
C XX: 1
C X:1


Thank you. Please help.
IP IP Logged
sgtjim
Newbie
Newbie
Avatar

Joined: 23 Aug 2011
Online Status: Offline
Posts: 32
Quote sgtjim Replybullet Posted: 24 Jan 2012 at 5:56am
I would use the "3 formula trick".

One Formula to Sum:
WhilePrintingRecords;
Shared NumberVar F3Sum;

F3Sum := F3Sum + {F3};

This formula goes in the details sections or whatever section you are display the data in.

One Formula to display:
WhilePrintingRecords;
Shared NumberVar F3Sum;
Shared NumberVar DisF3Sum;

DisF3Sum := F3Sum;

This formula goes in the footer of the level you wish to summaries. So for a grand total summary, place this display formula in the report footer. This formula may seem redundant but in many cases the "sum" formula will not display correctly without this step.

One Formula to Reset:

Note: this is only used if you are doing group level summaries, if you only want a grand total in the report footer then this formula is not needed.

WhilePrintingRecords;
Shared NumberVar F3Sum;

F3Sum := 0;

This formula will go in the group header of the corresponding summary. So if you wanted a summary in Group Footer 1 then this formula would go into Group Header 1.

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

So, you would need to use the 3 formula trick for each column you with so summarize. For your example F3 and F2 would each get their own set of three formulas for each level you are wishing to summarize.

Hope this helps.!.
IP IP Logged
bluem007
Newbie
Newbie


Joined: 18 Jan 2012
Location: Philippines
Online Status: Offline
Posts: 7
Quote bluem007 Replybullet Posted: 24 Jan 2012 at 2:14pm
Thanks for your help. :)
But I'm confused.

Where should i put the 'formula to sum in details section?
When I'm trying somewhere in the detail section a prompt message occur

"The formula result must be a boolean"

What should I do? I can't try the next formula until the first work
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jan 2012 at 4:26am
you are most likely putting the formula sgttim gave you in the select expert. That formula area is only for creating a boolean (true/false) evaluation to determine if each row should be included or excluded from the report.
In the field explorer there is a an option for "Formula Fields".
Right click on that and select new.
That is where you create new formulas to be used in the report.
 
That being said, if your data is more dynamic I think I would use a crosstab to get what you want.
Create a formula (as above) to combine F1 and F2
{table.F1} & {table.F2}
insert a crosstab in the report header or footer
insert the above formula field as teh Row
insert F3 as a sum in the summarized fields
play around with the formatting to make it visually what you want
 
IP IP Logged
bluem007
Newbie
Newbie


Joined: 18 Jan 2012
Location: Philippines
Online Status: Offline
Posts: 7
Quote bluem007 Replybullet Posted: 25 Jan 2012 at 3:37pm
Thank you very much for helping me!Clap

It really works. Smile

But I just a problem in one thing.

In the detail section I suppress the duplicated fields and remove both field in the report.
And I also supress some field so it will not appear.

See this thread:
http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15403&PID=49298#49298


How can I remove that field in the crosstab?Confused




Edited by bluem007 - 25 Jan 2012 at 9:58pm
IP IP Logged
bluem007
Newbie
Newbie


Joined: 18 Jan 2012
Location: Philippines
Online Status: Offline
Posts: 7
Quote bluem007 Replybullet Posted: 25 Jan 2012 at 9:28pm
I try suppressing fields in crosstab but nothing happen... What should I do? T_T
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Jan 2012 at 4:08am
you cannot follow the same advice as what was given in the other thread as I believe the crosstab is built before the group selection criteria is applied.
Do you know what records you need to exclude or is that information dynamic?
IP IP Logged
bluem007
Newbie
Newbie


Joined: 18 Jan 2012
Location: Philippines
Online Status: Offline
Posts: 7
Quote bluem007 Replybullet Posted: 27 Jan 2012 at 5:01am
Ex. the generated report is like this:

Group1
Fields:        F1    F2    F3
               A      X      1    
               B      X      1
               B      XX     1
               C      XXX    1

Group2
Fields:        F1    F2    F3
               A      X      1    
               C      XX     1

Cross tab
           1     Total
A    X     1       1
C    XX    1       1
C    XXX   1       1

I used this formula in suppressing the duplicate data in each group.
sum({table.F3},{table.F1}) = 1

I generate the report monthly.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.