Using a bridge parameter

Contents[Hide]

1. Overview

Bridge parameters can be added to a data cube to be inserted between a transform's parameter and where the parameter values come from. You can use a bridge parameter to convert values from a hierarchy or another type not directly compatible with your query or transform before passing them. For example, a hierarchy member filter on a metric set or dashboard selects an entire hierarchy member, which your database query or stored procedure won't be able to recognize without first converting it.

The script language used in bridge parameters is DundasScript, which is also used in other parts of the application such as formulas and is similar to C#.

Note
This is an advanced topic for developers who are comfortable writing script or database query languages.

2. Examples

The following examples provide step-by-step instructions for some of the common uses of bridge parameters. Unless otherwise specified, all of these examples are based on the Adventure Works sample SQL Server database.

2.1. Multi-level hierarchy with single selection

In this example, you use the bridge parameter to select a single member on the lowest level of the multi-level hierarchy filter:

  1. The following stored procedure is included in the Adventure Works database. @productID is the external parameter used in this stored procedure.
    CREATE PROCEDURE [dbo].[sp_ProductID_Hierarchy] 
          @ProductID int	 
    AS
    BEGIN
          SET NOCOUNT ON;
          SELECT [ProductID] AS ProductID, [OrderQty] as OrderQty
          FROM [Sales].[SalesOrderDetail]
          WHERE [ProductID] = @ProductID
    END
    
  2. Create a new hierarchy from the main menu, and drag the [Production].[Products] table to the canvas. Rename the hierarchy to ProductHierarchy.

    Product hierarchy
    Product hierarchy

  3. Create a new data cube, and drag the sp_ProductID_Hierarchy stored procedure to the canvas.

    Sample stored procedure
    Sample stored procedure

    Select the Stored Procedure transform and click Bridge Parameters in the toolbar.

    Bridge Parameters
    Bridge Parameters

    Click Add parameter and define the Bridge Parameter settings:

    • Name: @ProdID
    • Description: Product ID
    • Public: Yes
    • Value Type: Single Member

    Expand the Hierarchies folder and the ProductHierarchy file, then drag the hierarchy to the Drop a hierarchy region.

    Drag and drop the hierarchy
    Drag and drop the hierarchy

    Click Create parameter and select the stored procedure's @ProductID parameter under the Existing Parameters section.

    Parameter Information
    Parameter Information

    Note
    You should take note of the unique name indicated for the Product level, C, as it represents the key of this hierarchy (i.e., ProductID).

    The following script placed in the Script field extracts the original value from the member's unique name and returns it to the stored procedure:

    // Create SingleMemberValue
    SingleMemberValue memberValue = (SingleMemberValue)$input$.Resolve();
    // convert memberValue name into an integer
    var mem = Convert.ToInt32(memberValue.Value.UniqueName.Replace(".C",""));
    // convert integer into Number
    Number myNumber = new Number(mem);
    // send back SingleNumberValue named $id$
    return new SingleNumberValue($id$, myNumber);
    

    Adding the bridge parameter script
    Adding the bridge parameter script

    Select a Test Value and click Test Script.

    Testing the bridge parameter script
    Testing the bridge parameter script

  4. Create or edit a dashboard, and in the Explore window, expand the Data Cubes folder and drag and drop the OrderQty measure from under your data cube to the dashboard canvas.

    Drag and drop measure
    Drag and drop measure

    Choose Filter in the toolbar, and add a member value filter, found under the Hierarchy category.

    Add a member value filter
    Add a member value filter

    In the Filter Visualizations popup, make sure your bridge parameter (e.g., @ProdID) is checked.

    Connect the filter
    Connect the filter

  5. View the dashboard, click to open the filter and expand one of the members to the lowest level, then select one (e.g., Mountain-100 Black, 42).

    Select a lowest level member
    Select a lowest level member

    Result
    Result

2.2. Multi-level hierarchy with single selection on either parent or child level

