Formulas

With IBM® Cognos® Analytics packages, when you drag items from the source tree directly to the cells of a worksheet you create a COGNAME or COGVAL formula. The formula references the item in the database. Because IBM Planning Analytics for Microsoft Excel uses COGNAME and COGVAL as part of its internal processing, do not use either of these strings to name any part of a worksheet or in any part of a cell formula.

COGNAME formulas

COGNAME formulas are used to supply the server, package, and member names to the cells of a worksheet. The COGNAME formula takes three objects: server, package, and member unique name (MUN).

Depending on your server version and type of cube, in addition to the system and package, you may have other information, such as the date of the last cube update available to you.

The syntax for the COGNAME formula is as follows:

=COGNAME("server", "package", "MUN")

The arguments in the formula are described as follows.

server: The server object can reference a cell, such as B4, use a named range, or use a literal value with the full path to a gateway, such as http://sampleserver/ibmcognos/cgi-bin/cognos.cgi. It can also reference a COGNAME formula that contains the server information.
package: The package object is derived from the package search path, which you can find in IBM Cognos Connection. Because this argument is a literal string, you can embed this information. Or, you can put this information in a cell and reference that cell in the argument. This enables you to switch over from a test system to a production system or to change your package easily. It can also reference a COGNAME formula that contains the package information you want to use.
member unique name: The member unique name supplies information about a particular name you want to display in your workbook, such as Years. You can obtain this information from IBM Cognos Report Studio by right-clicking an object and recording the string, which includes schema.

The following is a sample COGNAME formula from the IBM Cognos sample database

=COGNAME("http://sampleserver/ibmcognos/cgi-bin/cognos.cgi", "/content/package[@name='Great Outdoors Company']", "[great_outdoors_company].[Years].[Years].[Years]->:[PC].[@MEMBER].[Years]")

COGVAL formulas

COGVAL formulas are used to supply data to the cells of a worksheet. The COGVAL formula takes three objects: server, package, and member unique names of members or measures (MUNs(X)).

The syntax for the COGVAL formula is as follows:

=COGNAME("server", "package", MUN1,MUN2,MUN3,MUNx)

The arguments in the COGVAL formula are the same as in the COGNAME formulas.

The following is a sample COGVAL formula from the IBM Cognos sample database

=COGVAL("http://sampleserver/ibmcognos/cgi-bin/cognos.cgi", "/content/package[@name='Great Outdoors Company']", $A2, C$1, $A$1 )

In this example, the cell references supply Products, Revenue, and 2004.

The COGVAL formula does not allow members from the same dimension to be supplied more than once.