Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

undesired "all" string in field when loading data from cube OLAP

Hi,

I have a problem that I don't manage to solve. I'm loading data from a cube OLAP with a MDX request. In addition with the measures, one of the loaded dimensions is:

[Energy Delivery].[Energy Type].ALLMEMBERS

ON AXIS(3),

It should contain 4 different possibilities:

- Auxiliary energy stream

- Energy from outside

- Local renewable energy

- Main energy stream

So when I open a listbox, I only see these 4 options. But if I create a chart, then I have an additional one, called "-", corresponding to ALL, that I don't want.

The problem is that if I replace in my script ALLMEMBERS by CHILDREN, then I lose navigability through other dimensions.

Thanks for you help.

1 Solution

Accepted Solutions
Not applicable

undesired "all" string in field when loading data from cube OLAP

I finally found a solution.

In fact, Qlikview seems not to support the cube format. With a MDX request, I was loading data as they were on my OLAP cube, that's to say with many dimensions. But if I want Qlikview, which play the role of the cube, to understand well the relations between the different sets of data, I need to load them as a set of columns. In order to do so, I use a SQL request to flatten the data and get every single measure and dimension as a columns table. Thus I write as many requests as the number of measures I have, as following:

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=Myserver;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];

SQL SELECT * FROM OPENQUERY(myopenserver,

'SELECT NON EMPTY

{

    [MyMeasures]

}

ON AXIS(0),

NON EMPTY

{

    [MyFirstDimension].MEMBERS

}

ON AXIS(1),

NON EMPTY

(

    [MySecondDimension].MEMBERS,

    [FirstAttribute].Children,

    [SecondAttribute].Children

)

ON AXIS(2)

FROM [MyCube];');

So create as many similar request as the number of measures you have. Also to access your Cube with an OPENQUERY, you need to have an administrator account. Then now that the data are flattened, the CHILDREN works fine and keep navigability.

1 Reply
Not applicable

undesired "all" string in field when loading data from cube OLAP

I finally found a solution.

In fact, Qlikview seems not to support the cube format. With a MDX request, I was loading data as they were on my OLAP cube, that's to say with many dimensions. But if I want Qlikview, which play the role of the cube, to understand well the relations between the different sets of data, I need to load them as a set of columns. In order to do so, I use a SQL request to flatten the data and get every single measure and dimension as a columns table. Thus I write as many requests as the number of measures I have, as following:

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=Myserver;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];

SQL SELECT * FROM OPENQUERY(myopenserver,

'SELECT NON EMPTY

{

    [MyMeasures]

}

ON AXIS(0),

NON EMPTY

{

    [MyFirstDimension].MEMBERS

}

ON AXIS(1),

NON EMPTY

(

    [MySecondDimension].MEMBERS,

    [FirstAttribute].Children,

    [SecondAttribute].Children

)

ON AXIS(2)

FROM [MyCube];');

So create as many similar request as the number of measures you have. Also to access your Cube with an OPENQUERY, you need to have an administrator account. Then now that the data are flattened, the CHILDREN works fine and keep navigability.

Community Browser