Tech Tip: Controlling Break Dimension Using Input Control

Related Articles

Saving the Planet with Squirrel365

May 3, 2023

Lessons on Co-Existence from the Philadelphia Zoo

April 13, 2023

Ancient Egypt and BusinessObjects – Both Light Years Ahead

February 15, 2023

In this tech tip, you will learn how to dynamically switch a break dimension using an input control.  We will start with a cross table that was created in the Alternate Break Value Highlighting tech tip, but you can substitute any 2 dimensions you would like to work with.  This will be accomplished by creating a few variables and an input control.

Here is a look at what we will set up.  However, if you would like to see how to apply row highlighting like you see below, please see the Alternate Break Value Highlighting tech tip.

To begin, add at least 2 dimensions to a table or cross table block.  I used a cross table above with a case count in the body.  Next, we need to add a break on the 1^st column in the block.  Highlight the data in the 1^st column (in this example it is Case Substatus) and click Break on the Analysis/Display menu.

After applying the break, we can manage the break to change the way the duplicate values appear and remove the break footer.  Use the Break drop down and select Manage Breaks.

De-select the Break footer and select Merge from the Duplicate values drop down.

After creating the block, we need to add an input control, which we will use to control the order of the columns in the block.  We will use the literal values of “Case Substatus” and “Reason Code”.

• Since these values don’t exist in the database, we will create a variable named Break Selector as a dimension with the formula =”Case Substatus”.

• Create a new Input Control and select the Break Selector dimension we just created.

If you do not see the Input Control panel on the left side, click the icon below to enable input control definition and interaction.

• Select one of the Simple Selection options.  I choose Radio Buttons in this example.

• Create a custom list of values in the Input Control Properties by clicking the ellipsis (3 dots) to the right of the “List of values” option.

• Move the value “Case Substatus” that was created in the variable named Break Selector to the right by highlighting and clicking the arrow pointing to the right or by double clicking on the “Case Substatus” value. Add the value “Reason Code” by typing the value in the “Type values here” box, just above where it displays “Break Selector”, and clicking the arrow pointing to the right (or you can press the enter key).

The list of values should now look like this:

• Click OK after you have completed adding both values to the input control. Back in the Input Control Properties screen, uncheck the “Allow selection of all values” box, as we only want to allow selection of one option or the other.
• We will need to set a default value for the input control since we are not allowing selection of all values. Click the ellipsis next to Default value(s) and add “Case Substatus” as the default.

• If you have more than one block on the report, then you may also need to select the block that you want controlled by the input control as a dependency. As a best practice, I recommend always naming the block with a meaningful name to aid in identification.  This can be accomplished by changing the Name in the Format Table/General dialog box.

• Click Finish to complete set up of the input control. Be sure to note the dimension name (Break Selector) and both labels used as values (Case Substatus and Reason Code) in the input control, as we will need exact references when building the variables.

Next we will create the following variables.  These will all use the result of the input control on the dimension Break Selector to change the order of the dimensions and the column headers.

• Break Dim

=If [Break Selector] = “Case Substatus” Then [Case Sub Status] Else [Reason Code]

• Second Dim

=If [Break Selector] = “Case Substatus” Then [Reason Code] Else [Case Sub Status]

• Column Header 1

=If [Break Selector] = “Case Substatus” Then “Case Substatus” Else “Reason Code”

• Column Header 2

=If [Break Selector] = “Case Substatus” Then “Reason Code” Else “Case Substatus”

If you have an existing block with at least 2 dimensions, we need to swap the references from the existing objects to the variables we created above.  Modify the block that was created earlier by replacing:

1^st column header =[Column Header 1]

2^nd column header =[Column Header 2]

1^st column data =[Break Dim]

2^nd column data =[Second Dim]

 

The structure only view for the block (under the Design drop down on top right menu) looks like this now.

Return to the “With Data” view by using the Design drop down on the top right menu again.  You should now be able to use the Break Selector input control to set the break on either Case Substatus or Reason Code!

If you would like to have the alternate break value shading applied to your block, and you previously went through the Alternate Break Value Highlighting tech tip, then it is as simple as changing the Break Highlight syntax that was created in those steps.  The original syntax hardcoded the Case Sub Status dimension and we just need to swap out for the Break Dim variable we created above.

Break Highlight original syntax

=Even(RunningSum(If(RunningCount([Case Sub Status];([Case Sub Status])) > 1; 0 ; 1)))

 

Break Highlight new syntax

=Even(RunningSum(If(RunningCount([Break Dim];([Break Dim]))>1;0;1)))