Using a bridge parameter

Contents[Hide]

This is an advanced topic for developers who are familiar with C# and stored procedures/sql/mdx.

Tip
You can use #TRACE  statements to debug your bridge parameter scripts.
 

1. Overview

You can use a bridge parameter to promote a placeholder to a hierarchy. Placeholders, sometimes called External Filters, are generally used as parameters in a Stored Procedure, Manual SQL, or Manual MDX. This can be useful if you want to use a hierarchy member filter in the dashboard.

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 AdventureWorks 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 AdventureWorks 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. Set up the hierarchy:
    1. Create a new hierarchy.
    2. Drag the [Production].[Products] table to the hierarchy designer and rename to ProductHierarchy.

      Product hierarchy
      Product hierarchy

  3. Set up the data cube:
    1. Create a new data cube.
    2. Drag the sp_ProductID_Hierarchy stored procedure to the canvas.

      Sample stored procedure
      Sample stored procedure

    3. Select the Stored Procedure node and click Bridge Parameters on the toolbar.

      Bridge Parameters
      Bridge Parameters

    4. Click Add parameter and define the Bridge Parameter settings:
      • Name: @ProdID
      • Description: Product ID
      • Public: Yes
      • Value Type: Single Member
    5. Expand the Hierarchies section, expand the ProductHierarchy hierarchy, and drag to the drop region.

      Drag and drop the hierarchy
      Drag and drop the hierarchy

    6. Click Create parameter and tick the ProdID checkbox 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).

    7. Use the following expression in the Script field:
      // 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 server-side script (c#)
      Adding the server-side script (c#)

      Important
      The backslash (\) and period (.) characters are escaped in bridge parameter scripts. If your data contains one of these characters, consider using a different key for your hierarchy or you may have to adjust your script to replace these characters.

    8. Select a Test Value and click Test Script.
      Testing the bridge parameter script
      Testing the bridge parameter script

      Note
      This script removes .C from the UniqueName. The string you have to remove from the unique name varies depending on how your hierarchy is set up. Once you are done with this example, you can try to debug the member filter using the Browser Developer Tools to determine the UniqueName of the parameter value sent to the bridge parameter.

      Debugging from the Parameter Value Changed event
      Debugging from the Parameter Value Changed event

  4. Set up the dashboard:
    1. Expand the Data Cube, drag and drop the OrderQty measure to the dashboard canvas.

      Drag and drop measure
      Drag and drop measure

    2. Add a Member Hierarchy filter control.

      Add filter control
      Add filter control

    3. On the Filter Visualizations dialog, make sure the @ProdID member is checked.

      View Parameter connection
      View Parameter connection

  5. View the dashboard, click and expand the hierarchy filter control to the lowest level. Select Mountain-100 Black, 42.

    Select lowest level member
    Select 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. Create the table [dbo].[Sample_data] with the following sample data:

    Sample Data
    Sample Data

  2. Create a stored procedure. Two parameters are used 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 two-level hierarchy with the parent level as Continent and child level as Country.

    Continent level
    Continent level

    Country level
    Country level

    Hierarchy preview
    Hierarchy preview

  4. Set up the data cube:
    1. Create a new data cube.
    2. Drag the sp_Country_Sales stored procedure to the canvas.
    3. Select the Stored Procedure transform node and click Bridge Parameters on the toolbar.
    4. Click Add parameter and define the bridge parameter settings:
      • Name@location
      • Descriptionlocation
      • PublicYes
      • Value TypeSingle Member
    5. Expand the Hierarchies section, expand the sample location hierarchy you have created and drag the location hierarchy to the drop region.
    6. Click Create parameter and tick the @level checkbox under the Existing Parameters section.
    7. Use the following expression in the Script field:
      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

      Your stored procedure should reflect the correct Unique Name generated.

    8. Tick the @name checkbox under the Existing Parameters section and use the following expression in the Script field:
      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);
      

      Set script for <em>@level</em> parameter
      Set script for @level parameter

  5. Set up the dashboard:
    1. Drag the Sales measure and the Location dimension from the new data cube to the dashboard canvas. Note that only North America results are shown, as was indicated in the stored procedure.
    2. Add a Member filter control and connect to the @location parameter. If this is the first filter on this dashboard, you may have to unselect the default connection to see this parameter.

      Add filter control
      Add filter control

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

    Select parent level member
    Select parent level member

  7. 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, you use the bridge parameter to select multiple members from a single-level hierarchy filter:

  1. Set up the hierarchy:
    1. Create a new hierarchy.
    2. Drag the [Person].[CountryRegion] table to the hierarchy designer and rename to CountryHierarchy.
    3. Rename the first level of the hierarchy to Country.
    4. Delete all levels except the first level.
    5. Set the Member Key Source to Name.

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

    6. Preview:

      Single-level hierarchy
      Single-level hierarchy

  2. Set up the data cube:
    1. Create a new Manual Query data cube.
    2. Select the Adventure Works data connector.
    3. 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
      
    4. Set up a $country$ placeholder. For more information on setting up a placeholder for a manual select, see the article on Using Manual Select placeholders.

      Placeholder
      Placeholder

    5. Set up the bridge parameter:
      1. Click Bridge Parameters, then Add parameter and define the bridge parameter settings:
        • Name: @country
        • Description: country
        • Public: Yes
        • Value Type: Collection Member
      2. Expand the Hierarchies section, expand the CountryHierarchy hierarchy, and drag it to the drop region.

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

      3. Click Create parameter and tick the country checkbox under the Existing Parameters section.
      4. Use the following expression in the Script field:
        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);
        
  3. 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

  4. 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 then pass this to your query as an additional parameter.

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. On the Define Bridge Parameter panel, tick the parameter checkbox for the @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. Set up the data cube:
    1. Create a new Manual Query data cube.
    2. Select the Adventure Works OLAP data connector.
    3. 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]
      
    4. Set up a $country$ placeholder. For more information on setting up a placeholder for a manual select, see the article on Using Manual Select placeholders.

       Set the string value to [Customer].[Customer Geography].[Country].&[Canada]:

      Sample Placeholder
      Sample Placeholder

    5. Set up the bridge parameter:
      1. Click Bridge parameter, then Add parameter and define the bridge parameter settings:
        • Name: @country
        • Description: country
        • Public: Yes
        • Value Type: Single Member
      2. Expand the Hierarchies section, expand the CountryHierarchy hierarchy, and drag it to the drop region.
      3. Click Create parameter and tick the country checkbox under the Existing Parameters section.
      4. Use the following expression in the Script field:
        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);
        
      5. 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 bridge parameters to select a range of dates:

  1. Create a Stored procedure. Two parameters are used in this sample:
    • @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
    
  2. Set up the data cube:
    1. Create a new Data cube.
    2. Drag the sp_SalesOrderDetail_Date stored procedure to the canvas.
    3. Select the Stored Procedure and click Bridge Parameters on the toolbar.
    4. Click Add parameter and define the bridge parameter settings:
      • Name: DateParams
      • Description: DateFrom and DateTo
      • Public: Yes
      • Value Type: RangeDate/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.

    5. Click Create parameter and tick the @DateFrom checkbox under the Existing Parameters section. Use the following expression in the Script field:
      return new SingleDateTimeValue($id$, $input$.LowerBoundaryValue)
      

      Set script for <em>@DateFrom</em> parameter
      Set script for @DateFrom parameter

    6. Tick the @DateTo checkbox under the Existing Parameters section and use the following expression in the Script field:
      return new SingleDateTimeValue($id$, $input$.UpperBoundaryValue)
      

      Set script for <em>@DateTo</em> parameter
      Set script for @DateTo parameter

  3. Set up the dashboard:
    1. Drag the LineTotal measure and the ModifiedDate hierarchy to the dashboard canvas.
    2. Add a Range Date (or Calendar Range) filter control and connect to the DateParams bridge parameter.

      Parameter binding.
      Parameter binding.

  4. Switch to View mode. Select a start (From) and end (To) dates.

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

3. Notes

  • If both the bridge parameter and the data cube element parameter are set to Public, the metric set inherited parameter will rely on the default settings on the bridge parameter and ignore the settings on the element parameter. To avoid this situation, set the data cube bridge parameter to private by unchecking its Public checkbox.

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