Formula Field not available for Running Total

[BillBrosius]

Using CR10.

I have a report with a few formula fields that I created and placed in the details section of my report. I now want to total the formula fields, but only one of the formula fields is available to me when creating the Running Total calculation. The only difference between the formula fields that I can tell is that the one that is available is a "string" and the others are "number" format.

Anyone know why this is happening?

Thanks.

 

[synapsevampire]

Right click any of the fields in the details section and select insert summary, the data types will determine the type of aggregates available, obviously you cannot sum a string field.

If you have a value that is stored as a string, then write a formula such as:

val({table.field})

Then you can use the formula in lieu of the field for summary fields. Most people avoid running totals unless they need the advanced functions therein.

-k

 

[BillBrosius]

***UPDATE***

The other thing I just noticed is that the Formula Fields that are not available for Running Total are using a "Summary" as part of their calculation. How can I get around this?

Thanks.

 

[lbass]

You need to share the contents of the formulas you cannot summarize. If they contain any other formulas, show the contents of those, too.

-LB

 

[BillBrosius]

OK,

I have a report that is grouped by "Industry". An example output would be this: (this isn't going to line up right)

Quantity Security MktVal % of Total MktVal
--------- --------- ------- -----------------
Technology
10,000 IBM 20,000 15.87
5,000 MSFT 5,000 3.97
------ -----------
25,000 19.84

Communications
20,000 ATT 100,000 79.37
1,000 VIZN 1,000 .79
------- ------------
101,000 80.16

======= ============
126,000 100.00

This is an over simplified example, but I need to be able to calculate the "% of MktVal" against the Report Total.

I am currently doing this using a Sumary Field for MktVal in a Formula as follows:

({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}/Sum ({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}))*100

Now, I need to be able to Total this Formula at the Group Level:

15.87 + 3.97 = 19.84, etc.

Any ideas?

Thanks.

 

[BillBrosius]

For some reason when you use the Sum in a Formula Field, it doesn't let you use the Formula in a Running Total Field or another Summary Field for that matter.

 

[synapsevampire]

Create a formula that contains:

numbervar MySum;
MySUm:=MySum+({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}/Sum ({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}))*100

-k

 

[BillBrosius]

Yes, but,

How would you have it reset after each group? As it is written, it would continue the total from group to group.

 

[BillBrosius]

I just saw the other thread. I will try to add a reset formula in the header.

 

[lbass]

I don't see why you can't just use the following at the industry group footer level:

Sum(({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld},{table.industry})/Sum ({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}))*100

And for the report footer:

Sum(({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld})/Sum ({sp_wcm_bb_carnwyld_rpt_select;1.mktvalsrc#1_hld}))*100

-LB

 

[BillBrosius]

lbass,

I can not use a formula to calculate the % of MktVal at the footer. The example I show is a simplified version, but in reality the individual "% of MktVal" uses a weighted average calculation that can not be applied at the group footer level. The individual record values need to be added together to give the correct result for the group.

 

[BillBrosius]

synapsevampire,

I can't seem to get the Header reset formula option to work.

numbervar MYSUM:=0;

MYSUM keeps increasing the total throughout the report.

 

[BillBrosius]

Also,

Puting the MYSUM Formula in the footer only adds the last record in the group to the total.

 

[lbass]

Okay, but please in the future supply the actual formula when asked, since the solutions offered will vary depending upon it.

Create these formulas:

//{@resetgrp} to be placed in the industry group header:
whileprintingrecords;
numbervar grpsum := 0;

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar grpsum := grpsum + {@yourformula};
numbervar grtot := grtot + {@yourformula};
//where {@yourformula} is your DETAIL level formula

//{@displaygrp} to be placed in the industry group footer:
whileprintingrecords;
numbervar grpsum;

//{@displaygrtot} to be placed in the report footer:
whileprintingrecords;
numbervar grtot;

-LB