I'm trying to optimize a report with a crosstab in congnos report studio that has an OLAP source.
I would like to filter the cube with a prompt with multiple values.
I'm using a slicer with a formula like this
set( [DS_PC_Mayorista].[Delegado Comercial].[Delegado Comercial]->?DELEGADO?)
But it don't seems to make the report faster. Is this the correct way of filtering an OLAP source report with a crosstab in Cognos Report Studio?
A slicer will isolate specific intersections in the data but won't necessarily filter it. You will end up with null values for intersections that don't match your criteria but the non-matching dimensional members will still be represented. Due to this, I rarely use slicers. Instead I replace my level data item with an expression that isolates the value(s) I care about. That can take the form of a set expression or simply a MUN if I only want one member worth of data. All measures will then automatically reflect only the values from the subset.
In your case, it looks like you are passing in a MUN as a parameter from a report prompt. For this I recommend creating a Calculated Member via the Query Calculation toolbox object. The expression should simply be
[DS_PC_Mayorista].[Delegado Comercial].[Delegado Comercial]->?DELEGADO?
The effect would be the same as if you had drug over a member from the source hierarchy. The difference in this case is that the member will be supplied by the prompt instead of being hard-coded.
