Hi,

I'm posting again because i accidentally pressed the post button.

I have a dimension with the following levels:

Perspective1
KPI1
UnitA
UnitB
UnitC
KPI2
UnitA
UnitB
UnitC
Perspective2
KPI1
UnitA
UnitB
UnitC
KPI2
UnitA
UnitB
UnitC
Etc.....

In my SQL Report i want to create a parameter where i can select t he Unit.
The report should return all the levels concerning that specific Unit. So if
i select UnitB, i should get:

Perspective1
KPI1
UnitB
KPI2
UnitB
Perspective2
KPI1
UnitB
KPI2
UnitB

I know you can do something like:
[All Perspective].[KPI].members
to get all the Units.
But now i want only one unit with all the KPIs. Is this possible?

Probably you would say to rearrange my cube and dimensions, but i already
did(because of another 'problem') and came up with this version. So, if it's
not possible than i will go back to my previous design, because basically
this design works best for me .

Thnx,

Stanley

Maybe this Foodmart query might help:

select
{[Measures].[Unit Sales]} on columns,
hierarchize(generate ( filter([Product Name].members,
right(product.currentmember.name,10)="Light Beer"),
ascendants(product.currentmember))) on rows
from Sales

Instead of

right(product.currentmember.name,10)="Light Beer"

you could use something like:

[Your dimension].currentmember.name="UnitB"

HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Stanley" wrote:

Hi Brian,

This helped some. I did get the KPIs of a certain Unit, but the outcome of
the query gives me the whole hierarchy,like when i filter on UnitA:

Perspective1
Perspective1 - KPI1
Perspective1 - KPI1 - UnitA
Kpi2 - UnitA
Perspective2 - KPI1
Perspective2 - KPI1 - UnitA
KPI2 - UnitA

I would like to have the lowest level only:

Perspective1 - Kpi1 - UnitA
KPI2 - UnitA
Perspective2 - Kpi1 - UnitA
KPI2 - UnitA

Thnx for your help.

- Stanley


"Brian Altmann" wrote:

I thought you wanted the whole hierarchy. A simpler query should be what you
want, such as:

select
{[Measures].[Unit Sales]} on columns,
filter([Product Name].members,
right(product.currentmember.name,10)="Light Beer")
on rows
from Sales

Regards,
Brian


"Stanley" wrote: