ARCHIBUS Client Server & Excel Pivot Table Reporting

BAATUG is a wonderful training event because 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 that 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. Exporting ARCHIBUS data to Microsoft Excel and updating 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, demonstrated in the image below.
  • A box will appear that lets you choose from which database you want to pull data. 
    • 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 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 using the steps below:
    • 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.

Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

RSC News: Featured Article

We are thrilled to announce that Steven Hanks, CEO of IWMS News, posted our most recent blog article to their online Newspaper, IWMS Daily.  We are honored to have 10 Keys to a Successful IWMS Integration: Pt. 1 featured as one of the daily articles.


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

10 Keys to a Successful IWMS Integration: Pt. 1 — Have an Internal Champion

There are 10 essential keys to facilitate a successful Integrated Workplace Management System (IWMS) implementation that we at RSC have found to be helpful through experience.  Each will be discussed at length over the next few months.

This week’s subject is RSC’s first key to a successful integration: having an Internal Champion.

So what is an Internal Champion?

An Internal Champion is an employee who oversees the implementation of an IWMS.  Some of his or her key responsibilities are publicizing and promoting the IWMS application throughout their company, negotiating between their company and IWMS’ sales team, and ensuring cooperation between departments.

How does having an Internal Champion benefit a company?

Unfortunately, an IWMS is often under-utilized for varying reasons: the company as a whole may be unsure of how to utilize the system to achieve it’s goals, the company may be unfamiliar with how the the system works, and many, many more reasons. Each company is different and has its own unique challenges. The Champion breaches these challenges and ensures that their company’s IWMS is in line with company goals and functions efficiently.  The Champion must manage the appropriate approach and timing of each newly added function of an IWMS system. In this sense, the Champion commits to clearly explaining to the IWMS’ sales team why the company may or may not be ready to integrate a new module to the IWMS.

In the pie chart mentioned a few posts back, we explained that when an IWMS is first being integrated into a company, there are three areas that need to be considered and managed: the company’s own business process, their political arena, or, in other words, the driving reasons and forces behind a company’s requests and needs, and the technology that will be most effective. The Internal Champion would act as an ambassador between internal departments and between the IWMS sales team and his or her own company by knowing and being aware of each of these areas and the driving force each one has. The Champion would then be able to explain and negotiate between each department and then represent the company accurately when implementing the IWMS.

Assign an Internal Champion

An Internal Champion is one of the key steps in implementing an IWMS successfully, and, consequentially, will lead to a flourishing IWMS that gives accurate reports and aids employees in locating, managing, and tracking.


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

Pt. 2: Managing CAFM Information Systems

A few weeks ago we shared a post on our Implementation Methodology, or the “pie chart” as we sometimes like to refer to it.  In this post we go into a little bit more detail on the topic.

To recap from the original post, Robert Stephen Consulting believes in giving appropriate attention to all areas throughout the IWMS implementation.  By focusing on areas other than the technology aspect, which only takes up about 20% of the entire process, RSC is able to provide a seamless transition that complements our client’s business process.

Synthesis

Translating our client’s desired goals into functional tools requires a strong design process. RSC synthesizes the desired program requirements into workable solutions by drawing upon staff strengths, professional experience, and strong design skills.

Start with the End Goal in mind: Reporting

When the end goal is understood the entire IMWS implementation will be driven to that goal. RSC has found that defining the desired reports at the beginning of the project focuses effort, drives decisions, and provides the framework for all discussions, meetings, goals, and activities. When the desired data for the reports is understood, activities in the IMWS that support that data are implemented and other activities are placed on hold for future needs or dismissed all together.

RSC conducts a meeting with their client’s staff to gain consensus and provide a forum for understanding, adjustments, and direction related to reporting. Knowing that standards drive clear reporting, RSC also provides a Standards Document to all clients recommending best practices and how they related to the ARCHIBUS IWMS system.

Partnering

RSC requires a strong partnering environment. Partnering with our clients is defined as “One that is united or associated with another or others in an activity or a sphere of common interest.” Working towards the IWMS goal of our client is RSC’s core competency. Our entire existence is providing these services for our clients. When our clients provide professional and ethical direction to RSC we respond in kind.

Partnering requires RSC and their client to transform their businesses in terms of relationships, behaviors, processes, communications and leadership. Neither RSC nor their client can succeed without the other.

We are partners in your success and not only for your technology implementation.  IWMS solutions are a tool used to support everyday processes.  They are not solutions for fixing problems.  RSC can help identify those areas instead of trying to put a technology “bandaid” over the problem like many implementation teams will do.

We have team members who understand facilities and have been where many of our clients have been.   Often the need or problem has been seen before. Instead of reinventing the wheel, RSC leverages past experience and presents a modified solution for the clients review. This allows both RSC staff and the Client to review the need and discover areas of improvement/functionality not previously considered.Please comment below with any thoughts, feedback, or questions.  We love hearing from you!


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

May 28th BAATUG

Just a reminder that RSC’s 2nd Quarter BAATUG event on Dashboards &  Report Writing in Web Central will be held at the San Ramon Regus Business Center May 28th from 11:30AM – 2:00PM.

A light lunch and refreshments will be served.

Check out the evite to RSVP and find out more!
http://www.evite.com/event/01ECKNNS4EC2VAPGYEPE4LFBT3UD3Y


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

AutoCAD Advantages: BPOLY Command

