• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

Crytal Reports XI R2 - Summing a calculated field with condition

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
gowflow
Asked:
gowflow
  • 5
  • 5
  • 4
2 Solutions
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
mlmccCommented:
If you want to show 27.5 in each record just use a formula like

DO_Amount
27.5

mlmcc
0
 
gowflowAuthor Commented:
@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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
IF it's a formula that simply provides a number. Try to start it with:
-----------------------------------
WhileReadingRecords;
27.5;
-----------------------------------
0
 
mlmccCommented:
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
 
gowflowAuthor Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
See my answer above.
0
 
mlmccCommented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Exactly.  This is because constants are treated as BeforeReadingRecords.
That's why I suggested added
WhileReadingRecords; to the formula.
0
 
mlmccCommented:
I tried it with WhileReadingRecords; The INSERT option still wasn't available

mlmcc
0
 
gowflowAuthor Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
mlmccCommented:
Agree.  You need to manually sum the values or you could use a running total with the appropriate evaluate when clause

mlmcc
0
 
gowflowAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now