Parameterized Expression: analysis

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 advanceCan 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|||

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"?>

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

</DataSource>

</DataSources>

<DataType>String</DataType>

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

</Values>

</DefaultValue>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

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

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

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

<Body>

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

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Black</Bottom>

</BorderColor>

</BorderWidth>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

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

</Textbox>

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

</GroupExpressions>

</Grouping>

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

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

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

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Solid</Bottom>

</BorderStyle>

</Style>

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

</Textbox>

</MatrixColumn>

</MatrixColumns>

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

</GroupExpressions>

</Grouping>

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

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

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

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

</Style>

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

</Textbox>

</ReportItems>

</DynamicRows>

</RowGrouping>

</RowGroupings>

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

</GroupExpressions>

</Grouping>

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

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

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

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

</Style>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

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

<ReportName>Caida Ventas por Producto</ReportName>

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

</Parameter>

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

</Parameter>

</Parameters>

</Drillthrough>

</Action>

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

<Style>

<TextDecoration>Underline</TextDecoration>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

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

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

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

<Style>

</Style>

</Textbox>

</ReportItems>

</Corner>

</Matrix>

</ReportItems>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

</Body>

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

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

</Field>

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

</Field>

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

</Field>

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

</Field>

</Fields>

<Query>

<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>

<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>

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

</Field>

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

</Field>

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

</Field>

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

</Field>

</Fields>

<Query>

<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>

</Report>

|||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.|||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.

Hello,
I have created a report which use MS SQL Server 2000 Analysis Services. I am
using MS Reporting Services 2000.
I have 7 parameters, 3 Of them are also linked to olap data source. The
other 4 parameters are non queried. Every parameter has non-queried defaults
and some defaults use expressions and custom code.
The problem is although parameters are not cascading, when I made selection
in combo box all report is refreshed and I parameters are defaulted. This
is the behaviour of cascading parameters but my parameters are not
cascading,
This is realy weird, I always use multiple param in my other reports which
are linked to relational datasources.
Any comments,
Thanks in advance
erdal
sample olap linked param
SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParamsI have recognised that, one Olap linked parameter works fine with other
parameters , but when I add the second olap linked parameter they behave as
if they are cascading but actualy there is no linkage between this two.
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:e9P4L43mGHA.4212@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have created a report which use MS SQL Server 2000 Analysis Services. I
am
> using MS Reporting Services 2000.
> I have 7 parameters, 3 Of them are also linked to olap data source. The
> other 4 parameters are non queried. Every parameter has non-queried
defaults
> and some defaults use expressions and custom code.
> The problem is although parameters are not cascading, when I made
selection
> in combo box all report is refreshed and I parameters are defaulted. This
> is the behaviour of cascading parameters but my parameters are not
> cascading,
> This is realy weird, I always use multiple param in my other reports which
> are linked to relational datasources.
> Any comments,
> Thanks in advance
> erdal
> sample olap linked param
> SELECT
> {RS_ns_PX_Time_Hier} ON COLUMNS,
> {[Time].Members} ON ROWS
> FROM
> RSParams
>|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.
Not sure if this is documented, I have checkd BOL and could not find
anything maybe somewhere else.
Regards,
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:%234DvI64mGHA.4208@.TK2MSFTNGP04.phx.gbl...
> I have recognised that, one Olap linked parameter works fine with other
> parameters , but when I add the second olap linked parameter they behave
as
> if they are cascading but actualy there is no linkage between this two.
>
> "erdal akbulut" <erdalim21@.yahoo.com> wrote in message
> news:e9P4L43mGHA.4212@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have created a report which use MS SQL Server 2000 Analysis Services.
I
> am
> > using MS Reporting Services 2000.
> >
> > I have 7 parameters, 3 Of them are also linked to olap data source. The
> > other 4 parameters are non queried. Every parameter has non-queried
> defaults
> > and some defaults use expressions and custom code.
> >
> > The problem is although parameters are not cascading, when I made
> selection
> > in combo box all report is refreshed and I parameters are defaulted.
This
> > is the behaviour of cascading parameters but my parameters are not
> > cascading,
> >
> > This is realy weird, I always use multiple param in my other reports
which
> > are linked to relational datasources.
> >
> > Any comments,
> >
> > Thanks in advance
> >
> > erdal
> >
> > sample olap linked param
> > SELECT
> > {RS_ns_PX_Time_Hier} ON COLUMNS,
> > {[Time].Members} ON ROWS
> > FROM
> > RSParams
> >
> >
>

Monday, March 26, 2012

Parent-Child-Dimension in SQL Srv 2005 Analysis Services

Tuesday, March 20, 2012

Parent Attribute Hierarchy

Parametrized Report with Analysis Services didn't work

Wednesday, March 7, 2012

Parameters behave as if they are cascading parameters

Parameterized Expression

Blog Archive

About Me