Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted September 21, 2015

Creating Dashboards for Mobile Devices with Datazen – Part 4

By Arshad Ali

Introduction

With the recent acquisition of Datazen by Microsoft, if you have SQL Server 2008 Enterprise Edition or later with Software Assurance, you can leverage (download and use) Datazen to create visually appealing dashboards for mobile devices across all the major platforms at no additional cost.

In my first article, I talked about Datazen Architecture and its different components, different Datazen applications like Datazen Publisher for authoring and publishing dashboards and Datazen Viewer to view published dashboards on different platforms. In my second article, I demonstrated installation and configuration of Datazen Server. In my third article, I demonstrated creating data source, data views and then creating dashboards using Datazen Publisher. In this article I will demonstrate creating filters, publishing dashboards to the Datazen server and creating drill-through dashboards.

Creating Filters in the Dashboards

I discussed creating data views in the last article, follow those steps to create data views and publish it to the Datazen Server. Next create a dashboard and include the required data views from the specified Datazen server into your dashboard. Once you are done, your screen should look similar to what you see below:

Adding data views to your dashboard
Figure 1 - Adding data views to your dashboard

Now drag-drop a Selection List control from the left pane to the dashboard canvas and then specify its appropriate properties (related to how it appears) and click on the Data View icon the left top corner.

Adding a Selection List control and setting its properties
Figure 2 - Adding a Selection List control and setting its properties

In the data view screen, you can map all the datasets with the common column and then specify Key and Label for the Selection List control:

Mapping data views for filtering
Figure 3 - Mapping data views for filtering

Now drag another control to the canvas (I have used the Comparison Category Chart control) and then specify to relate it with the Selection List control as shown below:

Assigning a filter to the selected control
Figure 4 - Assigning a filter to the selected control

Now click on Run Preview icon on the top left pane to preview the dashboard. As you can see in the image below, “All” is selected for the filter and hence you can see data for all the dates available.

Filter control in action - Selected "All"
Figure 5 - Filter control in action - Selected "All"

In the next preview screen, I selected one specific date and hence you can see data for that specific date only (data gets filtered based on selection):

Filter control in action - Selected one specific filter value
Figure 6 - Filter control in action - Selected one specific filter value

Now, let me demonstrate how to use Time Navigator for browsing data based on dates. As you can see below, I dragged a Time Navigator control to the dashboard canvas and set the appropriate properties:

Adding a Time Navigator control to the dashboard
Figure 7 - Adding a Time Navigator control to the dashboard

Now select the Time Navigator control on the dashboard canvas and click on the Data View icon on the top left pane and then finally select the measure(s) or value(s) that you want to see in the Time Navigator control, as shown below:

Specifying values to be shown in Time Navigator control
Figure 8 - Specifying values to be shown in Time Navigator control

The Time Navigator control automatically detects the column of date data type. Now click on the control which you want to link to Time Navigator control; in my case I chose Comparison Category Chart and then clicked on Data View icon. Finally, select the Time Navigator control as a filter for that selected control, as shown below:

Adding Time Navigator control to other chart control
Figure 9 - Adding Time Navigator control to other chart control

Now, let’s preview the dashboard with these changes. As you can see, I selected “All” in Time Navigator control and I could see data for all the periods in the Comparison Category Chart control on the bottom.

Time Navigator in action - multiple selection
Figure 10 - Time Navigator in action - multiple selection

As you can see below, I have now selected one specific month in the Time Navigator control and accordingly data gets refreshed to show data for that specific period.

Time Navigator control in action - single selection
Figure 11 - Time Navigator control in action - single selection

That’s not all, Datazen lets us create cascading filters; for example, you can have Region as a top level filter, Sub Region as the next level filter and then Country as the next level and so on:

Adding a cascading filters in dashboard
Figure 12 - Adding a cascading filters in dashboard

As Datazen allows creating dashboards for a variety of devices, you can define different layouts for different devices (Computer, Tablet and Phone). To do that, you need to click on the Layout icon on the top right corner as shown in the image below:

Different layouts for different devices
Figure 13 - Different layouts for different devices

Datazen has several inbuilt themes to quickly give a different look and feel (color combination) for the dashboard; you need to click on the theme icon on the top right corner, as shown in the image below, to change the theme for your dashboard.

