Formatting drill down report with varying depths of hierarchy

Good Morning, all,
I have been tasked to design reports for our budget data.
The client wants to be able to drill down through our company
hierarchy.
Some departments are five levels deep in the hierarchy, but others are
only three levels deep.
Right now a very simplified version of my dataset looks like
LEVEL0 LEVEL1 LEVEL2 LEVEL4 BUDGET
900
500000
900
600 200000
900
400 100000
900 400
250 50000
900 400
350 50000
900 400 350
612 40000
900 400 350
611 10000
So, what i want is if i drill down to 600, I shouldn't be able to
drill down any further, because there is no lower level. But if I
drill down to 400, I should be able drill down further. Right now, I
can keep drilling down two more levels below 600 until I get to the
detail level.
Can I control this with layout properties?
any ideas, suggestions, resources, war stories, or good clean jokes
would be appreciated.
KathrynFor archival purposes, I solved this by using Analysis Services to
create a cube out of my relational data. The recursive relationship
above became a dimension named Par. I created a report in Reporting
Services using the cube as a datasource. When defining the Group for
Par, you have to use two properties, UniqueName and ParentUniqueName.
These properties are created automatically when you create your MDX
query using Query Builder.. In my case, when I edited the Par Group,
I set the Group On expression to Fields!Par.UniqueName and the Parent
group to Fields!Par.ParentUniqueName. Set Visibility to Hidden and
have the Toggle Item be the textbox itself, in my case Par (which
seems recursive, but that's what you want.)
Good luck,
Kathrynsql

Format_String causes crash excel 2007

Hello,

I have the following mdx script command :

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00";

End Scope;

when I now generates an excel 2007 olap report, I get the correct formatting.

But when I change the MDX to

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00 d";

End Scope;

So now I expect a 'd' after the figures.

But when I generate the same report in excel 2007, excel crashes.

Anyone an idea?

thx

This is only a guess but you can try "#,#.00d" .

It can be the space that is causing the problem.

When you write a percent format you can use "##.##%" so if my suggestion do not work SSAS2005 expects a code it understands and d is not supported.

HTH

Thomas Ivarsson

|||

Hi Thomas

I tried it, but it also cause excel to crash.

The weird thing is that in the 'browser' of Analysis Services the formatting is shown correct.

So I think that it is a bug in excel.

|||

I can confirm this problem with Excel 2007. It works fine in ProClarity Professional 6.3

Yes, it looks like a bug in Excel 2007.

Regards

Thomas Ivarsson