Friday Training Follow Up

Robert Stephen Consulting, LLC has begun to provide free 30-minute Client Training session every Friday again!  RSC prides itself in providing learning opportunities for our clients.  We feel that if our clients know the system better, we will able to work better with them to discover and implement the solutions that they need.  

Our first session was 10/9.  Last week we discussed Client/Server vs. Web Central vs. Smart Client.  Here are some highlights from the training, in case you missed it:

Client/Server 

Client/Server has been the go-to program for years. It is a solid work horse, but it is no longer being developed. 21.1 was the last version. If you have Smart Client, I recommend using it as much as possible.

Benefits:

• Pretty much indestructible - Connection to DB can be very direct.
• Bullet proof database update tools
• LOTS of history: *.abs files and *.avw files that may not exist in Web Central

Web Central

Web interface to the ARCHIBUS database, only requires a web browser to run.

Benefits:

• Roles based
• Views based
• "Pretty" Reports/ Dashboards
• Pages interface
• Web server

Smart Client

The new Client/Server.

Benefits:

• On the fly creation of views
• Saved views are easy to develop and instantly available
• Filters are easier to develop
• Views with relationships between tables are easy to produce
• Limited to 3 levels down
• Better integration between application and AutoCAD

We are excited to have another RSC Friday Training on Friday, Oct. 16 from 10:00 AM - 10:30 AM PDT

Please register by clicking on this link and follow the instructions:

https://attendee.gototraining.com/r/6155681089126080257

This week's free 30-Minute training will cover Reports, specifically Smart Client data tools, including import/export, and drawing highlights.

After registering you will receive a confirmation email containing information about joining the training.

Robert Stephen Consulting, LLC Friday Trainings are open to all!  If you'd like to be included in our weekly email list with topics and information about that week's training session, please contact us at info@rsc2lc.com and we will be sure to get you on the list!

Like what you read?  Subscribe to the blog and follow us on Twitter, Facebook, Linkedin and Pinterest to keep up to date with the latest news from Robert Stephen Consulting, LLC.

Thoughts? Questions?  Comment below and let us know what you think!  We'd love to hear your insights.

Follow @rsc2lc Tweet

ARCHIBUS Client Server & Excel Pivot Table Reporting

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.

Subscribe to the blog and follow us on Twitter, Facebook, and Pinterest to keep up to date with the latest news from Robert Stephen Consulting, LLC.

Follow @rsc2lc Tweet