Hi,

Is it possible to set two criteria in a MDX query, like:

select {[Measures].[Monthlyvalue],[Measures].[Monthlytarget]} on columns,
{[Period].[All Period].[2004].[January]} on rows
from Measure_Table
where ([Unit].[All Units].[UnitA] and [Measures].[cm Available]=1)

I get an error on execution.

Thnx,

Stanley

Hi Stanley,

You seem to have more than one problem with your query:

1) You can't use the equal-operator (=) (unless used in a filter-expression).
2) You can't use the and-operator (unless used in a filter-expression)
3) The where-clause (= slicer dimension) in a MDX-query need to identify a
so called "tuple"
(a "slice" of your cube). You can however identify a "combined" slice like
this:
([Dimension1].[Member1], [Dimension2].[Member2]...., [DimensionN].[MemberN])
You could say, that an "and" is implied in this specification.

In the concrete case, I can't quite figure out the purpose of your query.

Do you want to sum the [cm Available] that on the specific INTERSECTION
(Period, Unit) are one, or do you want to sum the [cm Available], that on
LEAF level are one ? This makes a significant difference.

In the first case you can simply add a filter-expression to your query,
something like this:
{ filter( [Period].[All Period].[2004].[January], [Measures].[cm
Available]=1 ) }

In the second case, things get somewhat more complicated...
On approach is making a dimension based on the [cm Available] measure. Then
your where-clause would resemble your original:
where ([Unit].[All Units].[UnitA], [cm Available dimension].[1])

There are other approaches, but without knowing more about your concrete
case, I can only offer you some hints, which might or might not address your
issue:
1) "calculated cells" as suggested in this post:
http://msdn.microsoft.com/newsgroups...355&sloc=en-us

2) "self referencing calculated member" as suggested by myself in this post
http://msdn.microsoft.com/newsgroups...a0a&sloc=en-us

Hope this helps...

Best regards
Dan Reving

"Stanley" wrote:

Hi Dan,

Thnx for your reply. I'm trying to add this filter with help from this site

http://www.databasejournal.com/featu...0894_3306101_2

but i can't get the query working. My original query looks like this:

with member [Period].ThisPeriod as '[Period].[All Period].[2004].[January]'
select
{Crossjoin({[Period].ThisPeriod},({[Measures].[Monthlyvalue],[Measures].[Monthlytarget],[Measures].[cm Available]}))} on columns,
{descendants([KPI].[All KPI].children)} on rows
from all_kpi
where [Unit].[All Units].[IP]

In this i want to add a criteria where the value of measure [Measures].[cm
Available] is equal to 1.

I have tried this:

with member [Period].ThisPeriod as '[Period].[All Period].[2004].[January]'
select
{Crossjoin({[Period].ThisPeriod},({[Measures].[Monthlyvalue],[Measures].[Monthlytarget],[Measures].[cm Available]}))} on columns,
{filter([KPI].[Name Descrip].members,([Measures].[cm Available])=1)} on rows
from all_kpi
where [Unit].[All Units].[IP]

But get an error:
Unable to display opened cellset. Cells cannot be created for this cellset
because one of the axes contains no positions.

If you can help me out with this, than everything would be solved.

Background of 'my solution':
I can set the criteria in the VIEW but that gives me other problems because
the i am doing some calculations in the cubes. This 'Available' field can
have the values 0 or 1 and it means wether this dimension child is available
(read: visible) or not. Like:
Available Value
UnitA 0 100
UnitB 1 0.5
UnitC 0 50

In the cube, in the dimension 'Unit' UnitA and UnitC should not be visible.
But UnitB is calculated out of UnitC/UnitA. When thA and B are not visible B
cannot be calculated. That's why i want to have them visible at all time in
the cube and filter A and B out in MDX (for use in SQL Reports).

Hope you understand my 'solution'.

Thnx,

Stanley

"Dan Reving" wrote:

Found a solution. Thnx.

"Stanley" wrote: