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

Calculated measures analysis cube

Hi Everyone,

I have a problem regarding measures in an analysis cube. I made connection to an analysis cube using: Microsoflt OLE DB Provider for OLAP services 8.0 or Microsoft OLE DB Provider for analysis services 10.

Both establish connection to the cube and give the possibility to load the data, which works fine. However the calculated measures in the cube are not shown and can not be loaded by QlikView.

Does anyone know a connector that does see the calculated measures? Or has another answer to this question?

Thank you in advance,

Marcel

Tags (2)
1 Reply
Employee
Employee

Re: Calculated measures analysis cube

There is no direct support for building or executing MDX-queries in QlikView. The following MDX-query (for the demo-database included with SQL 2005) will not work if you cut-and-paste it into QlikView:


SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS

FROM [Adventure Works]

WHERE ( [Sales Territory].[Southwest] )


However, by using openrowset() in SQL, it is possible to encapsulate the MDX-query in a standard SELECT statement. In this example Microsoft OLEDB for Olap Services is used to query Analyis Services:

SQL SELECT * FROM

openrowset('MSOLAP.3','DATA SOURCE=localhost;Initial Catalog=Adventure Works DW',

'SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,

{ [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS

FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )')


Note: Support for openrowset is disabled by default in SQL 2005 Server.

Community Browser