Different themes to choose from
Figure 14 - Different themes to choose from

Once you are done with changes, you can click on Run Preview to preview your dashboard for the new layout (Phone in this case) as shown below:

Previewing how a dashboard would look like on different device
Figure 15 - Previewing how a dashboard would look like on different device

Creating Drill-through Dashboards

Datazen makes creating dashboards with drill-through capabilities much easier. First of all, you need to create a child dashboard and then create the parent dashboard. Now click on the control (on which drill-through should work) in the parent dashboard and click on the Drill-through Target link as shown below:

Creating drill-down for dashboards
Figure 16 - Creating drill-down for dashboards

Click on Dashboard and then select the child dashboard on which you want to navigate from your parent dashboard, as shown below:

Picking up child dashboard to drill-down to
Figure 17 - Picking up child dashboard to drill-down to

On the next screen, you need to specify parameters mapping between parent and child dashboards’ control so that when you reach to the child dashboard from the parent dashboard after drill-through, you can see data for the selection you have made on the parent dashboard.

Mapping parameter values between parent and child dashboards
Figure 18 - Mapping parameter values between parent and child dashboards

While browsing the dashboard in the Run Preview mode, drill-through does not work, as shown below, and hence you need to save your changes, return back to home and then click on the parent dashboard there. At this time, you can click on the drill-through enabled control to test the drill-through functionality.

Drill-down preview does not work in designer preview
Figure 19 - Drill-down preview does not work in designer preview

For example, in the image below when I click on the control (for which I have enabled drill-through), it takes me to the child dashboard:

Testing the drill-down capability
Figure 20 - Testing the drill-down capability

As you can notice in the next image (the child dashboard), data gets filtered based on selection and parameter values passed to the child dashboard from the parent dashboard:

Child dashboard brought up after clicking on parent dashboard
Figure 21 - Child dashboard brought up after clicking on parent dashboard

Please note, when you are deploying your dashboards to the Datazen server, you need to set the references accordingly or else it will still look for the child dashboard in the local folder and will fail. I mean, first you need to publish your child dashboard, update your parent dashboard to refer to the child dashboard from the Datazen server (and not from local working folder) and then finally publish the parent dashboard to the Datazen server.

Publishing Dashboard to the Datazen Server

Once you are done with your development, you can publish your dashboards to the Datazen server to share it with others. To do that, right click on the dashboard canvas, first save the dashboard locally and then click on Publish to Server icon as shown below:

Saving and Publishing dashboard
Figure 22 - Saving and Publishing dashboard

On the Publish to Server wizard, you need to select the Datazen server on which you want to deploy the dashboard (I talked about connecting to Datazen server in this article) and click on the Publish button as shown below:

Publishing dashboard to the Datazen Server
Figure 23 - Publishing dashboard to the Datazen Server

Once deployed, you can login to the Datazen Server and verify dashboard deployment as shown below:

Viewing published dashboard on the Datazen Server
Figure 24 - Viewing published dashboard on the Datazen Server

Once you have published your dashboards, you or others (with required permissions) can view them using the Datazen viewer or using a browser as shown below:

Viewing published dashboard in browser - with multiple selections
Figure 25 - Viewing published dashboard in browser - with multiple selections

While browsing even in a browser, it works much the same way as it works in Datazen viewer as shown below. I selected one specific month in the Time Navigator control and you can see data was filtered based on that selection, as shown below:

Viewing published dashboard in browser - with single selection
Figure 26 - Viewing published dashboard in browser - with single selection

Conclusion

Datazen enables dashboards and KPIs creation based on data from a variety of data sources and publishes it to Datazen Server. After publishing, dashboards and KPIs are accessible on any mobile device via its native app, or through any major browser if the required access has been provided.

In this article of the series, I demonstrated creating filters, publishing dashboards to the Datazen server and creating drill-through dashboards.

Resources

Creating Dashboards for Mobile Devices with Datazen – Part 1

Creating Dashboards for Mobile Devices with Datazen – Part 2

Creating Dashboards for Mobile Devices with Datazen – Part 3

Datazen Server Download

Datazen Publisher and Client Applications download

Datazen Product Documentation

See all articles by Arshad Ali



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM