report studio in_range for cube

[koolbachi]

Hi,

I have cube which i am using in Report studio.

When I use in_range function in filter its not working.

example :filter is not(cube_exprssion) in_range(0:0).

I am not getting the data for the prompt value which has values other than zero. Any idea?

Thanks in advance

[CognosPaul]

The following works perfectly for me:

filter(
[Cube].[Date].[Date].[Month],
tuple([Cube].[Measures].[Actual Value],[Cube].[KPI].[KPI].[KPI1]->:[PC].[@MEMBER].[001])
    not in_range {1:7000}
)

That will return a set of all months where the tuple of KPI001, Actual Value, and the evaluated month is not between 1 and 7000.

How are you using the this? Is it a filter in the detail filters pane, or are you using the Filter function? Are you attempting to filter an attribute or a value (such as a measure or tuple). Is this DMR or a real OLAP cube? Is this a powercube?

[koolbachi]

Thanks Pual for the suggestion but that didn't work for me.

- I am using the function in Detail Filter.
- its real OLAP cube, Is there any difference if its powercube ?
- Its a measure.

To be more specify the problem is that I have prompt for countries..In one of the country there is zero values which is not required in the output of graph. but whats happening is that country whole data is excluded.

Regards

[CognosPaul]

When working with dimensional data you do not need to use detail filters. They can cause unexpected results. For example, if you have a crosstab with years going across, sales types crossjoined with countries, and you want to filter the sales types to those with less than 1000 revenue for that year. A detail filter will filter all the sets, and filter them fairly inefficiently.

Remove the detail filter, create a data item in the query based on my suggestion and use that instead of the country level. Your crosstab should look similar to:

Corner    | Years
----------+-------
Data Item |   123


The reason I asked if your source was a powercube is powercube attributes appear to be always string, even if you selected a numeric source. Attempting to do an in_range filter on a string will not work. But since you're doing it on a measure it doesn't matter.

[koolbachi]

Hey Pual thanks for the information.

Actually In my output its graph. where I need to exlcude the data in measures having zero.

so the 0:0 range is not shown.

Regards

[koolbachi]

Hi Paul,

If i disable the filters I am getting all the Values including zeros for all the coutries.

pls suggest if there any way to exclude zero range in the graph.

thanks in advance

regards

[CognosPaul]

Remove the Countries data item from your graph. Drag a Query Calculation in its place.

The calculation should be filter([Countries],[Measure]<>0)