Sum based upon field values 1

[baycolor]

I'm using CRV11...

I have a bunch of detail data returned from a view and I want to do a lot of calculations based upon the data. And then I want to display these calculation results in the detail section of my report. I won't be displaying any detail data as its returned from the database in the detail section. NOTE: The data read from the database is not grouped or anything.

Some of the detail data look like...
JobFunction SalesOrg JobFunctionDesc AccountOwner
751 755 Sales N
751 755 Sales Y
751 755 Sales Y
752 759 Marketing N

First thing I needed to do is count the number of persons that have a JobFunction = 751 and a SalesOrg value of 755 and are AccountOwners.

Simple I would think - create a formula:

********************************
WhileReadingRecords;

Global NumberVar Cnt755Ao;

If {RMAL_V_PB_DETAIL.Job_Function} = 751 and
{RMAL_V_PB_DETAIL.Account_Owner} = 'Y' and
{RMAL_V_PB_DETAIL.Sales_Org} = 755 and
Not IsNull({RMAL_V_PB_DETAIL.Sales_Org})
then
(
Cnt755Ao := Cnt755Ao + 1
)
********************************

Then I would have the output of this formula value available for displaying in the detail section.

I originally put this formula field in the Detail Section (no other fields in the section). One row shows up for each returned detail row - in above example 4 rows are returned. Row 1 has a 0 formula value, row 2 has a 1 formula value row 3 has a 2 formula value and row 4 has a 0 formula value. Output is correct. But I only want the output formula value of 2 (the total based upon my conditions)

I need to do a ton of formulas like this.

How do I get just the final total formula value (2) for display purposes (1 row only)?

Thanks in advance.

 

[baycolor]

Wouldn't you know it after I post I make some progress...

I created a different formula and then I Inserted a Summary based upon that formula. New formula:

********************************
WhileReadingRecords;

If {RMAL_V_PB_DETAIL.Job_Function} = 751 and
{RMAL_V_PB_DETAIL.Account_Owner} = 'Y' and
{RMAL_V_PB_DETAIL.Sales_Org} = 755 and
Not IsNull({RMAL_V_PB_DETAIL.Sales_Org})
then 1
********************************

I then put the formula field in the Report Footer section and I've got the correct value being displayed.

New problem is since I need to use this value in calculations with other formula fields that will be created the same way I did a test and put the following formula field in the Report Footer section:

WhilePrintingRecords;
{@CntCommUsPo} / 2

The output from this formula is 0.00. How do I use the values that I calculate via formulas and Insert Summary in other formulas?

Thanks

 

[lbass]

You can't accumulate across detail rows using a running total (manual or otherwise) and then display the final result in those detail rows. You could use a formula like {@755Ao}:

If Not IsNull({RMAL_V_PB_DETAIL.Sales_Org}) and
{RMAL_V_PB_DETAIL.Job_Function} = 751 and
{RMAL_V_PB_DETAIL.Account_Owner} = 'Y' and{RMAL_V_PB_DETAIL.Sales_Org} = 755 then 1

You could then create a second formula:

sum({@755Ao})

...and place it in the detail section, but this would repeat for each detail row. I suppose if this were the only item in the row, you could right click on it and choose "Suppress if duplicated", but this would result in one row per report--the same as just right clicking on the formula and displaying the result in the report footer. How do you want your final report to look?

Note that in the formula your null check should be first.

-LB

 

[lbass]

Just remove the whilereadingrecords and whileprintingrecords. You don't need them.

-LB

 

[baycolor]

Thanks.

I found I was able to do a calculation in the formula editor. The inserted summary fields are available for selecting and doing calculations. After I selected my summarized field in the editor it built the following formula:

Sum ({@755Ao}) / 2

When this was displayed in the Report Footer section the output was correct.

My only questions now is should I be building a complete report in my Report Footer section? Is that a bad idea?
There won't be anything in this report besides a lot of calculations like the above.

Thanks for the quick responses.

 

[lbass]

Building a report in the footer is fine. Depending upon what you want to calculate, though, it might make more sense to use group summaries. For example, if you wanted calculations on every combination of job function and sales org, it would make more sense to insert groups on these fields, and then insert summaries on data at these group levels. You would then suppress the detail section so that the results would display in a summary fashion. You might want to experiment a little with this.

-LB

 

[baycolor]

Thanks for all your help.