September 25, 2014 1 Comment
This blog post is inspired by a recent customer request to challenge their decision to use Design Studio for some “dashboard requirements”. Showing how you can create a dashboard in Webi doesn’t mean I told the customer not to use Design Studio. Much more it is to show that finally a dashboard as well as every other type of BI front end solution is made up of requirements and not primarily by the tool you build the solution. Please refer to my Generic Tool Selection Process for more details as well as my post regarding BI specific requirements engineering.
Having said this, let’s have a look at how we can use latest Webi 4.1 features to quickly build an interactive dashboard without the need of (much) scripting. First of all here is what the final result looks like:
You can select values from the left side bar (Product Lines), you can select States by directly clicking into the table and you can switch from the bar chart to a line chart. Here you see it in action:
The first step to achieve this, is to create the basic table and the two charts. Until the dynamic switch is implemented, I placed them side by side. Next add a simple input control in the left side bar:
Next thing is to define the table as an additional input control – right click the table and choose “Linking” and “Add Element Link”, choose the two chart objects as dependencies:
Next we need to create the “switch” to toggle the two charts. As I would like to position this switch at the top right corner of the chart, I again use a table input control. To generate the two necessary table values (namely “Bar Chart” and “Line Chart”) I prepared a simple Excel spreadsheet:
In 4.1 you can now finally upload this sheet directly into the BO repository:
If you need to update the Excel sheet later on, this is now feasible as well:
Finally, in Webi add the Excel sheet as a second query:
In the report we need now two tables: A visible one to represent the chart switch and a (hidden – see the “Hide always” option) dummy table to act as a dependency for the first:
The most tricky part is to create a variable to retrieve the selected value:
Here the formula for copy / paste:
=If( Pos(ReportFilterSummary(“Dashboard”);”Chart Type Equal “) > 0)
Then Substr(ReportFilterSummary(“Dashboard”);Pos(ReportFilterSummary(“Dashboard”);”Chart Type Equal “) + Length(“Chart Type Equal “);999)
Else “Bar Chart”
(The idea for this formula I grabed from David Lai’s Blog here)
Finally you need to configure the hide formula for both charts:
Positive: I’m not too technical anymore (I do more paperwork than I wish sometimes…). Therefore I don’t consider me a “developer” and I like solutions for the so called “business (power) user” more and more. Therefore I like Webi. It took me about 60 minutes to figure out how to create this kind of interactive dashboard. I didn’t need to install anything – I could do everything web based. Except for one single formula (which I didn’t need to write myself) I could click together the above sample. And I dare to say it looks like some kind of a dashboard :-) In addition I have all the basic features of Webi like a broad range of data source support, plenty of export possibilities, Office integration and so on. Even integrating an Excel spreadsheet as a data source is now finally a no-brainer.
Negative: Clearly, Webi is not a “design tool”. For example I wasn’t able to show icons for my chart switch instead of the text lables. Putting a background image to the table doesn’t work well if the table is used as input control. When I discussed this prototype with the customer they also mentioned that there are still too many options end users might get confused with (e.g. that there is a “filter” section showing whether the Bar Chart or the Line Chart value is chosen). In Webi you can’t change that. Toolbars, tabs etc. are just there where they are. Live with it or choose a different tool.
Bottom line: Have a look at my Generic Tool Selection Process and the mentioned hands-on test. The above example is exactly what I mean with this: Create a functional prototype in one or two tools and then do a fact based decision depending on your requirements and end user expectations.
Important remark: This post focused on the technical aspect of the dashboard. The visual representation doesn’t yet fit to best practices mentioned in my earlier articels (e.g. about SUCCESS) In a next blog post I will outline how to optimize the existing dashboard in this regard.