Hyperion MDX Provider Release 7 API Function Reference and Example

# API Reference

This document describes the Essbase API functions specifically built to deal with MDX manipulations.

This file contains the following sections:

General Information

Reference

- MDX Provider Function Declarations
- EssMdxNewQuery()
- EssMdxExecuteQuery()
- EssMdxFreeQuery()
- EssMdxNewQuery()
- EssMdxGetAxes()
- EssMdxGetAxisInfo()
- EssMdxGetAxisMembers()
- EssMdxGetCellAtOffset()
- EssMdxGetCellAtIndices()
- EssMdxGetCellStatus()
- EssMdxGetClusterInfo()
- EssMdxGetClusters()
- EssMdxGetClusterDimMembers()
- EssMdxGetClusterMembers()
- EssMdxGetDimInfo()
- EssMdxGetMbrIdentifier()
- EssMdxGetMbrProperty()
- EssMdxGetPropertyInfo()
- EssMdxGetValue()
- EssMdxSetDataLess()
- EssMdxSetMbrIdType()
- EssMdxSetNeedCellStatus()

MDX Sample Query

Top

## MDX Provider API General Information

MDX queries that follow the grammar given in the MDX functional specification can be submitted to the server through the MDX-API. The query results can then be retrieved by the client using this API.

The grammar of Essbase MDX statements is covered in the MaxL Data Manipulation Language (MaxL DML) section of the Essbase Technical Reference.

A few basic MDX concepts and terminology are reviewed here. An MDX query consists of several axis specifications, and an optional slicer specification. Each axis specifies a set-valued expression. A set is an ordered collection of tuples, with tuples being a sequence of members from one or more dimensions. Tuples in a set are homogeneous in dimensionality (each tuple has members from the same dimensions in the same order).

An example of a set expression based on the Sample Basic database is:

       Union(
          CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
          Union(
             CrossJoin([Total Expenses].Children, {[Actual]}),
             {([Opening Inventory], [Variance]), ([Additions], [Variance %])}
          )
       )

This expression uses several MDX functions: Union, CrossJoin, Children. The value of this expression is the set:

       {
          ([Sales], [Actual]),
          ([Sales], [Budget]),
          ([Profit], [Actual]),
          ([Profit], [Budget]),
          ([Marketing], [Actual]),
          ([Payroll], [Actual]),
          ([Misc], [Actual]),
          ([Opening Inventory], [Variance]),
          ([Additions], [Variance %])
       }

Note that in the result of the CrossJoin, the tuples are ordered so that the first dimension changes slowest. The tuples in this set have the dimensionality: ([Measures],[Scenario]). The dimensionality of tuples across axis sets must not overlap.

In addition to the set expression, each axis specifies the name of the axis (COLUMNS, ROWS, PAGES, etc.) or the axis number (AXIS(0), AXIS(1), etc.). The cube consisting of all possible combinations of tuples, one from each axis, constitutes the result of the query. Dimensions that are not present in any axis and in the slicer default to having their root member included in defining the result cube. The slicer, if present, specifies a set, with a single tuple, which identifies the members of interest along the respective dimensions. This makes the final result a slice of the cube created from the axes. The result of an MDX query contains the metadata about each axis and the slicer, as well as the data values in the cells in the result cube.

Here is a complete MDX query:

       SELECT
          Union(
             CrossJoin({[Sales], [Profit]}, {[Actual], [Budget]}),
             Union(
                CrossJoin([Total Expenses].Children, {[Actual]}),
                {([Opening Inventory], [Variance]), ([Additions], [Variance %])}
             )
          ) ON COLUMNS,
          CrossJoin(
             [200].Children,
             {[East], [West]}
          ) ON ROWS
       FROM
          Sample.Basic
       WHERE
          {[Jan]}

The result of this query has 9 tuples on the column axis and 8 tuples on the row axis, which means there are 72 cells in all. Each cell has an ordinal, or offset, which depends on the position of its tuples along each axis. Offsets and positions start at 0. The cells are ordered so that the first axis position changes the fastest.

For example, the cell identified by tuple 3 in the column axis and tuple 4 in the row axis is at offset 3 + 9*4 = 39.

- Tuple 3 in the column axis is ([Profit], [Budget]).
- Tuple 4 in the row axis is ([200-30], [East]).
- Cell 39 is therefore ([Profit], [Budget], [200-30], [East], [Jan]).

The concept of clusters is needed for reasons of efficiency. A set can be considered to be an ordered collection of tuples, or it can be considered to be an ordered collection of clusters. A cluster is a collection of tuples that involve all possible combinations of certain members from each of the set's dimensions. The tuples need to ordered in the same manner as in the output of the CrossJoin function (the first dimension changes the slowest). Use of the CrossJoin function causes clusters to be created, but the server may determine clusters from the results of other functions as well.

Top

## MDX Provider API Reference

The C API for MDX query processing is designed to fit in with the existing Essbase APIs. Client programs are given handles to various structures internal to the API, and use methods to access their components. The number of functions is kept small by judicious combining of output results normally needed together. Except where noted, memory allocated by the API for its internal structures is freed when the client invokes the query free function. ESS_MDX is the prefix used for the handle types, and EssMdx is the prefix used for the functions introduced by the MDX-API.

Top

### MDX Provider Declarations

