dynamic reporting

Hi Experts...
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>

Dynamic ParallelPeriode

Hi,

with this MDX expression as a calculated member in my cube

100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])

I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.

Thanks

Hans

Hi Hans,

Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?

>>

select

{[Measures].[Sales Amount]} on 0,

Generate(Ascendants([Date].[Calendar].[Date].&[800]),

{[Date].[Calendar].CurrentMember,

ParallelPeriod([Date].[Calendar].[Calendar Year],

1, [Date].[Calendar].CurrentMember)}) on 1

from [Adventure Works]

-

Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20

>>

|||

Hi Deepak,

Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )

but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like

IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))

which work fine, but this doesn't work here. How can I do it here?

Thanks

Hans

|||

Hi Hans,

With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:

([Measure].[SalesGrowth], [Date].[Date].Members) =

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]

* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[
Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember
.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Lev
el.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.Uni
queName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns
, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (…) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Va
lue &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:

> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i nee
d:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].
1;Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

sql

Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira

|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

|||Hi,
is it possible to use a parameter as mentioned below?

select [Measures].[Internet Sales Amount] on 0,
@.ProductCategories on 1
from [Adventure Works]
And the MDX it would actually run against Analysis Services would be:
select [Measures].[Internet Sales Amount] on 0,
{[Product].[Category].&[1],[Product].[Category].&[2]} on 1
from [Adventure Works]

Dynamic MDX Query

Hi,
I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test
I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.
If I select Station in report parameter then following report format i need:
Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...
If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...
I tryed using following query but not sucessful:
WITH SET [Groupby] AS STRTOSet(@.Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]
let me know if you have any solution?
Dinesh Patelproblem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (â?¦) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:
> Hi,
> I want to build Dynamic MDX query in SQL Server 2005. Is it possible?
> ex. I have one SQL Server Report parameter which contain following value:
> 1. Station
> 2. Free Test
> I have three Dimension Station, Free Test, Overall Result and one measure
> Total Test.
> If I select Station in report parameter then following report format i need:
> Station | Pass | Fail | Total Test
> x1 60 40 100
> x2 ... ... ...
> If I select Free Test in parameter then following report format i need:
> Free Test | Pass | Fail | Total Test
> yes 60 40 100
> no ... ... ...
> I tryed using following query but not sucessful:
> WITH SET [Groupby] AS STRTOSet(@.Query1)
> SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
> NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel
>

Dynamic MDX Parameter Query Question

