| Author |
 Topic  |
|
|
Adrian
New Zealand
28 Posts |
 Posted - 09/24/2006 : 22:03:50
|
Hi
I have a report where I include a selection of members with all their children. I want to have a sum of all the children (without doubling up their parents, which are subtotals). Not all the members are selected, so I can't use the overall total for the hierarchy. Does anyone know of a way to do this with ReportPortal. The only way I can think of is with a calculated member, but that will prevent the filters from working.
Cheers
Adrian |
|
|
sefer
Germany
105 Posts |
Posted - 09/25/2006 : 04:06:12
|
Hi Adrian,
if i undestand you right, try the "drilldown"-mode.
Sefer |
 |
|
|
marco
Netherlands
588 Posts |
Posted - 09/25/2006 : 05:16:47
|
Hi,
1. You can use the Drilldownmode + Show totals of ReportPortal or
2. You can use the calcultaed measure:
([Measures].{Measurename1],[Customers].[All customers],[Products].[Drinks])
Best regards,
Marco
|
|
|
|
Adrian
New Zealand
28 Posts |
Posted - 09/26/2006 : 16:27:58
|
Hi
Thanks for replying guys
I think my explanation was a bit poor, I thought I'd give an example to help out. Just to note, I don't actually have a problem obtaining the data, just formatting it in such a way that the user likes.
This is what we would like to see:
_________________Col1____Col2
Product1_________10______15
__SubProduct1A____5_______7
__SubProduct1B____5_______8
Product2__________3_______1
__SubProduct2A____3_______1
Total____________13______16
The 'Total' is the bit I am struggling with. If I add sum to the bottom of the exisiting report I would get 26 and 32 as results. If I add a calculated measure I can't incorporate it nicely with the Product Dimension (unless there is a way I don't understand). If I use Drill Down I lose the subtotals (Product1,Product2). I can't use the 'All Product' as the total as we are purposely not including Product3.
Cheers
Adrian |
Edited by - Adrian on 09/26/2006 16:32:49 |
|
|
|
marco
Netherlands
588 Posts |
Posted - 09/26/2006 : 23:22:12
|
Hi,
If you don't want to have Product 3 in the list than you can add Product dimension in your page filter area and show only the needed items and so in the olap report table you see all calculations ok. If you prefer you can also hide the dimension in the page filter area.
Only the grand total is above and not below in the list.
Regards, Marco |
|
|
|
sefer
Germany
105 Posts |
Posted - 09/27/2006 : 01:07:51
|
Hi Adrian,
there are to options to do this:
OLAP:
1.)Expand-Mode
2.)put your products and measures to the table and expand
3.)Options-->Summary: Maximum
OWC:
1.)put your products and measures to the table and expand
thats all
regards
Sefer
|
|
|
|
Adrian
New Zealand
28 Posts |
Posted - 09/27/2006 : 19:31:40
|
Hi
I have tried those methods and:
1. using the filters doesn't work as the All Product member will always display the total including Product3
2.OLAP with maximum, doesn't work as it only gives me the Maximum Value, in my example it would be 10 and 15 (as the 'Total' is what I'm trying to determine).
3. OWC, this does work very easily, I set this up for the first time and gives me just what I want. I'm pretty keen to keep only one type of report to keep it simple for the users, so probably won't move to this.
Thanks for your effort guys. I might give up on this for the time being. I think this needs to be fixed in report portal itself as I can't imagine a requirement to double up the Sum (ie sum Product2 along with Product2A)
Cheers
Adrian |
|
|
|
admin
1645 Posts |
Posted - 10/03/2006 : 18:44:10
|
One thing you can do is create a calculated member for your dimension on the Analyses Services side. The formula could look something like this:
Sum(
Except(
[Customers].[All Customers].[USA].Children,
{[Customers].[All Customers].[USA].[OR]}
)
)
|
|
|
|
Adrian
New Zealand
28 Posts |
Posted - 11/02/2006 : 13:00:28
|
Hi
This would definately work, but we have too many reports to create all these calculated measure in AS. Also users put on there own totals. Is it possible to get the sum functionality fixed up similar to how it is in excel?
Cheers |
|
|
| |
Topic |
|
Report Portal
Sum of bottom level information
