BAATUG is a wonderful training event. It is more than just information on ARCHIBUS. Often times Head of Robert Stephen Consulting, LLC, Bob Stephen, demonstrates tips, techniques, and best practices that make your workflow significantly easier.
A while back, Bob Stephen shared some tricks at an amazing BAATUG event hosted by Stanford. This little technique Bob taught was so good we created a tutorial to share with all you who missed the event.
This tutorial is broken into two parts:
(1) Writing reports in Microsoft Excel
(2) How to export ARCHIBUS data to Microsoft Excel and update it regularly
Sometimes you need a report that just isn’t in
ARCHIBUS, or that you don’t WANT to be in ARCHIBUS, because you’d rather use
the flexibility and power of Excel to combine the data from ARCHIBUS with other
sources, or to slice and dice it using Excel Pivot Tables. All of these things are possible using the
technique described below.
The reason this works is because ARCHIBUS Client Server uses ODBC connections to reach the database, and so can Excel. From this point forward, we will assume you know the afm password to the database.
Below are the steps:
- Open a new, blank workbook in Microsoft Excel
- On the "Data" tab, choose "From Other Sources" then "From Microsoft Query" from the drop down:
- A box will appear that lets you choose which database you want to pull data from
- These are all ODBC data sources. If you don’t see the one you want, go into Client Server and set up a Project to connect to that database. Once you've set up the Project connection it will become available. (ARCHIBUS Client Server creates ODBC Data Sources to make its own connection to the database; we are piggybacking off that)
- Choose your data source and click “OK”.
- For now, we’ll leave the “Query Wizard” box checked.
- A database login dialog box will appear. The Login ID will likely default to "afm." In order to move forward you will need to know the password for the database. Enter the password and hit "OK."
- The Query Wizard will now appear. You should be able to choose which database table you would like to use for the data. In this example we will use the em and rm tables.
- Pick the columns you want and hit the "Next" button.
- NOTE: If you pick data from different but related tables, the Query Wizard does the join for you.
- From there you will be taken through some other forms; it is safe to click the defaults to get the data into Excel.
- You should see something that looks like a grid -- a rich set of data about rooms and the people that occupy them:
- With just a little more effort a more more detailed analysis can be done with Count of seated employees by Division Department sliced by Region/Site
- If and when the data in ARCHIBUS changes, you just need to Refresh it:
- Return to the "Data" tab
- Right click on the data, and click "Refresh"
- Your data is now up to date
- If you are using Pivot Tables, you will need to refresh these as well after making the changes
- Want to Edit your Query?
- Click on the data table
- Select the "Data" tab, then "Properties" from the drop down menu
- Push the button to the right of "Name" to get "Connection Properties" dialog box
- Click the "Definitions" Tab, then "Edit Query" tab
- And you'll be right back to editing the Query
- A Quick Note of Caution:
- Queries are not portable. They want to be run from the machine on which they were created. If you email the spreadsheet to a friend, they will be able to see and change the pivot table, but will not be able to refresh the data or access the query.
Follow @rsc2lc Tweet
Posts
