Crystal 2008 can calculate Weighted averages ?

[robercr12]

Hi,

We are using crystal version 2008.

Can you tell me if Weighted averages can be calculated using crystal reports?
We were having issues using Bex and we did not want to store the calcuated values in cubes.

Can you tell me in brief what all do I need to use to be able to do weighted averages like combo of formulas + Stored procedures and or sub-reports?

any links to such info would also be very helpful

Thanks in advance
bob

 

[lbass]

If you insert a running total, you have the option of weighted average as a summary. Beyond that, I don't have any suggestions.

-LB

 

[robercr12]

Hi,

Thanks for the quick reply.

Our final value would be based off of 4 different calculations all interconnected with each other.
So if we have one calucated weighted average based on a query -- it will have to be used in an other query and so on and the final result will have to be calculated in crystal reports. (cascading type)

I hope this explanation helps.
Please advise if this is possible or if you would like more clarfication

thanks

 

[MCuthill]

robercr12,

Four independent calculations, with a weighted average of the total of the four?

I would think Shared variables running through 4 Subreports would be the approach to take.

What type of data are you seeking the weighted average for?

At a very high-level, in pseudo-code:
Main Report (above sub-rpts)
- Initialize variables, setting values to zero

In each Sub Report:
- calculate value needed for W-avg as well an accumulation for the "total" using the variables set above.
- for example: if w-avg of Rate vs Balance, you would have one Variable to store Rate x Balance and another adding Balance to itself

In main report, below the sub reports:
- Final calculation, something like: RateXBalVariable / TotalBalVariable

If you provide a bit more detail as to the type of calculation/fields involved, more specific help can most likely be provided.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

quick oversight:

In each Sub Report:
- calculate value needed for W-avg as well an accumulation for the "total" using the variables set above.
- for example: if w-avg of Rate vs Balance, you would have one Variable to store Rate x Balance (and added to itself) and another adding Balance to itself

You of course need the sum of the Rate x Balance figure in the final calculation, so it would also need to be accumulated as calculated.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[robercr12]

thanks !

So we can summarize data coming from sub-reports?

Secondly,
we can access stored procedures from within crystal -- correct?

Thanks
bob

 

[MCuthill]

robercr12,

Yes to the first, the second... I have never worked with and can't say for certain.

The key to summarizing between Subreports, Multiple-Subreports and Main Report is declaring variables of "Shared" type, if you miss the declaration, they can only be used within the report they exist (whether a subreport or a main).

An example declaration (using the Balance x Rate scenario above) is simply:
{@VariableRESET_TotalBalance}

WhilePrintingRecords;
Shared NumberVar TotalBal:=0;

Carrying this example to a subreport, the accumulation of Balance would look something like:
{@VariableSET_TotalBal}

WhilePrintingRecords;
Shared NumberVar TotalBal:= TotalBal+{BalanceField};

To display the total balance after all subreports/accumulation would be:
{@VariableDISPLAY_TotalBal}

WhilePrintingRecords;
Shared NumberVar TotalBal;

Note: you can do all of your resets and/or variable setting in one formula if you wish, I personally opt to keep them separate. From my formula field names, I am sure you can surmise my reasoning for doing so - I had a report with over 100 over these types of variables, and having the variable name I was working with in the formula name was very handy when tracking that many variables.

To be honest, I never know when to include or not include the "whileprintingrecords;" line... but I always have it in there and it has worked for my uses. I know there are other functions (WhileReadingRecords, for example) and I am not too sure the difference between them and also know you can leave the clause out entirely.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."