OLAPFormula

Description:

This function returns the formula that governs the specified data point.

This function should return any formula that is applied to the specific cell. If no formula governs the specified data point this function returns #VALUE!.

The function takes as a parameter the name of the database, the name of the cube within the database and the members to be accessed in order to determine the exact intersection where the value resides.

The function has the following syntax:

Syntax: OLAPFormula (database, cube, member1,member2,…)

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

• Database : Enter the name of the database which has the dimension that you want to access / enter the cell reference which has the name of this Database.
• Cube : Enter the name of the cube which exists in this database mentioned above that you wish to access /enter the cell reference which has the name of such a dimension.
• Members k1, k2, …k'n : 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/ enter the cell reference which has such a member as its value.

Remarks

• If a connection is not established with a database the result will be a #value.
• 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 the to determine the exact intersection points.
• All the arguments are compulsory and neither argument is optional.

Example

This function may be easier to understand with an example.

• Open a specific database and bring a slice from that database into Excel. Make sure that there is connection between PowerOLAP and Excel.
• Click on a certain cell in Excel. Insert OLAPFormula function in that cell by selecting Insert Function command icon. In the Insert Function dialog box that appears select PowerOLAP as category then choose function OLAPFormula. Click OK.
• In the Function Arguments dialog box, specify the reference for database name, cube name and the pertinent dimension names. When finished select OK. If the data point returns a value that is actually a result of a formula, then this function will return that formula.
• For example open the 'Avon Trading Company' database, then just narrow down Months to show only the First quarter months. Next, create a slice view of the 'Financial Data' cube then slice into excel.
 



• Let's click on cell B30, assuming in this case that is where we want the formula to be returned. Then we insert the function OLAPFormula and specify the required parameters.

=OLAPFormula($B1,$B2,$C3,$C4,$C5,$B9,$A10)

 

Following the formula above we can identify that B1 is the database name, B2 is the cube name, and the rest are all dimension members. The above formula should return a value:

"//Revenue from Margin Cube Details and {""Account.Revenue""}= Margin.[""Product.Total Product"", ""Margin Account.Revenue""];"