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


Mahasweta Das said...

Hi Manohar,
Will you please help me out?
I need some help on QlickView.
PLease send me your mail id ,which will be helpful for me,if you dont mind.
Thanks & Regards,

madhukar said...

Hi Manohar,

I have seen good information on ur blog for interactive reporting. I need some help on dashboard section w.r.t dates. I want to see the reports from date to till date. how is it possible in dashboard section by giving options like from date and till by selecting the dates i need to get results..please help me on this issue..


Alvaro said...

Hi Manohar,

I am using Hyperion Intelligence Designer version 8.5 and I have to fill up a combobox with a data set that I´ve got from a select command. How can I do that ?

Any help is appreciated.

Thank you very much.