×

AEC

Productivity Corner: The Power of ProjectWise

6 Nov, 2006 By: Matt Blumenfeld

---

How To Create ProjectWise Reports Using Microsoft Office

It's 3:30 on Friday afternoon when the boss calls. "I need a summary of all the files we have stored in ProjectWise, sorted by application, for all of our office locations. There's no hurry, though. I don't need this until the managers' meeting at 9 a.m. on Monday."

"No problem," you think to yourself. "I'll just call the SQL guy and have him run a query for me and I'll be done with this task."

As it turns out, it won't be that easy. The SQL guy is on the beach in Cancun for the next two weeks. You resign yourself to spending a long weekend slogging through file lists and typing summaries by hand. Oh well, maybe you'll be finished in time to watch The Sopranos on Sunday night.

Fortunately, things may not be nearly as bleak as they seem. If you have a ProjectWise client and Microsoft Office installed on your computer, you are practically finished. By using ProjectWise's ability to copy file information to the clipboard and Microsoft Excel's built-in sorting and charting functions, you can create a great-looking report and start your weekend on time.

Preparation
To create this report, you'll need the following:

• A list of information you want for your report,
• The ProjectWise client software installed on your computer,
• Microsoft Office installed on your computer and
• A ProjectWise data source to connect to.

For this report, you want to find out how many files you have, what types of files you have and where the files are stored. Before you can go any further, you must set up a view in ProjectWise that displays that information.

Log into your ProjectWise data source and use the View Editor (View / Choose Columns) to create a new view that contains the columns on which you want to report. Once you've extracted the list of files from ProjectWise, you'll use the data in these columns to generate the report. For this exercise, be sure to include the columns File Name, Application and Storage. You can add as many other columns from ProjectWise as you like. When you generate your report, you only need to use the columns that are pertinent to the report.

Extracting File Information
Once you are logged into the ProjectWise data source, right-click the Documents folder and choose Find Documents.

If you leave all of the fields blank, Find Documents will return a list of all the files stored in ProjectWise. If you want information for a specific area, you can select the folder in which you want the program to start looking for documents. Because the boss wants to know about everything, we'll leave everything blank. Check the box marked Show results in new window.

Depending on the number of files that ProjectWise manages, the search results may take a while to display. Once the search is complete, highlight a file in the search window and press Ctrl+A to highlight all the files in the search results window. Right-click on the highlighted files and choose Copy List to Clipboard Tab Separated. This copies all of the information in the displayed columns of the Search Results dialog to the Clipboard so that you can use it elsewhere.

Now it's time for the fun part of the exercise: manipulating the data with Microsoft Office. Now you're done with ProjectWise and all that's left is to paste the tab-separated list into Excel and then manipulate it to create a pretty picture or two for your report.

Start Excel and open a blank worksheet. In the first cell, right-click and choose Paste in order to paste the file list into the worksheet.

Once the file list is copied, there are many ways to manipulate the information to make it display in a meaningful manner. Let's start by sorting the list to simplify getting the totals requested. Highlight all of the cells in the spreadsheet. Although you can select the cells with the mouse, it's often easier to use Ctrl+A to select everything in the spreadsheet. Now, go to the Data pull-down menu and choose Sort.

When the Sort dialog comes up, select the radio button marked Header row and the column headers from ProjectWise show up in the pull-downs to choose your sort order. Choose Sort by Storage, Then by Application and Then by File Name. When you click the OK button, your data is sorted in the spreadsheet.

The next step is to create subtotals of the information that the boss wanted for his Monday meeting. Make sure that everything in the spreadsheet is highlighted and then from the Data pull-down menu, choose Subtotals.

Fill in the Subtotal dialog to achieve the results you want. In this case, let's count all of the files in each storage area by using the Excel Count function to count filenames.

Click the OK button, and Excel counts all the files in each storage area and prints the total below the last entry in the storage area. If you look at the left side of the Excel window, you'll notice a number of black lines with "-" signs. You can click the "-" to hide the details and show just your subtotals.

Things are really starting to come together! You now have a spreadsheet with a listing of all the files that matched your Find Documents query and you have got things subtotaled so that it's easy to see how many files are stored in each location. Now, use Excel to find a subtotal of files by application in each storage area.

Highlight everything in the spreadsheet and choose Data / Subtotals again. This time, at each change in Application (remember, we sorted the file list by application earlier), count each entry and add the total to the Created By column in the spreadsheet. This time, make sure that you uncheck the box labeled Replace current subtotals.

Your spreadsheet should now look something like the illustration below. Notice that most of the data was hidden by collapsing the various application categories so that all that's visible is the name of each application with a count of the files associated with the application, and the name of the storage area with a count of all the files stored there.

Almost Finished
You could print this information out and be out of the office in time to make it to happy hour or your weekly poker game, but it doesn't take much additional effort to add some visual impact to your report by using the Excel chart function. To report on different aspects of your data, just use Excel's Sort and Subtotal functions, and then use the results to create reports or charts that suit your needs. The sample charts below should help you get started creating your own great visuals.

With this information, you should be able to create your own quick reports on documents stored in ProjectWise. This approach works fine unless you have to process very large numbers of files, as Excel will only let you work with about 65,000 rows of data. If you have more than 65,000 documents, you can use a database program, such as Microsoft Access, to process your Find Documents results.

Sample Charts