Showing posts with label source. Show all posts
Showing posts with label source. Show all posts

Sunday, March 25, 2012

Distinct problem - how can I sort this?

Hi All

I have a table sent to me from another source over which i have no control of the content (they don't sem to be able to get a distinct instance of persons for some reason? - anyway that is out of my control)
Now this table contains multiple instances of persons, each person has a unique identifier plus a set of dates and a field that indicates the state of that person (can be 'Curr' or 'Ex').

Fields are as follows:-

PersonID - char(10) - This is unique identifier for each person
Date1 - DateTime
P_State - char(20)

Sample of data from table1:-
-
PersonID - Date1 - P_State
A11324 1998-04-21 Curr
A11324 1999-05-01 Ex
A11324 1998-07-12 Ex
A11324 1998-05-23 Curr
B44321 1999-07-01 Curr
B11111 1999-07-01 Ex
B11111 1998-03-01 Curr
B22222 1999-03-31 Ex
B22222 1998-04-11 Curr
B33333 2004-03-10 Curr
B44443 2002-09-01 Curr
-

What I need to get is the latest instance of each person and their present p_state, I've tried many ways but can't seem to get a distinct instance of the latest record for each person, can anyone point me in the right direction please.

thanks.

select personid,max(date1),p_state

from table1

group by personid,p_state

|||thanks
but that doesn't give me a single instance of each person?

It's much the same as

select personid,date1,p_state
from table1
order by personid,date1

is it not ?

edit
Sorted now thanks, I removed the p_state in your query and then used the output as a basis for another query.

Thanks.

Wednesday, March 21, 2012

Disregard: SSIS not available as a data source

I have a full install of the June CTP on a brand new Win2K3 server.

When I attempt to add a data source to my report project, the list of possible data sources is:

Microsoft SQL Server
OLE DB
Microsoft SQL Server Analysis Services
Oracle
ODBC
XML

That's it. SSIS is not on the list (neither is SAP which I though was going to be an option as well).

Anyone have any ideas why this might be?Disregard - Never let your enterprise architects download anything for you if the edition matters :)|||Hi

I was also under the impression that SSIS could be directly used as a data source. So does that require a specific version?sql

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Sunday, March 11, 2012

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

I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
The cube has a parent-child dimension and a fact table. I would like to
display the parent-child data in a report . Does anyone know how to build an
MDX query that would give me the ID and the ParentID from the dimension to
build the parent-child grouping relationship in the report? The MDX builder
does not let me access the ID field or the ParentID field. I always get the
Name of the dimension.
Also, if there's another way to do this, please let me know.
Any help is greatly appreciated.
ThanksI 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.
"Raghu" <Raghu@.discussions.microsoft.com> wrote in message
news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> The cube has a parent-child dimension and a fact table. I would like to
> display the parent-child data in a report . Does anyone know how to build
> an
> MDX query that would give me the ID and the ParentID from the dimension to
> build the parent-child grouping relationship in the report? The MDX
> builder
> does not let me access the ID field or the ParentID field. I always get
> the
> Name of the dimension.
> Also, if there's another way to do this, please let me know.
> Any help is greatly appreciated.
> Thanks
==========================================================
<?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>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.UniqueName</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Employees.ParentUniqueName</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Reseller_Sales_Amount">
<rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Level()</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Reseller_Sales_Amount_1">
<rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<Parent>=Fields!Employees.ParentUniqueName</Parent>
<GroupExpressions>
<GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employees</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>13</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="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>12</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="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Reseller Sales Amount</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>0.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
<Height>0.75in</Height>
</Table>
</ReportItems>
<Height>1.375in</Height>
</Body>
<rd:ReportID>380d94a9-e8f4-4147-8f13-1f6d88259335</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText> SELECT NON EMPTY { [Measures].[Reseller Sales
Amount] } ON COLUMNS, NON EMPTY {
(DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS</CommandText>
<DataSourceName>DataSource1</DataSourceName>
<rd:MdxQuery><QueryDefinition
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="<CommandType>MDX</CommandType><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Employees</HierarchyName><HierarchyUniqueName>[Employee].[Employees]</HierarchyUniqueName><LevelName>Employee
Level 02</LevelName><UniqueName>[Employee].[Employees].[Employee Level
02]</UniqueName></ID><ItemCaption>Employees</ItemCaption><UniqueName>true</UniqueName><IsParentChild>true</IsParentChild></Item><Item><ID
xsi:type="Measure"><MeasureName>Reseller Sales
Amount</MeasureName><UniqueName>[Measures].[Reseller Sales
Amount]</UniqueName></ID><ItemCaption>Reseller Sales
Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure
Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
/><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON
EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY {
(DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,
LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions
/></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="Employees">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level"
UniqueName="[Employee].[Employees]" /></DataField>
</Field>
<Field Name="Reseller_Sales_Amount">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[Reseller Sales Amount]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||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.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>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.
>
> "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
>> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
>> The cube has a parent-child dimension and a fact table. I would like to
>> display the parent-child data in a report . Does anyone know how to build
>> an
>> MDX query that would give me the ID and the ParentID from the dimension
>> to
>> build the parent-child grouping relationship in the report? The MDX
>> builder
>> does not let me access the ID field or the ParentID field. I always get
>> the
>> Name of the dimension.
>> Also, if there's another way to do this, please let me know.
>> Any help is greatly appreciated.
>> Thanks
>
> ==========================================================> <?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>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.UniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.ParentUniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount">
> <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount_1">
> <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> <Grouping Name="table1_Details_Group">
> <Parent>=Fields!Employees.ParentUniqueName</Parent>
> <GroupExpressions>
> <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>14</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Employees</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>13</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="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>12</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="textbox3">
> <rd:DefaultName>textbox3</rd:DefaultName>
> <ZIndex>11</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Level</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <rd:DefaultName>textbox6</rd:DefaultName>
> <ZIndex>10</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Reseller Sales Amount</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> </Header>
> <TableColumns>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> <Visibility>
> <Hidden>true</Hidden>
> </Visibility>
> </TableColumn>
> <TableColumn>
> <Width>0.875in</Width>
> <Visibility>
> <Hidden>true</Hidden>
> </Visibility>
> </TableColumn>
> <TableColumn>
> <Width>0.5in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.75in</Width>
> </TableColumn>
> </TableColumns>
> <Height>0.75in</Height>
> </Table>
> </ReportItems>
> <Height>1.375in</Height>
> </Body>
> <rd:ReportID>380d94a9-e8f4-4147-8f13-1f6d88259335</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
> <CommandText> SELECT NON EMPTY { [Measures].[Reseller Sales
> Amount] } ON COLUMNS, NON EMPTY {
> (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
> PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
> FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> <rd:MdxQuery><QueryDefinition
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns="<CommandType>MDX</CommandType><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><QuerySpecification
> xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
> xsi:type="Level"><DimensionName>Employee</DimensionName><HierarchyName>Employees</HierarchyName><HierarchyUniqueName>[Employee].[Employees]</HierarchyUniqueName><LevelName>Employee
> Level 02</LevelName><UniqueName>[Employee].[Employees].[Employee Level
> 02]</UniqueName></ID><ItemCaption>Employees</ItemCaption><UniqueName>true</UniqueName><IsParentChild>true</IsParentChild></Item><Item><ID
> xsi:type="Measure"><MeasureName>Reseller Sales
> Amount</MeasureName><UniqueName>[Measures].[Reseller Sales
> Amount]</UniqueName></ID><ItemCaption>Reseller Sales
> Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure
> Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
> /><QueryProperties /></QuerySpecification><Query><Statement> SELECT NON
> EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS, NON EMPTY {
> (DESCENDANTS([Employee].[Employees].[Employee Level 02].ALLMEMBERS) ) }
> DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
> PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM [Adventure Works] CELL
> PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,
> FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions
> /></Query></QueryDefinition></rd:MdxQuery>
> </Query>
> <Fields>
> <Field Name="Employees">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField><?xml version="1.0" encoding="utf-8"?><Field
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level"
> UniqueName="[Employee].[Employees]" /></DataField>
> </Field>
> <Field Name="Reseller_Sales_Amount">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField><?xml version="1.0" encoding="utf-8"?><Field
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
> UniqueName="[Measures].[Reseller Sales Amount]" /></DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>5.625in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||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?
"Robert Bruckner [MSFT]" wrote:
> 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.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> >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.
> >
> >
> > "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> > news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> >> I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> >> The cube has a parent-child dimension and a fact table. I would like to
> >> display the parent-child data in a report . Does anyone know how to build
> >> an
> >> MDX query that would give me the ID and the ParentID from the dimension
> >> to
> >> build the parent-child grouping relationship in the report? The MDX
> >> builder
> >> does not let me access the ID field or the ParentID field. I always get
> >> the
> >> Name of the dimension.
> >>
> >> Also, if there's another way to do this, please let me know.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks
> >
> >
> > ==========================================================> >
> > <?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>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox10">
> > <ZIndex>3</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.UniqueName</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <ZIndex>2</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!Employees.ParentUniqueName</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Reseller_Sales_Amount">
> >
> > <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> > <ZIndex>1</ZIndex>
> > <Style>
> > <TextAlign>Center</TextAlign>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>=Level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="Reseller_Sales_Amount_1">
> >
> > <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> > <Style>
> > <TextAlign>Right</TextAlign>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> >
> > <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > <Height>0.25in</Height>
> > </TableRow>
> > </TableRows>
> > <Grouping Name="table1_Details_Group">
> > <Parent>=Fields!Employees.ParentUniqueName</Parent>
> > <GroupExpressions>
> >
> > <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </Details>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <ZIndex>14</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>
> > <PaddingTop>2pt</PaddingTop>
> > </Style>
> > <CanGrow>true</CanGrow>
> > <Value>Employees</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <ZIndex>13</ZIndex>
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingRight>2pt</PaddingRight>|||#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.
"Raghu" <Raghu@.discussions.microsoft.com> wrote in message
news:25F0BE41-DDA1-491E-9043-A7E36AA633E7@.microsoft.com...
> 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?
> "Robert Bruckner [MSFT]" wrote:
>> 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.
>>
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:e4fKfdq1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>> >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.
>> >
>> >
>> > "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
>> > news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
>> >> I'm trying to use an OLAP cube as my data source (built using SSAS
>> >> 2005)
>> >> The cube has a parent-child dimension and a fact table. I would like
>> >> to
>> >> display the parent-child data in a report . Does anyone know how to
>> >> build
>> >> an
>> >> MDX query that would give me the ID and the ParentID from the
>> >> dimension
>> >> to
>> >> build the parent-child grouping relationship in the report? The MDX
>> >> builder
>> >> does not let me access the ID field or the ParentID field. I always
>> >> get
>> >> the
>> >> Name of the dimension.
>> >>
>> >> Also, if there's another way to do this, please let me know.
>> >>
>> >> Any help is greatly appreciated.
>> >>
>> >> Thanks
>> >
>> >
>> > ==========================================================>> >
>> > <?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>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.Value</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox10">
>> > <ZIndex>3</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.UniqueName</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox5">
>> > <rd:DefaultName>textbox5</rd:DefaultName>
>> > <ZIndex>2</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Fields!Employees.ParentUniqueName</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="Reseller_Sales_Amount">
>> >
>> > <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
>> > <ZIndex>1</ZIndex>
>> > <Style>
>> > <TextAlign>Center</TextAlign>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>=Level()</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="Reseller_Sales_Amount_1">
>> >
>> > <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
>> > <Style>
>> > <TextAlign>Right</TextAlign>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> >
>> > <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > </TableCells>
>> > <Height>0.25in</Height>
>> > </TableRow>
>> > </TableRows>
>> > <Grouping Name="table1_Details_Group">
>> > <Parent>=Fields!Employees.ParentUniqueName</Parent>
>> > <GroupExpressions>
>> >
>> > <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
>> > </GroupExpressions>
>> > </Grouping>
>> > </Details>
>> > <Header>
>> > <TableRows>
>> > <TableRow>
>> > <TableCells>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox1">
>> > <rd:DefaultName>textbox1</rd:DefaultName>
>> > <ZIndex>14</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>
>> > <PaddingTop>2pt</PaddingTop>
>> > </Style>
>> > <CanGrow>true</CanGrow>
>> > <Value>Employees</Value>
>> > </Textbox>
>> > </ReportItems>
>> > </TableCell>
>> > <TableCell>
>> > <ReportItems>
>> > <Textbox Name="textbox4">
>> > <rd:DefaultName>textbox4</rd:DefaultName>
>> > <ZIndex>13</ZIndex>
>> > <Style>
>> > <PaddingLeft>2pt</PaddingLeft>
>> > <PaddingBottom>2pt</PaddingBottom>
>> > <PaddingRight>2pt</PaddingRight>|||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.
"Robert Bruckner [MSFT]" wrote:
> 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.
>
> "Raghu" <Raghu@.discussions.microsoft.com> wrote in message
> news:A3B4E2B3-B78D-4571-9D90-69EC03FD18E4@.microsoft.com...
> > I'm trying to use an OLAP cube as my data source (built using SSAS 2005)
> > The cube has a parent-child dimension and a fact table. I would like to
> > display the parent-child data in a report . Does anyone know how to build
> > an
> > MDX query that would give me the ID and the ParentID from the dimension to
> > build the parent-child grouping relationship in the report? The MDX
> > builder
> > does not let me access the ID field or the ParentID field. I always get
> > the
> > Name of the dimension.
> >
> > Also, if there's another way to do this, please let me know.
> >
> > Any help is greatly appreciated.
> >
> > Thanks
>
> ==========================================================> <?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>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.UniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!Employees.ParentUniqueName</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount">
> <rd:DefaultName>Reseller_Sales_Amount</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Center</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Reseller_Sales_Amount_1">
> <rd:DefaultName>Reseller_Sales_Amount_1</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Sum(Fields!Reseller_Sales_Amount.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.25in</Height>
> </TableRow>
> </TableRows>
> <Grouping Name="table1_Details_Group">
> <Parent>=Fields!Employees.ParentUniqueName</Parent>
> <GroupExpressions>
> <GroupExpression>=Fields!Employees.UniqueName</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>14</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>Employees</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>13</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="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>12</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>

Friday, March 9, 2012

Displaying images from SSAS data source

I'm trying to display the images from the Adventure Works SSAS database in a SSRS report.

I've added an image object to the table, set MIME to image/jpeg (as it is set in the value fiel of the attribute in AW SSAS database) but I'm getting just a 'x' instead of the image and following output:

Preview complete -- 0 errors, 1 warnings

[rsInvalidExpressionDataType] The Value expression used in textbox ‘LargePhoto’ returned a data type that is not valid.

Preview complete -- 0 errors, 1 warnings

[rsInvalidExpressionDataType] The Value expression used in textbox ‘LargePhoto’ returned a data type that is not valid.

Preview complete -- 0 errors, 1 warnings

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type that is not valid.

[rsInvalidDatabaseImage] The Value expression for the image ‘image1’ did not evaluate to an image.

Preview complete -- 0 errors, 2 warnings

But displaying images the same way just using the relational AW DW table DimProduct works well.

(the only difference I've seen is: in the query generator from SSAS there is a cryptic text value displayed as the value of the LargePhoto attribute, if using relational table there is a '<binary>')

relational query:

select productalternatekey, largephoto from dimproduct

SSAS query:

WITH MEMBER measures.test

AS

[Product].[Large Photo].currentmember.membervalue

SELECT NON EMPTY

{ [Measures].[Internet Sales Amount], measures.test } ON COLUMNS,

NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS * [Product].[Large Photo].[Large Photo].ALLMEMBERS) } ON ROWS

FROM [Adventure Works]

Where is the problem ? How can I display images from SSAS in SSRS ?

Any ideas are appreciated.

Jan.

A binary member value comes from SSAS as Base64-encoded. You need to use the following expression in your textbox Value property in SSRS:

=System.Convert.FromBase64String(Fields!test.Value)

|||

Great help Teo!

That's it, thanks a lot !

Jan.

Wednesday, March 7, 2012

displaying data source in report

How would you display a dataset's data source in a report?
I want to say something like "This report uses data from X database"
Thanks!
DominiqueCreate a new dataset (data tab, select combo and new dataset).
Switch to generic designer (the button is to the right of the ...)
Put this in:
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140026946.331926.266950@.z14g2000cwz.googlegroups.com...
> How would you display a dataset's data source in a report?
> I want to say something like "This report uses data from X database"
> Thanks!
> Dominique
>|||Whoops, sent before done. OK here is the whole answer:
Create a new dataset (data tab, select combo and new dataset).
Switch to generic designer (the button is to the right of the ...)
Put this in:
declare @.DBID int
select @.DBID = db_id()
select db_name(@.DBID) as DatabaseName
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140026946.331926.266950@.z14g2000cwz.googlegroups.com...
> How would you display a dataset's data source in a report?
> I want to say something like "This report uses data from X database"
> Thanks!
> Dominique
>|||Dear Bruce:
I definitely feel on the right track, to get other information, I did
something like
declare @.DBID int
select @.DBID = db_id()
select db_name(@.DBID) as DatabaseName, @.@.version as Version ,
@.@.servername as ServerName
(which I would expect to only return a single row)
And to display, I created a list in layout view, and added the fields
within the list to text boxes. The biggest pain was that I had to
manually add the fields to the dataset, and sometimes VS .NET 2003
would reset the list of fields back to nothing.
Thanks for all the help!!!
Dominique|||You shouldn't have had to manually add the field names. Most likely you just
needed to click on the refresh fields button (to the right of the ..., it
looks like the refresh button of IE).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dom" <dominique.plante@.gmail.com> wrote in message
news:1140038964.115390.256080@.f14g2000cwb.googlegroups.com...
> Dear Bruce:
> I definitely feel on the right track, to get other information, I did
> something like
> declare @.DBID int
> select @.DBID = db_id()
> select db_name(@.DBID) as DatabaseName, @.@.version as Version ,
> @.@.servername as ServerName
> (which I would expect to only return a single row)
> And to display, I created a list in layout view, and added the fields
> within the list to text boxes. The biggest pain was that I had to
> manually add the fields to the dataset, and sometimes VS .NET 2003
> would reset the list of fields back to nothing.
> Thanks for all the help!!!
> Dominique
>

Friday, February 24, 2012

Display subtotals and grand total

Hi,

I have a table:

CREATE TABLE [dbo].[TBL_REPORT1](

[Source] [varchar](3) NULL,

[Contract No] [varchar](15) NOT NULL,

[Business Group] [varchar](4) NULL,

[Customer Name] [varchar](50) NULL,

[Equipment Description] [varchar](20) NULL,

[Lease Type] [varchar](2) NULL,

[Term] [int] NULL,

[Booking Date] [datetime] NULL,

[# of Assets] [int] NULL,

[Equipment Cost] [money] NULL,

[Restructured] [varchar](3) NOT NULL

)

Sample Data

INSERT INTO [TBL_REPORT1] VALUES('SFS','319-0010146-001','SEF','NorthBay Healthcare Group','SBT Performance Cont','LP',132,'Apr 4 2007 12:00:00:000AM',1,2612000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015625-023','SEF','Black Diamond Properties, Inc.','Kubota L48 TLB Tract','OL',60,'Apr 3 2007 12:00:00:000AM',1,36000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015648-007','SEF','The River Wilderness Club, Inc.','Honda Salsco Greens','OL',48,'Apr 5 2007 12:00:00:000AM',1,11401.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0013599-020','VEN','THYSSENKRUPP BUDD COMPANY','COMPUTER GEAR','CS',30,'Apr 5 2007 12:00:00:000AM',1,232965.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0016965-002','VEN','GREEN OAK TOWNSHIP','COMPUTER GEAR','CS',33,'Apr 5 2007 12:00:00:000AM',1,56789.9100,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0052401-001','VEN','Zircon Corp.','INJECTION MOLDING MC','CS',70,'Apr 11 2007 12:00:00:000AM',1,74380.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','766-0001804-007','IGP','Helena Chemical Company','1800 GAL','TL',36,'Apr 18 2007 12:00:00:000AM',17,292147.7000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-001','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,276928.4500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-002','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,377415.3500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002053-001','CBF','VIH Helicopters USA, Inc.','Sikorsky S-61N','CS',84,'Apr 6 2007 12:00:00:000AM',1,4612500.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0014680-024','CPM','SUN MICROSYSTEMS, INC.','trade receivable','CS',2,'Apr 5 2007 12:00:00:000AM',1,20177632.2300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0015956-014','CPM','Autozone Inc.','trade receivable','CS',11,'Apr 3 2007 12:00:00:000AM',1,2128173.3600,'No')

I want to display subtotals - sum of [Equipment Cost] for each [Business Group] and also the grand total.

Sample Output:

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Count 3 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Count 2

I tried with CUBE and ROLLUP but then with multiple fields it was not giving me the right output. Can anyone help. Thanks.

Posted above was the sample count subtotal generated in Excel. I am interested in getting only the subtotal count/sum and the grand total. Labels like CBF Count, CPM Count and not required.

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Total 5266844 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Total 22305806 SFS 766-0001804-007 IGP Helena Chemical Company 1800 GAL TL 36 00:00.0 17 292147.7 No IGP Total 292147.7 Grand Total 27864797

|||

If using SQL Server 2005, have you looked at COMPUTE?

http://msdn2.microsoft.com/en-us/library/ms181708.aspx

Dan

|||Thanks.|||I hope it does what you need!