This function queries for detail transaction within
The function has the following syntax:
(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
- 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.
This function may be easier to understand with an
- 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.
"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.