Here is a little tip shared by one of our AutoCAD experts, Matt, at Robert Stephen Consulting, LLC.  Enjoy!

Background

The BPOLY command is useful to create a polyline that bounds existing geometry. It is much like the HATCH command or a paintbucket/flood-fill in a paint program.

General Usage

Often lines around a room are determined as LINEs or XLINEs. BPOLY is then used to create the polyline that fills the Remaining Area without any gaps.

After all the rooms have been PLINE’d, BPOLY is used to create circulation and other remaining spaces.

Cautions

We’ve found a little problem with the BPOLY command.  The BPOLY command may skip vertexes when you use it on

  • A fairly large area
  • Containing small vertex offsets

There are two solutions:

  1. Split large areas into smaller ones (particularly large circulation spaces).
  2. Use special care to be certain that vertices align.

How big is this problem?  This appears to happen with distances on the order of 1/100,000.  It could cause your total area to be off by fractions of a percent overall.

The real problem arises when we have to track down a remaining area in a drawing and run across these little glitches.  It is becomes difficult to tell where the actual problem lies.

  • Polylines should be Concave whenever possible
  • BPOLY is view dependent, the entire area to be filled needs to be viewable on the screen before running the command.

A complicated area, like this:

Should be simplified to something like this:

As always, we hope you enjoyed our post.  Please comment below with any thoughts, feedback, or questions.  We love hearing from you!


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

AutoCAD Answer Day on Twitter

Did you know AutoCAD is having their first ever Answer Day right now?!  If you have any AutoCAD questions, tweet them directly at @AutoCAD from 6AM to 6PM PST.  This is an awesome opportunity to get your questions answered directly from the source in real time.  Don’t miss out on the awesome opportunity!


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.

ARCHIBUS Cited as a Leader in Gartner 2014 Magic Quadrant for Integrated Workplace Management System

ARCHIBUS was cited, yet again, as a leader in the Gartner Magic Quadrant for IWMS.  I’m sure everyone who works with ARCHIBUS can understand why they are still going strong 7 years in a row!

Here is the article by Cynthia Zawadski at ARCHIBUS:

ARCHIBUS, Inc. (www.archibus.com), developer of the #1 real estate, infrastructure, and facilities management software in the world, is positioned in the Leaders quadrant of the 2014 “Magic Quadrant for Integrated Workplace Management Systems”1 report by Gartner, Inc., which positions IWMS software companies according to their vision and ability to execute.

“Leaders,” says Gartner’s definition of the term, “have strength in applications and platform technology, demonstrate a high level of quality in product reliability and service, have strong operational and organizational capabilities and financial stability, have global reach and offer a strong vision of customer needs, reflected in a robust development road map.”In their 2014 findings, Gartner recognized ARCHIBUS as a Leader for these notable reasons:

  • ARCHIBUS has one of the strongest international presences of the vendors in this Magic Quadrant …Through its Federated Eco-System of partners and ASCs, ARCHIBUS products and services are available in over 190 countries and 32 languages.
  • ARCHIBUS offers a full range of integrated IWMS functionality, and is particularly strong in real estate, facility and building operations management, asset management, Building Information Modeling (BIM) 4.0, environmental sustainability, and risk management.
  • ARCHIBUS’ integrated commercial off-the-shelf structure allows for rapid time-to-value deployments — for example, actionable results within 90 days, depending on implementation complexity. It is also relatively scalable for small, midsize and large organizations.

“Being cited as a Leader for the 7th time in a row, in the 2014 Gartner Magic Quadrant for IWMS, is both an honor and recognition of the continuing evolution of ARCHIBUS and the loyalty of over 7 million members in the ARCHIBUS Federated Eco-System — now over 30 years — with a customer retention of over 96.4%,” said ARCHIBUS Founder and President/CEO Bruce K. Forbes. The latest release of ARCHIBUS features new productivity-enhancing capabilities that cover a wide range of organizational needs including new features for the mobile apps, the addition of GIS capabilities for geo-referencing everything from equipment to buildings, and integration of facilities information with BIM data for improved building lifecycle management. Capital project management is also streamlined with enhanced project management and portfolio forecasting tools. Making work easier, too, are new consoles in some applications that provide a simplified, single-screen “central command post” for monitoring maintenance and other activities. A new generation of Web-based work services offers self-service requests for repairs or making meeting room reservations — as well as booking support services — easier, from anywhere in the world. Perhaps most important, ARCHIBUS has simplified integration with other EAM and ERP systems via ARCHIBUS Connectors that enable facilities data-sharing with Microsoft, Oracle, Sage, SAP, UFIDA, and other enterprise or point systems.  Industry standard transfer formats, such as OSCRE, COBIE, and buildingSmart IFC are also used. “Removing complexity from real estate, infrastructure and facilities management has been our goal for over three decades,” added Forbes. “The latest release of ARCHIBUS achieves that to an even higher degree through our guiding design principle of process simplicity through greater software intelligence. It has become a stepping stone for ubiquitous information and facilities management (UIFM™) and pervasive computing for everyone.”

1  Gartner, Inc., “Magic Quadrant for Integrated Workplace Management Systems, 2014” by Rob Schafer, June 26, 2014.


Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, and Linkedin to keep up to date with the latest news from RSC, LLC.
Thoughts? Questions? Comment below and let us know what you think! We’d love to hear your insights.