Multi-Unit Report #29

[admin]

Posted by: Mark Koenig, Providence Health System

Date: December 11, 2009, 9:08 pm

This is a very important resource to know about, however, it does take a little “tweaking” to be useful. Some things I find especially useful with this report include:

• Activities (programs) associated with each unit (institution) show up on the same line – very important once you start filtering the data.

• All activities from every unit in the health system are exported into one file

• Data can easily be filtered to see what activities provide the greatest financial benefit, e.g. I created a separate file that only included activities throughout the System valued at $250,000 or greater.

• Data can easily be filtered to see what activities serve the greatest number of people.

• Data can be filter by activity to see if institutions are consistent with reporting specific activities, e.g. CPE programs, use of room space, etc. (you can’t tell if the methodology for reporting the expense is consistent, however, it let’s you know that organizations have/have not remembered to include them).

• Identify inconsistencies with how activities are tracked related to categories.

• At a glance you can calculate the total number of activities tracked for the whole system.
To make the report user friendly takes a little work. If you are not Excel savvy, you will need someone to help. (I have become a little more savvy thanks to people helping me figure all this out!) Here are the basic steps. Once you are logged into CBISA:

1. Click on the tab for “Reports and Listings”

2. Click on the box for “Multi-Unit” reports

3. Go to the very bottom of the report options and select #29 which is called “Multi-Activities – Alternate Version”.

4. Select your settings for the date range and report grouping information (“Organization” and your “health system”) and then hit “Preview”

5. Export to Excel using the “Excel 97-2000 (XLS)” option

6. Name and save the file

Now the real fun begins – you may need someone with Excel skills. You cannot filter the data until a number of columns are deleted and the formatting of the cells is consistent. There are columns that in reality do not contain any information and they must first be deleted There is also a column connected with “persons served” that contains merged cells. This column must first be “unmerged”.

The last step is to delete the row between the column headings and the actual data (the title row and the data need to be contiguous for the filter feature to work). I am still using Excel 2003. To turn on the filter feature with 2003 you select the drop-down menu for Data and select “Auto-filter”. You can now begin “filtering your time away”, if you haven’t already. I realize this all sounds a little intimidating, but once you have created this file you can save it and have quick and easy access to the information in the future.

[admin]

Response: Eileen Barsi, CHW

Date: December 15, 2009, 7:25 pm

Well Mark, I’m a little nervous about trying, but given a block of time you know where you’ll find me!! Thanks so much for sharing this information.

[admin]

Response: Trina Hackensmith, Lyon Software

Date: December 16, 2009, 9:21 am

Please note: if you export to Excel using the “data only” option, you do not have to unmerge cells, just adjust column widths. This is the way the report is intended to run.

[Author]

Posts