Impressive Dashboards with Google Sheets

Over the years, I’ve used Google Sheets a few times, and usually for basic tasks. Yesterday, I was surprised and impressed to see what it can do, thanks to Ben Collins. He builds interactive dashboards with Google Sheets, complete with pivot tables and maps, using the built-in features. Who knew that all this was possible, in a free, online spreadsheet application?

Continue reading “Impressive Dashboards with Google Sheets”

Make All Queries Replicable in Access

Recently, I’ve been making changes to an Access database that someone else built. In most cases, that’s enough of a challenge, but this database has an extra hurdle – it’s set up for replication.

Access Database Design Master

One copy of the database is set up as a Design Master, and replicas are made from that copy. Everyone can make changes and additions in their copy, then all the copies are synchronized, to pull all the data together.

I hit a snag, right near the end of the development phase, so I’m posting this solution, in case it helps someone else — or me, later, when I need the code again. 😉

Don’t Move the Design Master

I’ll spare you the gory details, but all the structural changes have to be made in the Design Master. If that file is lost or corrupted, you can create a new Design Master from one of the replicas in the set.

My client sent me the Design Master to work on, and I’ve been busily making changes for several weeks. Now, it’s time to send it back, so they can use all the fancy new forms and reports.

Unfortunately, I learned that when the Design Master is moved from its original folder (i.e. when the client sent me a zipped copy), the moved copy becomes a replica.

Create a New Design Master

So, I had to get another copy of the database, with all the current data, and strip out everything except the tables. Then, I imported all the queries, forms and reports from the new version, and will send it back to my client.

When they get the database, they’ll make it the Design Master, and create new replicas for everyone to use.

It’s an annoying workaround, but we’ve done a few tests, and it works fine.

Make the Queries Replicable

It’s easy to do an import of all the forms, queries and reports that are in another database, and that step took just a couple of minutes. However, all the imported objects should come in marked as “Replicable”, because that is property setting in the other database.

The forms and reports were fine, but none of the queries had the Replicable property turned on. There are over 100 queries, and I wanted a way to turn on that property programmatically, not manually.

You’d think that would be easy, but I spend a long time searching in Google, Bing, and my shelf full of Access books. Nothing helpful appeared in the search results, but I didn’t give up!

Finally, I found an article, written in 1999, on the Microsoft website, and it had the code I was looking for — Implementing Database Replication with JRO

Set a Reference

The code has to run from another database, so I used a different copy of the client’s database. The database with the queries to update is closed.

In the database where I put the replication code, I had to set a reference to JRO in the Visual Basic Editor (Tools > Reference)

accessreplicationreferencejro

The Replication Code

The following code will change Replicable setting for the specified object to True.

Sub MakeObjectReplicable(strDBPath As String, _
                         strObjectName As String, _
                         strObjectType As String)
   Dim repMaster      As New JRO.Replica
   repMaster.ActiveConnection = strDBPath
   repMaster.SetObjectReplicability strObjectName, strObjectType, True
   Set repMaster = Nothing
End Sub

Loop Through the Queries

The sample code in the article only changed one query, but I wanted to change them all. The database I used to run this code had all the same queries as the target database, so I looped through its queries.

As you can see in the code comments, even though I’m updating queries, the object type is “Tables”. I tried “Queries” first, but that didn’t work, and then I noticed this warning in the Microsoft article:

Important   Even though the SetObjectReplicability property provides an ObjectType argument to specify the type of object you are working with, the argument only accepts a value of “Tables” for both queries and tables.

The code runs quickly, and this will be handy if we ever have to rebuild the Design Master. Use this at your own risk though – I’m certainly not a Replication expert, and it might not work in all cases.

Sub MakeAllQueriesReplicable()
Dim strPath As String
Dim strFile As String
Dim qry As QueryDef
strPath = "C:\Data"
strFile = "MyNewDesignMaster.mdb"
on error resume next
For Each qry In CurrentDb.QueryDefs
	'NOTE: object type 'Tables' is used for both tables and queries
	MakeObjectReplicable strPath & "\" & strFile, qry.Name, "Tables"
Next qry
End Sub

____________________________

Future of Productivity Giveaway Winner

