 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Reporting Services / December 2005Basic 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.