We are using Oracle BI Discoverer 11g (220.127.116.11.0) and Operating System: amd64 Windows 7 6.1.
For some reason, either totals or subtotal won't work in any our DISCO reports. I just realized this a few days back and have tested a few reports. None of them gives me the totals or subtotals. In the cell, it is just 'Sum:' and no values. The amount columns contains no null values for those reports, absolute values either > 0.00 or = 0.00.
One example: a report for GL journals and here is what I tested after open a DISCO report as a Plus user:
- In edit mode
- Under select items page
- Click on calculation tab
- Click on My Totals
- Click on New Totals
- Then choose a detail amount such as period_cr, total=f(x)sum, choose either grand total at bottom or subtotal at each change in: (I picked journal name)
- When the result comes back, in total or subtotal cell, there is only Sum: and not like Sum: 12,345.67
Very strange and I remember those reports worked for us before. We just moved to 11 a few months back and I am not sure if that has anything to do with this.
I have no real help for you as we're not on 11, but have you checked your aggregation settings? (I assume it's still under tools/options/aggregation.) I'd try all the possible options and see if any make a difference.
If not that, could it be one of the registry settings related to aggregation? I don't remember the specifics but maybe that would trigger some ideas. If something is different from the way it was set in your prior version...?
Thanks for your suggestions. Yes, I have tried all the possible ways for aggregations, plus setting up way to deal with null values, but so far nothing helps. I forgot to mention that the subtotal for single row works and for multiple rows, sometimes it works, sometimes it won't. The only difference I can tell is that when the query is from a detailed table, such as po_distributions_all, it works and if the query is from higher level table, such po_lines_all, it won't. Any ideas why?
Sorry, not much help here. I'm on a different platform (Desktop, version 10), but what you're describing about detail table vs. higher level table would make me suspect the AggregationBehavior registry setting. I know that doesn't apply to you but maybe someone else can help out based on that.