Parametrized Report with Analysis Services didn't work

Ask a question

Monday, May 30, 2005 5:01 PM

0

Y made 2 report with parametrized MDX queries and they only work with the default values. But when I select some values from the combos with legal values the report is empty.
The weird thing is when I test the report in visual studio it works really fine. But when I go from http://somemachine/reports it fails.
Can anybody try this?
The MDX querie is ok. I have a lot of experience with MDX in SQL 2000.
It says something like this.

WITH
MEMBER [Measures].[Caida] AS 'IIF([Measures].[Variacion]<-0.5, [Measures].[Variacion], NULL)'
SELECT
NON EMPTY { [Measures].[Caida]} ON COLUMNS,
NON EMPTY { STRTOMEMBER(@Grupo).CHILDREN } ON ROWS
FROM Ventas
WHERE ( {STRTOMEMBER(@Mes)} )

I made others easier queries with the same behavior of the empty report.
Can anybody help me or know what happend?
Thanks in advance
- Reply
- Quote

All Replies

Thursday, June 02, 2005 8:06 PM

Owner

0

Can you post an RDL / query that runs against the AdventureWorks sample cube and exposes the same issue?
Additional questions:
* Which build of BI Development Studio are you running?
* Which build of RS 2005 is running on the report server?
* Which build of AS 2005 is running on the AS server?

-- Robert
- Reply
- Quote
Tuesday, June 07, 2005 12:02 PM

0

I'm not using adventure works. I'm using all of my own. But if you see the query is really simple (no tricks). Can you help me?

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="OLAP_INSA">

<rd:DataSourceID>b60468c5-c60b-47e2-942d-eb4686e92533</rd:DataSourceID>

<DataSourceReference>OLAP_INSA</DataSourceReference>

</DataSource>

</DataSources>

<InteractiveHeight>11in</InteractiveHeight>

<ReportParameters>

<ReportParameter Name="Anio">

<DataType>String</DataType>

<DefaultValue>

<Values>

<Value>[Fecha].[Standard].[Anio].&[15]</Value>

</Values>

</DefaultValue>

<Prompt>A¤o</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>Anio</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<BorderColor>

<Bottom>Black</Bottom>

</BorderColor>

<BorderWidth>

<Bottom>3pt</Bottom>

</BorderWidth>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>Caida de las Ventas respecto a¤o anterior</Value>

</Textbox>

<List Name="List1">

<Width>14.07936cm</Width>

<Grouping Name="list1_Anio">

<GroupExpressions>

<GroupExpression>=Fields!Anio.Value</GroupExpression>

</GroupExpressions>

<PageBreakAtEnd>true</PageBreakAtEnd>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Anio.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<DataSetName>OLAP_INSA</DataSetName>

<ReportItems>

<Textbox Name="Anio">

<Width>12.69841cm</Width>

<rd:DefaultName>Anio</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>900</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<FontSize>18pt</FontSize>

<BorderStyle>

<Bottom>Solid</Bottom>

</BorderStyle>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Height>0.8381cm</Height>

<Value>="A¤o: "+Fields!Anio.Value</Value>

</Textbox>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>3.5cm</Width>

</MatrixColumn>

</MatrixColumns>

<Width>7cm</Width>

<DataSetName>OLAP_INSA</DataSetName>

<RowGroupings>

<RowGrouping>

<DynamicRows>

<Grouping Name="matrix1_Grupo">

<GroupExpressions>

<GroupExpression>=Fields!Grupo.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<Sorting>

<SortBy>

<SortExpression>=Fields!Grupo.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<ReportItems>

<Textbox Name="Grupo">

<rd:DefaultName>Grupo</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!Grupo.Value</Value>

</Textbox>

</ReportItems>

</DynamicRows>

<Width>3.5cm</Width>

</RowGrouping>

</RowGroupings>

<ColumnGroupings>

<ColumnGrouping>

<Height>0.60952cm</Height>

<DynamicColumns>

<Grouping Name="matrix1_Mes">

<GroupExpressions>

<GroupExpression>=Fields!Mes.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<ReportItems>

<Textbox Name="Mes">

<rd:DefaultName>Mes</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<FontWeight>700</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<FontFamily>Times New Roman</FontFamily>

<PaddingTop>2pt</PaddingTop>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!Mes.Value</Value>

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

<ColumnGrouping>

<Height>0.53333cm</Height>

<StaticColumns>

<StaticColumn>

<ReportItems>

<Textbox Name="textbox8">

<rd:DefaultName>textbox8</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<FontWeight>600</FontWeight>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<FontSize>9pt</FontSize>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>Ca¡da mas de 25%</Value>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

<MatrixRows>

<MatrixRow>

<MatrixCells>

<MatrixCell>

<ReportItems>

<Textbox Name="Caida_mas_de_25_">

<rd:DefaultName>Caida_mas_de_25_</rd:DefaultName>

<Action>

<Drillthrough>

<ReportName>Caida Ventas por Producto</ReportName>

<Parameters>

<Parameter Name="Mes">

<Value>=Fields!Mes.Value</Value>

</Parameter>

<Parameter Name="Grupo">

<Value>=Fields!Grupo.Value</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<ToolTip>Ver detalle caida de las ventas para este mes y este grupo</ToolTip>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<TextDecoration>Underline</TextDecoration>

<PaddingTop>2pt</PaddingTop>

<Format>#.00%</Format>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<Color>Blue</Color>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!Caida_mas_de_25_.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

<Height>0.60952cm</Height>

</MatrixRow>

</MatrixRows>

<Top>1.26984cm</Top>

<Corner>

<ReportItems>

<Textbox Name="textbox5">

<rd:DefaultName>textbox5</rd:DefaultName>

<ZIndex>4</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingTop>2pt</PaddingTop>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

</Style>

<CanGrow>true</CanGrow>

<Value />

</Textbox>

</ReportItems>

</Corner>

<Style />

</Matrix>

</ReportItems>

<Top>0.8381cm</Top>

<Style>

<FontWeight>900</FontWeight>

<FontFamily>Times New Roman</FontFamily>

<FontSize>18pt</FontSize>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

<Height>3.86032cm</Height>

<Style />

</Body>

<LeftMargin>2.5cm</LeftMargin>

<BottomMargin>2.5cm</BottomMargin>

<rd:ReportID>bc8342a4-de2c-4d93-ab3c-f257505a4aa9</rd:ReportID>

<PageWidth>21cm</PageWidth>

<DataSets>

<DataSet Name="OLAP_INSA">

<Fields>

<Field Name="Anio">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /></DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Mes">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Mes]" /></DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Grupo">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Producto].[Grupo].[Grupo]" /></DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="Caida_mas_de_25_">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Caida mas de 25%]" /></DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</CommandText>

<QueryParameters>

<QueryParameter Name="Anio">

<Value>=Parameters!Anio.Value</Value>

</QueryParameter>

</QueryParameters>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Mes</LevelName><UniqueName>[Fecha].[Standard].[Mes]</UniqueName></ID><ItemCaption>Mes</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Producto</DimensionName><HierarchyName>Grupo</HierarchyName><HierarchyUniqueName>[Producto].[Grupo]</HierarchyUniqueName><LevelName>Grupo</LevelName><UniqueName>[Producto].[Grupo].[Grupo]</UniqueName></ID><ItemCaption>Grupo</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Caida mas de 25%</MeasureName><UniqueName>[Measures].[Caida mas de 25%]</UniqueName></ID><ItemCaption>Caida mas de 25%</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</Statement><ParameterDefinitions><ParameterDefinition><Name>Anio</Name><DefaultValues><DefaultValue>[Fecha].[Standard].[Anio].&[15]</DefaultValue></DefaultValues><Caption>Anio</Caption><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Fecha].[Standard].ALLMEMBERS ON ROWS FROM [Ventas]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

<DataSet Name="Anio">

<Fields>

<Field Name="Anio">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Fecha].[Standard].[Anio]" /></DataField>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<Field Name="ParameterCaption">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /></DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ParameterValue">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /></DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Field Name="ParameterLevel">

<DataField><?xml version="1.0" encoding="utf-8"?><Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" /></DataField>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<DataSourceName>OLAP_INSA</DataSourceName>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</CommandText>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterCaption</MeasureName><UniqueName>[Measures].[ParameterCaption]</UniqueName></ID><ItemCaption>ParameterCaption</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterValue</MeasureName><UniqueName>[Measures].[ParameterValue]</UniqueName></ID><ItemCaption>ParameterValue</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterLevel</MeasureName><UniqueName>[Measures].[ParameterLevel]</UniqueName></ID><ItemCaption>ParameterLevel</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

</DataSets>

<RightMargin>2.5cm</RightMargin>

<Width>16cm</Width>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>
- Reply
- Quote
Friday, June 10, 2005 1:26 PM

0

After 2 days of research and kicking my desktop.

I found finnally that to build parameters you must not use UNIQUENAME. Instead use MEMBER [Measures].[ParameterValue] AS ' "[DimensionName].["+[DimensionName].CURRENTMEMBER.NAME+"]" '

It's works fine.

I think there is a bug using STRTOMEMBER(@parameter) when @parameter is in the UNIQUENAME form.

I hope it helps you too.
- Reply
- Quote
Friday, June 17, 2005 7:41 PM

0

I edit the previous post where i said that you can only put dimensions on columns. That's wrong, i'm using dimension in columns and rows and works really fine.
- Reply
- Quote