Hyperion Interactive Reporting Learning Series Adding Interactive Pivots and drill down/up functionality

Adding Interactive Pivots


drill down/up functionality

In this series we’ll talk about how to add the interactive pivots. Pivots are cross tabular representation of data and provide rich data analysis view of data. One can drill down, Drill Up and drill across and navigate the data from different point of view. Pivot consists of Page, Row and Column layouts.

There are several advantages to using pivot tables. One advantage is that a pivot table summarizes the data contained in a long list into a compact format. A pivot table can also help one find relationships within the data that are otherwise hard to see because of the amount of detail. Yet another advantage is that a pivot table organizes the data into a format that is very easy to convert into charts.

In the last post I have shown you how to create dashboards. In the same series we’ll see how to add pivot to the existing report bqy and subsequently add to dashboard.

The first step is to create a pivot in add the pivot and bind it so some query. To create a pivot select Make sure you select the correct query, you want to bind your pivot with. In the below example I want to associate my pivot with Revenue by sales.

I want to provide the drill down and drill up functionality to the pivot so I’m modifying my report request line to add some new columns. The drill down depends on the dimension hierarchy chosen while designing the data model.

The below new column are added in order to provide the drill down functionality

Select Dollars in Facts, Sales Person Name in Row Labels

Once you apply the Row Labels and facts the pivot looks like this way.

As we added two new columns to facilitate the business to drill the Sales person details for specific district and region wise.

Once you create all four pivots

a)       Revenue by Sales Person.

b)       Revenue by Customer.

c)       Revenue by Products

d)       Revenue by region.

There is another option in IR to create pivot directly from chart.

Insert->Pivot This Chart

QRevenue_By_Customer                 PRevenue_By_Product                      PRevenue_By_Region

Now I’m going to add this Pivot to my Dashboard and want to give a complete integrated look to my Dashboard.

I’ll add a new Dashboard and place all these Pivot into the dashboard, finally with script I’ll enable users to select the Pivot and Chart view

1)       InsertàNew Dashboard

2)       Drag the Pivot from section and place them into Dashboard.

3)       Make the pivot interactive to the user by selecting

The Final look of the report.

Let’s add some script to the dashboard and allow users to select whether they want to see data in Chart or in Pivot format. Finally we’ll allow users to export the report in the desire format.

Now I want to display chart or pivot based on user request. I’ll place one control in dashboard and allow users to see the report layout based on user request. The diagram is in given diagram show Object hierarchy. We need to place this script into given user define control script part.

So now users can toggle between chart and pivot view.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s