Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • XL3DataSeriesLookup

    Returns the set of values from a range of given members. Often used to provide an OLAP data source for In-Cell Chart formulae.

    Use the Insert Formula > Data Series menu or ribbon item to insert the formula using a wizard. See here for details.

    Syntax

    XL3DataSeriesLookup( Connection, MemberSet, Measure, Hierarchy1, Member1,…, Hierarchy100, Member100 )

    Before XLCubed Version 9, this formula was limited to 30 parameters. This allowed up to 13 hierarchy-member pairs.

    Parameters

    Parameter Description
    Connection Connection number to use
    MemberSet An XL3MemberSet formula specifying the set to return
    Measure The measure to use
    Hierarchy1,…, HierarchyN Name of the hierarchy that the following member applies to e.g. "Measures" or "[Customer].[Customer Geography]"
    Member1,…, MemberN Either a single member unique name or an XL3Member formula

    Examples (based on the Bicycle Sales cube)

    Returns the a set of 12 Revenues for the months of 2003

    =XL3DataSeriesLookup(1,XL3MemberSet(1,"[Time]","[Time].[All].&[2003]","DescendantsAt","[Time].[Month]"),"[Measures].[Value]","[KeyFigures]","[KeyFigures].[All].&[Revenue]")

    Returns a line chart for a set of 12 Revenues for the months of 2003

    =XL3SparkLineM( XL3DataSeriesLookup(1,XL3MemberSet(1,"[Time]","[Time].[All].&[2003]","DescendantsAt","[Time].[Month]"),"[Measures].[Value]","[KeyFigures]","[KeyFigures].[All].&[Revenue]") )

    See Also