Basic Reporting Services / Analysis Server 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?Joel,
The reason you're getting the blank row is because of the 'total' rows
in your dataset (ie North America <blank> 3000). The alternative would
be to remove this total row (via modifying your MDX) and letting the
report total for the continent by doing a SUM on the Amount col.
Check the visibility property of the Amount textbox to make sure it is
toggled by the [+] Continent textbox.
Also, to hide duplicates, there is a property on a Textbox which will
hide repeated values.
Hopefully that will get you started.
Andy Potter|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.|||Thank you for your response -
For your first suggestion, do you mean to create a row in the report
with formula USA+Canada+Mexica and a second row with formula
UK+Germany? This isn't really my hierarchy, in reality, the dimension
has 7 levels and hundreds of thousands of members so manual totals are
not an option.
Or do you mean to suppress the rows with a blank via my MDX? If so,
any ideas on how to formulate this?
I tried the toggle for the amount text box and it has no effect. the
Hide Repeated on the Amount text box has no effect either. I tried
hiding repeats for amount conditioned on Continent and Country.