RS/MDX - Column Parameter / Field name in dataset problem

In creating a dataset with MDX, the field names (column captions) for
my dataset always resolve to the actual member name in the AS cube.
For example, if i use the following query as the datasource
With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
The field name in the dataset still appears as [Measures].[Quantity]
instead of [GenericMeasureName] .
Why is this a problem? I need to parameterize Measures... If I build
the report with a field name of [Measures].[Quantity] and the user
selects [Measures].[Revenue] then the report no-worky.
I cannot put measures as rows, or page, or a slicer dimension or
anything like that...those are parameterized also. The users need to
filter on all dimensions for this (and many other) report.
I also tried to "Edit Data Set" and change the field name on the
"Fields" tab (edit dataset dialog) and same behavior.
I've been searching BOL, etc for a day now with no luck. It seems like
the answer should be a quick click on a setting somewhere... what am I
missing? thanks in advance.
JoelNot sure if this is exactly what you need, but I'll tell you how I've solved
a similar problem, based on cascading and dynamic parameters.
My report has 2 sets of parameters:
1) What dimension to slice on
2) Values in that dimension
My report is built so that I can put any of my parameters as my WHERE clause
in my MDX statement. The trick is to use the Uniquename of a member.
The first parameter is called Selection. I've hard coded a list of
dimensions, with a sort of Friendly name as the Label and the actual name of
the dimension as Value.
The next parameter is called SelectionSelected, and is based on an mdx query
building a list of parametrs based on the dimension selected previously.
Dataset is called ListDimensions and the mdx looks like this:
="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
"].defaultmember,1,self)'
MEMBER [MEASURES].[List_Qualified_Name] AS '[" & Parameters!Selection.Value
& "].CurrentMember.UniqueName'
MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value &
"].CurrentMember.Name'
SELECT non empty {[MEASURES].[List_Report_Name],
[MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
AXIS(1) FROM [MyCube]"
The set will still keep the name of whatever dimensionname I used to get the
fields. But you don't need to use the name of this set for anything. You
need to use the member fields, and these will still be called
Measures_List_Qualified_Name and Measures_List_Report_Name.
In your report parameters, the parameter SelectionSelected should be tied to
your ListDimensions dataset, with Measures_List_Qualified_Name as your Value
field and Measures_List_Report_Name as the Label field. The unique name
(Measures_List_Qualified_Name) will be in the form of
[Dimension].[MemberLevel1].[MemberLevel2], while the
Report_Name will be the usual name (Like Store1).
So, in your report dataset, you use your basic mdx query, but in the WHERE
clause, you add Parameters!SelectionSelected.Value.
="Select {[Measures].[Totalt amount} on columns,
non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
from [MyCube]
where (" & Parameters!SelectionSelected.Value & ")"
Now, the trick is that you can't really use the same dimension on more than
one axis, but that would be a problem anyway. You may have to tweak your mdx
a bit to make it work, but using Dimension.Currentmember.Uniquename should
be usefull for you.
Kaisa M. Lindahl
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
> In creating a dataset with MDX, the field names (column captions) for
> my dataset always resolve to the actual member name in the AS cube.
> For example, if i use the following query as the datasource
> With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
> SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
> The field name in the dataset still appears as [Measures].[Quantity]
> instead of [GenericMeasureName] .
> Why is this a problem? I need to parameterize Measures... If I build
> the report with a field name of [Measures].[Quantity] and the user
> selects [Measures].[Revenue] then the report no-worky.
> I cannot put measures as rows, or page, or a slicer dimension or
> anything like that...those are parameterized also. The users need to
> filter on all dimensions for this (and many other) report.
> I also tried to "Edit Data Set" and change the field name on the
> "Fields" tab (edit dataset dialog) and same behavior.
> I've been searching BOL, etc for a day now with no luck. It seems like
> the answer should be a quick click on a setting somewhere... what am I
> missing? thanks in advance.
> Joel
>|||Kaisa -
This is wonderful. I have used a version of this technique before, but
your solution is even more dynamic with the introduction of the
parameter to select the dimension. Many Thanks and I know this will be
useful to me many many times.
I still can't figure out a way to make this work for the measures
dimension however, though it makes great sense for all other
dimensions.
The trick is using measure names the rows AND the columns (as you
mentioned) and I can't figure out how to twist the axis's correctly for
this result.
I'm thinking of creating a fake dimension with one member and use that
for the column header. Then couple this with your solution. Not sure
if this will work...keeping my fingers crossed
thanks again for the code!
Kaisa M. Lindahl wrote:
> Not sure if this is exactly what you need, but I'll tell you how I've solved
> a similar problem, based on cascading and dynamic parameters.
> My report has 2 sets of parameters:
> 1) What dimension to slice on
> 2) Values in that dimension
> My report is built so that I can put any of my parameters as my WHERE clause
> in my MDX statement. The trick is to use the Uniquename of a member.
> The first parameter is called Selection. I've hard coded a list of
> dimensions, with a sort of Friendly name as the Label and the actual name of
> the dimension as Value.
> The next parameter is called SelectionSelected, and is based on an mdx query
> building a list of parametrs based on the dimension selected previously.
> Dataset is called ListDimensions and the mdx looks like this:
> ="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
> "].defaultmember,1,self)'
> MEMBER [MEASURES].[List_Qualified_Name] AS '[" & Parameters!Selection.Value
> & "].CurrentMember.UniqueName'
> MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value &
> "].CurrentMember.Name'
> SELECT non empty {[MEASURES].[List_Report_Name],
> [MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
> AXIS(1) FROM [MyCube]"
> The set will still keep the name of whatever dimensionname I used to get the
> fields. But you don't need to use the name of this set for anything. You
> need to use the member fields, and these will still be called
> Measures_List_Qualified_Name and Measures_List_Report_Name.
> In your report parameters, the parameter SelectionSelected should be tied to
> your ListDimensions dataset, with Measures_List_Qualified_Name as your Value
> field and Measures_List_Report_Name as the Label field. The unique name
> (Measures_List_Qualified_Name) will be in the form of
> [Dimension].[MemberLevel1].[MemberLevel2], while the
> Report_Name will be the usual name (Like Store1).
> So, in your report dataset, you use your basic mdx query, but in the WHERE
> clause, you add Parameters!SelectionSelected.Value.
> ="Select {[Measures].[Totalt amount} on columns,
> non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
> from [MyCube]
> where (" & Parameters!SelectionSelected.Value & ")"
> Now, the trick is that you can't really use the same dimension on more than
> one axis, but that would be a problem anyway. You may have to tweak your mdx
> a bit to make it work, but using Dimension.Currentmember.Uniquename should
> be usefull for you.
> Kaisa M. Lindahl
> "JoelBarish" <stacijon@.gmail.com> wrote in message
> news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
> > In creating a dataset with MDX, the field names (column captions) for
> > my dataset always resolve to the actual member name in the AS cube.
> >
> > For example, if i use the following query as the datasource
> > With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
> > SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
> >
> > The field name in the dataset still appears as [Measures].[Quantity]
> > instead of [GenericMeasureName] .
> >
> > Why is this a problem? I need to parameterize Measures... If I build
> > the report with a field name of [Measures].[Quantity] and the user
> > selects [Measures].[Revenue] then the report no-worky.
> >
> > I cannot put measures as rows, or page, or a slicer dimension or
> > anything like that...those are parameterized also. The users need to
> > filter on all dimensions for this (and many other) report.
> >
> > I also tried to "Edit Data Set" and change the field name on the
> > "Fields" tab (edit dataset dialog) and same behavior.
> >
> > I've been searching BOL, etc for a day now with no luck. It seems like
> > the answer should be a quick click on a setting somewhere... what am I
> > missing? thanks in advance.
> >
> > Joel
> >|||Glad to help, I've been pretty satisfied with that solution myself. ;)
Anyway, what are you trying to do with the measures? Creating a query "where
[Measures].[Totalamount] > 0" or something?
You could try asking for help in the Olap-newsgroup,
microsoft.public.sqlserver.olap, as you may get around the problem by
rewriting your mdx somewhat.
Kaisa
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136821283.237700.90330@.g47g2000cwa.googlegroups.com...
> Kaisa -
> This is wonderful. I have used a version of this technique before, but
> your solution is even more dynamic with the introduction of the
> parameter to select the dimension. Many Thanks and I know this will be
> useful to me many many times.
> I still can't figure out a way to make this work for the measures
> dimension however, though it makes great sense for all other
> dimensions.
> The trick is using measure names the rows AND the columns (as you
> mentioned) and I can't figure out how to twist the axis's correctly for
> this result.
> I'm thinking of creating a fake dimension with one member and use that
> for the column header. Then couple this with your solution. Not sure
> if this will work...keeping my fingers crossed
> thanks again for the code!
>
>
> Kaisa M. Lindahl wrote:
>> Not sure if this is exactly what you need, but I'll tell you how I've
>> solved
>> a similar problem, based on cascading and dynamic parameters.
>> My report has 2 sets of parameters:
>> 1) What dimension to slice on
>> 2) Values in that dimension
>> My report is built so that I can put any of my parameters as my WHERE
>> clause
>> in my MDX statement. The trick is to use the Uniquename of a member.
>> The first parameter is called Selection. I've hard coded a list of
>> dimensions, with a sort of Friendly name as the Label and the actual name
>> of
>> the dimension as Value.
>> The next parameter is called SelectionSelected, and is based on an mdx
>> query
>> building a list of parametrs based on the dimension selected previously.
>> Dataset is called ListDimensions and the mdx looks like this:
>> ="WITH set [Listrows] as 'descendants([" & Parameters!Selection.Value &
>> "].defaultmember,1,self)'
>> MEMBER [MEASURES].[List_Qualified_Name] AS '[" &
>> Parameters!Selection.Value
>> & "].CurrentMember.UniqueName'
>> MEMBER [MEASURES].[List_Report_Name] AS '[" & Parameters!Selection.Value
>> &
>> "].CurrentMember.Name'
>> SELECT non empty {[MEASURES].[List_Report_Name],
>> [MEASURES].[List_Qualified_Name]} ON AXIS(0), non empty {[Listrows]} ON
>> AXIS(1) FROM [MyCube]"
>> The set will still keep the name of whatever dimensionname I used to get
>> the
>> fields. But you don't need to use the name of this set for anything. You
>> need to use the member fields, and these will still be called
>> Measures_List_Qualified_Name and Measures_List_Report_Name.
>> In your report parameters, the parameter SelectionSelected should be tied
>> to
>> your ListDimensions dataset, with Measures_List_Qualified_Name as your
>> Value
>> field and Measures_List_Report_Name as the Label field. The unique name
>> (Measures_List_Qualified_Name) will be in the form of
>> [Dimension].[MemberLevel1].[MemberLevel2], while the
>> Report_Name will be the usual name (Like Store1).
>> So, in your report dataset, you use your basic mdx query, but in the
>> WHERE
>> clause, you add Parameters!SelectionSelected.Value.
>> ="Select {[Measures].[Totalt amount} on columns,
>> non empty descendants([Clients].defaultmember, 0, self_and_after) on rows
>> from [MyCube]
>> where (" & Parameters!SelectionSelected.Value & ")"
>> Now, the trick is that you can't really use the same dimension on more
>> than
>> one axis, but that would be a problem anyway. You may have to tweak your
>> mdx
>> a bit to make it work, but using Dimension.Currentmember.Uniquename
>> should
>> be usefull for you.
>> Kaisa M. Lindahl
>> "JoelBarish" <stacijon@.gmail.com> wrote in message
>> news:1136572195.653007.143290@.z14g2000cwz.googlegroups.com...
>> > In creating a dataset with MDX, the field names (column captions) for
>> > my dataset always resolve to the actual member name in the AS cube.
>> >
>> > For example, if i use the following query as the datasource
>> > With Set [GenericMeasureName] as '{[Measures].[Quantity]}'
>> > SELECT {[GenericMeasureName]} ON COLUMNS, .... etc
>> >
>> > The field name in the dataset still appears as [Measures].[Quantity]
>> > instead of [GenericMeasureName] .
>> >
>> > Why is this a problem? I need to parameterize Measures... If I build
>> > the report with a field name of [Measures].[Quantity] and the user
>> > selects [Measures].[Revenue] then the report no-worky.
>> >
>> > I cannot put measures as rows, or page, or a slicer dimension or
>> > anything like that...those are parameterized also. The users need to
>> > filter on all dimensions for this (and many other) report.
>> >
>> > I also tried to "Edit Data Set" and change the field name on the
>> > "Fields" tab (edit dataset dialog) and same behavior.
>> >
>> > I've been searching BOL, etc for a day now with no luck. It seems like
>> > the answer should be a quick click on a setting somewhere... what am I
>> > missing? thanks in advance.
>> >
>> > Joel
>> >
>|||Hi Kaisa -
The users want a report that includes only one measure and they want to
choose the measure. They want EITHER Revenue OR Quantity OR Cost OR
Discount on the report, but not all of these. I tried to convince them
to have all these on the report and only look at the one they want, but
they won't accept that... they have some downstream procedures that
will be impacted or similar...
This isn't necessarily a problem, I could put the measure in the WHERE
if I had to. However, I then encounter the same problem with whatever
dimension I move to the columns. For example, I can't use the Time
dimension in the column because users want to select Time from a
parameter (result: changing column name problem for Time dimension).
Users also want a parameter for Line Of Business, etc. (all fields are
parameters therefore all fields have changing column/field names).
What I have done for now is create a calculated field in the dataset:
(Psuedo code): Iif(parameter=revenue,
revenue.value,(iif(parameter=quantity,quantity.value,(iif(parameter=cost,
...etc.) ... then reference the calculated field in the report
definition.
(I've read on this forum that CASE statements are not allowed in
expressions.)
I don't like this solution because I don't like hardcoding this much
into a report. And... there are a very large number of reports that
require this same type of coding.
I will post the mdx question on the OLAP forum. meanwhile, if you
happen to think of a different/better way to skin this cat, let me
know... you seem to have some creative solutions up your sleeve.
gracias, joel b
--i like MSAS a lot (i've worked with three other major OLAP vendors
and the development environment in MSAS is tops). I am new to
Reporting Services but so far it seems it could be enhanced to interact
with MSAS better. I continue to run into problems that don't fit well
into MSRS and they are all specific to sourcing from a cube.|||As Reporting Services isn't all that dynamic, I guess some hard coding has
to be done for each report. But you still might be able to do some tricks.
Even if the users only want to see one measure, you can still make the
report query find numbers for all the measures they can choose from. And
then do some visibility checks on the actual columns in your report. You
probably shouldn't have too much of a difference in the query time, even if
you get numbers for both Revenue AND quantity.
And if you want a super generic report that can't be grouped too much, you
can do something like this:
with member [Measures].[MyMeasure] as '[Store].currentmember.name'
member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
select
{[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store
Sales],
[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3]} on
columns,
{filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
from [Sales]
The first calculated member will give you the store names in a column, the
second will give you that member's level, the third the unique name in the
form of [Store].[All Stores].[USA] etc. You can then group on Measure3, but
you might want to do some visibility checks to not dump out all the rows a
lot of times. Set all columns and rows to Visibility - Hidden = True, and
make it visible if the data you want to show equals ... something.
All calculated members will be called by the name you give it, contrary to
sets. So you can change the query to something like this, and all your
column names will controllable:
with member [Measures].[MyMeasure] as '[Store].currentmember.name'
member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
member [Measures].[MyMeasure5] as '[Measures].[Store Sales]'
select
{[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3],[Measures].[MyMeasure4],[Measures].[MyMeasure5],[Measures].[MyMeasure6]}
on columns,
{filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
from [Sales]
Your dimension used on rows and the first three measures needs to be the
same. And you need to filter on what ever measure you want to filter on, but
you can use [MyMeasure4] instead of [Unit Sales]. But you don't use the
row-based dimensions on your report rows, you use the measure. On the other
hand, there will be some differences from report to report, or else you
could create one huge generic report and just leave work for the day. ;)
Kaisa M. Lindahl
"JoelBarish" <stacijon@.gmail.com> wrote in message
news:1136829966.359151.155080@.g14g2000cwa.googlegroups.com...
> Hi Kaisa -
> The users want a report that includes only one measure and they want to
> choose the measure. They want EITHER Revenue OR Quantity OR Cost OR
> Discount on the report, but not all of these. I tried to convince them
> to have all these on the report and only look at the one they want, but
> they won't accept that... they have some downstream procedures that
> will be impacted or similar...
> This isn't necessarily a problem, I could put the measure in the WHERE
> if I had to. However, I then encounter the same problem with whatever
> dimension I move to the columns. For example, I can't use the Time
> dimension in the column because users want to select Time from a
> parameter (result: changing column name problem for Time dimension).
> Users also want a parameter for Line Of Business, etc. (all fields are
> parameters therefore all fields have changing column/field names).
> What I have done for now is create a calculated field in the dataset:
> (Psuedo code): Iif(parameter=revenue,
> revenue.value,(iif(parameter=quantity,quantity.value,(iif(parameter=cost,
> ...etc.) ... then reference the calculated field in the report
> definition.
> (I've read on this forum that CASE statements are not allowed in
> expressions.)
> I don't like this solution because I don't like hardcoding this much
> into a report. And... there are a very large number of reports that
> require this same type of coding.
> I will post the mdx question on the OLAP forum. meanwhile, if you
> happen to think of a different/better way to skin this cat, let me
> know... you seem to have some creative solutions up your sleeve.
> gracias, joel b
>
> --i like MSAS a lot (i've worked with three other major OLAP vendors
> and the development environment in MSAS is tops). I am new to
> Reporting Services but so far it seems it could be enhanced to interact
> with MSAS better. I continue to run into problems that don't fit well
> into MSRS and they are all specific to sourcing from a cube.
>|||I created this report, based on data from the Foodmart 2000- cube.
Most of it can be edited from Notepad, if you want to add a dimension or
change what a column is showing. If you want to add more columns, then you
have to do it in VS.
Kaisa
<?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</Right>
</BorderStyle>
<TextAlign>Right</extAlign>
<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</FntWeight>
</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">
<rd:DataSourceID>124dab9d-708f-4c42-88b0-429857870478</rd:DataSourceID>
<DataSourceReference>Foodmart</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</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>
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:%23vOCcGpFGHA.2708@.TK2MSFTNGP11.phx.gbl...
> As Reporting Services isn't all that dynamic, I guess some hard coding has
> to be done for each report. But you still might be able to do some tricks.
> Even if the users only want to see one measure, you can still make the
> report query find numbers for all the measures they can choose from. And
> then do some visibility checks on the actual columns in your report. You
> probably shouldn't have too much of a difference in the query time, even
> if you get numbers for both Revenue AND quantity.
> And if you want a super generic report that can't be grouped too much, you
> can do something like this:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name'
> member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
> member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> select
> {[Measures].[Unit Sales],[Measures].[Store Cost],[Measures].[Store
> Sales],
> [Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3]} on
> columns,
> {filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
> from [Sales]
> The first calculated member will give you the store names in a column, the
> second will give you that member's level, the third the unique name in the
> form of [Store].[All Stores].[USA] etc. You can then group on Measure3,
> but you might want to do some visibility checks to not dump out all the
> rows a lot of times. Set all columns and rows to Visibility - Hidden => True, and make it visible if the data you want to show equals ...
> something.
> All calculated members will be called by the name you give it, contrary to
> sets. So you can change the query to something like this, and all your
> column names will controllable:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name'
> member [Measures].[MyMeasure2] as '[Store].currentmember.Level.Name'
> member [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
> member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
> member [Measures].[MyMeasure5] as '[Measures].[Store Sales]'
> select
> {[Measures].[MyMeasure],[Measures].[MyMeasure2],[Measures].[MyMeasure3],[Measures].[MyMeasure4],[Measures].[MyMeasure5],[Measures].[MyMeasure6]}
> on columns,
> {filter([Store].members,[Measures].[Unit Sales] > 100) } on rows
> from [Sales]
> Your dimension used on rows and the first three measures needs to be the
> same. And you need to filter on what ever measure you want to filter on,
> but you can use [MyMeasure4] instead of [Unit Sales]. But you don't use
> the row-based dimensions on your report rows, you use the measure. On the
> other hand, there will be some differences from report to report, or else
> you could create one huge generic report and just leave work for the day.
> ;)
> Kaisa M. Lindahl
>