The type definitions are as follows:

    typedef void *ESS_MDX_QRYHDL_T;                /* MDX query handle */
    typedef unsigned long ESS_MDX_MEMBERIDTYPE_T;  /* MDX mbr id type */
    typedef void *ESS_MDX_AXISHDL_T;               /* MDX axis handle */
    typedef void *ESS_MDX_DIMHDL_T;                /* MDX dim handle */
    typedef unsigned long ESS_MDX_PROPTYPE_T;      /* MDX property type */
    typedef void *ESS_MDX_PROPHDL_T;               /* MDX property handle */
    typedef void *ESS_MDX_CLUSTERHDL_T;            /* MDX cluster handle */
    typedef void *ESS_MDX_MBRHDL_T;                /* MDX mbr handle */
    typedef void *ESS_MDX_CELLHDL_T;               /* MDX cell handle */
    typedef unsigned long ESS_MDX_CELLSTATUS_T;    /* MDX cell status */

The constant definitions are as follows:

    /* MDX member identifier types (ESS_MDX_MEMBERIDTYPE_T) */
    #define ESS_MDX_MEMBERIDTYPE_NAME            8
    #define ESS_MDX_MEMBERIDTYPE_ALIAS          16

    /* MDX property value types (ESS_MDX_PROPTYPE_T) */
    #define ESS_MDX_PROPTYPE_BOOL                ESS_DT_BOOL
    #define ESS_MDX_PROPTYPE_DOUBLE              ESS_DT_DOUBLE
    #define ESS_MDX_PROPTYPE_DATETIME            ESS_DT_DATETIME
    #define ESS_MDX_PROPTYPE_STRING              ESS_DT_STRING
    #define ESS_MDX_PROPTYPE_ULONG               ESS_DT_ULONG
    #define ESS_MDX_PROPTYPE_NONE                0

    /* MDX cell status bitmasks (ESS_MDX_CELLSTATUS_T) */
    #define ESS_MDX_CELLSTATUS_LINKEDOBJS        0x00000001
    #define ESS_MDX_CELLSTATUS_DYNCALC           0x00000002
    #define ESS_MDX_CELLSTATUS_CALCEDMBR         0x00000004
    #define ESS_MDX_CELLSTATUS_READONLY          0x00000008

ESS_MDX_PROPVALUE_T

    typedef struct ess_mdx_propvalue_t
    {
       ESS_MDX_PROPTYPE_T ulPropType;   /* ESS_MDX_PROPTYPE_XXXX */
       union
       {
          ESS_BOOL_T bData;       /* Boolean value */
          ESS_ULONG_T ulData;     /* Ulong value */
          ESS_STR_T strData;      /* String value */
          ESS_DATETIME_T dtData;  /* Datetime value */
          ESS_DOUBLE_T dblData;   /* Double value */
       } value;
    } ESS_MDX_PROPVALUE_T;

Top

### EssMdxExecuteQuery()

Executes the specified query on the currently connected database.

Syntax

    ESS_FUNC_M EssMdxExecuteQuery(ESS_MDX_QRYHDL_T   hQry);

hQry | input | Query handle

Top

### EssMdxGetAxes()

Returns information about the axes in the query.

After EssMdxExecuteQuery(), use EssMdxGetAxes(), EssMdxGetAxisInfo(), and EssMdxGetDimInfo() to obtain information on the nature of the axes in the submitted query.

Syntax

    ESS_FUNC_M EssMdxGetAxes(ESS_MDX_QRYHDL_T      hQry,
                             ESS_PULONG_T          pulNAxes,
                             ESS_MDX_PPAXISHDL_T   pphAxes,
                             ESS_MDX_PAXISHDL_T    phSlicer);

hQry | input | Query handle
pulNAxes | output | Number of axes
pphAxes | output | Array of axis handles
phSlicer | output | Slicer axis handle

Top

### EssMdxGetAxisInfo()

Returns information about the specified axis.

After EssMdxExecuteQuery(), use EssMdxGetAxes(), EssMdxGetAxisInfo(), and EssMdxGetDimInfo() to obtain information on the nature of the axes in the submitted query.

Syntax

    ESS_FUNC_M EssMdxGetAxisInfo(ESS_MDX_AXISHDL_T    hAxis,
                                 ESS_PULONG_T         pulSize,
                                 ESS_PULONG_T         pulNDims,
                                 ESS_MDX_PPDIMHDL_T   pphDims);

hAxis | input | Axis handle
pulSize | output | Number of tuples in axis
pulNDims | output | Number of dimensions in axis
pphDims | output | Array of dimension handles

Top

### EssMdxGetClusters()

Returns the clusters within the specified axis.

Obtain information about the contents of an axis set using :

- EssMdxGetClusters(),
- EssMdxGetClusterInfo(),
- EssMdxGetClusterMembers(),
- EssMdxGetAxisMembers(),
- EssMdxGetMbrIdentifier(),
- EssMdxGetMbrProperty().

Syntax

    ESS_FUNC_M EssMdxGetClusters(ESS_MDX_AXISHDL_T        hAxis,
                                 ESS_PULONG_T             pulNClusters,
                                 ESS_MDX_PPCLUSTERHDL_T   pphClusters);

hAxis | input | Axis handle
pulNClusters | output | Number of clusters
pphClusters | output | Array of cluster handles

Top

### EssMdxGetClusterInfo()

Returns information about the specified cluster.

Obtain information about the contents of an axis set using :

- EssMdxGetClusters(),
- EssMdxGetClusterInfo(),
- EssMdxGetClusterMembers(),
- EssMdxGetAxisMembers(),
- EssMdxGetMbrIdentifier(),
- EssMdxGetMbrProperty().

