sql dwh problem: linking

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!

Wednesday, March 21, 2012

Dynamic Linking - Crystal XI - Image doesnt change

Friday, February 24, 2012

Dynamic columns using MDX and parameters

Wednesday, February 15, 2012

Dynamic age of customer based on transactions being viewed

sql dwh problem

Blog Archive

About Me