OLAP Query. More than one Column/Row.

[Former Member]

Hi all experts.

I am developing an OLAP query and need to create it in this way:

Material: As BI Characteristic.

Unit: As BI Characteristic.

Value: As BI Ratio.

Returned structure in MII should be like this:

Material Unit Value

Mat1 M3 250

Mat2 KG 140

Mat3 M3 200

The BI query I am using is showing data in that way using BEx Analyzer.

However, I am not able to create it using MII's MDX syntax coded like this:

SELECT

{

[Measures].[xxxxxxxxxxxxxxxxxxxxxxxxxxx] ---> This is my BI Ratio "Value"

}

ON COLUMNS,

NON EMPTY

{

[SM_MATERI].MEMBERS, [1CUDIM].MEMBERS ---> These are Materials and Units

}

ON ROWS

FROM MYCUBE/MYQUERY

Which will return data in this way, whithout crossing Materials & Units:

Material Unit Value

Mat1 -

---

250

Mat2 -

---

140

Mat3 -

---

200

-

---

M3 450

-

---

KG 140

Any idea about how should I code my query?

I have checked both SAP's Help website and SDN Posts but without success.

Many thanks and kind regards.

Albert.

Edited by: a.pozos.basterrica on Apr 27, 2011 9:19 AM

[salvatore_castro · Accepted solution]

Albert,

It's tough to follow what you're after without knowing the definition of your cube but here's a document that will help you:

[http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/uuid/458ba574-0a01-0010-2aab-d1ee76ee9820|https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/uuid/458ba574-0a01-0010-2aab-d1ee76ee9820]

It's also available under the Connectivity section of the MII Wiki, in case this link doesn't work for some reason. Also, the best way to write your OLAP Queries is not from within the MII OLAP Query template but rather via the SAP GUI interface of the BW system in transaction code MDXTest. This is a bit more comprehensive and gives you better error messages.

Hope this helps,

Sam

[Former Member]

Hi guys, many thanks for your replies.

We finally solved it by entering in MDXTEST transaction which gives a better MDX queries composition environment.

There we were able to create queries with CROSSJOINS functions, which helped us in solving our issues.

Thanks again and kind regards,

Albert.

[agentry_src]

I have pinged someone to respond, but also suggest that you google it as well. Sometimes it works better than using the SDN Search function (also check that your SDN search was not limited by the default 90 day period).

Regards,

Mike