Syntax

    ESS_FUNC_M EssMdxGetClusterInfo(ESS_MDX_CLUSTERHDL_T   hCluster,
                                    ESS_PULONG_T           pulSize,
                                    ESS_PULONG_T           pulNDims,
                                    ESS_PPULONG_T          ppulDimSizes);

hCluster | input | Cluster handle
pulSize | output | Number of tuples in cluster
pulNDims | output | Number of dimensions in cluster (same as that in the axis that this cluster belongs to)
ppulDimSizes | output | Array of dimension sizes (number of members)

Top

### EssMdxGetClusterMembers()

Returns the tuple at the specified position within the given cluster.

Note: The client should use EssFree() when done with `pphMbrs`.

Obtain information about the contents of an axis set using :

- EssMdxGetClusters(),
- EssMdxGetClusterInfo(),
- EssMdxGetClusterMembers(),
- EssMdxGetAxisMembers(),
- EssMdxGetMbrIdentifier(),
- EssMdxGetMbrProperty().

Syntax

    ESS_FUNC_M EssMdxGetClusterMembers(ESS_MDX_CLUSTERHDL_T   hCluster,
                                       ESS_ULONG_T            ulIndex,
                                       ESS_MDX_PPMBRHDL_T     pphMbrs);

hCluster | input | Cluster handle
ulIndex | input | Tuple position within cluster (first dimension changes slowest)
pphMbrs | output | Array of member handles for the tuple

Top

### EssMdxGetAxisMembers()

Returns the tuple at the specified position in the given axis. Use this function to directly retrieve a particular tuple from an axis.

Note: The client should use EssFree() when done with `pphMbrs`.

Obtain information about the contents of an axis set using :

- EssMdxGetClusters(),
- EssMdxGetClusterInfo(),
- EssMdxGetClusterMembers(),
- EssMdxGetAxisMembers(),
- EssMdxGetMbrIdentifier(),
- EssMdxGetMbrProperty().

Syntax

    ESS_FUNC_M EssMdxGetAxisMembers(ESS_MDX_AXISHDL_T    hAxis,
                                    ESS_ULONG_T          ulIndex,
                                    ESS_MDX_PPMBRHDL_T   pphMbrs);

hAxis | input | Axis handle
ulIndex | input | Tuple position within axis
pphMbrs | output | Array of member handles for tuple

Top

### EssMdxGetCellAtOffset()

Returns the cell at the specified offset.

Obtain the cell values in the cube formed from the axes in the query using :

- EssMdxGetCellAtOffset(),
- EssMdxGetCellAtIndices(), and
- EssMdxGetValue().

Syntax

    ESS_FUNC_M EssMdxGetCellAtOffset(ESS_MDX_QRYHDL_T     hQry,
                                     ESS_ULONG_T          ulOffset,
                                     ESS_MDX_PCELLHDL_T   phCell);

hQry | input | Query handle
ulOffset | input | Cell offset (first axis changes fastest)
phCell | input | Cell handle

Top

### EssMdxGetCellAtIndices()

Returns the cell at the intersection of the specified tuple indices.

Obtain the cell values in the cube formed from the axes in the query using :

- EssMdxGetCellAtOffset(),
- EssMdxGetCellAtIndices(), and
- EssMdxGetValue().

Syntax

    ESS_FUNC_M EssMdxGetCellAtIndices(ESS_MDX_QRYHDL_T     hQry,
                                      ESS_PULONG_T         pulIndices,
                                      ESS_MDX_PCELLHDL_T   phCell);

hQry | input | Query handle
pulIndices | input | Tuple indices, one for each axis
phCell | output | Cell handle

Top

### EssMdxGetValue()

Returns the specified cell's value.

Obtain the cell values in the cube formed from the axes in the query using :

- EssMdxGetCellAtOffset(),
- EssMdxGetCellAtIndices(), and
- EssMdxGetValue().

Syntax

    ESS_FUNC_M EssMdxGetValue(
       ESS_MDX_CELLHDL_T      hCell,
       ESS_PBOOL_T            pbIsMissing,
       ESS_PBOOL_T            pbNoAccess,
       ESS_PDOUBLE_T          pdValue
                             );

hCell | input | Cell handle
pbIsMissing | output | Whether cell value is #Missing
pbNoAccess | output | Whether cell value is #NoAccess
pdValue | output | The cell's value, if not #Missing

Top

### EssMdxFreeQuery()

Frees memory used for the specified query.

Syntax

    ESS_FUNC_M EssMdxFreeQuery(ESS_MDX_QRYHDL_T   hQry);

hQry | input | Query handle

Top

### EssMdxNewQuery()

Takes the MDX query specified by pszQry and returns a query handle.

Syntax

    ESS_FUNC_M EssMdxNewQuery(
       ESS_HCTX_T             hCtx,
       ESS_STR_T              pszQry,
       ESS_MDX_PQRYHDL_T      phQry
                             );

hCtx | input | API context handle
pszQry | input | Query text
phQry | output | Query handle

Top

### EssMdxSetMbrIdType()

Sets the type of member identifier desired in the result. Defaults to ESS_MDX_MEMBERIDTYPE_NAME.

Syntax

    ESS_FUNC_M EssMdxSetMbrIdType(
       ESS_MDX_QRYHDL_T       hQry,
       ESS_MDX_MEMBERIDTYPE_T mbrIdType
                                 );

hQry | input | Query handle
mbrIdType | input | Member identifier desired (name/alias): - ESS_MDX_MEMBERIDTYPE_NAME - ESS_MDX_MEMBERIDTYPE_ALIAS

