Only parameter values which have facts...

In a reporting services report based on a cube I have a parameter in which I
only want to show the values which have measures in a measuregroup asociated
with it.
For Example: I only want to choose products which have sales in the cube.
(retrieving the values outside the cube is not an option)
How can I achieve this'
Thanx
PieterChange the select for the product list to include a where clause for the
measure:
WHERE ([Measures].[Product Sales])
"Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> In a reporting services report based on a cube I have a parameter in which
> I
> only want to show the values which have measures in a measuregroup
> asociated
> with it.
> For Example: I only want to choose products which have sales in the cube.
> (retrieving the values outside the cube is not an option)
> How can I achieve this'
> Thanx
> Pieter|||Thaks Tim,
but where do I have to put this? In SQL I would know where, but in MDX?
My quey looks like this:
WITH MEMBER [Measures].[ParameterCaption] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS
'[Kalender].[Jaar Nummer Lang].CURRENTMEMBER.UNIQUENAME' MEMBER
[Measures].[ParameterLevel] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Kalender].[Jaar Nummer Lang].ALLMEMBERS ON ROWS FROM [Cube]
Thanks.
Pieter
"Tim Dot NoSpam" wrote:
> Change the select for the product list to include a where clause for the
> measure:
> WHERE ([Measures].[Product Sales])
> "Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
> news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> > In a reporting services report based on a cube I have a parameter in which
> > I
> > only want to show the values which have measures in a measuregroup
> > asociated
> > with it.
> > For Example: I only want to choose products which have sales in the cube.
> > (retrieving the values outside the cube is not an option)
> > How can I achieve this'
> >
> > Thanx
> >
> > Pieter
>
>|||HI,
When I change my query to this:
WITH MEMBER [Measures].[ParameterCaption] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS
'[Kalender].[Jaar Nummer Lang].CURRENTMEMBER.UNIQUENAME' MEMBER
[Measures].[ParameterLevel] AS '[Kalender].[Jaar Nummer
Lang].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Kalender].[Jaar Nummer Lang].ALLMEMBERS ON ROWS FROM [Cube] WHERE
([Measures].[Product Sales])
I get the following error:
TITLE: Microsoft Visual Studio
--
Query preparation failed.
--
ADDITIONAL INFORMATION:
The Measures hierarchy already appears in the Axis0 axis. (Microsoft SQL
Server 2005 Analysis Services)
--
BUTTONS:
OK
--
Can anybody tell me what I am doing wrong and and maybe a suggestion how to
solve this
"Tim Dot NoSpam" wrote:
> Change the select for the product list to include a where clause for the
> measure:
> WHERE ([Measures].[Product Sales])
> "Pieter M" <Pieter M@.discussions.microsoft.com> wrote in message
> news:0CA17070-4932-445B-A8ED-AA529B48FAE7@.microsoft.com...
> > In a reporting services report based on a cube I have a parameter in which
> > I
> > only want to show the values which have measures in a measuregroup
> > asociated
> > with it.
> > For Example: I only want to choose products which have sales in the cube.
> > (retrieving the values outside the cube is not an option)
> > How can I achieve this'
> >
> > Thanx
> >
> > Pieter
>
>