Hi:
Thanks first.Did anyone know how to send the query statement for dynamic
MDX Parameter query in sql server reporting service 2005.For example in
2000,we could send the statement like this:
= " select {[Measures].[aaa]} on axis(0),
{" + Parameters@.Parm_time.value + "} on axis(1)
from [Cube] "
,but it failes in 2005.Anyone know why it failes?
--
zhouhuituanI'm pretty sure you need to write the parameters like this:
Parameters!Parm_time.Value in 2005.
Also, make sure everything is on the same line (there will be page breaks,
but don't add them yourself.)
Does your syntax work without the parameters? Are you sure your parameter
value looks like the code you test your query with?
Also, are you using the graphic query builder at all? In my opinion, the
query syntax that appears when using the query builder is very different
from the MDX queries used with AS 2000, and takes a while to get used to.
The filtering in the query works differently than before, especially when
you mess around with the parameters.
What is the error message?
Kaisa M. Lindahl Lervik
"zhouhuituan" <zhouhuituan@.discussions.microsoft.com> wrote in message
news:D231360E-7B36-41B2-A476-15FE606435BB@.microsoft.com...
> Hi:
> Thanks first.Did anyone know how to send the query statement for dynamic
> MDX Parameter query in sql server reporting service 2005.For example in
> 2000,we could send the statement like this:
> = " select {[Measures].[aaa]} on axis(0),
> {" + Parameters@.Parm_time.value + "} on axis(1)
> from [Cube] "
> ,but it failes in 2005.Anyone know why it failes?
> --
> zhouhuituan

Dynamic MDX Deployement in Reporting Services

I have created a Reporting Services report that uses MDX to retrieve data
from a cube. The MDX statements must use a parameter so they are placed into
text strings. The report will run with the Dynamic MDX in Visual Studio but
when I deploy it to the Web Server it locks up.
I believe that the dynamic MDX is causing the problem. The reports that
connect to the Cube that do not use parameters run fine on the web.
Any advice would be appreciated.
Thanks.I found this to be very helpful with parameterized MD
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
"AndyT" wrote:
> I have created a Reporting Services report that uses MDX to retrieve data
> from a cube. The MDX statements must use a parameter so they are placed into
> text strings. The report will run with the Dynamic MDX in Visual Studio but
> when I deploy it to the Web Server it locks up.
> I believe that the dynamic MDX is causing the problem. The reports that
> connect to the Cube that do not use parameters run fine on the web.
> Any advice would be appreciated.
> Thanks.

dynamic mdx

Hello i'm new user,

i have a calculated member

the function LINREGSLOPE juste calculate the slope ( ie: a) of the equation Y= a *X+b

calculated member Name= Gain

LINREGSLOPE([TIME].[Month].&[2006-02-01T00:00:00]:[TIME].[Month].&[2006-06-01T00:00:00],[Mesures].[NumofCustomer],[TIME].[Month], [TIME].[Rank])

i write

the formula do the job veru well. What i want to do now is to

transform the expression to be dynamic with two parameter

I hope this will help.

I seen in this forum that this is possble. how to extract

this information. How can i concat the parametre!month.value with the

string ?

i see idea of solution here but i dont know how to exploit it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443431&SiteID=1

how can i do this please help

thanks

hi,

If I am not mistaken, I think you want to restrict the query based on static value instead of dynamic parameters. Right? If that is the case you can just include a where condition to your query like this:

where [Dimension].[Heirarchy].&[Member1]:[Dimension].[Heirarchy].&[Member2]

Note: Dimesion is your dimension name

Heirarchy is your heirachy name.

Member1 is the start date

Member 2 is the end date

I hope this will help.

Sincerely,

Amde

|||thanks amde,

no it's the opposite from static de dynamique.

if the user change the parametre the query will change too.i want to write my expresion with a parameter.

i want somethink like this:

linregslope([Time].[month].&"param1":[Time].[month].&"param2",[measures].[numberCustomer],[Time].[Month].[MonthNumber]).

if you have others questions feel free to ask me.

visite the link in my first post.

I hope this will help.

any idea is welcome

casagrandi

|||

ok thanks

i found the solution

this isi the code:

WITH

set [RangeMonth] as LastPeriods(STRTOMEMBER (@.FromTIMEMonth,CONSTRAINED),STRTOMEMBER (@.ToTIMEMonth,CONSTRAINED))

MEMBER [Measures].[GAIN PER MONTH] AS 'LINREGSLOPE([RangeMonth],[Measures].[mymeasure],Rank([TIME].[Month], [TIME].[Month].Members))'

,format="percent"

SELECT NON EMPTY {[Measures].[GAIN PER MONTH],[Measures] ..........

sql

Dynamic columns using MDX and parameters

I am linking to MS Analysis Services datasrce. When reporting I would like
to have the column to be displayed be determined by a parameter.
ie.
Parameter pFaccode = 'ABC'
report layout using a table:
Facility 'ABC' Case Count
Male 100
Female 200
Parameter pFaccode = 'XYZ'
report layout using a table:
Facility 'XYZ' Case Count
Male 300
Female 350
In the data tab of MS Visual Studio .NET report designer, I first issue the
following query so the data fields get created:
"SELECT {[ABC]} ON COLUMNS, {[Sex].Members} ON ROWS FROM [cases_test]"
This creates "sex_sexdesc" and "fac_ABC" dataset fields.
I then add the parameter logic by modifying the query as follows:
="SELECT {[" & Parameters!pFaccode.Value & "]} ON COLUMNS, {[Sex].Members}
ON ROWS FROM [cases_test]"
The report works when the parameter passed is "ABC". It will not work when
I pass "XYZ". When passing "XYZ" I get the following error:
"The 'fac_ABC' field is missing from the returned result set from the data
source."
I have successfully used parameters in the ROWS and WHERE sections of the
MDX queries. Is it possible to do it in the COLUMNS section as I described
above? If so, how?
Thank You,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________Hi Dan,
From your descriptions, I understood that you would like to use
parameterized MDX queries in columns. Have I understood you? Correct me if
I was wrong.
I have noticed that you are able to make it parameterized in ROWS and
WHERE, it should be no difference wherever you put the parameters.
I download the samples below and add a parameter in MDX_Expression.rdl like
below:
="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
WHERE ([" + Parameters!pCity.Value + "])"
It works fine:-)
Reporting Services 2000 - OLAP Sample Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-
9c83-c6801f0576ff&DisplayLang=en
For your issues, I would like to suggest you REFRESH the dataset first to
see whether it works. Secondly, Set a default value for your parameter and
then try again.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||You have understood.
I am still unable to get this to work. I tried your sample below and I
experienced the same behavior as I did with my project.
The first thing I did was refresh the following MDX query:
SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
Name].members on Rows from Sales
This produced "Measures_Unit_Sales" data field. This is the field I display
in the table.
I then modified the query to incorporate the parameter exactly as you
stated:
="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
WHERE ([" + Parameters!pCity.Value + "])"
When I ran the report it worked when the parameter was "Unit Sales", but it
does not work for parameter "Store Cost". I get the following error:
"The data set 'FoodMart_2000' contains a definition for the field
'Measures_Unit_Sales'. This field is missing from the returned result set
from the data source."
I must be missing a step. RS seems to want the original data field
returned.
Thanks for your help,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
Phone: 716-636-0100 ext-13
Fax: 716-636-1458
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:cprxzdJzEHA.3152@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> From your descriptions, I understood that you would like to use
> parameterized MDX queries in columns. Have I understood you? Correct me if
> I was wrong.
> I have noticed that you are able to make it parameterized in ROWS and
> WHERE, it should be no difference wherever you put the parameters.
> I download the samples below and add a parameter in MDX_Expression.rdl
like
> below:
> ="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
> Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
> WHERE ([" + Parameters!pCity.Value + "])"
> It works fine:-)
> Reporting Services 2000 - OLAP Sample Reports
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-
> 9c83-c6801f0576ff&DisplayLang=en
> For your issues, I would like to suggest you REFRESH the dataset first to
> see whether it works. Secondly, Set a default value for your parameter and
> then try again.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||Hi Dan,
It seems really odd, you might not have bound the created field to the data
base field correctly. Unfortunately, I was not able to reproduce your issue
in house. Would you please try the following steps?
1. Set default value (such as %) to the filed
Parameters!Report_Parameter_1.Value
2. Does it work after refresh the Dataset
3. Delete and then Recreate the Dataset
If the above steps all fail, plase paste your rdl files here or send it
directly to me, v-mingqc@.online.microsoft.com (remove online, it's only for
spam)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
I tried your suggestions and I cannot get it to work. I appended the .rdl
code below. It seems that the problem is that when I originally create the
dataset using,
"SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
Name].members on Rows from Sales",
it creates the "Measures_Unit_Sales" data field. I place this field in my
table object. When I run the report using the parameter it expects the
"Measures_Unit_Sales" data field to be returned.
Thanks for your Help,
Dan
<?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>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>Whitev/Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33inv/Height>
<CanGrow>true</CanGrow>
<Value>cascading_report</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solidv/Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
v/Style>
vZIndex>3</ZIndex>
vrd:DefaultName>textbox2</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>Promotion Media Media Type</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
v/Style>
vZIndex>2</ZIndex>
vrd:DefaultName>textbox3</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>Measures Unit Sales</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Promotion_Media_Media_Type">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
v/Style>
vZIndex>1</ZIndex>
vrd:DefaultName>Promotion_Media_Media_Type</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>=Fields!Promotion_Media_Media_Type.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_Unit_Sales">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
v/Style>
vrd:DefaultName>Measures_Unit_Sales</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>=Fields!Measures_Unit_Sales.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>FoodMart_2000</DataSetName>
<Top>0.33in</Top>
<Width>2in</Width>
<TableColumns>
<TableColumn>
<Width>1inv/Width>
</TableColumn>
<TableColumn>
<Width>1inv/Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="FoodMartv2000">
<rd:DataSourceID>11ccb8b2-d400-4ba6-ac9e-2a7cc7bba86d</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Promotion_Media_Media_Type">
<DataField>[Promotion Media].[Media Type].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Unit_Sales">
<DataField>[Measures].[Unit Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>="SELECT {[Measures].[" + Parameters!pMeasure.Value + "]} on
columns,ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
Sales],DESC) on rows FROM SALES"</CommandText>
</Query>
</DataSet>
<DataSet Name="dsCountry">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_CountryUniqueName">
<DataField>[Measures].[CountryUniqueName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_CountryDisplayName">
<DataField>[Measures].[CountryDisplayName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>with member [Measures].[CountryUniqueName] as
'[Customers].currentmember.UniqueName'
member [Measures].[CountryDisplayName] as '[Customers].currentmember.Name'
SELECT {[Measures].[CountryUniqueName],[Measures].[CountryDisplayName]} on
Columns,
{[Customers].[Country].membersv} on rows
from
Sales</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="dsState">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Customers_State_Province">
<DataField>[Customers].[State Province].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_StateUniqueName">
<DataField>[Measures].[StateUniqueName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_StateDisplayName">
<DataField>[Measures].[StateDisplayName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>= "with member [Measures].[StateUniqueName] as
'[Customers].currentmember.uniqueName'
member [Measures].[StateDisplayName] as '[Customers].currentmember.name'
SELECT {[Measures].[StateUniqueName], [Measures].[StateDisplayName]} on
Columns,
{DESCENDANTS({" & Parameters!pCountry.Value & " },
[Customers].[State Province])}von rows from sales"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>63006e5b-c9b5-4104-adbd-20dcf956075c</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="pCountry">
<DataType>Stringv/DataType>
<AllowBlank>truev/AllowBlank>
<Prompt>Country:v/Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>dsCountry</DataSetName>
<ValueField>Measures_CountryUniqueName</ValueField>
<LabelField>Measures_CountryDisplayName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="pState">
<DataType>Stringv/DataType>
<AllowBlank>truev/AllowBlank>
<Prompt>State:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>dsState</DataSetName>
<ValueField>Measures_StateUniqueName</ValueField>
<LabelField>Measures_StateDisplayName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="pMeasure">
<DataType>Stringv/DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>Unit Sales</Value>
</Values>
</DefaultValue>
<AllowBlank>truev/AllowBlank>
<Prompt>Measure</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Unit Sales</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Cost</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
Phone: 716-636-0100 ext-13
Fax: 716-636-1458
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:wcEuX9VzEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> It seems really odd, you might not have bound the created field to the
data
> base field correctly. Unfortunately, I was not able to reproduce your
issue
> in house. Would you please try the following steps?
> 1. Set default value (such as %) to the filed
> Parameters!Report_Parameter_1.Value
> 2. Does it work after refresh the Dataset
> 3. Delete and then Recreate the Dataset
> If the above steps all fail, plase paste your rdl files here or send it
> directly to me, v-mingqc@.online.microsoft.com (remove online, it's only
for
> spam)
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Michael,
Were you able to find the error?
Let me know asap, I am depending it.
Thanks,
Dan
"Dan" <dan_zaccarine@.hanfordbay.com> wrote in message
news:euHiW8YzEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Michael,
> I tried your suggestions and I cannot get it to work. I appended the
.rdl
> code below. It seems that the problem is that when I originally create
the
> dataset using,
> "SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
> Name].members on Rows from Sales",
> it creates the "Measures_Unit_Sales" data field. I place this field in my
> table object. When I run the report using the parameter it expects the
> "Measures_Unit_Sales" data field to be returned.
> Thanks for your Help,
> Dan
> <?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>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontFamily>Times New Roman</FontFamily>
> <BackgroundColor>Brown</BackgroundColor>
> <BorderWidth>
> <Bottom>3pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>Black</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <FontSize>18pt</FontSize>
> <TextAlign>Center</TextAlign>
> <Color>Whitev/Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Height>0.33inv/Height>
> <CanGrow>true</CanGrow>
> <Value>cascading_report</Value>
> </Textbox>
> <Table Name="table1">
> <Style>
> <BackgroundColor>White</BackgroundColor>
> <BorderWidth>
> <Top>3pt</Top>
> </BorderWidth>
> <BorderStyle>
> <Top>Solidv/Top>
> </BorderStyle>
> </Style>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.21in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>2pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>DarkRed</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>900</FontWeight>
> v/Style>
> vZIndex>3</ZIndex>
> vrd:DefaultName>textbox2</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>Promotion Media Media Type</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>2pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>DarkRed</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>900</FontWeight>
> v/Style>
> vZIndex>2</ZIndex>
> vrd:DefaultName>textbox3</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>Measures Unit Sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.21in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="Promotion_Media_Media_Type">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> v/Style>
> vZIndex>1</ZIndex>
> vrd:DefaultName>Promotion_Media_Media_Type</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>=Fields!Promotion_Media_Media_Type.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Measures_Unit_Sales">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> v/Style>
> vrd:DefaultName>Measures_Unit_Sales</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>=Fields!Measures_Unit_Sales.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>FoodMart_2000</DataSetName>
> <Top>0.33in</Top>
> <Width>2in</Width>
> <TableColumns>
> <TableColumn>
> <Width>1inv/Width>
> </TableColumn>
> <TableColumn>
> <Width>1inv/Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>0.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="FoodMartv2000">
> <rd:DataSourceID>11ccb8b2-d400-4ba6-ac9e-2a7cc7bba86d</rd:DataSourceID>
> <DataSourceReference>FoodMart 2000</DataSourceReference>
> </DataSource>
> </DataSources>
> <Width>5in</Width>
> <DataSets>
> <DataSet Name="FoodMart_2000">
> <Fields>
> <Field Name="Promotion_Media_Media_Type">
> <DataField>[Promotion Media].[Media Type].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_Unit_Sales">
> <DataField>[Measures].[Unit Sales]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>="SELECT {[Measures].[" + Parameters!pMeasure.Value + "]} on
> columns,ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
> Sales],DESC) on rows FROM SALES"</CommandText>
> </Query>
> </DataSet>
> <DataSet Name="dsCountry">
> <Fields>
> <Field Name="Customers_Country">
> <DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_CountryUniqueName">
> <DataField>[Measures].[CountryUniqueName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_CountryDisplayName">
> <DataField>[Measures].[CountryDisplayName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>with member [Measures].[CountryUniqueName] as
> '[Customers].currentmember.UniqueName'
> member [Measures].[CountryDisplayName] as '[Customers].currentmember.Name'
> SELECT {[Measures].[CountryUniqueName],[Measures].[CountryDisplayName]} on
> Columns,
> {[Customers].[Country].membersv} on rows
> from
> Sales</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="dsState">
> <Fields>
> <Field Name="Customers_Country">
> <DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Customers_State_Province">
> <DataField>[Customers].[State Province].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_StateUniqueName">
> <DataField>[Measures].[StateUniqueName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_StateDisplayName">
> <DataField>[Measures].[StateDisplayName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>= "with member [Measures].[StateUniqueName] as
> '[Customers].currentmember.uniqueName'
> member [Measures].[StateDisplayName] as '[Customers].currentmember.name'
> SELECT {[Measures].[StateUniqueName], [Measures].[StateDisplayName]} on
> Columns,
> {DESCENDANTS({" & Parameters!pCountry.Value & " },
> [Customers].[State Province])}von rows from sales"</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>63006e5b-c9b5-4104-adbd-20dcf956075c</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="pCountry">
> <DataType>Stringv/DataType>
> <AllowBlank>truev/AllowBlank>
> <Prompt>Country:v/Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>dsCountry</DataSetName>
> <ValueField>Measures_CountryUniqueName</ValueField>
> <LabelField>Measures_CountryDisplayName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="pState">
> <DataType>Stringv/DataType>
> <AllowBlank>truev/AllowBlank>
> <Prompt>State:</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>dsState</DataSetName>
> <ValueField>Measures_StateUniqueName</ValueField>
> <LabelField>Measures_StateDisplayName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="pMeasure">
> <DataType>Stringv/DataType>
> <Nullable>true</Nullable>
> <DefaultValue>
> <Values>
> <Value>Unit Sales</Value>
> </Values>
> </DefaultValue>
> <AllowBlank>truev/AllowBlank>
> <Prompt>Measure</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>Unit Sales</Value>
> </ParameterValue>
> <ParameterValue>
> <Value>Store Cost</Value>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>
> --
> ___________________________
> Daniel J. Zaccarine
> Hanford Bay Associates, Ltd.
> dan_zaccarine@.hanfordbay.com
> http://www.hanfordbay.com
> ___________________________
> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
> news:wcEuX9VzEHA.3440@.cpmsftngxa10.phx.gbl...
> > Hi Dan,
> >
> > It seems really odd, you might not have bound the created field to the
> data
> > base field correctly. Unfortunately, I was not able to reproduce your
> issue
> > in house. Would you please try the following steps?
> >
> > 1. Set default value (such as %) to the filed
> > Parameters!Report_Parameter_1.Value
> > 2. Does it work after refresh the Dataset
> > 3. Delete and then Recreate the Dataset
> >
> > If the above steps all fail, plase paste your rdl files here or send it
> > directly to me, v-mingqc@.online.microsoft.com (remove online, it's only
> for
> > spam)
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
>|||Hi Dan,
The columns returned must be fixed in terms of naming so you can refer to
it in the report. The MDX syntax for this is
"With [measures].[Measures_Unit_Sales] as [Measures].[" +
Parameters!Report_Parameter_1.Value + "]
However, it sometimes doesn't make sense, I am looking further into this
issue, it might be a limitation. I will make you updated as soon as
possible.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
I was able to get the dynamic column logic to work.
FYI, for my project, I first issued the following query:
with member [fac].[facility] as '[ABC]' member [fac].[region] as
'Ancestor([ABC],1)' member [fac].[nation] as 'Ancestor([ABC],2)'
SELECT {[fac].[facility],[fac].[Region],[fac].[nation]} ON COLUMNS,
CrossJoin({[cmggrpcd].Members}, {[Sex].Members}) ON ROWS FROM [cases_test]
WHERE ([changedate].[All changedate].[2004])
This created cmggrpcd_Cmggrpcd, Sex_Sexdesc, fac_facility, fac_region and
fac_nation dataset fields. I displayed these on the report.
Then, I added the parameter logic as follows:
="with member [fac].[facility] as '[" & Parameters!pFaccode.Value & "]'
member [fac].[region] as 'Ancestor([" & Parameters!pFaccode.Value & "],1)'
member [fac].[nation] as 'Ancestor([" & Parameters!pFaccode.Value & "],2)'
SELECT {[fac].[facility],[fac].[Region],[fac].[nation]} ON COLUMNS,
CrossJoin({[cmggrpcd].Members}, {[Sex].Members}) ON ROWS FROM [cases_test]
WHERE (" & Parameters!pTime.Value & ")"
Now the columns are based on the pFaccode prompt as desired.
Thanks again for all your help.
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:My3d2Qd0EHA.2544@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> The columns returned must be fixed in terms of naming so you can refer to
> it in the report. The MDX syntax for this is
> "With [measures].[Measures_Unit_Sales] as [Measures].[" +
> Parameters!Report_Parameter_1.Value + "]
> However, it sometimes doesn't make sense, I am looking further into this
> issue, it might be a limitation. I will make you updated as soon as
> possible.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Dan,
You are welcome!
It's great to hear that you have resolved it and thanks for sharing this
with us!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!