Prologika Forums

I'm not sure if this question belongs to this forum or the Reporting services forum.

My problem is that I am trying to generate a pie chart in reporting services that shows the top 10 items based on the Quantity measure and sums the other components up and shows them in one slice. The pie should have 11 slices, one for each of the top 10 items and 1 for the other items.

Here is the mdx query I'm using

with set [TopComponents] as
'Generate(
{Topcount([Component].[Name].Members, 10, [Measures].[Quantity]) as TC},
Head(VisualTotals({[Component].[Name].currentMember, [Component].[Name].Children - TC}, "* Others"))
)'
Select [Measures].[Quantity] on columns,
[TopComponents] on rows
From [Time Registration]

This query returns what I want when I execute it in the SQL Server management studio but when I try to use it in Reporting services, the "others" member is changed to "null" and the pie chart that uses the dataset omits it showing only the top 10 items.

Any ideas on how I can fix this?

Does the 11th member show up when you switch to the OLE DB Provider for Analysis Services 9.0 in the report data source?

No it doesn't. The problem was that the 11th member does show up in the dataset but the member name is shown as (null) but the value is there. This is the result of the query in SQL Server Management Studio:

All other 496.75
item 1 583.75
item 2 539.5
item 3 437.25
.......

This is the result when I execute it query in the data tab in Reoorting Services:

(null) 496.75
item 1 583.75
item 2 539.5
item 3 437.25
.......

When I switched to the OLE DB Provider for Analysis Services 9.0 (null) was replaced with blank

496.75
item 1 583.75
item 2 539.5
item 3 437.25
.......

Do you think that the reason could be that the result from the VisualTotal clause has a different ordinal from the other members so that reporting services consider it to be the aggrigation of the other 10 members?

I believe this is happening because RS requests the set as a tabular rowset. You will probably get the same results in SSMS if you request the results as rowset instead of cellset (you need to use XMLA for this). So, your question turns into "can I get the VisualTotals member with a tabular rowset?" and I don't know the answer. I suggest you post this question to the SSAS public forum.

Thanks for the answer. I will post this question on the public forum.

I guess I could get around this problem by creating a stored procedure that calculates this dataset for me.