Crystal Report Formula will be..?

Altaf Patni
Altaf Patni used Ask the Experts™
on
Count({B_Dtl.BNo}) where ({B_Dtl.G_Name}) = ({LSheet.T_Name})
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
You could do a running total where you count the {B_Dtl.BNo} field where the condition is {B_Dtl.G_Name}) = ({LSheet.T_Name} and reset when appropriate (either at a group level or never, most likely).
Altaf PatniSoftware Developer

Author

Commented:
Thanks for Quick Reply sir..

I want count of records in Detail section.
and this is the reason i want to use formula.

Commented:
add  a running total field

field to summarize pick a field
type of summary  count
evaluate
for each record


reset
never

see example in word doc
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
You can't have a 'count' in a detail field unless it's incremental, which is why you'd use a running total as indicated in my post.
Altaf PatniSoftware Developer

Author

Commented:
Following is Detail Data..

CstNo - BNo  - Gdate      - TName - Weight - Amt    - BAmt

1          1001 - 24-03-12 -  ABC      - 500      - 5000  -  5500
1          5102 - 24-03-12 -  ABC      - 100      -   150  -    250
1          2050 - 24-03-12 -  ABC      - 450      - 1100  -  1500
2          1051 - 24-03-12 -  ABC      - 200      - 1100  -  1150
2          5312 - 24-03-12 -  ABC      - 750      -     50  -    950
2          4135 - 24-03-12 -  ABC      -   50      - 1500  -  1400

1          3101 - 24-03-12 -  WXYZ    - 500      - 5000  -  5500
1          2702 - 24-03-12 -  WXYZ    - 100      -   150  -    250
1          4650 - 24-03-12 -  WXYZ    - 450      - 1100  -  1500
2          1751 - 24-03-12 -  WXYZ    - 500      - 5000  -  5500
2          3612 - 24-03-12 -  WXYZ    - 100      -   150  -    250
2          4135 - 24-03-12 -  WXYZ    - 450      - 1100  -  1500

1          1001 - 25-03-12 -  JKLM      - 500      - 5000  -  5500
1          5102 - 25-03-12 -  JKLM      - 100      -   150  -    250
2          1051 - 25-03-12 -  JKLM      - 500      - 5000  -  5500
2          5312 - 25-03-12 -  JKLM      - 100      -   150  -    250
2          4135 - 25-03-12 -  JKLM      - 450      - 1100  -  1500

1          3101 - 25-03-12 -  PQRS      - 500      - 5000  -  5500
1          2702 - 25-03-12 -  PQRS      - 100      -   150  -    250
1          4650 - 25-03-12 -  PQRS      - 450      - 1100  -  1500
1          1751 - 25-03-12 -  PQRS      - 500      - 5000  -  5500
2          3612 - 25-03-12 -  PQRS      - 100      -   150  -    250
2          4135 - 25-03-12 -  PQRS      - 450      - 1100  -  1500


And what i want is in my report is as follow...

CstNo   BCount  CstDate     TName    Sum
1          3          - 24-03-12 -  ABC      - 1050      - 6250  -  7250
2          3          - 24-03-12 -  ABC      - 1000      - 2650  -  3500

1          3          - 24-03-12 -  WXYZ    - 750      -   700  -     500
2          3          - 24-03-12 -  WXYZ    - 550      -   300  -     850

1          2          - 25-03-12 -  JKLM     - SUM      - SUM  -  SUM
2          3          - 25-03-12 -  JKLM     - SUM      - SUM  -  SUM

1          4          - 25-03-12 -  PQRS     - SUM      - SUM  -  SUM
2          2          - 25-03-12 -  PQRS     - SUM      - SUM  -  SUM
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Try this formula

If ({B_Dtl.G_Name}) = ({LSheet.T_Name})  then
    1
Else
    0

Insert it in the detail sectionRight click it
Click INSERT --> SUMMARY
Type - SUM
Put it in the group/report footer as appropriate

You can then remove the formula from the detail section

mlmcc
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
My solution will do that, exactly as I have stated.  If you create a running total to increment the count or sum based on that evaluation condition and reset it at a group level (which in your case seems to be first by date then by customer) then you'll get the results you need by placing the running total in the group footer.
Altaf PatniSoftware Developer

Author

Commented:
i tried that formula and so far i am near to get my report...:-)
Group problem...
Which field must i assign as a group.?
B_Detail.G_Name = Lsheet.T_Name.
B_Detail.CST = Lsheet.LsNo.
B_Detail.GRDate = Lsheet.LDate.

in above fields There is no any UNIQUE field.

Most of data is in B_Detail,  but data is...in detail format. not in summary form.
Altaf PatniSoftware Developer

Author

Commented:
Example data.

CST   DATE    Name   B_Count     Amount    B_Amount
1      16-03      ABC       120             500          550
2      15-03      ABC         90             200          300
3      15-03      ABC       115           1100          900

1      15-03      XYZ         25              300          450

1      17-03      MATR      50              760          200
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
None of them.

Does the report have a group?
If not then there is no field to use for the group field

FOr the result you want, itlooks like you need to group on
TName then CstNo

mlmcc
Altaf PatniSoftware Developer

Author

Commented:
ok..
according to this groups (TName then CstNo)
it looks likes my report...But
for Sum of Weight i am not getting correct sum ( by running total from B_Dtl.Weight) and same for others Sum
Altaf PatniSoftware Developer

Author

Commented:
I am getting sum exactly i wanted.
using suggested formula ..

New Formula -Art_Ttl
If ({B_Dtl.G_Name}) = ({LSheet.T_Name})  then
    {B_Dtl.B_Art}
Else
    0

then placed in Detail section then
Click INSERT --> SUMMARY
Type - SUM
Then
Removed This formula from detail section.

Is this Correct way to get sum for other fields..?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
It should work

mlmcc
Altaf PatniSoftware Developer

Author

Commented:
Thanks Michael
its working,,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial