How to use the same detail filter on 2 crosstabs with different cubes

[hpmkruit]

Hello,

Hopefully someone can help me on this one:

I have a report based on 2 different cubes. For each cube i have made a crosstab. These cubes both have an equal hierarchy named "Organisation structure". Now i want to use 1 filter prompt (something like a tree prompt) to filter both crosstabs on their Organisation hierarchy.

This i can't seem to get done, because i have to select a source for the prompt from one of the 2 cubes. This means i can filter one crosstab, but the other won't recognize the filter source, because it's from a different cube.

Any help would be greatly appreciated and i'm planning to contribute to this forum myself very soon too.

[billylodz]

http://www.cognoise.com/community/index.php/topic,7540.msg24800.html#msg24800

[hpmkruit]

Thanks for the hint of using caption().

But i'm still puzzling about how to implement this.

This is the situation:
I'm filling Parameter1 with the hierarchy of cube 1 like this:

rootMembers([Cube1].[Hierarchy].[Level])

so the values of Paramater1 look like: [Cube1].[Hierarchy].[Level].[] etc

The crosstab of the other cube, cube2, filters its hierarchy like so:
[Cube2].[Hierarchy].[Level] = ?Parameter1?

This fails, because the name of Cube1 isn't equal to Cube2

Where should i use the caption function?

Thanks in advance.

[hpmkruit]

I'm experimenting with the caption() function, but to no avail.

When i set the detail filter of the Cube2 crosstab query to:

caption([Cube2].[Hierarchy].[Level]) = ?Parameter1?

I get the following error:

"The comparison is not valid. The value can only be compared to another value or measure."

[billylodz]

what is working in my report is

in prompt  I use Level - Long Name so I have string in ParamValue

and Filter in 2 queries
caption([Cube1].[Hierarchy].[Level]) = ?Parameter1?
and
caption([Cube2].[Hierarchy].[Level]) = ?Parameter1?

[hpmkruit]

If i use Level - Longname for my prompt, only the Long name of the Level is displayed in the prompt, that's just 1 value. How did you set it up so all member's Long Name's were displayed in the prompt?

BTW, i'm using a value prompt for testing

[hpmkruit]

Still trying to get it to work properly.

i assigned the 2 filters:

caption([Cube1].[Hierarchy].[Level]) = ?Parameter1?
and
caption([Cube2].[Hierarchy].[Level]) = ?Parameter1?

Then i filled a Tree Prompt with the Hierarchy i want to filter on.
This tree is filled by a new query which contains a data item. This data item is filled with the selected hierarchy by the expression:

rootMembers([Cube1].[Hierarchy].[Level])

Next i linked the output of the tree prompt to Parameter1 so that the two crosstabs will be filtered by the selection of the tree prompt (so i hoped).

When i do this and run the report, the tree prompt only displays the root member! When i remove the outputparameter, the entire hierarchy shows up again, but then there is no filtering.

I'm puzzled by these events and do not know what to do next. Any advise would be very nice.

[hpmkruit]

I have come up with a temporary work around, because Parameter1 has to be a single value, not something dynamic as rootmembers() supplies.

I have populated the tree prompt using the descendants() function. Problem is that i have to specify wich levels are shown, something i want the users to do for themselves. Now the whole tree is expanded with all specified levels shown.

If someone knows a way to populate the tree with something like rootmembers() so that not all hierarchy levels are expanded initially, i would be glad to hear it.