Basic Reporting Services / Analysis Services report - flattened rowset problem

I am just getting started with Reporting Services 2000 and I need some help PLEASE! My source is MSAS 2000 cube. This whole flattened rowset is confusing me. Insight will be HUGELY appreciated.

I have a MSAS hierarchy that looks like this:
WORLD
-North America
USA
Canada
Mexico
-Europe
UK
Germany
etc...

I use the following MDX to get my dataset
Select [Amount] on Columns, [World].AllMembers on ROWS From Cube

I get the following Reporting Services dataset with THREE columns:
(nothing) 5000
North America 3000
North America USA 1000
North America Canada 1000
North America Mexico 1000
Europe 2000
Europe UK 1000
Europe Germany 1000

What I want is a report that looks like this -- (Two columns)
DESIRED REPORT
Location Amount
+ North America 3000
+ Europe 2000
World 5000

When the user Clicks the + icon , The report should expand. Like below:
DESIRED REPORT AFTER DRILLDOWN
Location Amount
USA 1000
Canada 1000
Mexico 1000
+ North America 3000
+ Europe 2000
World 5000

This seems like it should be the most basic report possible for a cube with a hierarchy. However, it seems that no matter what I try, I get variations of the following:
Three columns:
Continent Country Amount
+ blank here!
+ North America (nothing!!!)
+ Europe (nothing!!!)

When a + icon is clicked I get (still no AMOUNTS!!!):
Continent Country Amount
+ blank here!
- North America (nothing!!!)
+USA (nothing!!!)
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)

and another clicks on USA for example yields:
Continent Country Amount
+ blank here!
- North America (nothing!!!)
-USA (nothing!!!)
1000
+Canada (nothing!!!)
+Mexico (nothing!!!)
+ Europe (nothing!!!)

The desired report is a simple report. It seems like a good candidate for a wizard. However, using the wizard, I get results such as the one above. The example above uses a wizard stepped format with subtotals and drilldown enabled.

Moving away from the wizard... If I build the report without the wizard some report rows have no titles and/or no amounts and/or amounts are repeated multiple times down the rows. The only thing I can think of is that I need to IIF statements in every row to hide these rows which seems inefficient. I think I must be doing something wrong.

Do I need to change my MDX in some way to eliminate rows with Blanks in the Continent and Country columns? Or is there something simple I can do in the report definition to make it behave differentlty?

Can someone get me started please?

This MSDN paper discusses how to work with flattened rowsets in Reporting Services, and includes some sample drilldown reports that should point you in the right direction. Basically, you can set filtering in your groups to select the right rows for a given group, or have the MDX query only return data from the lowest level:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios. (33 printed pages)

...

Building "Drill-Down" Reports against OLAP Data

Up until this point, all of the data contained within the example reports has belonged to only one level from each dimension. For example, if you ask the question, "What are unit sales by promotion media type?" even though the user might be given the option to select any available time period, the resulting dataset will only contain data from a single level of each dimension. The included level of the time dimension might change each time the report is executed, but for each report execution only one level of the time dimension will be referenced. What happens when you need to add interactivity to the report by allowing the users to "drill-down" on members in the table?

...

>>

|||

I am having the same problem. When I run my MDX query in designer I do not get the "All" member for any of my dimensions. Displaying that member is a requirement for all of my reports.

I did review the above mentioned white paper as well as download the sample reports, however none of those reports uses the "ALL" member.

Any suggections?

Here is my simple code:

Thanks in advance !!

SELECT { [Measures].[Responses], [Measures].[Responders], [Measures].[Trans Amount] } ON COLUMNS,
{ { { [Providers].Members} * { [Product].Members } * { [Channel].Members } } }
Dimension Properties Member_Unique_Name ON ROWS
FROM [Programmatic_Balance_Build]

|||

The problem with returning an "All" member in a flattened rowset is discussed elsewhere in the above paper:

>>

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

>>

|||

Thank you Deepak -

This whitepaper opened up a lot of concepts for RS and AS. It was just what I needed. Thank you for pointing me in this direction.

I still don't like some of the integration issues between RS and AS . But I now understand the behaviors and work with them. And most importantly, still deliver the reports.