Saturday, June 13, 2009

Brio Cell Double Click

Sometimes there is a requirement wherein users would like to see the detailed data by simply double clicking on a data cell value of a summary report. This feature is illustrated in the snapshots below.

Let's say this is a summary report in a dashboard showing total sales value for some particular product category.
Now for example if a user want to see the detailed breakup of value '250169.11' which is 3rd in the row.
Here the user just need to double click on the cell and the detailed breakup report would appear as shown below(This is just a portion of the detailed report).

Today we will see how we can do this in Brio or Hyperion Interactive Reporting.
Here the datasource for the summary report as well as the detailed report is same.
To perform this-
1. Create a query as usual by making a database connection and selecting tables. The request line items should contain all the columns that may be required in the summary report as well as in the detailed report.
2. Process the query to retreive the results.
3. Insert 'New Pivot' to Create a pivot having presenting summary level report which then will be displayed in a dashboard.
4. Insert 'Table report' to create a table report and select all the columns you want to display in detailed level report.
5. Insert 'New Report' to create a detailed report and here select the columns displayed under the table we just created. In the picture below, take the columns displayed under the 'Table1'.

6. Insert 'New Dashboard' and drag the 'Pivot1' we created for the summary report to this dashboard.
7. Under the design mode, Right click the pivot and select 'Scripts'.

8. Under 'Event Trigger', you will notice an event 'Cell Double Click'. Write the code in the main window to perform cell double click event. Please refer the sample code provided below and customise as per your object names and column names.
Sample Code:
SectionTable = ActiveDocument.Sections["Table1"]
SectionPivot = ActiveDocument.Sections["Pivot1"]
ReturnVal = 0ReturnVal = Application.Alert("Do You want to drill?", "Drill to Details?","Drill","Cancel")
if (ReturnVal == 1) {
SectionTable.Limits["Product Family"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Category"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Name"].SelectedValues.RemoveAll()
SectionTable.Limits["Product Publisher"].SelectedValues.RemoveAll()
SectionTable.Limits["Day Key"].SelectedValues.RemoveAll()
for (i = 1; i <= SectionPivot.SideLabels.Count; i++) {
if (SectionPivot.SideLabels[i].Name == "Product Category") {SectionTable.Limits["Product Category"].SelectedValues.Add(Pivot1.SideLabelValues[i])}
if (SectionPivot.SideLabels[i].Name == "Product Name") {SectionTable.Limits["Product Name"].SelectedValues.Add(Pivot1.SideLabelValues[i])}}
for (i = 1; i <= SectionPivot.TopLabels.Count; i++) {
if (SectionPivot.TopLabels[i].Name == "Product Category") {SectionTable.Limits["Product Category"].SelectedValues.Add(Pivot1.TopLabelValues[i])}
if (SectionPivot.TopLabels[i].Name == "Product Name") {SectionTable.Limits["Product Name"].SelectedValues.Add(Pivot1.TopLabelValues[i])}}
9. Now check the syntax and deselect the design mode and in the dashboard, double click a cell value and you would be able to see the detailed break up of the selected value.
Manohar Rana