Top

### EssMdxGetDimInfo()

Returns information about the specified dimension, including the properties available for members in this dimension.

Syntax

    ESS_FUNC_M EssMdxGetDimInfo(
       ESS_MDX_DIMHDL_T       hDim,
       ESS_PSTR_T             ppszName,
       ESS_PULONG_T           pulNProps,
       ESS_MDX_PPPROPHDL_T    pphProps
                               );

hDim | input | Dimension handle
ppszDimName | output | Dimension name
pulNProps | output | Number of properties returned
pphProps | output | Array of property handles

Top

### EssMdxGetPropertyInfo()

Returns information about the specified property.

Syntax

    ESS_FUNC_M EssMdxGetPropertyInfo(
       ESS_MDX_PROPHDL_T      hProp,
       ESS_PSTR_T             ppszName,
       ESS_MDX_PPROPTYPE_T    pPropType
                                    );

hProp | input | Property handle
ppszName | output | Property name
pPropType | output | Property type: - ESS_MDX_PROPTYPE_BOOL - ESS_MDX_PROPTYPE_DOUBLE - ESS_MDX_PROPTYPE_STRING - ESS_MDX_PROPTYPE_DATETIME - ESS_MDX_PROPTYPE_ULONG

Top

### EssMdxGetMbrIdentifier()

Returns the identifier for the specified member.

Syntax

    ESS_FUNC_M EssMdxGetMbrIdentifier(
       ESS_MDX_MBRHDL_T       hMbr,
       ESS_PSTR_T             ppszIdentifier
                                     );

hMbr | input | Member handle
ppszIdentifier | output | Member identifier (name or alias)

Top

### EssMdxGetMbrProperty()

Returns the value of the specified property for the specified member. The property value will have a type of ESS_MDX_PROPTYPE_NONE if the property is not applicable to the member.

Syntax

    ESS_FUNC_M EssMdxGetMbrProperty(
                                    ESS_MDX_MBRHDL_T       hMbr,
                                    ESS_MDX_PROPHDL_T      hProp,
                                    ESS_MDX_PPROPVALUE_T   pPropValue
                                   );

hMbr | input | Member handle
hProp | input | Property handle
pPropValue | output | Property value

Top

### EssMdxSetDataLess()

Turns on a query execution mode in which cell data are not retrieved. EssMdxGetCellAtOffset() and EssMdxGetCellAtIndices() should not be called for the query. The default is to retrieve cell data.

Syntax

    ESS_FUNC_M EssMdxSetDataLess(
       ESS_MDX_QRYHDL_T       hQry
                                );

hQry | input | Query handle

Top

### EssMdxSetNeedCellStatus()

Turns on retrieval of cell status information. By default the cell status information is not retrieved.

Syntax

    ESS_FUNC_M EssMdxSetNeedCellStatus(
       ESS_MDX_QRYHDL_T       hQry
                                      );

hQry | input | Query handle

Top

### EssMdxGetClusterDimMembers()

Returns the member handles for the specified dimension within the given cluster.

Syntax

    ESS_FUNC_M EssMdxGetClusterDimMembers(
       ESS_MDX_CLUSTERHDL_T   hCluster,
       ESS_ULONG_T            ulIndex,
       ESS_MDX_PPMBRHDL_T     pphMbrs
                                         );

hCluster | input | Cluster handle
ulIndex | input | Dimension index within axis containing cluster
pphMbrs | output | Array of member handles for the specified dimension

Top

### EssMdxGetCellStatus()

Returns the status of the cell specified by hCell. The status can be tested against the bitmasks in pulStatus to determine whether the cell is of the corresponding type. This function should be called only after an earlier call to EssMdxSetNeedCellStatus().

Syntax

    ESS_FUNC_M EssMdxGetCellStatus(
       ESS_MDX_QRYHDL_T       hQry,
       ESS_MDX_CELLHDL_T      hCell,
       ESS_MDX_PCELLSTATUS_T  pulStatus
                                  );

hQry | input | Query handle
hCell | input | Cell handle
pulStatus | output | Cell status: Bitmap with the following masks: - ESS_MDX_CELLSTATUS_LINKEDOBJS - ESS_MDX_CELLSTATUS_DYNCALC - ESS_MDX_CELLSTATUS_CALCEDMBR - ESS_MDX_CELLSTATUS_READONLY

Top

