Using a bridge parameter

Contents[Hide]

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

1. Overview

You can use a bridge parameter to promote a placeholder to a hierarchy. Placeholders, or 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

2.1. Multi-Level Hierarchy with Single Selection

This will allow you to select a single member on the lowest level of the multi-level hierarchy filter.

Output - A member from the lowest level can be selected from the hierarchy filter.
Output - A member from the lowest level can be selected from the hierarchy filter.

Steps:

  1. Setup the Stored procedure. Sample below is created based on AdventureWorks. @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
    

    Sample Stored Procedure
    Sample Stored Procedure

  2. Setup the hierarchy.
    1. Create a new Hierarchy.
    2. In this sample, drag and drop the Products table to the Hierarchy Designer, and rename to ProductHierarchy (optional).

      ProductHierarchy
      ProductHierarchy

  3. Setup the Data Cube.
    1. Create a new Data cube.
    2. Drag and drop the sp_ProductID_Hierarchy stored procedure to the canvas.

      Sample Stored Procedure
      Sample Stored Procedure

    3. Select the Stored Procedure transform node and from the toolbar, select Configure under Bridge Parameters.

      Configure
      Configure

    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 and drag and drop the ProductHierarchy to the drop region.

      Drag and drop the hierarchy
      Drag and drop the hierarchy

    6. Click Create parameter to open up the Parameter Information screen.

      Create Parameter
      Create Parameter

    7. Tick the parameter box under the Existing Parameters section.

      Parameter Information
      Parameter Information

    8. Use the script below in the Expression section:
      // 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#)

      Note: Once you're done with this example, you can try to debug the member filter using the Browser Developer Tools. This will give you an idea on why in the script, there is a line that removes ".C" from the UniqueName, and returning a number. The reason behind these is that the key of the hierarchy level is the ProductID, and the auto-generated Unique Name of the level as seen from the bridge parameter setup is "C". In this case, "776.C" is the uniqueName when "Mountain-100 Black, 42" is selected. The string you have to remove from the unique name varies depending on how your hierarchy is set up. Therefore, in order to know what to remove, you will need to use a debugger first to determine what the unique name of the parameter value is that is sent to the bridge parameter.

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

      Hierarchy using ID as key
      Hierarchy using ID as key

      Product level Unique Name
      Product level Unique Name

  4. Setup 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 View Parameter dialog, you can leave the defaults as is. Make sure the @ProdID member is checked.

      View Parameter connection
      View Parameter connection

  5. Testing:

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

    Select lowest level member
    Select lowest level member

  6. Result

    Result
    Result

2.2. Single-Level Hierarchy with Multiple Selection

This will allow you to select multiple members from a single-level hierarchy filter.

Output - Multiple members can be selected at the same time form the hierarchy filter.
Output - Multiple members can be selected at the same time form the hierarchy filter.

Steps:

  1. Setup the hierarchy.
    1. Create a new single-level Hierarchy.
    2. In this sample, drag and drop the [Person].[CountryRegion] table to the Hierarchy Designer.

      CountryHierarchy
      CountryHierarchy

    3. Rename the hierarchy to CountryHierarchy.
    4. Rename the first level of the hierarchy to Country
    5. Delete all levels except the first level
    6. Set the Key to Name.

      Setup Hierarchy
      Setup Hierarchy

    7. Preview:

      Single Level Hierarchy
      Single Level Hierarchy

  2. Setup the Data Cube.
    1. Create a new Data Cube.
    2. In this sample, use the Manual Query below:
        SELECT B.[Name] as 'Country',
               SUM(A.[SalesYTD]) as 'SalesYTD'
        FROM [AdventureWorks 2012].[Sales].[SalesTerritory] A
        INNER JOIN [AdventureWorks 2012].[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, here is a sample function used in the manual query 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
          
    3. Setup the Placeholder. This is required if you're using manual query. See Section 2.1 from this article.

      Placeholder
      Placeholder

    4. Setup Bridge Parameter.
      1. Click Add parameter and define the Bridge Parameter settings:
        • Name: @country
        • Description: country
        • Public: Yes
        • Value Type: Collection Member

        Bridge Parameter configuration
        Bridge Parameter configuration

      2. Expand the Hierarchies section and drag and drop the CountryHierarchy to the drop region.

        Drag and drop the hierarchy
        Drag and drop the hierarchy

      3. Click Create parameter to open up the Parameter Information screen.

        Create parameter
        Create parameter

      4. Use the bridge parameter script below:
        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. Testing: Select Canada and France from the dropdown checkbox list:

    Select Canada and France
    Select Canada and France

  4. Result:

    Result
    Result

2.3. Inverted Selection

To handle inverted selection, you need to check the parameter property IsInverted, then pass this to your query as additional placeholder/parameter.

Example: Retrieve everything except Canada and France:

Sample of inverted selection
Sample of inverted selection

Sample 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

Bridge parameter script:

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

CollectionMemberValue memberValue = (CollectionMemberValue)$input$.Resolve();
var myString = memberValue.IsInverted;
return new SingleBooleanValue ($id$, myString);

Result:

Result
Result

2.4. MDX using Single-Level Hierarchy and Single Selection

This uses an MDX with a placeholder, and will allow you to select a single member from a single-level hierarchy. 

Steps:

  1. Setup a single level hierarchy:

    Setup Hierarchy
    Setup Hierarchy

    Single Level Hierarchy
    Single Level Hierarchy

  2. Setup the Data Cube.
    1. Create a new Data Cube.
    2. Use the Manual MDX Query below.
      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]
      
    3. Setup the placeholder. This is required if you're using manual mdx query. See to Section 4 from this article.

      Sample Placeholder below. Default value is set to [Customer].[Customer Geography].[Country].&[Canada]:

      Sample Placeholder
      Sample Placeholder

    4. Setup Bridge Parameter.
      1. Click Add parameter and define the Bridge Parameter settings:
        • Name: @country
        • Description: country
        • Public: Yes
        • Value Type: Single Member
      2. Use the bridge parameter script below:
        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);
        
  3. Testing: On the filter, select Germany in the dropdown.

    Select Germany
    Select Germany

  4. Result:

    Result
    Result

