Dynamic Grouping and Columns
- by Tim Dexter
Some good collaboration between myself and Kan Nishida (Oracle BIP Consulting) over at bipconsulting on a question that came in yesterday to an internal mailing list.
Is there a way to allow columns to be place into a template dynamically? This would be similar to the Answers Column selector. A customer has said Crystal can do this and I am trying to see how BI Pub can do the same.
Example:
Report has Regions as a dimension in a table, they want the user to select a parameter that will insert either Units or Dollars without having to create multiple templates.
Now whether Crystal can actually do it or not is another question, can Publisher? Yes we can!
Kan took the first stab. His approach, was to allow to swap out columns in a table in the report. Some quick steps:
1. Create a parameter from BIP server UI
2. Declare the parameter in RTF template
You can check this post to see how you can declare the parameter from the server.
http://bipconsulting.blogspot.com/2010/02/how-to-pass-user-input-values-to-report.html
3. Use the parameter value to condition if a particular column needs to be displayed or not.
You can use <?if@column:.....?> syntax for Column level IF condition.
The if@column is covered in user documentation. This would allow a developer to create a report with the parameter or multiple parameters to allow the user to pick a column to be included in the report.
I took a slightly different tack, with the mention of the column selector in the Answers report I took that to mean that the user wanted to select more of a dimensional column and then have the report recalculate all its totals and subtotals based on that selected column. This is a little bit more involved and involves some smart XSL and XPATH expressions, but still very doable. The user can select a column as a parameter, that is passed to the template rather than the query. The parameter value that is actually passed is the element name that you want to regroup the data by. Inside the template we then reference that parameter value in our for-each-group loop. That's where we need the trixy XSL/XPATH code to get the regrouping to happen.
At this juncture, I need to hat tip to Klaus, for his article on dynamic sorting that he wrote back in 2006. I basically took his sorting code and applied it to the for-each loop.
You can follow both of Kan's first two steps above i.e.
Create a parameter from BIP server UI - this just needs to be based on a 'list' type list of value with name/value pairs e.g. Department/DEPARTMENT_NAME, Job/JOB_TITLE, etc. The user picks the 'friendly' value and the server passes the element name to the template.
Declare the parameter in RTF template - been here before lots of times right?
<?param@begin:group1;'"DEPARTMENT_NAME"'?>
I have used a default value so that I can test the funtionality inside the template builder (notice the single and double quotes.)
Next step is to use the template builder to build a re-grouped report layout. It does not matter if its hard coded right now; we will add in the dynamic piece next.
Once you have a functioning template that is re-grouping correctly. Open up the for-each-group field and modify it to use the parameter:
<?for-each-group:ROW;./*[name(.) = $group1]?>
'group1' is my grouping parameter, declared above. We need the XPATH expression to find the column in the XML structure we want to group that matches the one passed by the parameter. Its essentially looking through the data tree for a match.
We can show the actual grouping value in the report output with a similar XPATH expression
<?./*[name(.) = $group1]?>
In my example, I took things a little further so that I could have a dynamic label for the parameter value. For instance if I am using MANAGER as the parameter I want to show:
Manager: Tim Dexter
My XML elements are readable e.g. DEPARTMENT_NAME. Its a simple case of replacing the underscore with a space and then 'initcapping' the result:
<?xdoxslt:init_cap(translate($group1,'_',' '))?>
With this in place, the user can now select a grouping column in the BIP report viewer and the layout will re-group the data and any calculations based on that column. I built a group above report but you could equally build the group left version to truly mimic the Answers column selector.
If you are interested you can get an example report, sample data and layout template here.
Of course, you can combine Klaus' dynamic sorting, Kan's conditional column approach and this dynamic grouping to build a real kick ass report for users that will keep them happy for hours..
