I am trying to run a total in discoverer but it comes up blank. I am building the report to run in both discoverer plus and desktop. When I add the total it shows blank(not null). The interesting thing is that cell sum, sum, and count will not work. However count distinct does work just fine. I am trying to total a calculated field. The calculated field comes from a business area. If i do a calulacation in the report to times it by 1 then the totals start to work. Is there any advice you can give me? In desktop I have tried changing the aggregation settings but it still does not work. Any help would be very appricated.
I just encountered an almost identical situation at a client of mine.
I'm wondering whether you are trying to calculate something like this:
WHEN fieldA = 'X' THEN 1
WHEN fieldA = 'Y' THEN 2
WHEN fieldA = 'Z' THEN 3
and now you are trying to do this:
SUM(something) x calculation above
The problem is caused by the initial calculation working at the Detail level, then trying to multiply the results by some aggregated value to get a new value. You cannot total or subtotal items calculated this way. Ideally you need a subquery to get the initial results and then apply the calculations on top of that.
I was wondering whether an analytic function using SUM OVER might be better and might work.
Can you give this a try?
The calculation i'm trying to total is below.
SPPCPAY(BAPAY Pr Pay Headers.Id,SPGETPPC('139'))
This just gets an amount from a paycode in our database. This calculation is titled Lease miles in the business area. In the report if i times the lease miles by 1 then I can total it. However if I do not times it by 1 then it doesn't let me total it.
Turns out I had a join set up wrong in the Business Area. I didn't have a 1:1 rule between fields and that was causing it not to calculate. I searched you past posts (there were a lot to go through) and found one where somebody had a simmilar issue and you had them check the joins. Thank you for the help. Its great to be able to get online and find the answers
Thanks for letting me know. Yes that would be caused by attempting to aggregate from the master folder in a master-child relationship. Discoverer tries to protect you and will not calculate totals or subtotals on items from the master folder. Changing the join property to 1:1 tells Discoverer you know best and so it will happily go on its way and do the calculations.
Of course you should only do this when you are absolutely certain that you will get the right answer.
Another way would be to alter the property called AllowAggregationOverRepeatedValues which you will find in the pref.txt. The reason for this setting is to protect against the situation where a user could build a report that pulls a metric from the header record, coupled with metrics from multiple detail records at the same time. Obviously, the metric for the header record will be repeated and if allowed to SUM will give the wrong answer.
However, if users are careful and know what they are doing, you can switch this behavior off to allow aggregations to work even when the values repeat. The onus is then on the user to ensure that they do not create SUM totals on values that should not be added together.
Hope this helps