In this example, you indicate the level and select a single member from a multi-level hierarchy filter:

  1. This example is based on a table named [dbo].[Sample_data] created in a database with the following sample data:

    Sample Data
    Sample Data

  2. A stored procedure with two parameters is created in this example:

    • @level: The level of the selected member in the hierarchy filter.
    • @name: The caption name of the selected member. Note that this could be on either the Continent or the Country level.
    CREATE PROCEDURE [dbo].[sp_Country_Sales] 
          @level nvarchar(2) = 'C',
    	  @name nvarchar(1000) = 'North America' 
    AS
    BEGIN
        SET NOCOUNT ON;
        IF (@level = 'C')
    	Begin
            	SELECT [Location] , [Sales]
    	        FROM [dbo].[Sample_data]
            	WHERE [Continent] = @name
    	End
        IF (@level = 'B')
    	Begin
    		SELECT [Location] , [Sales]
    		FROM [dbo].[Sample_data]
    		WHERE [Country] = @name	
    	End
    END
    
  3. Create a hierarchy from the main menu, and add two levels: Continent and Country.

    Continent level
    Continent level

    Country level
    Country level

    Hierarchy preview
    Hierarchy preview

  4. Create a new data cube, and drag the sp_Country_Sales stored procedure to the canvas.

    Select the Stored Procedure transform and click Bridge Parameters in the toolbar.

    Click Add parameter and define the bridge parameter settings:

    • Name@location
    • Descriptionlocation
    • PublicYes
    • Value TypeSingle Member

    Expand the Hierarchies folder and the hierarchy file you created, then drag the hierarchy to the Drop a hierarchy region.

    Click Create parameter and select the stored procedure's @level parameter under the Existing Parameters section.

    The following script used in the Script field passes the level unique name directly to our stored procedure:

    SingleMemberValue memberValue = (SingleMemberValue)$input$.Resolve();
    var myString = string.Empty;
    myString = memberValue.Value.LevelUniqueName;
    return new SingleStringValue($id$, myString);
    

    Script for the <em>@level</em> parameter
    Script for the @level parameter

    Select the stored procedure's @name parameter under Existing Parameters and use the following script in the Script field to remove the level unique name portion of the unique name, leaving only the original key values:

    SingleMemberValue memberValue = (SingleMemberValue)$input$.Resolve();
    var myString = string.Empty;
    var rep = string.Empty;
    rep = "." + memberValue.Value.LevelUniqueName;
    myString = memberValue.Value.UniqueName.Replace(rep, "");
    return new SingleStringValue ($id$, myString);
    

    Script for the <em>@level</em> parameter
    Script for the @level parameter

  5. Create or edit a dashboard, and drag the Sales measure and the Location dimension from the data cube to the dashboard canvas. Note that only North America results are shown, as was indicated in the stored procedure.

    Add a member value filter and connect it to the @location parameter. (If you don't see this parameter, un-check any initial selection so that incompatible parameters become available.)

    Connect a member value filter
    Connect a member value filter

  6. View the dashboard and select Asia from the parent level of the filter. The result should show only records for Asia.

    Select parent level member
    Select parent level member

    Select USA from the child level.

    Select child level member
    Select child level member

2.3. Single-level hierarchy with multiple selection

In this example, a bridge parameter is used to select multiple members from a single-level hierarchy filter:

    1. Create a new hierarchy from the main menu, drag the [Person].[CountryRegion] table to the canvas and rename the hierarchy to CountryHierarchy.

      Rename the first level of the hierarchy to Country, then delete all levels except the first level.

      Set the Member Key Source to Name.

      Set up the single-level hierarchy
      Set up the single-level hierarchy

      The hierarchy preview looks like the following:

      Single-level hierarchy
      Single-level hierarchy

    2. Create a new Manual Query data cube. Select the Adventure Works data connector and use the following manual query:

      SELECT B.[Name] as 'Country',
             SUM(A.[SalesYTD]) as 'SalesYTD'
      FROM [AdventureWorks].[Sales].[SalesTerritory] A
      INNER JOIN [AdventureWorks].[Person].[CountryRegion] B
             ON A.[CountryRegionCode]=B.[CountryRegionCode]
      WHERE ( $country$ = 'All' 
             OR B.[Name] IN (select * from dbo.Split( $country$, ',')))
      GROUP BY B.[Name]
      

      For reference, this manual query relies on the following function to split the comma-delimited string parameter:

      CREATE FUNCTION [dbo].[split](@string varchar(max), @delimiter char(1))
      RETURNS @temptable TABLE (items varchar(8000)) AS
      BEGIN
          DECLARE @idx int
          DECLARE @substring varchar(8000)
       
          SELECT @idx = 1
          IF (@string IS NULL)
              RETURN
       
          WHILE (@idx != 0)
          BEGIN
              SET @idx = charindex(@delimiter, @string)
              IF (@idx != 0)
                  SET @substring = left(@string, @idx - 1)
              ELSE
                  SET @substring = @string
       
              IF (len(@substring) > 0)
                  INSERT INTO @temptable (items) values (@substring)
       
              SET @string = right(@string, len(@string) - @idx)
          END
          RETURN
      END
      

      Set up a $country$ placeholder. For more information on setting up a placeholder for a manual select, see the article Using manual select placeholders.

      Placeholder
      Placeholder

    3. Click Bridge Parameters in the data cube's toolbar, then Add parameter. Define the bridge parameter settings:

      • Name: @country
      • Description: country
      • Public: Yes
      • Value Type: Collection Member

      Expand the Hierarchies folder and the CountryHierarchy hierarchy file, and drag the hierarchy to the Drop a hierarchy region.

      Drag the hierarchy to the drop region
      Drag the hierarchy to the drop region

      Click Create parameter and select the stored procedure's country checkbox under the Existing Parameters section.

The following script placed in the Script field extracts the member key values and concatenates them together:

CollectionMemberValue memberValue = (CollectionMemberValue)$input$.Resolve();
var commaDelimitedString = string.Empty;
//Pass "All" string if user selected "All" checkbox or "All" token 
if (memberValue.Token != null && memberValue.Token == PredefinedToken.All)    
{
  commaDelimitedString = "All";
}
else
{
  for (int i = 0; i < memberValue.Values.Count; i++)
  {
    if (commaDelimitedString.Length == 0) 
    { //Need to strip off appended level unique name... see Example 1 
      commaDelimitedString = memberValue.Values[i].UniqueName.Replace(".A", "");
    }
    else
    {
      //concatenate all selected values to a comma-delimited string
      commaDelimitedString = string.Format("{0},{1}", commaDelimitedString, memberValue.Values[i].UniqueName.Replace(".A", ""));
    }
  }
}
return new SingleStringValue($id$, commaDelimitedString);
  1. On a dashboard, drag SalesYTD and Country from your new data cube to the canvas and add a member filter. Make sure the filter is connected to the bridge parameter.

    Member filter parameters
    Member filter parameters

  2. Switch to View mode and select multiple values from the filter.

    Result
    Result

2.4. Single-level hierarchy with inverted multiple selection

To handle inverted selection, you need to check the parameter property IsInverted and. You can pass it to your query as an additional parameter, for example.

This example re-creates and expands on the previous example:

  1. Make sure the Country hierarchy is set up the same way as in the previous example.
  2. Set up a data cube with the following stored procedure:
    CREATE PROCEDURE [dbo].[sp_Country_multiple_inverted] 
          @IsInverted bit = 0, 
          @country varchar(8000) = ''
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT B.[Name] as 'Country',
              SUM(A.[SalesYTD]) as 'SalesYTD'
        FROM [Sales].[SalesTerritory] A
        INNER JOIN [Person].[CountryRegion] B
              ON A.[CountryRegionCode]=B.[CountryRegionCode]
        WHERE ( @IsInverted = 0 AND
              ( @country = 'All' or B.[Name] IN (select * from dbo.Split( @country, ',')))
              ) OR
              ( @IsInverted = 1 AND
              (  B.[Name] NOT IN (select * from dbo.Split( @country, ',')))
              ) 
        GROUP BY B.[Name]
    END
    
  3. Create the @country bridge parameter, similar to the previous example.
  4. In the Define Bridge Parameter dialog, select the stored procedure's @IsInverted parameter and use the following script:
    var memberValue = $input$.Resolve();
    var myString = memberValue.IsInverted;
    return new SingleBooleanValue ($id$, myString);
    

    Bridge parameter script for inverted filter
    Bridge parameter script for inverted filter

  5. Create the same dashboard as in the previous example. Switch to View mode, select multiple values from the filter, and invert the selection.

    Result
    Result

2.5. Single-level hierarchy with single selection using MDX 

In this example, you use the bridge parameter to select a single member from a single-level hierarchy on an MDX data cube: 

  1. Make sure the single-level Country hierarchy is set up the same way as in the previous examples.
  2. Create a new Manual Query data cube. Select the Adventure Works OLAP data connector, and use the following manual MDX query:

    SELECT {[Measures].[Internet Sales Amount]} DIMENSION PROPERTIES MEMBER_TYPE ON COLUMNS,
    { DESCENDANTS(StrToMember($country$),[Customer].[Customer Geography].[State-Province]) } 
                DIMENSION PROPERTIES MEMBER_TYPE ON ROWS
    FROM [Adventure Works]
    

    Set up a $country$ placeholder (see the Passing placeholder value from dashboard to manual MDX select for more details on placeholders). Set the string value to [Customer].[Customer Geography].[Country].&[Canada]:

    Sample Placeholder
    Sample Placeholder

  3. Click Bridge parameter in the data cube's toolbar, then Add parameter and define the bridge parameter settings:
    • Name: @country
    • Description: country
    • Public: Yes
    • Value Type: Single Member
  4. Expand the Hierarchies folder and expand the CountryHierarchy hierarchy file, and drag the hierarchy to the Drop a hierarchy region.
  5. Click Create parameter and select the stored procedure's the country parameter under Existing Parameters.
  6. The following script placed in the Script field forms the OLAP cube's unique name from the key extracted from Dundas BI's hierarchy member unique name:
    SingleMemberValue memberValue = (SingleMemberValue)$input$.Resolve();
    var myString = string.Empty;
    myString = string.Format("[Customer].[Customer Geography].[Country].&[{0}]", memberValue.Value.UniqueName.Replace(".A",""));
    return new SingleStringValue ($id$, myString);
    

    Select Germany as the Test Value and click Test Script.

    Test for Germany
    Test for Germany

2.6. Date range selection

In this example, you use a bridge parameter to pass a range of dates to two stored procedure parameters:

    1. This example is based on a stored procedure with two parameters:
      • @DateFrom: Start date.
      • @DateTo: End date.
      CREATE PROCEDURE [dbo].[sp_SalesOrderDetail_Date] 
          @DateFrom datetime,
          @DateTo datetime
      AS
      BEGIN
          SET NOCOUNT ON;
          SELECT 
              [ModifiedDate] AS ModifiedDate,
              [LineTotal] AS LineTotal
          FROM [Sales].[SalesOrderDetail]
          WHERE [ModifiedDate] BETWEEN @DateFrom and @DateTo
      END
      

      Note
      The BETWEEN statement's end value is inclusive, which means values matching @DateTo are included in this example.

    2. Create a new data cube and drag the sp_SalesOrderDetail_Date stored procedure to the canvas.

      Select the Stored Procedure transform and click Bridge Parameters in the toolbar.

Click Add parameter and define the bridge parameter settings:

      • Name: DateParams
      • Description: DateFrom and DateTo
      • Public: Yes
      • Value Type: Range Date/Time

        Note
        The RangeDate/Time value type corresponds to a Range Date filter. As an alternative, you could use the Range Member value type, which corresponds to the Calendar Range filter.

Click Create parameter and select the stored procedure's @DateFrom parameter under Existing Parameters. The following script placed in the Script field returns the date value selected as the beginning of the range:

return new SingleDateTimeValue($id$, $input$.LowerBoundaryValue);

Script for the <em>@DateFrom</em> parameter
Script for the @DateFrom parameter

If you choose Range Member as your bridge parameter's data type, the lower boundary value is a member value, and the following script would return the inclusive start date of that member:

return new SingleDateTimeValue($id$, $input$.LowerBoundaryValue.MemberTime);

Select the @DateTo parameter under Existing Parameters and use the following script to return the date selected as the end of the range:

return new SingleDateTimeValue($id$, $input$.UpperBoundaryValue);

Script for the <em>@DateTo</em> parameter
Script for the @DateTo parameter

If you choose Range Member as your bridge parameter's data type, the following script would return the range end member value's exclusive upper boundary date:

return new SingleDateTimeValue($id$, $input$.UpperBoundaryValue.UpperBoundaryTime);
  1. Create or edit a dashboard, and drag the LineTotal measure and the ModifiedDate hierarchy to the dashboard canvas from your data cube in the Explore window.

    Add a Range Date (or Calendar Range) filter and connect it to your bridge parameter.

    Connect the filter
    Connect the filter

  2. Switch to View mode and select start (from) and end (to) dates.

    Result after selecting start and end dates.
    Result after selecting start and end dates.

2.7. Number range selection

In this example, you use a bridge parameter to pass a range of numbers to two stored procedure parameters:

  • This example is based on a stored procedure with two parameters:
    • @from: float
    • @to: float
    CREATE PROCEDURE [dbo].[sp_3Level_sales_range] 
          @from float,
    	  @to float
    AS
    BEGIN
          SET NOCOUNT ON;
    
    
    SELECT T.[Continent] AS [Continent], 
    T.[Country] AS [Country], 
    T.[Location] AS [Location], 
    T.[Sales] AS [Sales], 
    T.[SalesTarget] AS [SalesTarget] 
    FROM [dbo].[3LevelSales] T
    WHERE T.[Sales] BETWEEN @from AND @to 
    

    Create a new data cube and drag the sp_3Level_sales_range stored procedure to the canvas.

    Select the Stored Procedure transform and click Bridge Parameters in the toolbar.

    Click Add parameter and define the bridge parameter settings:

    • Name: @Sales
    • Public: Yes
    • Range Type: Range Number

    Click Create parameter and select the stored procedure's @from parameter under Existing Parameters. The following script placed in the Script field returns the number selected as the beginning of the range:

    RangeNumberValue rangeNumberValue = (RangeNumberValue)$input$;
    if (rangeNumberValue.IsTokenized)
    {
       rangeNumberValue = rangeNumberValue.Resolve();
       return new SingleNumberValue($id$, rangeNumberValue.LowerBoundaryValue)
    }
    else
    {
      return new SingleNumberValue($id$, rangeNumberValue.LowerBoundaryValue)
    }
    

    Attached Parameter Information (from)
    Attached Parameter Information (from)

    Click Create parameter and select the stored procedure's @to parameter under Existing Parameters. The following script placed in the Script field returns the number selected as the end of the range:

    RangeNumberValue rangeNumberValue = (RangeNumberValue)$input$;
    if (rangeNumberValue.IsTokenized)
    {
       rangeNumberValue = rangeNumberValue.Resolve();
       return new SingleNumberValue($id$, rangeNumberValue.UpperBoundaryValue)
    }
    else
    {
      return new SingleNumberValue($id$, rangeNumberValue.UpperBoundaryValue)
    }
    

    Attached Parameter Information (to)
    Attached Parameter Information (to)

    Create or edit a dashboard, and drag the Sales measure and the Location hierarchy to the dashboard canvas from your data cube in the Explore window.

    Add a Range Number parameter filter and connect it to your bridge parameter.

    Connect the filter.
    Connect the filter.

    Switch to View mode and select from and to numbers.

    Result after selecting from and to.
    Result after selecting from and to.

    3. Notes

    • When you create a bridge parameter and define the expression for the Attached Parameter, it will take the Default Token Value from the value indicated for the selected parameter. After the Attached Parameter has been created, the Default Token Value will take precedence over the value for the selected parameter (changing the selected parameter value will not change the data preview).

    4. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri