?
Solved

Crytal Reports XI R2 - Summing a calculated field with condition

Posted on 2012-09-20
14
Medium Priority
?
594 Views
Last Modified: 2012-09-22
Hello,

I have this complex report that have following sections in my Table Details_mid:
Arrival Date
Abreviation
BL
EquType

Now the records in the database are in the following pattern:
Arrival Date 1/1/2012 1/1/2012 1/1/2012 1/1/2012 .....
Abreviation          XYZ           XYZ           ABC        ABC
BL                          001          001           002         003
EquType                   C              R               C             C

The question is the following: I have in the report several fields that exist in the database like QTY,  BAS_P, OTH_P, BAS_C, OTH_C that I am able to summarize these are costs and quantity values and I am able to summarize them thru all the group sections in the report and it is working fine.

My detail section is as follows:
QTY     @FIO          BAS_P       OTH_P        BAS_C       OTH_C         @TOTDUE

@FIO is a complex formula of several fields and it can be summarized with no problem.
@TOTDUE formula is like this: BAS_C+OTH_C+@FIO

NOW I NEED TO INTRODUCE THE FOLLOWING:
$27.5 should be added as DO for each BL so that the line would show like this:
QTY     @DO    @FIO          BAS_P       OTH_P        BAS_C       OTH_C         @TOTDUE
and I need to summarize @DO in all the below section which I am not able to do and also the @TOTDUE will become like this:

@TOTDUE formula is like this: BAS_C+OTH_C+@FIO+@DO
When I do this I get the following error:
A summary has been specified on a non recurring Field: Details @TOTDUE

So in the example of the data previously posted the DO should be like following:
Arrival Date 1/1/2012 1/1/2012 1/1/2012 1/1/2012 .....
Abreviation          XYZ           XYZ           ABC        ABC
BL                          001          001           002         003
EquType                    C              R               C             C
DO                        27.5              0           27.5       27.5 ...

So for same Abreviation same date same BL only 1 instance of 27.5 etc... this is not a field but a value we need to show in the report with all the totals in all the sections.

Appreciate any help and sorry for the extended explanation on this. If needed I can post the report but prefer not to as it contain sensitive data.

Rgds/gowflow
0
Comment
Question by:gowflow
  • 5
  • 5
  • 4
14 Comments
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38417533
This error typically comes up if you try to do a summary on a shared variable or on a formula that has an evaluation time of whileprintingrecords.  

Hopefully, this should get you going in the right direction.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38417584
If you want to show 27.5 in each record just use a formula like

DO_Amount
27.5

mlmcc
0
 
LVL 31

Author Comment

by:gowflow
ID: 38417808
@IdoMillet and @mlmcc tks ur both replies here is to clarify:

I am looking to summarize this variable DO and this I am not able when I right click on the @DO (that contains 27.5) and choose Insert it shows me Field Headding as per the below picture. I need to see Insert Summary or Subtotal which shows when you right click on a field it does not do it here WHY ????

Snapshot of the report
tks ur help.
gowflow
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 23

Expert Comment

by:Ido Millet
ID: 38417857
IF it's a formula that simply provides a number. Try to start it with:
-----------------------------------
WhileReadingRecords;
27.5;
-----------------------------------
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38418228
SHow the formula

It can't have WhilePrintingRecords; as the first line and it can't have any SHARED variables declared.

There are also some functions that can't be used.

mlmcc
0
 
LVL 31

Author Comment

by:gowflow
ID: 38418699
Well my formulas has simply 27.5 in there and still cannot create a summary out of this formula. You mean to say I should be able to summarize this formula ? and insert subtotals in each section as this is what I am aiming at ?? if yes I would appreciate to know how.
Tks/Rgds

gowflow
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38418727
See my answer above.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 38419014
Interesting.  I just tried in CR2008.  

Formula1  -  Cannot be summarized.  With no field heading got the insert field header option
27.5

Formula2  - same as above
WhileReadingRecords
27.5

Formula 3 - works
If  {Field1} = {Field1} then
    27.5

mlmcc
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 38419307
Exactly.  This is because constants are treated as BeforeReadingRecords.
That's why I suggested added
WhileReadingRecords; to the formula.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38419717
I tried it with WhileReadingRecords; The INSERT option still wasn't available

mlmcc
0
 
LVL 31

Author Comment

by:gowflow
ID: 38420961
We are getting closer !!!!!

@IdoMillet: you are correct if @DO has
WhileReadingRecords;
27.5

Then yes I can use Summary

@mlmcc You are correct if @DO has
If {Field1}={Field1} then 27.5

Then yes I can use Summary

Thanks for both suggestions which opens a new possibility that I did not know so far.

My problem though is not solved as you may look at my initial question you will notice that I do not need 27.5 at each and every record like the data I gave you example with. I need this to work like following and here I am not able to get the Summary to show:

if {Details_mid.BL}<>Next({Details_mid.BL}) Then 27.5 Else 0

I need to have this formula working in @DO as I need 1 instance of 27.5 for each BL and sometimes I have 100 records for the same BL and I need only 1 time 27.5 not 100 times.

I tried putting
WhilePrintingRecords;
if {Details_mid.BL}<>Next({Details_mid.BL}) Then 27.5 Else 0

then I do not get Summary

I Tried putting
WhileReadingRecords;
if {Details_mid.BL}<>Next({Details_mid.BL}) Then 27.5 Else 0

I got Next({Details_mid.BL})  This Function cannot be used as it must be evaluated later.

Appreciate all your help to de-mistify this.

PS I REMIND BOTH OF YOU I AM USING CR XI R2 and not CR2008 and NO WAY I CAN UPGRADE as need this version to support all the apps I developped in vb6 that is no more supported by CR2008.

Rgds/gowflow
0
 
LVL 23

Assisted Solution

by:Ido Millet
Ido Millet earned 1000 total points
ID: 38421214
The use of Next() in your formula turns it into a WhilePrintingRecords() function and you would no longer be able to sum it.

Instead, capture that logic in your data source (use Command, View, or Stored Procedure) or use variables to accumulate the value using the desired logic.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38421757
Agree.  You need to manually sum the values or you could use a running total with the appropriate evaluate when clause

mlmcc
0
 
LVL 31

Author Closing Comment

by:gowflow
ID: 38424807
Great !! I had solved via variables but needed to get it working thru Summary. Which obviously is not possible as you clearly mentioned.

The trick I learned here from mlmcc was the way to turn the constant into field which is GREAT as then you can use the Running Totals which facilitate life much easier then throwing variables here and there.

I also appreciate the input of IdoMillet for turning the constant into field by adding the WhileReadingRecords

I thank both of you for the great input and will fairly split it here.
Tks again
gowflow
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question