This function builds a slice over the specified range
for the selected cube.
OLAPNavigate is a similar function to the OLAPTable,
OLAPPivot and OLAPReadWrite excel functions that allows for creating
of a slice report directly from Excel. The "Navigate" in
"OLAPNavigate" implies drilldown/drill up functionality
and nesting or stacking of Dimensions in Rows and Columns.
The function has the following syntax:
Syntax: OLAPNavigate
(server, database, file, cube, k1, k2, ...kN)
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 OLAPNavigate 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.
=OLAPNavigate("","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.