Showing posts with label displayed. Show all posts
Showing posts with label displayed. Show all posts

Monday, March 26, 2012

dynamic query

Hello friends,

I want to create a dynamic query based on input of the parameter.

If the user passes nothing then all fields should be displayed else use query based on parameter.

I had view sample of MSDN ,but I got error [BC30203].

Is there another way to it ?Please help.

I use:

= iif(Parameters!SQLQuery.Value<>"",Parameters!SQLQuery.Value,"SELECT somecolumn from sometable") as "select statement"

and provide a query in the SQLQuery-Parameter..

You could also use:

= iif(Parameters!SomeID.Value<>"","SELECT somecolumn from sometable where id=" & Parameters!SQLQuery.Value,"SELECT somecolumn from sometable where id=123")

|||

Thanks For Your Reply

But I m still confusing.

I had used the iif (condition) in the generic query designer but i cannot retrive the fields which I want from the query.

The Query is executing but the data set does not contain any fields.

for eg:

="Select Idnummer,.....

iif(parameter is null,nothing,"AND ART IN ( " & parameter.value & ")")

Please reply sooner.

|||

You can't check your query anymore, thats right. When writing the query as ="select .. " & some_condition .. Hitting the "!"-Button has no effect.. This statement is evaluated at runtime.. So, you have to go to Preview-Mode and check if the result looks right..

If you are missing the Fields!.. for report design, the easiest way is to execute a "normal" sql-statement once (this will add the fields) and then transform your sql-string..

|||

Dynamic query should be avoided wherever possible. It is much harder to do (as you have seen).

I do this, I have a parameter that says All and returns a value of All (it could also return a number if a number field, just make it a value that does not exist in the database.

Do this:

selct * from sometable where (somefield = @.MyParam or @.MyParam = 'All') and ...

|||

I dunno if this is better, but should do the same thing:

The query will be:

select * from TableName
where FieldName LIKE (CASE WHEN @.param IS NULL THEN '%' ELSE @.param END)

|||

Hello Sir,

How can I implement "ALL" in my parameter

The table field does not contain 'ALL" .

If the parameter selected is 'ALL',then query should execute with the parameter contaning 'ALL' the values.Then my problem could be solve if the parameter contains 'ALL'.

please give a sample to implement 'ALL' in my parameter and in query.

Please reply soon.

Thanks

|||

u can use the query in this way,


SELECT AreaName, AreaCode
FROM Area
UNION
SELECT ' All' AS Areaname, '' AS AreaCode
ORDER BY AreaName

This will add 'All' n ur drop down box n using case statement in query u can get the desired results.

regards

Satyendra

Thursday, March 22, 2012

Dynamic Page Breaks

How can I create dynamic page breaks based on a number of records I only want
to be displayed per page?To create a page break after a specified number of rows:
1. Select the table properties
2. Add a grouping
3. In the Expression field add the following:
=Ceiling(RowNumber(Nothing)/30)
4. Check the â'Page break at endâ' check box.
reeves
"Terry" wrote:
> How can I create dynamic page breaks based on a number of records I only want
> to be displayed per page?|||Thank you so kindly.
By the way, should the new page break group be added to the already existing
groups?
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?|||Terry,
Yes you need to add a group even if groups already exist. Where it is placed
might get a little tricky and need to be played with.
I got this info from a technical article: Report Design: Best Practices and
Guidelines
but it looks like this article got updated to: Report Design Tips and Tricks
(http://msdn2.microsoft.com/en-us/library/bb395166(sql.90).aspx) and there
looks to be another way in the new article to do what your after.
Here is the site for some great articles:
http://msdn2.microsoft.com/en-us/library/aa496080.aspx
The Chart one is VERY good.
Reeves
"Terry" wrote:
> Thank you so kindly.
> By the way, should the new page break group be added to the already existing
> groups?
>
> "Reeves Smith" wrote:
> > To create a page break after a specified number of rows:
> >
> > 1. Select the table properties
> > 2. Add a grouping
> > 3. In the Expression field add the following:
> >
> > =Ceiling(RowNumber(Nothing)/30)
> >
> > 4. Check the â'Page break at endâ' check box.
> >
> > reeves
> >
> > "Terry" wrote:
> >
> > > How can I create dynamic page breaks based on a number of records I only want
> > > to be displayed per page?|||I have tried this on multiple occassions and I always get this error message...
[rsInvalidGroupExpressionScope] A group expression for the table â'table1â'
uses the RowNumber function with a scope parameter that is not valid. When
used in a group expression, the value of the scope parameter of RowNumber
must equal the name of the group directly containing the current group.
Build complete -- 1 errors, 0 warnings
I'm sure there is something else that i am missing...
Any suggestions will be greatly appreciated.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?

Friday, February 24, 2012

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!