## MDX Sample Client Program

    #if defined _WIN32 || defined _WINDOWS
    #include <windows.h>
    #endif

    #include <string.h>
    #include <stdio.h>
    #include <ctype.h>
    #include <stdlib.h>
    #include <assert.h>
    #include <time.h>
    #if defined _WIN32 || defined _WINDOWS
    #pragma pack(push,localid,1)
    #endif
    #include <essapi.h>
    #if defined _WIN32 || defined _WINDOWS
    #pragma pack(pop,localid)
    #endif

    ESS_HINST_T  hInst;
    ESS_HCTX_T hCtx;
    #define MAXQRYLEN 65536
    ESS_CHAR_T qry[MAXQRYLEN];
    ESS_STR_T AppName = "Sample";
    ESS_STR_T DbName = "Basic";

    static ESS_CHAR_T *axisnames[] =
    {
       "COLUMNS", "ROWS", "PAGES", "CHAPTERS", "SECTIONS"
    };

    void ESS_Init()
    {
        ESS_STS_T    sts;
        ESS_INIT_T InitStruct = {ESS_API_VERSION,
                                 NULL,
                                 0L,
                                 255,
                                 NULL,
                                 NULL,
                                 NULL,
                                 NULL,
                                 NULL,
                                 NULL,
                                 NULL,
                                 0L
                                };
       if ((sts = EssInit(&InitStruct, &hInst)) != ESS_STS_NOERR)
       {
          printf("EssInit failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssInit sts: %ld\n", sts);
    }

    void ESS_Login ()
    {
       ESS_STS_T sts = ESS_STS_NOERR;
       ESS_USHORT_T Items;
       ESS_PAPPDB_T pAppsDbs = NULL;
       ESS_CHAR_T SvrName[ESS_SVRNAMELEN];
       ESS_CHAR_T UserName[ESS_USERNAMELEN];
       ESS_CHAR_T Password[ESS_PASSWORDLEN];

       /* Initialize parameters */
       strcpy(SvrName,"localhost");
       strcpy(UserName,"essexer");
       strcpy(Password,"password");
       sts = EssLogin(hInst, SvrName, UserName, Password, &Items,
                      &pAppsDbs, &hCtx);
       if ( (sts != 0) && (sts != 1051093L) && (sts != 1051090L) )
       {
          printf("EssLogin failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssLogin sts: %ld\n", sts);
    }

    void ESS_MdxAxis(ESS_MDX_QRYHDL_T hQry,
                     ESS_MDX_AXISHDL_T hAxis,
                     ESS_STR_T pszAxisName
                    )
    {
       ESS_STS_T    sts;
       ESS_ULONG_T ulNAxisDims, ulAxisSize;
       ESS_ULONG_T ulNClusters, ulClusterSize, ulNClusterDims;
       ESS_ULONG_T ulAxisDimCnt, ulIndex, ulPropCnt;
       ESS_ULONG_T ulClusterCnt, ulClusterDimCnt;
       ESS_PULONG_T ulaDimSizes;
       ESS_MDX_PCLUSTERHDL_T haClusters;
       ESS_MDX_CLUSTERHDL_T hCluster;
       ESS_MDX_PDIMHDL_T haDims;
       ESS_STR_T pszDimName, pszMbrIdentifier, pszPropName;
       ESS_MDX_PMBRHDL_T haMbrs;
       ESS_PULONG_T ulaNProps = NULL;
       ESS_MDX_PPPROPHDL_T haaProps = NULL;
       ESS_MDX_PPROPHDL_T haProps;
       ESS_MDX_PROPHDL_T hProp;
       ESS_MDX_PROPTYPE_T propType;
       ESS_MDX_PROPVALUE_T propval;

       if ((sts = EssMdxGetAxisInfo(hAxis, &ulAxisSize, &ulNAxisDims,
                                    &haDims)) != ESS_STS_NOERR)
       {
          printf("EssMdxGetAxisInfo failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxGetAxisInfo sts: %ld\n", sts);
       printf("%s Size %ld Num dims %ld\n", pszAxisName,
              ulAxisSize, ulNAxisDims);
       if (ulAxisSize == 0)
       {
          return;
       }
       if ((sts = EssAlloc(hInst,
                           ulNAxisDims * sizeof(ESS_ULONG_T),
                           (ESS_PPVOID_T) &ulaNProps)) != ESS_STS_NOERR)
       {
          printf("EssAlloc failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssAlloc sts: %ld\n", sts);
       if ((sts = EssAlloc(hInst,
                           ulNAxisDims * sizeof(ESS_MDX_PPROPHDL_T),
                           (ESS_PPVOID_T) &haaProps)) != ESS_STS_NOERR)
       {
          printf("EssAlloc failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssAlloc sts: %ld\n", sts);
       for (ulAxisDimCnt = 0; ulAxisDimCnt < ulNAxisDims;
            ulAxisDimCnt++)
       {
          if ((sts = EssMdxGetDimInfo(haDims[ulAxisDimCnt],
                                      &pszDimName,
                                      &ulaNProps[ulAxisDimCnt],
                                      &haaProps[ulAxisDimCnt])) != ESS_STS_NOERR)
          {
             printf("EssMdxGetDimInfo failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetDimInfo sts: %ld\n", sts);
          printf("Dim %ld name %s #props %ld\n", ulAxisDimCnt,
                 pszDimName, ulaNProps[ulAxisDimCnt]);
          haProps = haaProps[ulAxisDimCnt];
          for (ulPropCnt = 0; ulPropCnt < ulaNProps[ulAxisDimCnt]; ulPropCnt++)
          {
             hProp = haProps[ulPropCnt];
             if ((sts = EssMdxGetPropertyInfo(hProp, &pszPropName,
                                              &propType)) != ESS_STS_NOERR)
             {
                printf("EssMdxGetPropertyInfo failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssMdxGetPropertyInfo sts: %ld\n", sts);
             printf("Property %ld type %ld name %s\n", ulPropCnt,
                    propType, pszPropName);
          }
       }
       if ((sts = EssMdxGetClusters(hAxis, &ulNClusters,
                                    &haClusters)) != ESS_STS_NOERR)
       {
          printf("EssMdxGetClusters failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxGetClusters sts: %ld\n", sts);
       printf("Num clusters %ld\n", ulNClusters);
       for (ulClusterCnt = 0; ulClusterCnt < ulNClusters;
            ulClusterCnt++)
       {
          hCluster = haClusters[ulClusterCnt];
          if ((sts = EssMdxGetClusterInfo(hCluster, &ulClusterSize,
                                          &ulNClusterDims,
                                          &ulaDimSizes)) != ESS_STS_NOERR)
          {
             printf("EssMdxGetClusterInfo failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetClusterInfo sts: %ld\n", sts);
          printf("Cluster %ld Size %ld\n", ulClusterCnt, ulClusterSize);
          for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims;
               ulClusterDimCnt++)
          {
             printf("Cluster Dim %ld Size %ld\n", ulClusterDimCnt,
                    ulaDimSizes[ulClusterDimCnt]);
          }
          for (ulIndex = 0; ulIndex < ulClusterSize; ulIndex++)
          {
             if ((sts = EssMdxGetClusterMembers(hCluster, ulIndex,
                                                &haMbrs)) != ESS_STS_NOERR)
             {
                printf("EssMdxGetClusterMembers failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssMdxGetClusterMembers sts: %ld\n", sts);
             for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims;
                  ulClusterDimCnt++)
             {
                if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulClusterDimCnt],
                                                  &pszMbrIdentifier)) != ESS_STS_NOERR)
                {
                   printf("EssMdxGetMbrIdentifier failure: %ld\n", sts);
                   exit ((int) sts);
                }
                printf("EssMdxGetMbrIdentifier sts: %ld\n", sts);
                printf("Mbr %ld identifier %s\n", ulClusterDimCnt,
                       pszMbrIdentifier);
                haProps = haaProps[ulClusterDimCnt];
                for (ulPropCnt = 0;
                     ulPropCnt < ulaNProps[ulClusterDimCnt];
                     ulPropCnt++)
                {
                   if ((sts = EssMdxGetMbrProperty(haMbrs[ulClusterDimCnt],
                                                   haProps[ulPropCnt],
                                                   &propval)) != ESS_STS_NOERR)
                   {
                      printf("EssMdxGetMbrProperty failure: %ld\n", sts);
                      exit ((int) sts);
                   }
                   printf("EssMdxGetMbrProperty sts: %ld\n", sts);
                   printf("Property %ld Type ", ulPropCnt);
                   switch (propval.ulPropType)
                   {
                      case ESS_MDX_PROPTYPE_ULONG:
                      {
                         printf("Ulong Value: %ld\n",
                                propval.value.ulData);
                         break;
                      }
                      case ESS_MDX_PROPTYPE_STRING:
                      {
                         printf("String Value: %s\n",
                                propval.value.strData);
                         break;
                      }
                      case ESS_MDX_PROPTYPE_BOOL:
                      {
                         printf("Bool Value: %s\n",
                                propval.value.bData ? "TRUE" : "FALSE");
                         break;
                      }
                      case ESS_MDX_PROPTYPE_DOUBLE:
                      {
                         printf("Double Value: %lf\n",
                                propval.value.dblData);
                         break;
                      }
                      case ESS_MDX_PROPTYPE_DATETIME:
                      {
                         ESS_CHAR_T tmpbuf[80];
                         struct tm* pTime;
                         pTime =  gmtime((time_t*)&(propval.value.dtData));
                         sprintf(tmpbuf, "%02i-%02i-%04i",
                                 pTime->tm_mon+1, pTime->tm_mday,pTime->tm_year+1900);
                         printf("DateTime Value: %s\n", tmpbuf);
                         break;
                      }
                      case ESS_MDX_PROPTYPE_NONE:
                      {
                         printf("NULL Value\n");
                         break;
                      }
                   }
                }
             }
             if ((sts = EssFree(hInst, (ESS_PVOID_T) haMbrs)) != ESS_STS_NOERR)
             {
                printf("EssFree failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssFree sts: %ld\n", sts);
          }
          for (ulClusterDimCnt = 0; ulClusterDimCnt < ulNClusterDims;
               ulClusterDimCnt++)
          {
             if ((sts = EssMdxGetClusterDimMembers(hCluster, ulClusterDimCnt,
                                                   &haMbrs)) != ESS_STS_NOERR)
             {
                printf("EssMdxGetClusterDimMembers failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssMdxGetClusterDimMembers sts: %ld\n", sts);
             for (ulIndex = 0; ulIndex < ulaDimSizes[ulClusterDimCnt];
                  ulIndex++)
             {
                if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulIndex],
                                                  &pszMbrIdentifier)) != ESS_STS_NOERR)
                {
                   printf("EssMdxGetMbrIdentifier failure: %ld\n", sts);
                   exit ((int) sts);
                }
                printf("EssMdxGetMbrIdentifier sts: %ld\n", sts);
                printf("Dim %ld Mbr %ld identifier %s\n", ulClusterDimCnt,
                       ulIndex, pszMbrIdentifier);
             }
          }
       }
       for (ulIndex = 0; ulIndex < ulAxisSize; ulIndex++)
       {
          if ((sts = EssMdxGetAxisMembers(hAxis, ulIndex,
                                          &haMbrs)) != ESS_STS_NOERR)
          {
             printf("EssMdxGetAxisMembers failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetAxisMembers sts: %ld\n", sts);
          for (ulAxisDimCnt = 0; ulAxisDimCnt < ulNAxisDims;
               ulAxisDimCnt++)
          {
             if ((sts = EssMdxGetMbrIdentifier(haMbrs[ulAxisDimCnt],
                                               &pszMbrIdentifier)) != ESS_STS_NOERR)
             {
                printf("EssMdxGetMbrIdentifier failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssMdxGetMbrIdentifier sts: %ld\n", sts);
             printf("Mbr %ld identifier %s\n", ulAxisDimCnt, pszMbrIdentifier);
             haProps = haaProps[ulAxisDimCnt];
             for (ulPropCnt = 0;
                  ulPropCnt < ulaNProps[ulAxisDimCnt];
                  ulPropCnt++)
             {
                hProp = haProps[ulPropCnt];
                if ((sts = EssMdxGetPropertyInfo(hProp, &pszPropName,
                                                 &propType)) != ESS_STS_NOERR)
                {
                   printf("EssMdxGetPropertyInfo failure: %ld\n", sts);
                   exit ((int) sts);
                }
                if ((sts = EssMdxGetMbrProperty(haMbrs[ulAxisDimCnt],
                                                hProp,
                                                &propval)) != ESS_STS_NOERR)
                {
                   printf("EssMdxGetMbrProperty failure: %ld\n", sts);
                   exit ((int) sts);
                }
                printf("EssMdxGetMbrProperty sts: %ld\n", sts);
                printf("Property %ld Type ", ulPropCnt);
                switch (propval.ulPropType)
                {
                   case ESS_MDX_PROPTYPE_ULONG:
                   {
                      printf("Ulong Value: %ld\n",
                             propval.value.ulData);
                      break;
                   }
                   case ESS_MDX_PROPTYPE_STRING:
                   {
                      printf("String Value: %s\n",
                             propval.value.strData);
                      break;
                   }
                   case ESS_MDX_PROPTYPE_BOOL:
                   {
                      printf("Bool Value: %s\n",
                             propval.value.bData ? "TRUE" : "FALSE");
                      break;
                   }
                   case ESS_MDX_PROPTYPE_DOUBLE:
                   {
                      printf("Double Value: %lf\n",
                             propval.value.dblData);
                      break;
                   }
                   case ESS_MDX_PROPTYPE_DATETIME:
                   {
                      ESS_CHAR_T tmpbuf[80];
                      struct tm* pTime;
                      pTime =  gmtime((time_t*)&(propval.value.dtData));
                      sprintf(tmpbuf, "%02i-%02i-%04i",
                              pTime->tm_mon+1, pTime->tm_mday,pTime->tm_year+1900);
                      printf("DateTime Value: %s\n", tmpbuf);
                      break;
                   }
                   case ESS_MDX_PROPTYPE_NONE:
                   {
                      printf("NULL Value\n");
                      break;
                   }
                }
             }
          }
          if ((sts = EssFree(hInst, (ESS_PVOID_T) haMbrs)) != ESS_STS_NOERR)
          {
             printf("EssFree failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssFree sts: %ld\n", sts);
       }
       if ((sts = EssFree(hInst, (ESS_PVOID_T) ulaNProps)) != ESS_STS_NOERR)
       {
          printf("EssFree failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssFree sts: %ld\n", sts);
       if ((sts = EssFree(hInst, (ESS_PVOID_T) haaProps)) != ESS_STS_NOERR)
       {
          printf("EssFree failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssFree sts: %ld\n", sts);
    }

    void ESS_MdxQry()
    {
       ESS_STS_T    sts;
       ESS_MDX_QRYHDL_T hQry;
       ESS_ULONG_T ulNAxes, ulNAxisDims, ulAxisSize, ulResultSize;
       ESS_ULONG_T ulNClusters, ulClusterSize, ulNClusterDims;
       ESS_ULONG_T ulAxisCnt, ulAxisDimCnt, ulIndex, ulPropCnt;
       ESS_ULONG_T ulCellOffset, ulClusterCnt, ulClusterDimCnt;
       ESS_MDX_CELLSTATUS_T ulCellStatus;
       ESS_PULONG_T ulaDimSizes;
       ESS_MDX_PCLUSTERHDL_T haClusters;
       ESS_MDX_CLUSTERHDL_T hCluster;
       ESS_MDX_PAXISHDL_T haAxes;
       ESS_MDX_PDIMHDL_T haDims;
       ESS_STR_T pszDimName, pszMbrIdentifier, pszPropName;
       ESS_MDX_AXISHDL_T hAxis, hSlicerAxis;
       ESS_MDX_PMBRHDL_T haMbrs;
       ESS_MDX_CELLHDL_T hCell;
       ESS_DOUBLE_T dValue;
       ESS_BOOL_T bIsMissing, bNoAccess;
       ESS_PULONG_T ulaNProps;
       ESS_MDX_PPPROPHDL_T haaProps;
       ESS_MDX_PPROPHDL_T haProps;
       ESS_MDX_PROPHDL_T hProp;
       ESS_MDX_PROPTYPE_T propType;
       ESS_MDX_PROPVALUE_T propval;

       if ((sts = EssMdxNewQuery(hCtx, qry, &hQry)) != ESS_STS_NOERR)
       {
          printf("EssMdxNewQuery failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxNewQuery sts: %ld\n", sts);

       if ((sts = EssMdxSetMbrIdType(hQry, ESS_MDX_MEMBERIDTYPE_ALIAS)) !=
                     ESS_STS_NOERR)
       {
          printf("EssMdxSetMbrIdType failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxSetMbrIdType sts: %ld\n", sts);

       if ((sts = EssMdxSetNeedCellStatus(hQry)) != ESS_STS_NOERR)
       {
          printf("EssMdxSetNeedCellStatus failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxSetNeedCellStatus sts: %ld\n", sts);

       if ((sts = EssMdxExecuteQuery(hQry)) != ESS_STS_NOERR)
       {
          printf("EssMdxExecuteQuery failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxExecuteQuery sts: %ld\n", sts);

       if ((sts = EssMdxGetAxes(hQry, &ulNAxes, &haAxes,
                                &hSlicerAxis)) != ESS_STS_NOERR)
       {
          printf("EssMdxGetAxes failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxGetAxes sts: %ld\n", sts);
       printf("Number of axes: %ld\n", ulNAxes);

       ulResultSize = 1;
       for (ulAxisCnt = 0; ulAxisCnt < ulNAxes; ulAxisCnt++)
       {
          hAxis = haAxes[ulAxisCnt];
          if ((sts = EssMdxGetAxisInfo(hAxis, &ulAxisSize, &ulNAxisDims,
                                       &haDims)) != ESS_STS_NOERR)
          {
             printf("EssMdxGetAxisInfo failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetAxisInfo sts: %ld\n", sts);
          printf("Axis %ld Size %ld Num dims %ld\n", ulAxisCnt,
                 ulAxisSize, ulNAxisDims);
          ulResultSize *= ulAxisSize;
       }

       if (hSlicerAxis)
       {
          ESS_MdxAxis(hQry, hSlicerAxis, "SLICER");
       }
       else
       {
          printf("Slicer Axis is empty\n");
       }

       for (ulAxisCnt = 0; ulAxisCnt < ulNAxes; ulAxisCnt++)
       {
          hAxis = haAxes[ulAxisCnt];
          ESS_MdxAxis(hQry, hAxis, axisnames[ulAxisCnt]);
       }
       for (ulCellOffset = 0; ulCellOffset < ulResultSize;
            ulCellOffset++)
       {
          if ((sts = EssMdxGetCellAtOffset(hQry, ulCellOffset,
                                           &hCell)) != ESS_STS_NOERR)
          {
             printf("EssMdxGetCellAtOffset failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetCellAtOffset sts: %ld\n", sts);
          if ((sts = EssMdxGetValue(hCell, &bIsMissing, &bNoAccess,
                                    &dValue)) != ESS_STS_NOERR)
          {
             printf("EssMdxGetValue failure: %ld\n", sts);
             exit ((int) sts);
          }
          printf("EssMdxGetValue sts: %ld\n", sts);
          if (bIsMissing)
          {
             printf("CellOffset %ld Value #Missing\n", ulCellOffset);
          }
          else if (bNoAccess)
          {
             printf("CellOffset %ld Value #NoAccess\n", ulCellOffset);
          }
          else
          {
             printf("CellOffset %ld Value %lf\n", ulCellOffset,
                    dValue);
          }
          if (!bNoAccess)
          {
             if ((sts = EssMdxGetCellStatus(hQry, hCell,
                                            &ulCellStatus)) != ESS_STS_NOERR)
             {
                printf("EssMdxGetCellStatus failure: %ld\n", sts);
                exit ((int) sts);
             }
             printf("EssMdxGetCellStatus sts: %ld\n", sts);
             if (ulCellStatus & ESS_MDX_CELLSTATUS_LINKEDOBJS)
             {
                printf("Cell status: LINKEDOBJS\n");
             }
             if (ulCellStatus & ESS_MDX_CELLSTATUS_DYNCALC)
             {
                printf("Cell status: DYNCALC\n");
             }
             if (ulCellStatus & ESS_MDX_CELLSTATUS_CALCEDMBR)
             {
                printf("Cell status: CALCEDMBR\n");
             }
             if (ulCellStatus & ESS_MDX_CELLSTATUS_READONLY)
             {
                printf("Cell status: READONLY\n");
             }
          }
       }

       if ((sts = EssMdxFreeQuery(hQry)) != ESS_STS_NOERR)
       {
          printf("EssMdxFreeQuery failure: %ld\n", sts);
          exit ((int) sts);
       }
       printf("EssMdxFreeQuery sts: %ld\n", sts);

    }

    void ESS_Term()
    {
       ESS_STS_T sts = ESS_STS_NOERR;
       if ((sts = EssTerm(hInst)) != ESS_STS_NOERR)
       {
          /* error terminating API */
          exit((ESS_USHORT_T) sts);
       }
       printf("EssTerm sts: %ld\n", sts);
    }

    void ESS_Logout()
    {
       ESS_STS_T sts = ESS_STS_NOERR;

       sts = EssLogout(hCtx);
       printf("\n\nEssLogout sts: %ld\n",sts);
    }

    void ESS_SetActive()
    {
       ESS_STS_T sts = ESS_STS_NOERR;
       ESS_ACCESS_T Access;

       sts = EssSetActive(hCtx, AppName, DbName, &Access);
       printf("EssSetActive sts: %ld\n",sts);
    }

    int main(int argc, char *argv[])
    {
       FILE *f;
       char *s, *sout;
       int n, l, e;

       assert(argc > 1);
       f = fopen(argv[1], "r");
       assert(f != NULL);
       s = qry;
       n = MAXQRYLEN;
       while (n > 0 && !feof(f) && fgets(s, n, f) != NULL)
       {
          l = strlen(s);
          s += l;
          n -= l;
       }
       if ((e = ferror(f)) != 0)
       {
          printf("fgets error %d\n", e);
          exit((int) e);
       }
       fclose(f);
       printf("The query is\n%s\n", qry);
       if (argc > 2)
       {
          AppName = argv[2];
       }
       if (argc > 3)
       {
          DbName = argv[3];
       }

       ESS_Init();
       ESS_Login();
       ESS_SetActive();

       ESS_MdxQry();

       ESS_Logout();
       ESS_Term();

       return 0;
    }

Top

©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com