MDX query using BottomCount

Hi,

Iam facing with a very strange MDX query related problem.

I want to display 2 reports one that display Five top records and the second report that displays 5 bottom records.

Following is my query which returns top 5 records.

SELECT { [Measures].[Order Count] } ON COLUMNS,
NON EMPTY TopCount (
Filter (
{ [PRODUCT].[PROD NAME].[PROD NAME]}, [Measures].[Order Count] >= 0
)
, 5, [Measures].[Order Count] ) ON ROWS
FROM
[ShippingOrder]

I created report using SQL Server 2005 Reporting Service and the data source is pointing to Analysis Services database.

The preview report shows proper records.

But my second report that queries to display the bottom 5 records does not display any records. The query is same except with BottomCount keyword.

SELECT { [Measures].[Order Count] } ON COLUMNS,
NON EMPTY BottomCount (
Filter (
{ [PRODUCT].[PROD NAME].[PROD NAME]}, [Measures].[Order Count] >= 0
)
, 5, [Measures].[Order Count] ) ON ROWS
FROM
[ShippingOrder]

Any idea what is going wrong?

Thanks in advance!

I don't see a reason why this shoudn't work. Does this query bring any data when you test it in SSMS?|||I don't see a reason why this shouldn't work. Does it bring any data when you test in SSMS?|||

No. It does not show me any record.

But I changed the query to

SELECT { [Measures].[Order Count] } ON COLUMNS,
BottomCount (NONEMPTY (
Filter (
{ [PRODUCT].[PROD NAME].[PROD NAME]}, [Measures].[Order Count] >= 0
))
, 5, [Measures].[Order Count] ) ON ROWS
FROM
[ShippingOrder]

The only change is that I applied the NONEMPTY clause to the BottomCount function.

This gives me the correct result. I dont understand clearly the difference between the first and this query.

Are the report data by any chance cached? I always see some wierd behaviour with inconsistent data been shown. I always have to delete the Report and Analysis services database and deploy again to see the new contents.

Is there any settings which mentions to see only the new set of data?

Thanks!

|||

Some results do get cached on your developer machine in same location as the rdl file with a .data extension. Depending on the queries you've run, the data gets cached to make the preview window more efficient when making visual style changes to the report.

I haven't quite worked out exactly when this happens but sometimes it's worth deleting the .data files to make it requery the data.

|||

The difference is that most likely your first query was returning empty [Order Count] cells (what happens when you click on Show Empty Cells?).

Most likely willl get the same (correct) results in a more efficient way if you replace your query with:

SELECT { [Measures].[Order Count] } ON COLUMNS,
BottomCount (

NONEMPTY ([PRODUCT].[PROD NAME].[PROD NAME].Members, {[Measures].[Order Count]}), 5, [Measures].[Order Count]

) ON ROWS

FROM
[ShippingOrder]

You shouldn't see any caching behaviour if you execute the query from the Data tab (use the Profiler to check). Switching to Preview will use cached data if you don't make changes to the report layout.