Hey,
I've got a few columns that called a custom assembly and fetch results
more or less correctly, but at the end of the column I'd like to sum up
that column.
Does it have a way of doing Sum(col3) or something, so I dont have to
implement any more custom assembly goodness?
Also, can I do =(col1+col2)?
Thanks in advance
Weston WeemsI suggest you create a calculated field. Do a right mouse click on your
field list, add an expression that calls your code. Now at this point it
will look like any other field and you can just sum it.
You can definitely do a sum(field1.value + field2.value) but that will be
using the field list, it is not summing your text boxes. You could need to
do this sum(Code.Mycode(field1.value) + Code.MyCode(field2.value)). Gets
confusing, which is why adding your own field to the field list is a good
idea in this instance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Weston Weems" <wweems@.gmail.nospammers.com> wrote in message
news:eimVKly3FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hey,
> I've got a few columns that called a custom assembly and fetch results
> more or less correctly, but at the end of the column I'd like to sum up
> that column.
> Does it have a way of doing Sum(col3) or something, so I dont have to
> implement any more custom assembly goodness?
> Also, can I do =(col1+col2)?
> Thanks in advance
> Weston Weemssql
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
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
>
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
>
Saturday, February 25, 2012
rs drops first column in data set
I started having this problem some time ago when trying to build a report using a stored procedure. Existing reports work fine, it is only new reports that result in this error:
The data set ‘WeeklyManagement’ contains a definition for the field ‘BrewNumber’. This field is missing from the returned result set from the data source.
I get this error when I try to preview the report. The missing field is always the first column in the dataset. I have tried everything I can think of, including repeating the column, rewriting the sproc, rewriting the report, everything. I cannot find any reason why this should be happening. Can anyone help? BTW, when I create the dataset and run the sproc, all fields are returned fine. When I examine the xml, all the fields are there, correctly defined.
Help, please! TIA. D. Lewis
Can you publish the sproc? Print statements did this to us in the past.|||Hmmm. Very interesting. Following is the sproc, which I altered to comment out the 'Print' statements (yes, I did have some in there for debugging purposes). I also as an experiment repeated the first column to see if it would work, and RS began dropping that new column; error message as below:
Build complete -- 0 errors, 0 warnings
The data set ‘WeeklyManagement’ contains a definition for the field ‘B’. This field is missing from the returned result set from the data source.
Preview complete -- 0 errors, 1 warnings
The sproc is a bit messy, and uses a temp table, but for completeness' sake here it is:
use snb01
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Alter PROCEDURE spBrewhouseOverview
AS
Declare @.BeginDate smalldatetime
Declare @.EndDate smalldatetime
Set @.BeginDate = DATEADD(dd,-45,Getdate())--'9/1/05'
set @.EndDate = Getdate()--'9/15/05'
--Print convert(varchar,@.begindate)
--Print convert(varchar,@.enddate)
--spBrewhouseOverview '8/2/05','8/3/05'
--5/20/03
--Have to convert the dates to char then to date again
--to strip out the time portion that crystall passes
--also added one day to the end time to
--allow for the fact that the end day would be midnight
--at the beginning of the ending day, which would otherwise
--effectively eliminate all brews on that day from showing up.
BEGIN
CREATE TABLE [#tblOverview] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProBrewID] [int] NULL,
[BrewNumber] [int] NULL ,
[Kettle] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTimeInKettle] [smalldatetime] NULL ,
[StartTimeBoil] [smalldatetime] NULL ,
[EndTimeBoil] [smalldatetime] NULL ,
[Hop1Time] [smalldatetime] NULL ,
[Hop2Time] [smalldatetime] NULL ,
[MossTime] [smalldatetime] NULL ,
[Hop3Time] [smalldatetime] NULL ,
[Hop4Time] [smalldatetime] NULL ,
/*[StartTimeMash] [smalldatetime] NULL ,
[SaccReqTime] [smalldatetime] NULL ,
[SaccTime] [smalldatetime] NULL ,
[MashOutTime] [smalldatetime] NULL ,
[StartTimeInLauter] [smalldatetime] NULL ,
[StartTimeVorlauf] [smalldatetime] NULL ,
[StartTimeLauter] [smalldatetime] NULL ,
[EndTimeLauter] [smalldatetime] NULL ,
[GravityVorlauf] [int] NULL ,
[GravityFirstRunnings] [int] NULL ,
[GravityLastRunnings] [int] NULL ,
[GravityLauter] [int] NULL ,
[HazeFirstRunnings] [int] NULL ,
[HazeLauterVorlauf] [int] NULL ,
[HazeLauterLauter] [int] NULL ,*/
[StartTimeRest] [smalldatetime] NULL ,
[StartTimeWhirlWaterVor] [smalldatetime] NULL,
/*[MashHour] [int] NULL,
[LauterHour] [int] NULL,
[KettleHour] [int] NULL,
[WhirlpoolHour] [int] NULL,
[BrewerMash] [Varchar] (20) NULL,
[BrewerLauter] [Varchar] (20) NULL,*/
[BrewerKettle] [Varchar] (20) NULL,
[BrewerWhirlpool] [Varchar] (20) NULL,
CONSTRAINT [PK_tblOverview] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
SET NOCOUNT ON
INSERT #tblOverview
(Probrewid
, Brewnumber
, Kettle
, StarttimeInKettle
, StartTimeBoil
, EndTimeBoil
, Hop1Time
, Hop2Time
, MossTime
, Hop3Time
, Hop4Time/*
, StartTimeMash
, SaccReqTime
, SaccTime
, MashOutTime
, StartTimeInLauter
, StartTimeVorlauf
, StartTimeLauter
, EndTimeLauter
, GravityVorlauf
, GravityFirstRunnings
, GravityLastRunnings
, GravityLauter
, HazeFirstRunnings
, HazeLauterVorlauf
, HazeLauterLauter*/
, StartTimeRest
, StartTimeWhirlWaterVor)
(
Select a.probrewid
, a.Brewnumber
, a.Kettle
, a.StarttimeInKettle
, a.StartTimeBoil
, a.EndTimeBoil
, a.Hop1Time
, a.Hop2Time
, a.MossTime
, a.Hop3Time
, a.Hop4Time
/*, b.StartTimeMash
, b.SaccReqTime
, b.SaccTime
, b.MashOutTime
, c.StartTimeInLauter
, c.StartTimeVorlauf
, c.StartTimeLauter
, c.EndTimeLauter
, c.GravityVorlauf
, c.GravityFirstRunnings
, c.GravityLastRunnings
, c.GravityLauter
, c.HazeFirstRunnings
, c.HazeLauterVorlauf
, c.HazeLauterLauter*/
, d.StartTimeRest
, d.StartTimeWhirlWaterVor
FROM Proleit.dbo.tblRptKettleAll a
INNER JOIN Proleit.dbo.tblRptMashingAll b on a.Probrewid=b.probrewid
INNER JOIN Proleit.dbo.tblRptLauterAll c on a.Probrewid=c.Probrewid
INNER JOIN Proleit.dbo.tblRptWhirlpoolAll d on a.Probrewid=d.probrewid
WHERE a.StartTimeBoil>=CONVERT(datetime,(CONVERT(varchar,@.BeginDate,112)))
AND a.StartTimeBoil<= DATEADD(day,1,CONVERT(datetime,(CONVERT(varchar,@.EndDate,112))))
)
--Comment out for troubleshooting
--declare a cursor for the table, loop through
--and update each brewer
DECLARE CurC CURSOR FOR SELECT Probrewid,MossTime,StartTimeRest
FROM #tblOverview
DECLARE @.Probrewid int
DECLARE @.TimeMoss datetime
DECLARE @.TimeWhirl datetime
OPEN CurC
FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
BEGIN
WHILE @.@.FETCH_STATUS=0
BEGIN
--Print convert(varchar,@.probrewid) + ' is probrewid'
--Print convert(varchar,@.TimeMoss) + ' is @.TimeMoss'
--Exec spBrewhouseOverviewSacc @.probrewid,@.timeSacc
--Exec spBrewhouseOverviewVorlauf @.probrewid,@.timeVorlauf
Exec Proleit.dbo.spBrewhouseOverviewKettle @.probrewid,@.timeMoss
Exec Proleit.dbo.spBrewhouseOverviewWhirlpool @.probrewid,@.timeWhirl
FETCH NEXT FROM CurC INTO @.probrewid,@.TimeMoss,@.TimeWhirl
END
END
CLOSE CurC
DEALLOCATE CurC
SET NOCOUNT ON
UPDATE #tblOverview
SET BrewerKettle='None Recorded'
WHERE BrewerKettle is null
UPDATE #tblOverview
SET BrewerWhirlpool='None Recorded'
WHERE BrewerWhirlpool is null
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,MossTime,Hop3Time) as Minutes
, BrewerKettle as Brewer
, 'Hop3Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,MossTime,Hop3Time)<10
OR DATEDIFF(n,MossTime,Hop3Time)>13
UNION ALL
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,Hop3Time,Hop4Time) as Minutes
, BrewerKettle as Brewer
, 'Hop4Time' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,Hop3Time,Hop4Time)<10
OR DATEDIFF(n,Hop3Time,Hop4Time) > 13
UNION ALL
SELECT
a.BrewNumber as B
, a.BrewNumber
, a.Kettle
, Convert(datetime,Convert(varchar,a.StartTimeInKettle,110)) as BrewDate
, DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor) as Minutes
, BrewerWhirlpool
, 'WhirlpoolTime' as Type
FROM #tblOverview a
WHERE DATEDIFF(n,StartTimeRest,StartTimeWhirlWaterVor)>21
Drop Table #tblOverview
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|||If you're not working with a whole lot of data, an @.Table variable instead of a temp table may be more efficient and you won't need the drop table statement at the end.
Try adding to your UNION ALL statements with a select statement at the top that sets a default value.... eg. SELECT 0 as B, 0 as BrewNumber, 0 as Kettle, 0 as BrewDate, etc.... to see if it is a problem with the naming of the fields and the first result set not returning a value.
Maybe dump that above dummy SELECT statement in various places within the stored procedure, so that you can find out where it is breaking. (not returning the right result set)
It could have something to do with the 2 execs you have going there... It would be interesting to see the SQL messages pane.|||I will set to work on those suggestions, thanks.
Just to clarify, the result set returned in Query Analyzer is fine, with no errors and no messages. When I refresh the dataset from within visual studio while designing the report, I also get the entire set with no issues. It is only when I try to preview the report that the thing bonks -- i.e., the first column is dropped and VS tells me that it is not defined.
Also, I went back and examined the two 'exec ....' statements, and they are simple update statements, with no 'Print....' or anything else. Very innocuous, actually. I placed them in separate sprocs because they are reused elsewhere and are long.
RS Charts - possible to add more types?
I'm creating a chart showing different types of work hours registered per
person during a given period. I'm using a "Simple Column" chart type. It
works nicely. But I'm quite sure that our customer wants a different chart
type, that can be found in Excel 2003. It's a 3D Column chart, that shows
the different values related to each other. Not stacked, but as columns
behind each other. There is no such chart in Reporting Services SP1.
Is it possible to add more chart types to RS SP1? How? Or is it possible to
hand code the RDL file to create a view that is like what the customer
wants?
The person in charge is very probably going to ask me about the layout of
the chart, so I just want to check...
Kaisa M. LindahlRS 2000 does not provide any additional chart types. However, it allows you
to use so-called static categories when you hand-code (!) the RDL file. Keep
in mind - a chart works similar to a matrix - static categories would be
static column groupings in a matrix. Chart series groupings are comparable
to row groupings in a matrix. It may be useful to first play with the data
in a matrix till you have the right structure, and then generate the chart
RDL from there. You may be interested in carefully reading this related
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=e384f9b6-d6d4-468d-b7c5-2ec0b680af14&sloc=en-us
In your case it sounds like you are looking for the ability to use static
categories in the chart. You will find a trivial example at the bottom of
this posting. Note: the report designer layout mode does not support static
categories - if you modify the layout it will reserialize the RDL and remove
static categories from the chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
All code samples are provided "AS IS" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:egVx4QU$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> I'm creating a chart showing different types of work hours registered per
> person during a given period. I'm using a "Simple Column" chart type. It
> works nicely. But I'm quite sure that our customer wants a different chart
> type, that can be found in Excel 2003. It's a 3D Column chart, that shows
> the different values related to each other. Not stacked, but as columns
> behind each other. There is no such chart in Reporting Services SP1.
> Is it possible to add more chart types to RS SP1? How? Or is it possible
to
> hand code the RDL file to create a view that is like what the customer
> wants?
> The person in charge is very probably going to ask me about the layout of
> the chart, so I just want to check...
> Kaisa M. Lindahl
===================================================<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2004.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2005.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Column</Type>
<Top>0.125in</Top>
<Title />
<Width>3in</Width>
<Height>3in</Height>
<CategoryGroupings>
<CategoryGrouping>
<StaticCategories>
<StaticMember>
<Label>FY2004</Label>
</StaticMember>
<StaticMember>
<Label>FY2005</Label>
</StaticMember>
</StaticCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!Series.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.5in</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>47e7de6a-2997-40c7-aa4c-e4eaacc48fe0</rd:DataSourceID>
<DataSourceReference>Northwind</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="FY2004">
<DataField>FY2004</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="FY2005">
<DataField>FY2005</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Series">
<DataField>Series</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select 191 as FY2004, 195 as FY2005, 'A' as Series
union all
select 188 as FY2004, 183 as FY2005, 'B' as Series</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>0a301010-0cf6-4e19-b90b-73a955e4965b</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
person during a given period. I'm using a "Simple Column" chart type. It
works nicely. But I'm quite sure that our customer wants a different chart
type, that can be found in Excel 2003. It's a 3D Column chart, that shows
the different values related to each other. Not stacked, but as columns
behind each other. There is no such chart in Reporting Services SP1.
Is it possible to add more chart types to RS SP1? How? Or is it possible to
hand code the RDL file to create a view that is like what the customer
wants?
The person in charge is very probably going to ask me about the layout of
the chart, so I just want to check...
Kaisa M. LindahlRS 2000 does not provide any additional chart types. However, it allows you
to use so-called static categories when you hand-code (!) the RDL file. Keep
in mind - a chart works similar to a matrix - static categories would be
static column groupings in a matrix. Chart series groupings are comparable
to row groupings in a matrix. It may be useful to first play with the data
in a matrix till you have the right structure, and then generate the chart
RDL from there. You may be interested in carefully reading this related
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=e384f9b6-d6d4-468d-b7c5-2ec0b680af14&sloc=en-us
In your case it sounds like you are looking for the ability to use static
categories in the chart. You will find a trivial example at the bottom of
this posting. Note: the report designer layout mode does not support static
categories - if you modify the layout it will reserialize the RDL and remove
static categories from the chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
All code samples are provided "AS IS" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:egVx4QU$EHA.2112@.TK2MSFTNGP14.phx.gbl...
> I'm creating a chart showing different types of work hours registered per
> person during a given period. I'm using a "Simple Column" chart type. It
> works nicely. But I'm quite sure that our customer wants a different chart
> type, that can be found in Excel 2003. It's a 3D Column chart, that shows
> the different values related to each other. Not stacked, but as columns
> behind each other. There is no such chart in Reporting Services SP1.
> Is it possible to add more chart types to RS SP1? How? Or is it possible
to
> hand code the RDL file to create a view that is like what the customer
> wants?
> The person in charge is very probably going to ask me about the layout of
> the chart, so I just want to check...
> Kaisa M. Lindahl
===================================================<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Excel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2004.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!FY2005.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>DataSet1</DataSetName>
<PointWidth>0</PointWidth>
<Type>Column</Type>
<Top>0.125in</Top>
<Title />
<Width>3in</Width>
<Height>3in</Height>
<CategoryGroupings>
<CategoryGrouping>
<StaticCategories>
<StaticMember>
<Label>FY2004</Label>
</StaticMember>
<StaticMember>
<Label>FY2005</Label>
</StaticMember>
</StaticCategories>
</CategoryGrouping>
</CategoryGroupings>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!Series.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<Left>0.5in</Left>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>47e7de6a-2997-40c7-aa4c-e4eaacc48fe0</rd:DataSourceID>
<DataSourceReference>Northwind</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="FY2004">
<DataField>FY2004</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="FY2005">
<DataField>FY2005</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Series">
<DataField>Series</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select 191 as FY2004, 195 as FY2005, 'A' as Series
union all
select 188 as FY2004, 183 as FY2005, 'B' as Series</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>0a301010-0cf6-4e19-b90b-73a955e4965b</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Subscribe to:
Posts (Atom)