Dynamic Message Box In Report

Hello Sir,

I m working in Microsoft SQL Server Analysis Services Designer
Version 9.00.1399.00 for creating reports .

Now, I m facing a problem to view a run time dialog box at time of my report.
Means ,
In my report procedure , i have fired 2 delete queries after firing of these queries i wanna show a message box with Message "Some Records are Deleted". and one Ok Button !!

and after showing this dialog box i wanna view my report output !!

The procedure of my report is :
--
create procedure MyTemp
as

-- Query1
delete from myTempTable1 where Id = 5

-- Query2
delete from myTempTable2 where Id = 10

-- After exectuing above two queries Query1 and Query2.
-- here i want to view amessage dialog box with message "Some Records are Deleted"

select Id, name from myTempTable1,myTempTable2
--

So, how can i do this !!
I will change in my proceudre or report !!
Please suggest me about solution of this problem .

If u dont have any solution regaring this then please suggest me where i will get
the solution of this problem .

There is no messagebox in reporting services. Yout will have to give the results back as a dataset and render it in the report if your want to display it.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dynamic Measure?

Hi,
I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate.
I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?

Should I create a calculated cell? (I tried that, but mdx does not work)
I used : Count({[Measures].[End Key]})< 366
Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.

Mdxyou can create a count measure. I found out by myself

Dynamic display of SQL in Excel?

I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.See if under Data menu...Import External Data...New Web Query

Not sure of the particulars but it would get you started.

Originally posted by shadowplayer
I have WebTrends web site traffic analysis running nightly on a SQL DB server. Every month, I must hit over 50 report URLs (pages use frames) to manually caputure 3 numbers into an Excel spreadsheet.

Can anyone provide any pointers how I might go about building a spreadsheet that could caputure/display this data automatically?

Thanks.|||Thanks for your reply. That's exactly what I ended up doing. Found it by accident when I noticed the "little yellow box" that appeared when I cut & pasted data from the webpage into my spreadsheet.

I just saved the qry file and opened it in notepad to config the values.

This will save me HOURS of work.

VERY cool.

Thanks again for your reply!

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!

Dynamic changes of AS Connection Manager failed

Hi,

in my actual szenario I have to change the Server (Connection String) of Analysis Services Connection Manager from my testserver SERVER1 to my production server SERVER2. Nearly everything is easy...I set a variable to the correct servername and I used a expression to change the connection string of the connection manager during run-time. I used this technic serveral time with ole db and it works fine.

Debugging of the project shows that the variable is set correctly. The delay validation flag is set to true. When I run the package the connection establish a connection to the server which I use due designtime.

Does anybody have an idea how I can fix it?

Regards
Klaus Hoeltgen

Hi,

I am having the same issue as Klaus - is there no suggestion to solve that?

Or is it "just by design"?

cheers,
Markus