Did you enter the Excel 2010 Future of Productivity Giveaway, that ended at noon yesterday, Tuesday, June 8th?

The giveaway prize was a copy of Microsoft Office 2010, a Flip video camera, and a Seagate 1TB hard drive.

Of course, I used Excel to select the winner, from all the valid comment entries. I typed the comment numbers onto an Excel worksheet, with RAND functions in the adjacent column. Then, I calculated the workbook 3 times, to mix up the RAND results.

Finally, I sorted the RAND column in ascending order, and the comment number at the top was the winner. To satisfy the auditors, here’s a video of the random comment number selection.

And the Winner Is…

As you saw in the video, the random number selected was 22 — the comment by Naomi B. Robbins. Congratulations, Naomi! You win the prize package:

  • one (1) copy of Microsoft Office 2010: Home and Business
  • a Flip Ultra HD Camcorder
  • a Seagate FreeAgent Desk Hard Drive (1 TB)

The prize value is approximately $589 USD, and someone from Ignite Social Media will be in touch with Naomi to arrange delivery.

Office2010Pkg

Thanks for Entering

Thanks for entering the giveaway, and thanks to Ignite Social Media for sponsoring it. I hope you saw a few new features in Excel 2010 that you’re looking forward to using!
____________

Excel 2010 Future of Productivity Giveaway

Office2010HB_small Have you installed Office 2010 yet? Would you like to win a copy, along with a couple of other great prizes? [for USA residents only]

In this short video, Microsoft employees and customers talk about the benefits of Excel 2010, both for the users and the IT department. Watch carefully — there will be a test later!

VIDEO IS NO LONGER AVAILABLE

The Future of Productivity Giveaway

The nice people at Ignite Social Media invited me to participate in a giveaway [for USA residents only], as part of the Office 2010 launch. No free stuff for me, but one of you can win a cool prize package. Yes, there’s probably a better adjective than “cool” — at least I didn’t say “groovy”. 😉

The Prize Package
  • one (1) copy of Microsoft Office 2010: Home and Business
  • a Flip Ultra HD Camcorder
  • a Seagate FreeAgent Desk Hard Drive (1 TB)

Here’s a picture of the prize package, obviously not shown to scale! The pictures are from the Microsoft Store site, so the actual prizes might look different. Based on the prices shown in the Microsoft Store, the prize value is approximately $589 USD.

Office2010Pkg

How to Enter

The giveaway is a scavenger hunt, so you’ll have to watch the video (at the top of this post) to find an answer to this question:

  • How does Excel 2010 make data analysis and reporting better/easier?

Add a comment below, with a unique, relevant answer to the question shown above. (Don’t just copy someone else’s comment!)

The Giveaway Rules
  • You must be a resident of the Unites States of America.
  • The entry deadline is 12:00 noon (Eastern time zone) on Tuesday, June 8th, 2010.
  • One entry per person – any additional entries will be deleted from the draw
  • A random draw will select the winner from all valid entries.
  • Winner will be notified by email, so please provide a valid email address. This will not be publicly visible, but will be shared with the contest organizers at Ignite Social Media, so they can contact the winner to arrange delivery.

The winner will be announced in this blog on Wednesday, June 9th.

More Future of Productivity Events

This widget has more videos, and you can click to learn more about the Future of Productivity launch.

VIDEOS ARE NO LONGER AVAILABLE

__________

Tableau Public Has Launched

If you’ve wanted to try Tableau data visualization software, now’s your chance! They’ve just launched Tableau Public, where you can upload your data, and use the free Tableau tools to create amazing interactive charts, maps and dashboards.

This example shows Economic Indicators & Stock Market Returns, and you can select from a drop down list of market metrics to update the chart.

Tableau Public interactive chart
Tableau Public interactive chart

As the product name implies, your saved data will be public, so it’s not the place to work with your top secret financial data. It’s a great opportunity to experiment with the Tableau software though, using dummy data, or data that you’re willing to share with the public.

With Tableau Public, you can connect to Excel, Access, and text files, with a limit of 100,000 rows of data per connection. You can save up to 50 Mb of content to the Tableau Public web servers.

Tableau Articles

There are other blogs where you can see dashboard examples, and see how people are using the software.

