OLAPQuery

Description:

This function queries for detail transaction within an intersection.

The function has the following syntax:

Syntax: OLAPQuery (database, cube, range, deleteRows, deleteCols)

Explained below are the parameters that have to be provided when building this function.

• Server : Enter the name of the server where the database you want to access is running or opened.
• Database : Enter the name of the database which has the dimension you want to access.
• File : Enter the name of the PowerOLAP database file and location.
• Cube : Enter the cube name which exists in the database specified above that you want to access.
• Members (k1, k2, ...kN) : Enter the name of the member from which a relationship will be established to determine the point where the values have to be read from in order to build the report.

Remarks

• If the database is not running on the server, leave the parameter for server blank but make sure to enclose it in double quotes ("").
• If a connection is not established with a database the result will be a #VALUE.
• The file location of the database must be accurate and spelled correctly.
• The database name, the cube name and the member names must be referenced by their exact name and should exist as such within the database.
• The member names should be chosen as such, that they allow the function to determine the exact intersection points for which it should create the report. The way that they should be specified in the formula should be in the order that the corresponding Dimensions were arranged in the cube.

Example

This function may be easier to understand with an example.

• For example, in a certain cell we want to bring back the fact data for Actual Canada Sales for the First Quarter of the UsingPO database. To do this we first need to open an excel worksheet. For this scenario let us assume that we want to access a local copy of the UsingPO database saved in our computer.
• In Excel, use the menu: Insert--> Function and select PowerOLAP from the drop down list.
• Choose the OLAPQuery function from the list of functions provided and click "Ok".
• When building the formula enter the database name (UsingPO), file name for the location of your PowerOLAP database (C:\Program Files (x86)\PowerOLAP\Examples\UsingPO.olp), cube name (Future Year Model), the member name to identify where the values will be read from and lastly identify the cell where the requested value will be returned.

=OLAPQuery("", "UsingPO", "(C:\Program Files (x86)\PowerOLAP\Examples\UsingPO.olp)", cube name ", "Future Year Model","Actual","Sales", "Canada", "1st Quarter", B7)

 

The above formula returns the Actual Sales value for Canada during the first quarter on the cell B7.