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
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.
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.