Use MDX

Question

Question

Rodrigo Flores · Jan 21, 2020

#Analytics #InterSystems IRIS BI (DeepSee)

Hi there,
We need your help, in the build pivot with DMX, we don't can run the pivot. The MDX use is:

WITH MEMBER [Measures].[CondicionesContCompleta] AS ( IIf([Measures].[CondicionesContCompleta] = Null, 0,[Measures].[CondicionesContCompleta] ) ) SELECT NON EMPTY ( [Gerencia].Gerencia].[Gerencia] ) ON ROWS, NON EMPTY ( [Measures].[CondicionesContCompleta] ) ON COLUMNS FROM [Condiciones]

and when we executed the pivot display the next dialog.

Error#5001: Expected operator within tuple: ('SELECT' @pos 156)(2)

Discussion (7)2

Log in or sign up to continue

Sam Duncan · Jan 21, 2020

After looking at this some more, there are another couple of suggestions I would make:

- If you want to display each member of the [Gerencia].[Gerencia].[Gerencia] level in its own row, you should use [Gerencia].[Gerencia].[Gerencia].MEMBERS as the set expression on rows. Your current syntax will return a single row.

- It's unnecessary to wrap the set expressions on rows and columns in parentheses. (You may, of course, need to use parentheses in these expressions if they involve functions such as NONEMPTYCROSSJOIN(level1.MEMBERS,level2.MEMBERS), but an expression like [Gerencia].[Gerencia].[Gerencia].MEMBERS by itself doesn't need them.)

- If you aren't already using it, you may find it helpful to use the Analyzer, as this gives you an interface to create pivot tables to display the data you want without having to write the MDX from scratch and worry about its syntax. When you create or modify a pivot table in the Analyzer, the underlying MDX query is generated, and is executed either automatically (by default) or when you refresh the pivot. If your goal is to create pivot tables and dashboards, you may not need to interact with the query text at all if you don't want to; if you're running queries programmatically, you can create a pivot table and then view the MDX that was generated (click on the pencil-and-paper icon above the "Columns" box) and use it as a template for your queries.

0 0

score 1 · Answer 1 · 2020-01-21T13:03:08-05:00

Hi Rodrigo,

Instead of using IIF here, you can us ISNULL(). ISNULL takes in a measure as the first parameter and the second parameter is the value to use if the first is null. So it would look like this:

ISNULL([Measures].[CondicionesContCompleta],0)

score 1 · Answer 2 · 2020-01-21T13:05:25-05:00

Also, it looks like your calculated measure name is the same as your built in measure name. This means you should change to be something like the following:
WITH MEMBER [Measures].[CondicionesContCompleta] ->
WITH MEMBER [Measures].[CondicionesContCompletaCalc]

score 1 · Answer 3 · 2020-01-21T13:12:14-05:00

I agree with Peter's suggestions, and I would also recommend wrapping the value expression for your calculated measure in single quotes rather than parentheses. After making these changes (and adding a missing [ to the hierarchy of the level you put on rows), your query might be roughly this:

WITH MEMBER [Measures].[CondicionesContCompletaCalc] AS 'ISNULL([Measures].[CondicionesContCompleta],0)' SELECT NON EMPTY ([Gerencia].[Gerencia].[Gerencia]) ON 1, NON EMPTY ([Measures].[CondicionesContCompletaCalc]) ON 0 FROM [Condiciones]

score 0 · Answer 4 · 2020-01-23T15:38:22-05:00

Thank you very much Samuel, i use normally the Analyzer but need more specification in my pivot, so i beginning in use a MDX for enrich my Dashboard.
I need a book of MDX for dummies... jaja a

this is my first step in MDX

score 0 · Answer 5 · 2020-01-21T13:20:36-05:00

Hi Peter, change te code but the execute pivot display de same error:

The MDX used:

WITH MEMBER [Measures].[CondicionesContCompletaCalc] AS ( ISNULL([Measures].[CondicionesContCompleta], 0) ) SELECT NON EMPTY ( [Gerencia].Gerencia].[Gerencia] ) ON ROWS, NON EMPTY ( [Measures].[CondicionesContCompletaCalc] ) ON COLUMNS FROM [Condiciones]

The error displayed:

Error#5001: Expected operator within tuple: ('SELECT' @pos 156)(2)

score 0 · Answer 6 · 2020-01-21T13:41:24-05:00

Hi Rodrigo,

This MDX query uses several suggestions from my comments below:

WITH MEMBER [Measures].[CondicionesContCompletaCalc] AS 'ISNULL([Measures].[CondicionesContCompleta],0)' SELECT NON EMPTY [Gerencia].[Gerencia].[Gerencia].MEMBERS ON 1, NON EMPTY [Measures].[CondicionesContCompletaCalc] ON 0 FROM [Condiciones]

If you try that and it doesn't work, please feel free to contact InterSystems support and we can help you with further debugging.