sql server reporting services: Displaying Parent-Child OLAP data in a report in SQL2005 RS -- sql server reporting services

Re: Displaying Parent-Child OLAP data in a report in SQL2005 RS

Robert Bruckner [MSFT]
10/21/2005 6:24:30 PM

I attached a sample report that shows how to do this.
Notes:
* in the graphical query designer you have to select a recursive hierarchy
from the cube (such as Employee.Employees in the AdventureWorks DW cube).
Based on the metadata, RS should automatically detect that this is a
recursive parent-child hierarchy and there will be additional extended field
properties available (such as .UniqueName and .ParentUniqueName)
* add a grouping in the report. The grouping expression is based on
=Fields!Employees.UniqueName and for the parent of the group set it to
=Fields!Employees.ParentUniqueName
* right now, you will need to have table columns that also reference the
UniqueName and the ParentUniqueName in the report - but you can hide them so
they are not visible. If you don't have these columns, the recursive
hierarchy won't work correctly.

-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

==========================================================

<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=(local);Initial Catalog="Adventure Works
DW"</ConnectString>
<DataProvider>OLEDB-MD</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>5aaa5577-4975-4616-a136-0b713dab6948</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet1</DataSetName>
<Top>0.5in</Top>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Employees">
<rd:DefaultName>Employees</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>=2 + Level() * 8 &
"pt"</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>

Re: Displaying Parent-Child OLAP data in a report in SQL2005 RS

Robert Bruckner [MSFT]
10/21/2005 7:37:46 PM

BTW: you don't need to have the two additional hidden table columns with
UniqueName and ParentUniqueName in the report (as shown in my sample). It is
sufficient to e.g. just change the expression in the first table column from
=Fields!Employees.Value to =Fields!Employees("Value"). This will turn off a
certain optimization related to extended field properties.

-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Displaying Parent-Child OLAP data in a report in SQL2005 RS

Raghu
10/24/2005 12:16:01 PM

Thank you Robert, that was very helpful. I got past a hurdle. I do have a
couple of folllowup questions inspired by your response (and my somewhat
novice knowledge of OLAP).
Raghu
---------
1. Dimension properties MEMBER_UNIQUE_NAME and PARENT_UNIQUE_NAME do get
added in the MDX as you mention. Any idea why they don't show up as fields in
the dataset? Your grouping expression used Employee.UniqueName. How are we
to know what the name of those fields are? Should we just strip of the MEMBER
keyword and any underscores and use as field names, or is there a list
somewhere? I'll try to search the docs as well. Also, when I tried to use
MEMBER_KEY property, it showed up in the result set unlike the others.

2. The grouping is based on names, which are a character based. Would this
affect performance adversely if the report data is large? One of the reasons
I wanted to use IDs was due to the names not being unique (But
MEMBER_UNIQUE_NAME seems to imply that Analysis services is generating one in
internally.)

3. I was somewhat surprised at the difference in behavior when we use
Employees("Value") and Employees.Value. Could you please shed some more light
on this as to what's happening here? I think when you use the first method,
you are basically using the indexer property in VB.NET syntax (please correct
me if I'm wrong.), but that's as far as I could get. Is this behavior
documented somewhere?

[quoted text, click to view]

Re: Displaying Parent-Child OLAP data in a report in SQL2005 RS

Robert Bruckner [MSFT]
10/24/2005 7:46:03 PM

#1: AS dimension properties (such as Member_Unique_Name) and cell properties
(such as Formatted_Value) automatically get translated into RDL extended
properties. Please check the "Using Extended Field Properties for Analysis
Services Data" in RS 2005 Books Online (e.g. at
http://msdn2.microsoft.com/en-us/library/ms156477). Consequently, they are
just field properties instead of additional fields (as they used to be in RS
2000 through the old OleDB provider for AS 2000). Here is the
mapping/translation for the predefined RDL field properties that happens
inside our AdoMd AS data extension:
RDL field property name: Type / AS translation:
Value Object (from Member_Caption)
IsMissing Boolean (field is defined in RDL but
not returned from query)
UniqueName String (from Member_Unique_Name)
BackgroundColor String (see Style.BackgroundColor; retrieved
from Back_Color)
Color String (see Style.Color; retrieved
from Fore_Color)
FontFamily String (see Style.FontFamily; retrieved
from Font_Name)
FontSize String (see Style.FontSize; retrieved
from Font_Size)
FontWeight String (see Style.FontWeight; retrieved
from Font_Flags)
FontStyle String (see Style.FontStyle;
retrieved from Font_Flags)
TextDecoration String (see Style.TextDecoration;
retrieved from Font_Flags)
FormattedValue String (from Formatted_Value)
Key Object (this is supposed to be
retrieved from Member_Key; however right now you have to use the collection
syntax to retrieve the value as =Fields!MemberName("Member_Key") )
LevelNumber Integer (from Level_Number)
ParentUniqueName String (from Parent_Unique_Name)

Note: the Member_Key field that shows up in the designer fields list won't
be mapped correctly at report execution. Therefore the value will just be
NULL right now. You have to use the following syntax to retrieve the value:
=Fields!MemberName("Member_Key").

#2: You could define custom properties (IDs) in your cube and retrieve them
through the MDX query (not sure which steps are exactly needed to set this
up in your cube - you may want to lookup AS 2005 BOL). These custom
properties would not get mapped to predefined property names. Therefore, you
would need to use the property collection syntax to access them, e.g.
=Fields!MemberName("MyID"). Note: even if your ids are numbers, I believe
the AS data provider will return them as strings. Anyway, you could then use
e.g. =CInt(Fields!MemberName("MyID")) for the grouping.

#3: In general, there is no difference in behavior for these expressions:
=Fields!FieldName.PropertyName -- static field/property reference
=Fields("FieldName").PropertyName -- dynamic field reference
=Fields!FieldName("PropertyName") -- dynamic property reference
=Fields(Fields!SomeOtherField.Value).PropertyName -- dynamic field name is
determined by the value of another field

However, we implemented several levels of performance optimizations that
would e.g. only retrieve those property values that are really used in the
report (e.g. if you use static references only throughout the entire
report). Using a dynamic property reference will turn off the optimization
for a certain field. Due to a bug, the usage of ParentUniqueName
specifically in the parent-child recursive grouping is not detected. By
using the dynamic property reference, you turn off the optimization, we will
retrieve the ParentUniqueName property value for the field also, and
everything will work. The fix for the detection will be included in a
service pack - in the meantime, just use the dynamic property reference to
turn off the optimization.

We will look into making the extended properties more "discoverable" through
visual indications at report design time in future releases. This includes
detecting/setting up recursive hierarchies more easily from AS data.

Hope this answers your questions,
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Displaying Parent-Child OLAP data in a report in SQL2005 RS

FurmanGG
3/29/2006 1:17:03 PM

Robert-

Fabulous post. Was wondering if you had any suggestions for using
parent-child dimensions in Report Builder...

(Of course, I'm talking about using a model which is generated on top of an
SSAS cube.) If I'm looking at a standard dimension, each level in the
hierarchy will be a separate attribute (such as Region and City), so it's
easy to just drop Region and City onto a Report Builder report and get the
nice nesting I want. However, with parent-child dimensions, each level isn't
a separate attribute... and since Report Builder doesn't render any
hierarchies in a dimension (whether it's a user-defined hierarchy or the
hierarchy built by the marking an attribute as the Parent), I can't figure
out a way to build a Report Builder report which would have a Region column
and a City column if both Region and City are part of the parent child
dimension. Any suggestions would be appreciated.

The reason we had to make it a parent-child dimension is because one measure
group can have facts at several levels... so the only way we could do this is
with a parent-child hierarchy as standard dimensions can only have data at
one grain per measure group. (Correct me if I'm wrong.)

Thanks for any suggestions.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.