Category: Excel

Tonight’s post came out of a conversation I had with my friend Chris here at Microsoft (yes, we’re both Chris).  I was looking to see if I could drillthrough from a map of the United States into each individual state, and see the county-level information of another Datazen dashboard.  The answer is – yes, kindof!

Why the caveat?  Because I want to dynamically have the state change based on which one I select for drillthrough.  You can’t do that by drilling from one dashboard to another inside the Datazen app.  But you can do it by using the web viewer functionality in Datazen for your lower level dashboard.  Let me show you what I did.

First, in the Datazen Designer, I dragged a single Range Stop Heat Map onto the canvas and renamed it accordingly.

Next, I used Power Query in Excel to search online and find the state by state results for the 2012 Presidential Election.  I brought the results into an Excel file, and loaded the file into my Datazen dashboard under the “Data View” tab by choosing “Add Data” and “Local Excel File”.

I made “Obama” the Values selection and “Romney” the Targets selection.  It doesn’t really matter which one you choose for which for this example, however.  Then I flipped back to my layout view.

I changed the range stop values to show a very narrow range for Neutral Start/Neutral End (99.98 and 99.99), so unless you had another Florida situation, you’d only see two colors for our map.  At this point, I saved it and created a new map dashboard.  But instead of using the out of the box maps, I loaded a new custom map from my local hard drive by choosing “Custom Map From File” from my map selection list.  Here I loaded the Pennsylvania map files I had (here they are if you would like to use them.  Just save them locally and extract them to somewhere on your machine – http://1drv.ms/1H1PtLp).

Then I created a new map dashboard, doing all the same setup items I did for the map of the US.  I called it “Pennsylvania” and published it to my Datazen server.

I want everyone to see this dashboard, so I need to make it public, which is something that Datazen server supports.  To do that, I need to add a guest account to my Datazen server, and then give it rights to that dashboard.  So I add a guest Server User, like so –

  Then I add Ol’ Guesty to my hub as a user –

Then I make sure I assign Sir Guest-A-Lot to my public dashboard (as you can tell, I’m kinda punchy right now).

Now I can link directly to this dashboard by using the following pattern as described in the Datazen documentation – http://local.server/viewer/public/dashboard?name=DashboardName. 

With that complete, I can go back to my original map dashboard and set up the drillthrough to the url of this state dashboard.  Since I want it to drillthrough to the selected state, and not just Pennsylvania, I’ll use the parameter value so my url looks like this –

http://sample.cloudapp.net/viewer/public/dashboard?name={{ RangeStopHeatMap3.SelectedItem }}

Now I publish my dashboard, and when I click on the state of Pennsylvania:

A web browser opens up and I get this!

So yeah, there’s obviously the drawback I need to do this 50 times in this example.  But it DOES work (kindof), just like Chris and I discussed.

Thanks for reading!