DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.

Displaying ADO MDX query results in reports

I'm working on an application to display sales data using sql server
2000 reporting services.
The data is in an MS Analysis Services cube, so I'm using MDX queries
in the data set.
The queries all have a similar format of the form:
SELECT
{[Measures].Members} ON COLUMNS,
{ MyDimension.members} ON ROWS
FROM MyCube
where the items in the COLUMNS clause are allways the same and
MyDimension might be SalesBranch, ProductCategory, etc.
When Reporting services works out the fields for the returned data set
it appears to be flattening out the MyDimension structure and using the
dimension level names for field names.
eg
<Fields>
<Field Name="Manufacturer">
<DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Id">
<DataField>[Measures].[Oe Id]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Realval_A">
<DataField>[Measures].[Oe Realval A]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
In the report layout I can select these fields and display them as
expected.
However the drawback is that since dimension level names are encoded
into report field names I have to have a separate report for each
dimension I want to put on the ROWS. ie one .RDL file for SalesBranch,
one for ProductCategory etc.
If this was SQL it would not be a problem because in the SQL SELECT
statement the column names could be fixed using "SELECT colname AS
othercolname ", but, being new to MDX, I can't find how to do this.
Does anyone know how to achieve this in MDX / ADO MD ? ie how to give
an MDX column an alias
Thanks
SteveHi Steve. I'm not quite clear on what you're trying to do. If, for
example, you had StateCode, CountryCode on your rows, you'd want them both
to be in the same column? Can you be more specific? From your
explanation, I can't think of a business reason why I'd want to do this.
-T
"steve caa" <steve_caa@.hotmail.co.uk> wrote in message
news:1147778689.038349.64180@.j33g2000cwa.googlegroups.com...
> I'm working on an application to display sales data using sql server
> 2000 reporting services.
> The data is in an MS Analysis Services cube, so I'm using MDX queries
> in the data set.
> The queries all have a similar format of the form:
> SELECT
> {[Measures].Members} ON COLUMNS,
> { MyDimension.members} ON ROWS
> FROM MyCube
> where the items in the COLUMNS clause are allways the same and
> MyDimension might be SalesBranch, ProductCategory, etc.
> When Reporting services works out the fields for the returned data set
> it appears to be flattening out the MyDimension structure and using the
> dimension level names for field names.
> eg
> <Fields>
> <Field Name="Manufacturer">
> <DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_Oe_Id">
> <DataField>[Measures].[Oe Id]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_Oe_Realval_A">
> <DataField>[Measures].[Oe Realval A]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> In the report layout I can select these fields and display them as
> expected.
> However the drawback is that since dimension level names are encoded
> into report field names I have to have a separate report for each
> dimension I want to put on the ROWS. ie one .RDL file for SalesBranch,
> one for ProductCategory etc.
> If this was SQL it would not be a problem because in the SQL SELECT
> statement the column names could be fixed using "SELECT colname AS
> othercolname ", but, being new to MDX, I can't find how to do this.
> Does anyone know how to achieve this in MDX / ADO MD ? ie how to give
> an MDX column an alias
> Thanks
> Steve
>|||Hi Tim
Thanks for the reply.
I'm really trying to be lazy and reuse the same RDL for a number of
different reports.
I'm generating the MDX query from parameters in a form that contains a
report viewer. The items selected to appear along columns will always
be the same but the dimension that appears on the rows will vary
depending on whether the user wants to see sales by branch or by
product. The depth of the dimension on the rows will allways be same ie
I'm only showing branch name, not branch name and region.
If the user selects performance by sales branch the MDX looks like
SELECT
{[Measures].Members} ON COLUMNS,
{ [Branch].members} ON ROWS
FROM MyCube
and the report displays the branch name in the first column and the
measures along the other columns
similarly if the user selects performance by product the MDX looks like
SELECT
{[Measures].Members} ON COLUMNS,
{ [Product].members} ON ROWS
FROM MyCube
and the report displays the product name in the leftmost column and the
measures along the other columns
Now, the reports are identical apart from the first column, so I
thought if there was a way to make the columns have defined names I
could reuse the same RDL for any number of reports.
In the reporting services designer, on the data tab, executing a query
fills the data grid and displays field names in the first row.
The field names appear to be based on the column or row names in the
MDX query eg when I select the performance by branch report, the column
in the data grid that displays the branch name is called
"[Branch].[Branch ID].[MEMBER CAPTION]", when I select the performance
by product report, the column in the data grid that displays the
product name is called "[Product].[Product ID].[MEMBER CAPTION]" .
In the designers layout tab these field names are linked to the cells
they are to be displayed in, but since the field names are different
for the different MDX queries I'm generating I need to have a different
RDL file for each query.
In SQL I'd be able to alias the columns so they had the same name eg
SELECT SalesBranch as FirstColumn, MeasureA, MeasureB From Sales
and
SELECT ProductName as FirstColumn, MeasureA, MeasureB From Sales.
So my question is :
is there a way for MDX to do what the SQL statements above are doing?
ie make the recordsets returned by ADO allways have predefined field
names.
Thanks
Steve|||Oh! Well in that case, create a custom set and use it in a dynamic query...
Build it first in the query builder to get the columns, then change the text
to this:
= "WITH SET [Branch] AS
'" & Parameters!TheBranchToShow.Value & ".ALLMEMBERS'
SELECT {[Measures].Members} ON COLUMNS,
{ [Branch] } ON ROWS
FROM MyCube"
HTH
"steve caa" <steve_caa@.hotmail.co.uk> wrote in message
news:1147871527.003112.157400@.38g2000cwa.googlegroups.com...
> Hi Tim
> Thanks for the reply.
> I'm really trying to be lazy and reuse the same RDL for a number of
> different reports.
> I'm generating the MDX query from parameters in a form that contains a
> report viewer. The items selected to appear along columns will always
> be the same but the dimension that appears on the rows will vary
> depending on whether the user wants to see sales by branch or by
> product. The depth of the dimension on the rows will allways be same ie
> I'm only showing branch name, not branch name and region.
> If the user selects performance by sales branch the MDX looks like
> SELECT
> {[Measures].Members} ON COLUMNS,
> { [Branch].members} ON ROWS
> FROM MyCube
> and the report displays the branch name in the first column and the
> measures along the other columns
> similarly if the user selects performance by product the MDX looks like
> SELECT
> {[Measures].Members} ON COLUMNS,
> { [Product].members} ON ROWS
> FROM MyCube
> and the report displays the product name in the leftmost column and the
> measures along the other columns
> Now, the reports are identical apart from the first column, so I
> thought if there was a way to make the columns have defined names I
> could reuse the same RDL for any number of reports.
> In the reporting services designer, on the data tab, executing a query
> fills the data grid and displays field names in the first row.
> The field names appear to be based on the column or row names in the
> MDX query eg when I select the performance by branch report, the column
> in the data grid that displays the branch name is called
> "[Branch].[Branch ID].[MEMBER CAPTION]", when I select the performance
> by product report, the column in the data grid that displays the
> product name is called "[Product].[Product ID].[MEMBER CAPTION]" .
> In the designers layout tab these field names are linked to the cells
> they are to be displayed in, but since the field names are different
> for the different MDX queries I'm generating I need to have a different
> RDL file for each query.
>
> In SQL I'd be able to alias the columns so they had the same name eg
> SELECT SalesBranch as FirstColumn, MeasureA, MeasureB From Sales
> and
> SELECT ProductName as FirstColumn, MeasureA, MeasureB From Sales.
> So my question is :
> is there a way for MDX to do what the SQL statements above are doing?
> ie make the recordsets returned by ADO allways have predefined field
> names.
> Thanks
> Steve
>|||Good idea.
This seems to work in the MDX Sample query tool, but in Reporting
Services designer it still mangles the structure of the underlying
dimension into the column headings, not the MDX SET name. I tried a
simple vb.NET app and it does the same so it's an ADO MDX issue, not
Reporting Services
Looks like I'll have to do it the long way.
Thanks
Steve|||The trick is to return the dimension names as measures, and then use the
measures in your report instead of the dimension names.
You start out with a usual query, and add Currentmember.Name,
currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
[Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
Then add x number of columns, for measures that you want to reuse.
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]'
Then select all the measures (both the dimension name ones, and the ones
based on the old measures)
I added a filter to mine, you don't need to do that.
The resulting MDX query looks like this:
with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
[Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
[Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
{[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
[Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
rows from [Sales]
After you've generated all the fields, change it as usual to a parameterized
query:
="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
"].currentmember.name' member [Measures].[MyMeasure2] as '[" &
Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
"].currentmember.UniqueName' member [Measures].[MyMeasure4] as
'[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
'[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
'[Measures].[Store Sales]' select {[Measures].[MyMeasure],
[Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
[Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
& Parameters!Amount.Value & ") } on rows from [Sales]"
And now you just add the dimensions you need as a set of report parameters
( drop down list with all the dimension unique names) and other parameters
you need, and try it out. :)
The drawback is that it gets kind of flat, but you can do quite a lot of
tricks with formating the different levels.
This is a sample that works against Foodmart2000:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<Top>0.25cm</Top>
<rd:DefaultName>textbox4</rd:DefaultName>
<Height>0.63492cm</Height>
<Width>13.25cm</Width>
<CanGrow>true</CanGrow>
<Value>="Generic report based on " & Parameters!Dimension.Value</Value>
</Textbox>
<Table Name="table1">
<Height>1.90476cm</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Parameters!Dimension.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Unit sales</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</ight>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Cost</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Sales</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Measures_MyMeasure</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_MyMeasure4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Measures_MyMeasure5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure5.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Measures_MyMeasure6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure6.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!Measures_MyMeasure3.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<Width>13.63624cm</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Unit Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77645cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Cost", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77646cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>15cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<rd:DataSourceID>8a442390-ee54-4c51-b68a-8a19b447060d</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>16cm</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Store_Store_Country">
<DataField>[Store].[Store Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_State">
<DataField>[Store].[Store State].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_City">
<DataField>[Store].[Store City].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_Name">
<DataField>[Store].[Store Name].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure">
<DataField>[Measures].[MyMeasure]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure2">
<DataField>[Measures].[MyMeasure2]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure3">
<DataField>[Measures].[MyMeasure3]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure4">
<DataField>[Measures].[MyMeasure4]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure5">
<DataField>[Measures].[MyMeasure5]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure6">
<DataField>[Measures].[MyMeasure6]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>="with member [Measures].[MyMeasure] as '[" &
Parameters!Dimension.Value & "].currentmember.name' member
[Measures].[MyMeasure2] as '[" & Parameters!Dimension.Value &
"].currentmember.Level.Ordinal' member [Measures].[MyMeasure3] as '[" &
Parameters!Dimension.Value & "].currentmember.UniqueName' member
[Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
{[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
[Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
on columns, {filter([" & Parameters!Dimension.Value & "].members,"
& Parameters!Measure.Value & " > " & Parameters!Amount.Value
& ") } on rows from [Sales]"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>247133a5-6ff9-4041-b398-bb2f76911f3e</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="Dimension">
<DataType>String</DataType>
<Prompt>Choose dimension</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Store</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Type</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Size in SQFT</Value>
</ParameterValue>
<ParameterValue>
<Value>Time</Value>
</ParameterValue>
<ParameterValue>
<Value>Product</Value>
</ParameterValue>
<ParameterValue>
<Value>Gender</Value>
</ParameterValue>
<ParameterValue>
<Value>Promotions</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Measure">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="[Measures].[Unit Sales]"</Value>
</Values>
</DefaultValue>
<Prompt>Choose measure</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>[Measures].[Unit Sales]</Value>
<Label>Unit Sales</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Cost]</Value>
<Label>Store Cost</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Sales]</Value>
<Label>Store Sales</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Amount">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>More than</Prompt>
</ReportParameter>
<ReportParameter Name="ShowAll">
<DataType>Boolean</DataType>
<DefaultValue>
<Values>
<Value>true</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Show all</Prompt>
</ReportParameter>
</ReportParameters>
<Language>nb-NO</Language>
</Report>|||Hi Kaisa,
I want same what steve want. I have two column in my report. First is
Dimension and second is measure. I have tryed what you suggested but not
working. can you please check where i am doing wrong?
I have two dimension:
[Dim Station].[Station Name].[Station Name]
[Dim Free Test].[Free Test].[Free Test]
and one measure:
[Measures].[Total Test Count]
My Cube Name is:
[OLAP Test Cube]
I want [Dim Station] and [Dim Free Test] dimension dynamically. I don't want
any filter if it require for Dynamic queyr then ok.
when i execute following query in Query builder then it giving me error. can
you please check this where i am wrong?
Query:
with member [Measures].[MyMeasure] as '[Dim Station].currentmember.name'
member [Measures].[MyMeasure2] as '[Dim Station].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Dim Station].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]}
on columns, {filter([Dim Station].members, [Measures].[Total Test Count]]>
0) } on
rows from [OLAP Test Cube]
Regards,
Dinesh Patel
"Kaisa M. Lindahl Lervik" wrote:
> The trick is to return the dimension names as measures, and then use the
> measures in your report instead of the dimension names.
> You start out with a usual query, and add Currentmember.Name,
> currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
> with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> Then add x number of columns, for measures that you want to reuse.
> member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
> Then select all the measures (both the dimension name ones, and the ones
> based on the old measures)
> I added a filter to mine, you don't need to do that.
> The resulting MDX query looks like this:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
> [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> [Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
> {[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
> [Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
> on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
> rows from [Sales]
>
> After you've generated all the fields, change it as usual to a parameterized
> query:
> ="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
> "].currentmember.name' member [Measures].[MyMeasure2] as '[" &
> Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
> "].currentmember.UniqueName' member [Measures].[MyMeasure4] as
> '[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
> '[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
> '[Measures].[Store Sales]' select {[Measures].[MyMeasure],
> [Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
> [Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
> Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
> & Parameters!Amount.Value & ") } on rows from [Sales]"
> And now you just add the dimensions you need as a set of report parameters
> ( drop down list with all the dimension unique names) and other parameters
> you need, and try it out. :)
> The drawback is that it gets kind of flat, but you can do quite a lot of
> tricks with formating the different levels.
> This is a sample that works against Foodmart2000:
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <RightMargin>2.5cm</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <Top>0.25cm</Top>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <Height>0.63492cm</Height>
> <Width>13.25cm</Width>
> <CanGrow>true</CanGrow>
> <Value>="Generic report based on " & Parameters!Dimension.Value</Value>
> </Textbox>
> <Table Name="table1">
> <Height>1.90476cm</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.63492cm</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Left>Solid</Left>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= Parameters!Dimension.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</Right>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Unit sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</ight>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Store Cost</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</Right>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Store Sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>|||I am working with AS2005 and RS2005 I have created one report also. can you
please check this code.
I am trying to change query to below but giving error:
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
my report code is below:
<?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="SLCTestDB">
<DataSourceReference>SLCTestDB</DataSourceReference>
<rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Dimension">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Free Test]</Value>
</Values>
</DefaultValue>
<Prompt>Dimension</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>[Station Name]</Value>
<Label>station</Label>
</ParameterValue>
<ParameterValue>
<Value>[Free Test]</Value>
<Label>Free</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.36in</Height>
<Value>Report3</Value>
</Textbox>
<Table Name="table1">
<DataSetName>SLCTestDB</DataSetName>
<Top>0.36in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="MyMeasure">
<rd:DefaultName>MyMeasure</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!MyMeasure.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>
</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!MyMeasure.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_MyMeasure">
<GroupExpressions>
<GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>
</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="MyMeasure4">
<rd:DefaultName>MyMeasure4</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!MyMeasure4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>My Measure</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>My Measure4</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.22in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.875in</Width>
</TableColumn>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Height>1in</Height>
</Body>
<rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="SLCTestDB">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>with member [Measures].[MyMeasure] as '[Free
Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]} on columns,
{[Free Test].members} on
rows from [OLAP Test Cube]</CommandText>
<DataSourceName>SLCTestDB</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><Type>Query</Type><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Dim Free
Test</DimensionName><HierarchyName>Free
Test</HierarchyName><HierarchyUniqueName>[Dim Free Test].[Free
Test]</HierarchyUniqueName><LevelName>Free Test</LevelName><UniqueName>[Dim
Free Test].[Free Test].[Free Test]</UniqueName></ID><ItemCaption>Free
Test</ItemCaption></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure</MeasureName><UniqueName>[Measures].[MyMeasure]</UniqueName></ID><ItemCaption>MyMeasure</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure2</MeasureName><UniqueName>[Measures].[MyMeasure2]</UniqueName></ID><ItemCaption>MyMeasure2</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure3</MeasureName><UniqueName>[Measures].[MyMeasure3]</UniqueName></ID><ItemCaption>MyMeasure3</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure4</MeasureName><UniqueName>[Measures].[MyMeasure4]</UniqueName></ID><ItemCaption>MyMeasure4</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>OLAP
Test Cube</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
/><QueryProperties /></QuerySpecification><Query><Statement>with member
[Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]} on columns,
{[Free Test].members} on
rows from [OLAP Test Cube]</Statement><ParameterDefinitions
/></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="Free_Test">
<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="[Dim Free Test].[Free Test].[Free Test]" /></DataField>
</Field>
<Field Name="MyMeasure">
<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].[MyMeasure]" /></DataField>
</Field>
<Field Name="MyMeasure2">
<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].[MyMeasure2]" /></DataField>
</Field>
<Field Name="MyMeasure3">
<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].[MyMeasure3]" /></DataField>
</Field>
<Field Name="MyMeasure4">
<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].[MyMeasure4]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
"Dinesh Patel" wrote:
> Hi Kaisa,
> I want same what steve want. I have two column in my report. First is
> Dimension and second is measure. I have tryed what you suggested but not
> working. can you please check where i am doing wrong?
> I have two dimension:
> [Dim Station].[Station Name].[Station Name]
> [Dim Free Test].[Free Test].[Free Test]
> and one measure:
> [Measures].[Total Test Count]
> My Cube Name is:
> [OLAP Test Cube]
> I want [Dim Station] and [Dim Free Test] dimension dynamically. I don't want
> any filter if it require for Dynamic queyr then ok.
> when i execute following query in Query builder then it giving me error. can
> you please check this where i am wrong?
> Query:
> with member [Measures].[MyMeasure] as '[Dim Station].currentmember.name'
> member [Measures].[MyMeasure2] as '[Dim Station].currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '[Dim Station].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3],
> [Measures].[MyMeasure4]}
> on columns, {filter([Dim Station].members, [Measures].[Total Test Count]]>
> 0) } on
> rows from [OLAP Test Cube]
> Regards,
> Dinesh Patel
> "Kaisa M. Lindahl Lervik" wrote:
> > The trick is to return the dimension names as measures, and then use the
> > measures in your report instead of the dimension names.
> >
> > You start out with a usual query, and add Currentmember.Name,
> > currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
> >
> > with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> > [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> >
> > Then add x number of columns, for measures that you want to reuse.
> > member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> > [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> > [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
> >
> > Then select all the measures (both the dimension name ones, and the ones
> > based on the old measures)
> > I added a filter to mine, you don't need to do that.
> >
> > The resulting MDX query looks like this:
> >
> > with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> > [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
> > [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> > [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> > [Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
> > {[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
> > [Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
> > on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
> > rows from [Sales]
> >
> >
> > After you've generated all the fields, change it as usual to a parameterized
> > query:
> >
> > ="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
> > "].currentmember.name' member [Measures].[MyMeasure2] as '[" &
> > Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
> > "].currentmember.UniqueName' member [Measures].[MyMeasure4] as
> > '[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
> > '[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
> > '[Measures].[Store Sales]' select {[Measures].[MyMeasure],
> > [Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
> > [Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
> > Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
> > & Parameters!Amount.Value & ") } on rows from [Sales]"
> >
> > And now you just add the dimensions you need as a set of report parameters
> > ( drop down list with all the dimension unique names) and other parameters
> > you need, and try it out. :)
> >
> > The drawback is that it gets kind of flat, but you can do quite a lot of
> > tricks with formating the different levels.
> >
> > This is a sample that works against Foodmart2000:
> > <?xml version="1.0" encoding="utf-8"?>
> >
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >
> > <rd:GridSpacing>0.25cm</rd:GridSpacing>
> >
> > <RightMargin>2.5cm</RightMargin>
> >
> > <Body>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox4">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > </Style>
> >
> > <ZIndex>1</ZIndex>
> >
> > <Top>0.25cm</Top>
> >
> > <rd:DefaultName>textbox4</rd:DefaultName>
> >
> > <Height>0.63492cm</Height>
> >
> > <Width>13.25cm</Width>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>="Generic report based on " & Parameters!Dimension.Value</Value>
> >
> > </Textbox>
> >
> > <Table Name="table1">
> >
> > <Height>1.90476cm</Height>
> >
> > <Style />
> >
> > <Header>
> >
> > <TableRows>
> >
> > <TableRow>
> >
> > <Height>0.63492cm</Height>
> >
> > <TableCells>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox1">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Left>Solid</Left>
> >
> > </BorderStyle>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>11</ZIndex>
> >
> > <rd:DefaultName>textbox1</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>= Parameters!Dimension.Value</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox2">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Right>Solid</Right>
> >
> > </BorderStyle>
> >
> > <TextAlign>Right</TextAlign>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>10</ZIndex>
> >
> > <rd:DefaultName>textbox2</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>Unit sales</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox3">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Right>Solid</ight>
> >
> > </BorderStyle>
> >
> > <TextAlign>Right</TextAlign>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>9</ZIndex>
> >
> > <rd:DefaultName>textbox3</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>Store Cost</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox10">
> >
> > <Style>
> >|||problem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (â?¦) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:
> I am working with AS2005 and RS2005 I have created one report also. can you
> please check this code.
> I am trying to change query to below but giving error:
> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> ".currentmember.name'
> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> ".currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> ".currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3],
> [Measures].[MyMeasure4]}
> on columns, {" & Parameters!Dimension.Value & ".members} on
> rows from [OLAP Test Cube]"
>
> my report code is below:
>
> <?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="SLCTestDB">
> <DataSourceReference>SLCTestDB</DataSourceReference>
> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="Dimension">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>[Free Test]</Value>
> </Values>
> </DefaultValue>
> <Prompt>Dimension</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>[Station Name]</Value>
> <Label>station</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>[Free Test]</Value>
> <Label>Free</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>20pt</FontSize>
> <Color>SteelBlue</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.36in</Height>
> <Value>Report3</Value>
> </Textbox>
> <Table Name="table1">
> <DataSetName>SLCTestDB</DataSetName>
> <Top>0.36in</Top>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="MyMeasure">
> <rd:DefaultName>MyMeasure</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>#6e9eca</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!MyMeasure.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>#6e9eca</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>
> </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.21in</Height>
> </TableRow>
> </TableRows>
> </Header>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="table1_MyMeasure">
> <GroupExpressions>
> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <Width>4.5in</Width>
> <Details>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <rd:DefaultName>textbox3</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>
> </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="MyMeasure4">
> <rd:DefaultName>MyMeasure4</rd:DefaultName>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!MyMeasure4.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.21in</Height>
> </TableRow>
> </TableRows>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>5</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>11pt</FontSize>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>SteelBlue</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>My Measure</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>4</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>11pt</FontSize>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>SteelBlue</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>My Measure4</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.22in</Height>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <TableColumns>
> <TableColumn>
> <Width>2.875in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Height>1in</Height>
> </Body>
> <rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="SLCTestDB">
> <Query>|||I'm sorry I haven't replied to this earlier, but I'm happy you've solved it.
I guess I was assuming you knew you had to use a non dynamic query to build
the fields correctly. I'll remember to add this if I give advice on dynamic
mdx again.
Kaisa M. Lindahl Lervik
"Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
news:D1FC2757-F590-4881-924C-D01BE05F454F@.microsoft.com...
> problem was solved.
> First execute following query and design the report.
> with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
> member [Measures].[MyMeasure2] as '[Free
> Test].currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
> Test].members} on
> rows from [OLAP Test Cube]
> and then Edit Dataset using (.) button and paste Dynamic query on Query
> String Textbox and click ok.
> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> ".currentmember.name'
> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> ".currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> ".currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3], [Measures].[MyMeasure4]}
> on columns, {" & Parameters!Dimension.Value & ".members} on
> rows from [OLAP Test Cube]"
> Regards,
> Dinesh Patel
>
> "Dinesh Patel" wrote:
>> I am working with AS2005 and RS2005 I have created one report also. can
>> you
>> please check this code.
>> I am trying to change query to below but giving error:
>> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
>> ".currentmember.name'
>> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
>> ".currentmember.Level.Ordinal'
>> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
>> ".currentmember.UniqueName'
>> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
>> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
>> [Measures].[MyMeasure3],
>> [Measures].[MyMeasure4]}
>> on columns, {" & Parameters!Dimension.Value & ".members} on
>> rows from [OLAP Test Cube]"
>>
>> my report code is below:
>>
>> <?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="SLCTestDB">
>> <DataSourceReference>SLCTestDB</DataSourceReference>
>> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <ReportParameters>
>> <ReportParameter Name="Dimension">
>> <DataType>String</DataType>
>> <DefaultValue>
>> <Values>
>> <Value>[Free Test]</Value>
>> </Values>
>> </DefaultValue>
>> <Prompt>Dimension</Prompt>
>> <ValidValues>
>> <ParameterValues>
>> <ParameterValue>
>> <Value>[Station Name]</Value>
>> <Label>station</Label>
>> </ParameterValue>
>> <ParameterValue>
>> <Value>[Free Test]</Value>
>> <Label>Free</Label>
>> </ParameterValue>
>> </ParameterValues>
>> </ValidValues>
>> </ReportParameter>
>> </ReportParameters>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <ReportItems>
>> <Textbox Name="textbox1">
>> <rd:DefaultName>textbox1</rd:DefaultName>
>> <ZIndex>1</ZIndex>
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>20pt</FontSize>
>> <Color>SteelBlue</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Height>0.36in</Height>
>> <Value>Report3</Value>
>> </Textbox>
>> <Table Name="table1">
>> <DataSetName>SLCTestDB</DataSetName>
>> <Top>0.36in</Top>
>> <TableGroups>
>> <TableGroup>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="MyMeasure">
>> <rd:DefaultName>MyMeasure</rd:DefaultName>
>> <ZIndex>3</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>#6e9eca</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!MyMeasure.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox5">
>> <rd:DefaultName>textbox5</rd:DefaultName>
>> <ZIndex>2</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>#6e9eca</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>
>> </Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.21in</Height>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> <Grouping Name="table1_MyMeasure">
>> <GroupExpressions>
>> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> </TableGroup>
>> </TableGroups>
>> <Width>4.5in</Width>
>> <Details>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox3">
>> <rd:DefaultName>textbox3</rd:DefaultName>
>> <ZIndex>1</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>
>> </Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="MyMeasure4">
>> <rd:DefaultName>MyMeasure4</rd:DefaultName>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!MyMeasure4.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.21in</Height>
>> </TableRow>
>> </TableRows>
>> </Details>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox2">
>> <rd:DefaultName>textbox2</rd:DefaultName>
>> <ZIndex>5</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <TextAlign>Right</TextAlign>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>11pt</FontSize>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>SteelBlue</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>My Measure</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox4">
>> <rd:DefaultName>textbox4</rd:DefaultName>
>> <ZIndex>4</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <TextAlign>Right</TextAlign>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>11pt</FontSize>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>SteelBlue</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>My Measure4</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.22in</Height>
>> </TableRow>
>> </TableRows>
>> <RepeatOnNewPage>true</RepeatOnNewPage>
>> </Header>
>> <TableColumns>
>> <TableColumn>
>> <Width>2.875in</Width>
>> </TableColumn>
>> <TableColumn>
>> <Width>1.625in</Width>
>> </TableColumn>
>> </TableColumns>
>> </Table>
>> </ReportItems>
>> <Height>1in</Height>
>> </Body>
>> <rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="SLCTestDB">
>> <Query>|||I am not expert, i received this solution from net.
Dinesh Patel
"Kaisa M. Lindahl Lervik" wrote:
> I'm sorry I haven't replied to this earlier, but I'm happy you've solved it.
> I guess I was assuming you knew you had to use a non dynamic query to build
> the fields correctly. I'll remember to add this if I give advice on dynamic
> mdx again.
> Kaisa M. Lindahl Lervik
>
> "Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
> news:D1FC2757-F590-4881-924C-D01BE05F454F@.microsoft.com...
> > problem was solved.
> >
> > First execute following query and design the report.
> >
> > with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
> > member [Measures].[MyMeasure2] as '[Free
> > Test].currentmember.Level.Ordinal'
> > member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
> > member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> > select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> > [Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
> > Test].members} on
> > rows from [OLAP Test Cube]
> >
> > and then Edit Dataset using (.) button and paste Dynamic query on Query
> > String Textbox and click ok.
> >
> > ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> > ".currentmember.name'
> >
> > member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> > ".currentmember.Level.Ordinal'
> > member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> > ".currentmember.UniqueName'
> > member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> > select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> > [Measures].[MyMeasure3], [Measures].[MyMeasure4]}
> > on columns, {" & Parameters!Dimension.Value & ".members} on
> > rows from [OLAP Test Cube]"
> >
> > Regards,
> > Dinesh Patel
> >
> >
> > "Dinesh Patel" wrote:
> >
> >> I am working with AS2005 and RS2005 I have created one report also. can
> >> you
> >> please check this code.
> >>
> >> I am trying to change query to below but giving error:
> >>
> >> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> >> ".currentmember.name'
> >> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> >> ".currentmember.Level.Ordinal'
> >> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> >> ".currentmember.UniqueName'
> >> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> >>
> >> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> >> [Measures].[MyMeasure3],
> >> [Measures].[MyMeasure4]}
> >> on columns, {" & Parameters!Dimension.Value & ".members} on
> >> rows from [OLAP Test Cube]"
> >>
> >>
> >>
> >> my report code is below:
> >>
> >>
> >> <?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="SLCTestDB">
> >> <DataSourceReference>SLCTestDB</DataSourceReference>
> >>
> >> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <ReportParameters>
> >> <ReportParameter Name="Dimension">
> >> <DataType>String</DataType>
> >> <DefaultValue>
> >> <Values>
> >> <Value>[Free Test]</Value>
> >> </Values>
> >> </DefaultValue>
> >> <Prompt>Dimension</Prompt>
> >> <ValidValues>
> >> <ParameterValues>
> >> <ParameterValue>
> >> <Value>[Station Name]</Value>
> >> <Label>station</Label>
> >> </ParameterValue>
> >> <ParameterValue>
> >> <Value>[Free Test]</Value>
> >> <Label>Free</Label>
> >> </ParameterValue>
> >> </ParameterValues>
> >> </ValidValues>
> >> </ReportParameter>
> >> </ReportParameters>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <ReportItems>
> >> <Textbox Name="textbox1">
> >> <rd:DefaultName>textbox1</rd:DefaultName>
> >> <ZIndex>1</ZIndex>
> >> <Style>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <FontSize>20pt</FontSize>
> >> <Color>SteelBlue</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Height>0.36in</Height>
> >> <Value>Report3</Value>
> >> </Textbox>
> >> <Table Name="table1">
> >> <DataSetName>SLCTestDB</DataSetName>
> >> <Top>0.36in</Top>
> >> <TableGroups>
> >> <TableGroup>
> >> <Header>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="MyMeasure">
> >> <rd:DefaultName>MyMeasure</rd:DefaultName>
> >> <ZIndex>3</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>#6e9eca</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>=Fields!MyMeasure.Value</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox5">
> >> <rd:DefaultName>textbox5</rd:DefaultName>
> >> <ZIndex>2</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>#6e9eca</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>
> >> </Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> </TableCells>
> >> <Height>0.21in</Height>
> >> </TableRow>
> >> </TableRows>
> >> </Header>
> >> <Sorting>
> >> <SortBy>
> >> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
> >> <Direction>Ascending</Direction>
> >> </SortBy>
> >> </Sorting>
> >> <Grouping Name="table1_MyMeasure">
> >> <GroupExpressions>
> >>
> >> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
> >> </GroupExpressions>
> >> </Grouping>
> >> </TableGroup>
> >> </TableGroups>
> >> <Width>4.5in</Width>
> >> <Details>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox3">
> >> <rd:DefaultName>textbox3</rd:DefaultName>
> >> <ZIndex>1</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>
> >> </Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="MyMeasure4">
> >> <rd:DefaultName>MyMeasure4</rd:DefaultName>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>=Fields!MyMeasure4.Value</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> </TableCells>
> >> <Height>0.21in</Height>
> >> </TableRow>
> >> </TableRows>
> >> </Details>
> >> <Header>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox2">
> >> <rd:DefaultName>textbox2</rd:DefaultName>
> >> <ZIndex>5</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <TextAlign>Right</TextAlign>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <FontSize>11pt</FontSize>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>SteelBlue</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>My Measure</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>