2.5. Single Selection on either Parent or Child level

This will allow you to select a single member from a multi-level hierarchy filter.

Output - Member from any level can be selected from the hierarchy filter, but only one at a time.
Output - Member from any level can be selected from the hierarchy filter, but only one at a time.

Steps:

  1. Sample Data:

    Sample Data
    Sample Data

  2. Create a Stored procedure. Two parameters are used in this sample:
    • @level : Level of the selected member in the hierarchy filter.
    • @name : Caption name of the selected member. Note that this could be 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. Setup the hierarchy.
    1. Create a new Hierarchy.
    2. In this sample, create a two-level hierarchy, with parent level as Continent and lowest level asCountry.

      Continent level
      Continent level

      Conuntry level
      Conuntry level

      Hierarchy preview
      Hierarchy preview

  4. Setup the Data Cube.
    1. Create a new Data cube.
    2. Drag and drop the sp_Country_Sales stored procedure to the canvas.
    3. Select the Stored Procedure transform node and from the toolbar, select Configure under Bridge Parameters.
    4. Click Add parameter and define the Bridge Parameter settings:
      • Name: @location
      • Description: location
      • Public: Yes
      • Value Type: Single Member
    5. Expand the Hierarchies section and drag and drop the location to the drop region.

      Drag and drop the hierarchy
      Drag and drop the hierarchy

    6. Click Create parameter to open up the Parameter Information screen.
    7. Tick the parameter box @level under the Existing Parameters section and add script below:

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

      Your Stored procedure should reflect the correct Unique Name generated.

      SingleMemberValue memberValue = (SingleMemberValue)$input$.Resolve();
      var myString = string.Empty;
      myString = memberValue.Value.LevelUniqueName;
      return new SingleStringValue ($id$, myString);
      
    8. Tick the parameter box @name under the Existing Parameters section and add script below:

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

      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);
      
  5. Setup the Dashboard.
    1. Expand the Data Cube, drag and drop the Sales measure and the Location dimension to the dashboard canvas.
    2. Add a Member Hierarchy filter control and connect to the @location parameter.

      Add filter control
      Add filter control

  6. Testing:
    1. View the dashboard, click and expand the hierarchy filter control and select Asia from the top level. Result should show only records for Asia.

      Select top level member
      Select top level member

    2. Select USA from the bottom level.

      Select bottom level member
      Select bottom level member

2.6. Using Date Parameters

This sample will show you how to use a Range Date filter and pass the values to your Stored Procedure parameters.

Output - Date filters can be used and selected values are passed to the Stored Procedure parameters.
Output - Date filters can be used and selected values are passed to the Stored Procedure parameters.

Steps:

  1. Sample Data:

    Sample Data - using <em>SalesOrderDetail</em> table
    Sample Data - using SalesOrderDetail table

  2. 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	
  3. Setup the Data Cube.
    1. Create a new Data cube.
    2. Drag and drop the sp_SalesOrderDetail_Date stored procedure to the canvas.
    3. Select the Stored Procedure transform node and from the toolbar, select Configure under Bridge Parameters.
    4. Click Add parameter and define the Bridge Parameter settings:
      • Name: DateParams

        In this sample, the Stored Procedure has 2 input parameters. However, we only need to create one Bridge Parameter. This Bridge Parameter will be using a RangeDate/Time type parameter.

      • Description: DateFrom and DateTo
      • Public: Yes
      • Value Type:
        • RangeDate/Time - In order to use Range Date filter control in the dashboard.
        • Range Member - In order to use Calendar Range filter control in the dashboard.
    5. Click Create parameter to open up the Existing Parameters section.
    6. Tick the parameter box @DateFrom under the Attached Parameter Information screen, add script below and submit the changes:

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

      return new SingleDateTimeValue($id$, $input$.LowerBoundaryValue)
      
    7. Tick the parameter box @DateTo under the Attached Parameter Information screen, add script below and submit the changes:

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

      return new SingleDateTimeValue($id$, $input$.UpperBoundaryValue)
      
  4. Setup the Dashboard.
    1. Expand the Data Cube, drag and drop 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.

  5. Testing: View the dashboard, from the range date filter, select a start (From) and end (To) dates. Result should show only records between those 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