There’s also a gallery with dashboard examples, such as the Fantasy Football 2009 Running Backs and Student Loan Default Rates.

TableauPublic01

Use Tableau Public

To get started, go to the Tableau Public page, and click the Download Tableau Public button. Then, enter your email address, and click Submit. To help you understand the software, you can watch the brief Tableau Public Preview video and the Tableau Public training videos.

After you install Tableau Public, open it, and connect to your Excel, Access or text data file. It’s quick and easy to create a graph, and Tableau will help by suggesting chart types for your data.

Your work in Tableau Public desktop will be saved to the Tableau Public web servers, not on your computer. On the web servers, your data will be accessible by anyone on the internet, so don’t use Tableau Public for confidential or sensitive data.

Share Your Results

After you save your work, you can share it, by embedding it on your blog or website, or by sharing a link to your data.

If you create a dashboard, you can post the link in the comments here, so other people can go and take a look.
___________
Related Links:
Last fall I wrote a couple of articles about Tableau, and uploaded a short video:

Adrift in a Sea of Numbers

Create a Chart from Excel Data in Tableau

I used a trial version of Tableau for a couple of weeks, which has all the features of the paid version. I was really impressed with what the software can do, and got in touch with the Tableau people, to see if I could participate in the Tableau Public beta.

The free version wasn’t available yet, so they provided me with a license for the paid version, so I could keep experimenting, and post my work in their public servers.
______________

Tableau Dashboard Trial Version Test

This summer I helped a client automate several Excel sales reports, comparing sales forecasts to actual sales, and last year’s results to this year’s.

It’s a complicated process, pulling numbers from different systems, updating lookup tables, compiling the numbers, and creating reports by product, by customer and by sales rep.

Before we automated the process, it took one person almost a week to create the reports, and despite all that effort, nobody was happy with the results.

  • The sales reps thought there was too much data to wade through – they wanted to focus on their customers and sales. Were sales on target? Which customers need more attention?
  • The managers wanted to see the big picture, and quickly assess how things were going overall. Were all the brands performing as expected? Which products or customers were growing or declining?

Tableau Dashboard

While working on the reports, I noticed people talking about Tableau dashboards in Twitter. I followed their links, and was really impressed by what I found.

With Tableau, we could quickly connect to the data in Excel, and also link to the lookup tables, so all the prices and descriptions would be accurate and instantly updated.

So, I downloaded a trial version of Tableau, and created some reports from the client’s data. It was really easy to get up and running, creating tables and charts, with quick filters that let me focus on a specific brand, customer, or sales rep.

Next month, when there’s a new set of data, the old file can simply be replaced by the new file (with the same name), and the charts and tables will automatically refresh when the Tableau workbook is opened.

Share the Results

After you create reports in Tableau, you can send out a pdf file, or a Tableau workbook, to share the results.

There’s a free Tableau reader, so recipients can open the workbook file, and adjust all the filters, sliders and other controls that you’ve added to the dashboard.

Each person can analyze the data in a way that’s meaningful to them, and drill down to the details, or step back to absorb the bigger picture.

To give you an idea of what’s possible in Tableau, I’ve created a sample workbook, shown below. Dan G. Murray, COO of InterWorks Inc., generously shared the sample sales data that he used for a presentation at the Tableau Customer Conference this summer.

With Dan’s data, I made a few dashboards, and Elissa Fink, VP Marketing at Tableau, published them on the Tableau Public server.

Try It Yourself!

Here’s the first dashboard in the workbook, focused on sales rep results and country totals. Each sales rep can see their overall total, and total and average sales by country.

The map gives a snapshot of sales location, and clearly shows that large sections of the USA aren’t buying the company’s products. You can experiment with the dashboard controls, and change the way the data is presented. You might have to widen your browser window, to see the full dashboard.

  • There’s a date range selector at the top left of the dashboard. Only the past four weeks of sales results are in the dashboard, but you can select a specific number of previous weeks, or other range, such as previous quarter.
  • At the bottom left of the dashboard, in the Salesman Name list, click the highlighter at the top right, to turn on the Highlight Selected Items feature. Then, click on a Sales Rep name in one section of the dashboard, and that name is highlighted in all the sections.

Dashboard on Tableau: Sales Rep Dashboard
_________________

Summer Giveaway for Excel Nerds Winners

Thanks for participating! You’re an awesome bunch of Excel nerds. The entries in the “You might be an Excel nerd…” giveaway were very entertaining, and read more like my resume than a list of contest entries.

Yep, I do some of those things, and I’ll bet you do too. 😉
Thanks again to the generous prize donors:

The Draw

As promised, I did a random draw of prizes and entries. But, in true Excel nerd fashion, I didn’t just drop the names into a hat. No, that might have only taken 5 minutes or so. Instead, I wrote a macro to do the draws for me.

Before the draw, I cleaned up the list of entries, removing the people who said they weren’t participating, and deleting any extra entries per person. I kept the comment numbers with the names, and created a numbered list of prizes, based on the order they appeared in the original giveaway post.

I set up a list to record the draw number, prize number and entry number, then ran the macro. There was even a drum roll at the start of the draw, but it was disappointingly quiet. Oh well, at least everything else went smoothly.

You’d probably do it differently, and maybe without the drum roll, but if you’d like to see the macro that I used, you can download the Draw Macro Sample file. Now you’ll be able to organize your own giveaways!

And the Winners Are…

After the numbers had been selected, I used a couple VLOOKUP formulas to pull the prize names and winner names from the original lists of numbered prizes and entries. Congratulations to all the winners! Here’s the list:

Giveaway20090729_Win

Collect Your Prize

I’ll send an email to all the winners on July 29, 2009, with instructions for claiming your prize. Please reply by 5 PM EDT on Monday August 10, 2009, or your prize will be put back into the vault for the next giveaway.

Note: Some of the prize donors will be away from their offices in August, so there may be a delay in mailing.

Update: I’ve sent out the emails, using the email address that you used in your comment. If you haven’t received your email, please let me know at ddalgleish @ contextures.com

______________

Create Multiple Home Pages in Firefox

Do you have two or more web pages that you use frequently? I like Google as a home page, and use it many times during the day.

I also refer to my own website several times, to make sure everything is working okay, or look up one of the Excel tips that are stored there.

I had Google set as my home page in Firefox, then opened Contextures.com later.

MultiTabsFireFox

It’s not a terribly painful process, but I’ve found a way to set multiple home pages, instead of one.

Set Multiple Home Pages

Here’s how I set up the multiple pages:

  • On the Firefox toolbar, click Tools
  • Click Options
  • Click  the Main heading, then click the Use Current Pages button
Firefox Options Use Current Pages button
Firefox Options Use Current Pages button
  • The URLs will appear in the Home Page box, separated by the pipe separator.
  • Click OK, to close the Options dialog box.

The next time you open Firefox, both home pages will automatically open.
_______________________

Automatically Update Access Front End Files

When sharing an Access database, you should have the data in one file (the back end) and the queries, reports, forms and modules in a separate database (the front end).

Give each user their own copy of the front end, linked to the shared back end.

Keep a master copy of the front end file, tucked away in a safe place. If you need to update the front end, such as adding new features to a form, make the changes in the master copy of the front end file (make a backup copy first!)

To distribute the revised front end to all users, you can use a utility like Tony Toews’ Auto FE Updater. I use this utility for most of the databases that I’ve installed at client sites, and it works beautifully.

[Update: This utility no longer has a free version]

The utility adds a shortcut to the user’s desktop, which they double-click to open the database. If the master copy of the front end has been updated, the utility downloads a copy of the revised version.

Remember to Compile

The only problem that I’ve had was with one user, who has Access 2000 installed. I’m using Access 2003 to update the master file, and all the other users have Access 2002 or 2003 installed.

Sometimes, after I’d updated the master file, that user couldn’t open the new version. Error messages would appear, and the VBA code looked like pieces were missing. Very strange.

When I made the latest change to the database, I compiled the code before closing the file.

DebugCompile

It dawned on me that maybe I’d occasionally forgotten to do this, and that could be causing the problem.

I checked the previous version, and it wasn’t compiled. When I uploaded the new compiled version, that user was able to open it with no problem.

That’s not definitive proof that compiling will prevent future problems, but I’m keeping my fingers crossed.
____________________