Top 7 Quick Tips to make Exporting Reports Less “Scary”!

We’re wrapping up a month of blog posts on policyIQ reports by saving the “scariest” topic for last: exporting reports from policyIQ to Microsoft Excel.   We receive a number of emails from users asking for help formatting or adjusting their exported reports – so here are seven of our top tips!

1. Remove the first row of merged cells

When exported into Excel, row 1 will always show you the Name of the Report.  This is great information, but the merged cells in that first row will make it difficult to sort and filter your content.  Instead, save your report using this name – and then remove that first row to give yourself the flexibility to sort, filter and rearrange your data more easily!

2. Automatically Resize Rows

Excel_resizerowsThe cells in your exported report results will be automatically formatted to “wrap text” – but they will be sized to only display one row. To show the full descriptions (such as a full “Project Task Description”), highlight the entire worksheet and then place your mouse on the line between any two row numbers. Double-click your left mouse button, and the rows will be automatically resized to show all of the text in each cell.

You might find that this makes your cells far too long if your text in any column is extensive.  In that case, you may prefer to repeat the highlight of your entire sheet, and instead resize a single row to what might be a typical size.  That will resize all of your rows to an identical height.

3. Remove dark row separators and replace with standard Excel gridlines

I don’t know about you, but I’m not a big fan of heavy outlines around the cells or rows in my Excel spreadsheets. I like to keep things simple and rely instead on the standard gridlines that Excel so conveniently provides. If you are like me, you can easily remove the borders with a few simple steps:

A. Highlight the entire worksheet.

B. Go to your toolbar and find the icons for your borders. Select the option for “No borders”.
Excel_noborder

C. If you want to add in the gridlines, you can go to View in your toolbar (or in your ribbon, for those Office 2007 users), and choose to view the gridlines.

D. You also need to remove the white “fill” in the background before those gridlines will appear. Highlight the sheet, and go to the Fill icon and choose “No Fill”.

Excel_nofill

4. Remove any undesirable HTML ‘tags’

Some of you may have found that when you export content into Excel, some characters in your text are appearing as the coded HTML tags rather than the characters themselves. Some examples might be dashes, which appear as ‐ rather than simply “-“, or quotes which appear as ". The explanation of why this happens could be a blog post in itself, but the bottom line is that it displays properly in the policyIQ pages, but MS Excel doesn’t interpret those correctly as the characters you intended.

There’s an easy fix, though! Highlight your entire worksheet and do a Find / Replace. (Just hit Cntrl F on your keyboard.) Enter ‐ as the text to find, and – as the text with which to replace it. Click Replace All and you’re done!

Excel_findreplacepng

5. Create hyperlinks in the “Item URL” column

You have the option of exporting the “Item URL” as one of the columns in your report results, however when exported into Excel, this is formatted as simple text. This means that it does not automatically launch a user to policyIQ, but rather has to be copied and pasted out into the browser.

Insert a new column in your report results, and use this simple formula to adjust text into hyperlinks:

=HYPERLINK(B2)

B2 is simply the cell that contains the URL.  Copy the formula the entire way down that new column to the bottom of your results, and now all of your rows have an active hyperlink to launch to the policyIQ page.

Excel_hyperlink

6. Separate a Multi-Select field into separate columns with an X to fill in the blanks

Do you have a multi-select field in your content somewhere that you’d prefer to pull apart into its component answers in your report results? A good example of this might be the Financial Statement Assertions on your Risks and/or Controls. You probably have a multi-select field, but you might find it more convenient to evaluate your data if you can pull those apart to see X’s in individual columns where that Financial Statement Assertion applies. There are a couple of ways to go about it, but based on my own trial and error experience, I’ve found that the following process works great.

A. Add columns to your report results worksheet – one column for each of the Financial Statement Assertions (or other multi-select value that you wish to separate out).

B. In each of those columns, you be using a formula to determine if that specific Financial Statement Assertion is included for this row. That formula is:

=IF(ISNUMBER(SEARCH(“Completeness”,I2)),”X”,””)

“Completeness” is the value or text that you are looking for.

I2 is the cell in which you are looking for that value – or the original column of data from your policyIQ report results.
“X” is the text that you wish to see in the new column if the text is found.

You’ll notice that the last item in the formula is “” – indicating that if the value is not found, no text should be entered.

And remember that you can always copy a formula the entire way down the column in your spreadsheet simply by dragging the lower right corner of any cell.  The formula will automatically adjust to look for the correct cell, such as I3, I4, etc in our example.

Excel_ifisnumber

7. Remove duplicated / repeated content in your Detail Link Report (ie “Risk Control Matrix”)

A Detail Link Report shows the relationships between content. The most common use of this type of report is a Risk Control Matrix, so you may show the Risks in the first column, and then all of the linked Controls in the second column. If a single Risk has five linked Controls, the exported Excel version of the report will repeat the Risk information five times (into five rows) followed in each row by one of the Controls.

You might prefer to see blank cells in those first columns if the Risk is repeated, so here is an easy way to identify which Risks ARE repeated and delete all of those repeats:

A. Add a column to your report beside either the Page ID for your Risks. (If you haven’t included Page ID in the report results, you can use Reference Number or Name – whatever is always unique for each Risk.)

B. In that new column, enter the following formula to determine if the Risk is a duplicate of the line above it: =IF(A2=A1,”Duplicate”,””)

A2 is the cell representing the Page ID (or Reference Number or Name) of the Risk in that row, while A1 represents the Page ID of the Risk in the row above it.

Duplicate will appear if the two cells are identical, meaning that the row is a duplicate and the data can be cleared.

Excel_duplicate

C. Once you have identified all of the duplicate rows, you can sort on that row – and then highlight all of the duplicate cells, right-click and choose to “Clear contents”.

Excel_clearcontents

D. Do not simply delete the rows! Remember that you do want to see the Control information in the columns further to the right in the spreadsheet. You just want to clear the duplicate contents.

policyIQ Reports module gives you the power and flexibility that you need to pull out data for analysis or presentation, but we know that many of you rely on the formatting capabilities of Excel to create the great looking reports that you distribute externally.  We hope that these tips can make those formatting tasks a bit quicker!  As always, if you have any questions, please contact our support team.

What tips do you have for formatting your reports?  Any ideas that you want to share in comments that help you to create spectacular report results?

Learning to Utilize Reports will help you get the most from policyIQ

Last Thursday, we dedicated our monthly CPE training event to the subject “Utilizing Reports to get the most from policyIQ”.  While we have recorded training sessions aimed at the teaching users how to create reports, we wanted to take a different approach in our live session and talk to our users about how to answer questions using reports.

There are two things you should ask yourself before you create a report

Not everyone finds creating reports a fun way to pass the time, unlike your author.  One of the key points that came out of the training session was a thought process that anyone can use to get started on their reports.   Essentially, that process requires that you ask yourself two questions before you start to build your report:

  1. What question do I need my report to answer?
  2. What am I going to do with the answer once I have it?

If you have those two pieces of information in front of you, you’re ready to get started!

Filter to get your answer – and add Columns for the detail you need to take action

With those two pieces of information, you start to build Filters – narrowing down the results that your report will bring back and giving you the “answer”.  Then add Columns to your report, so that you have all of the detail provided in the final report that you need to take the next step and take action on that answer.

For example, let’s say that we’re managing a project or initiative – and we’re using policyIQ to track all of the tasks that need to be completed.  We have a status meeting in a few hours, so we need to run some reports to take into that meeting.

Tips and Hints!

There were a few tips or hints shared during our training session that we think are worth sharing with everyone:

  • Do you need to report on items that are “past due” or that occurred in the past?Any Date field can be filtered in a number of ways, including choosing to look for items where the date is “on or before” a specified date. But if you want to run your report more than once for all items where that date is in the past, consider adding a filter for the “Last 9999 Days”. With that filter in place, you can run the report again and again, but never have to change your filters.
  • Use the NOT filter!In our training session, we used an example of looking for items where the Status was incomplete. Our Status field had four dropdown choices: Not Started, In Progress, On Hold and Complete. Rather than selecting all three of the other choices, we selected “Complete” as our filter. Back on our main Report Editor, we simply clicked on the “Complete” filter and dragged it below the radio buttons into a new set of filters. We changed the radio button from AND to NOT, and now we had a filter looking for everything where the Status was NOT “Complete”.  (See illustration below.)NOTFilter

Questions from the audience

What a great audience!  We asked for participation in a number of places throughout the session – asking our audience to give us some examples of different reports that they might need to create.  We had several really great questions that we thought we’d take a moment to answer here for everyone.

When you export a Summary Report, does the detail also get exported?

We created examples of both Detail and Summary reports in our training class.  Detail reports are qualitative lists of content (or lists of content and their relationships to each other) with columns that show you the detail of each item.  Summary reports are quantitative reports, showing you the results numerically across your chosen parameters.  You can drill into a Summary report to get to the detail, if desired.

You can export either type of report to Excel, however when exporting a Summary report, the export will show you only the quantitative distribution.  You can export the detail behind it, though, using this trick: In the lower right hand corner of every Summary report you will see the grand total of records in that report.  If you click on that number, you’ll be taken to a Detail report that lists all of those records.  You can export this Detail report, as well.

So the answer to the question is “not directly” – but you can get to the same Details and export that separately!

When looking at a date to go back from, does it include that date also?

If you are using a filter to show items in the past and using the filter for “On or Before”, the results will include items where the date equals your starting date.  For example, “On or Before 10/26/2010” will include all items where the date is 10/26/2010 and also items where the date is earlier than 10/26/2010.

Want to catch up on the materials?

If you weren’t able to join us last week, but you’d like to catch up with the materials that we presented, here are some quick links to take you there.

As always, contact our support team if you have any questions or if we can help you with a specific report!

Calling all Reporting Nerds! Use these tips to share your hard work and make everyone more efficient!

ReportsFTWI am going to admit something about myself that very few people know.  I’m kind of a nerd.*  I love creating reports and analyzing data.  I create spreadsheets and databases for fun.  I know that most people don’t think in terms of database tables, filters and queries.  They don’t lie awake at night wondering how complex the database structure of Facebook must be – or spend an entire weekend building a custom database to inventory her personal library with ratings, reviews and ISBN numbers.**

I’m a minority, but I also know that I’m not alone.  Stand proud, fellow Data Nerds!  While your coworkers might tease you for the Excel spreadsheet you’ve created to keep track of your DVD collection, you are also the first place they go when they need help grabbing the right data.

Share reports efficiently – and answer the same questions less frequently!

policyIQ is designed to be a collaborative tool – and that’s true in Reports, as it is in all other areas of the application.  If you’re the resident Reporting Nerd in your organization, you can share your hard work with other members of your organization to save them time and headaches – and ultimately save yourself from being asked the same questions over and over again.

Here’s a few tips to get the most of your time and energy:

      • Create site-wide report Categories
        In version 6.7, we’ve introduced the ability to create site-wide Categories. (Until 6.7, Categories were always specific to each user. Now they can be shared with all users.) Create some Categories for your users to make it easier to find specific Reports. You might create reports based on usage (Sarbanes-Oxley, Account Reconciliation, Contract Management, etc); or based on the frequency that the reports might be run (Monthly Reports, Quarterly Reports, Annual Reports, On-going Status Reports.) What Categories you establish should be based on how your organization uses policyIQ – and what will make the most sense to your users.

 

      • Add Administrators or Viewers to critical reports – or to basic examples
        If you’ve created a report that would be valuable to others, add those individuals (or the Group to which they belong) to the Administrators or Viewers property of the report.

 

Administrators are able to make edits to the filters. If you don’t want any non-nerdy types messing with your report filters, you may prefer to simply add them as Viewers.

Viewers can run the report, but they cannot make any changes to the original Filter or Column selections.

  • Utilize the Description field to explain your report, how to use it or how to customize it
    If you do share a report with other users, use the Description field to add context to the report. The twenty filters that you’ve added make perfect sense to you – but to someone who doesn’t think in terms of data, it might not be clear. And if the report is something that is beneficial to others in slightly revised version – such as changing a Folder filter or a date range – explain how the report can be revised for those situations.

With shared reports already available, clear explanations and Categories that make everything easy to find, our Reporting Nerds can empower their fellow coworkers to retrieve their own answers and save everyone time and money!  And who knows, maybe one of those coworkers is an undercover Nerd who just needs a little guidance and experience.

What happens when the Reporting Nerd needs help?

Reach out to the policyIQ support team!  The ultimate Data Nerds*** are here to help you create what you need.  We won’t even tell anyone that we helped!  You get all the credit.

*    I’m told that this is actually quite common knowledge.  Sorry, my bad.
**   It wasn’t the WHOLE weekend.  It was actually just one Saturday.
*** Go ahead and tell them I said that.  They won’t mind.

Risk Control Matrix: A common report takes on some uncommon challenges

My dad is a high school teacher.  It’s hard to ask him a “quick question”, because he prefers to teach you how to find the answer rather than to just give it to you.  Sometimes he can be the most frustrating man in the entire world.  (Just ask my mother.  She’ll agree.)

As it turns out, I am a lot like my father.  If you ask me how to create a Risk Control Matrix report in policyIQ, I am likely to ask twenty questions first.  I could give you an answer, but it wouldn’t be the best answer.  When it comes to reporting, it is important to understand what the questions are that you are really trying ask your data to answer.

In all cases below, a Risk Control Matrix report will use the Detail Link report type in policyIQ.  (And remember that we use the terminology of “Risks” and “Controls”, which can easily be substituted for your “Objectives” and “Activities”.)  Let’s think about some of the questions that this common report might answer, including some uncommon perspectives.

What are our Risks and Controls?

Okay, so this is a really basic question, but it needs to be answered.  I think of this as the “big picture” report.  While you can look into your Folders in policyIQ to review the details of the Risks and related Controls, a single report to show all of those linked together is a great way to see the big picture.  Your external auditors might ask to see all of your Risks, sorted by Business Process, and the Controls that you have in place to mitigate them.

For this report, you’ll likely have just two data sets.  Data Set 1 will use a Template filter for all Risks in your organization, while Data Set 2 will filter for the Controls.  Remember to add the appropriate Columns to the report.  Most organizations use policyIQ Folders to capture Business Processes. If you need to sort by Business Process, be sure to include a Column for Folders in your report.

There are lots of variations on this simple question:

    • What are the Risks and the Key Controls in place to mitigate them?
      Filter Data Set 2 further by limiting to the Control Template Field of “Significance” to just those that are “Key”.
    • What are the Significant Risks and the related Controls?
      Filter Data Set 1 to the Template Field of “Risk Significance” to just those that have a “High” or “Significant” value.
    • What are your Risks, Controls and Test Plans across the organization?
      If Test Plans are part of the data request, include Data Set 3 and add a filter for pages from the Test Template. (Some organizations have separate Test Plan pages, which might be included.) Select your Columns to show just the test planning information and not the most current testing results; this data request isn’t about the testing results, just the plans in place to do the testing.
    • What are the Risks and Controls for my Business Process?Process Owners want to review their documentation from time to time.  (Okay, so Audit Directors WANT them to review their documentation from time to time.)  Filter the standard Risk Control Matrix for a single Business Process – and provide an overview for your Process Owner.  Make the process owner an Administrator on the report – and suggest that he/she save it to Favorite Reports for easy access.
    • Am I over-controlled or under-controlled?You can start to answer this question with this simple Risk-Control Matrix.  Make sure that your Risks to Financial Statement Assertions are adequately addressed by your controls (include Financial Statement Assertions as Columns in your results), and in those cases where they are addressed many times over, consider if some of those Controls may be downgraded to non-key.  If you want more information about Control Rationalization, check out our recent Risk Assessment blog post and training session.

All of these reports are coming from a risk-based perspective.  That’s most common, but it’s not unheard of to want to see your reporting from the control perspective.  Get wild and crazy!  Flip around your data sets!  Data Set 1 can filter for Controls, while Data Set 2 might filter for the Risks.

What Risks are vulnerable because of Deficiencies identified in our organization?

Ooh, good question!  This is what your testing is all about, right – where are we left vulnerable?  This can be a fun one to put together, too.

Start with your Deficiencies.  If you don’t have a separate page for Deficiencies, start with Test pages that have a “Failed” status.

Data Set 1: Filter for Deficiencies identified in the designated period of time.  (Likely to be this testing year.)
Data Set 2: Filter for the Test pages.
Data Set 3: Filter for the Control pages.
Data Set 4: Filter for the Risk pages.

This report – with four data sets of linked information and lots of great information – will provide you with the list of Risks that are left exposed by the Deficiencies that have been identified.  But is this the whole picture?  Some of those Risks might have additional or compensating Controls in place.  So…

Data Set 5: Filter for Control pages.

Again? Crazy, I know.  Data Set 3 will only show the Control that is linked to that failed Test.  But with this last data set, you can check to see if there is more than one Control in place that mitigates the Risk.  If so, you might decide that there is no vulnerability here.  You’re covered with another Control.

What is the complete Test Plan for this Business Process?

I’ve heard this question asked by many testers who are tasked with testing a complete process.  Before they dig in and get started, they’d like to take a look at the “big picture”.  Like we mentioned above, they could look page by page, but there’s something about pulling it all together that can make the information easier to digest.

For a tester, though, they rarely want to start at the Risk level.  Instead, they are more interested in the individual Tests that they are expected to complete – and how it rolls up into the big picture.

Data Set 1: Filter for the Test pages in that particular Business Process.  (Again, this is often based on a Folder, so be sure to add the appropriate filters.)
Data Set 2: Filter for Controls.
Date Set 3: Filter for Risks.

When you add Risks or Controls to your report, be sure to add columns that indicate the Business Process that they fall into.  In some cases, a Control or Risk might fall into more than one process.  Your tester will want to know that – and possibly even verify that the Test isn’t duplicative of something someone else is working on.

What do YOU want to know about your Risks, Controls, Tests, Deficiencies or Action Plans?

You’ll often hear us tell you that your ability to report in policyIQ is limited only by your imagination.  We know that’s not entirely true, but there are so many possibilities for powerful reports that we can’t possibly tell you about them all.  Instead, let us work with you.  If you aren’t sure how to create the report you need, contact us by email or give us a call and let us help you to ask the right questions.

Oh, and I apologize in advance if I sound like a 9th grade math teacher as I help you to “learn how to do it on your own next time”.  It’s genetic.

Want to categorize reports for the entire organization? Site-wide Report Categories are coming in 6.7!

I think we might have mentioned recently that we’re pretty excited about version 6.7.  The introduction of Custom Alerts will open up new doors for our policyIQ users – allowing you to use policyIQ to stay on top of those things that are most important to your organization.

But Custom Alerts is not the only new feature coming in version 6.7.  In fact, the introduction of Site-Wide Report Categories is likely to be met with even more enthusiasm by our existing clients.  When we rolled out version 6, we were thrilled to be able to offer Report Categories, allowing users to better organize all of those reports that they create and run – or those that are shared with them. At the time, it seemed most important to make those categories user-specific, so each individual could have their own custom categories.  As we’ve learned over the past few years, site-wide, shared categories are even higher in demand.

sitewidereportcategoriesYou asked.  We answered.

Site Administrators in your policyIQ site can now create Categories, and designate those as “visible site-wide”.  With this setting, the Report Category will be available in the left hand navigation of the Reports module for all users who have access to reports.

Don’t worry – those personal Report Categories aren’t going anywhere.  Users will still be able to have their own Categories – and Reports can be indexed into as many Categories as you’d like.  Keep your own lists of Reports, but use Site-Wide Reports to share those most critical to your entire user base.

Need some ideas of Categories?  We’re full of ideas!

  • Sarbanes-Oxley Reports
    All of the critical reports in your SOX process can be bundled together and made available via a standard Category. If you have various locations, you might consider creating a Category for each Location, with reports filtered to just those results that apply to them.
  • Site Maintenance Reports
    For organizations with a number of Site Administrators or Location Administrators, create a Category into which you can place some standard maintenance reports. User lists with most recent log-in date, or pages with the Last Updated Date. Keep on top of the information that is going stale – or just report on Pages in Draft that still need to be completed and published.
  • Policy Management Reports
    Using policyIQ for Corporate Policies? Keep a Category of reports available to include reports on things like All Policies Updated in the last 30 days, or Policies past their expiration date.
  • External Auditor Reports
    You’ve created reports for your external auditors – now make it simple for them to pull up those reports and dive into their audit work!

No matter how you are using policyIQ, I’m sure you can think of ways that you’d like to implement site-wide Report Categories.  Not a Site Administrator?  Contact your Site or Location Administrator for help in creating a site-wide Category that will be useful for the organization.  Need help?  Contact our support team!

Effectively reporting on changes

If you’ve done any reporting in policyIQ, you know just how powerful our reporting tool is. Using filter selections, you can report on pretty much anything that is going on in your policyIQ site. And with our customizable column selections, you can display exactly the amount of detail that you need in your report results.

Change reports are not quite as well-known as our more standard report types, but they offer an extremely valuable insight! These reports give you a look at what changes have – or have not – occurred in your policyIQ site over a selected period of time. And when you look at the long list of change filters we provide, you’ll find that there are a ton of different changes you can report on.

First, let’s take a look at where you’ll find the change filters, so you can start building your very own change report.

Selecting Change filters

Create a new Page report, and then select Changes from the Add Filters dropdown list.  The Changes window will open, with a list of different change types you can select, as shown in the image to the right. change_filter

With all of these change options available, you may be wondering which of these would be most useful to you. As you can see, we’ve given you the ability to report on some very specific types of changes. When was an Administrator Group added or removed? When was an attachment or a linked item added or removed? When was the Page removed from a Folder?

But there are some change filters here that most users would find helpful, once they realize what they can do with them. For example, the Item Field Changed filter can let you see when a specific custom field on a Page has changed. You likely know how to create a report that lists all of your Key Controls within a selected process. The Item Field Changed filter takes your reporting a step further and lets you see when the Significance field on your Control Pages was changed from Key to another value (e.g., Secondary or Non-Key), or vice versa.

Tell me everything! Sometimes, you may just want a comprehensive list of all the changes that have occurred on a particular Page. In that case, you’ll find the Any Change filter to be extremely valuable. Of course, a report like that is likely to bring back quite a few results, so don’t be alarmed if the report takes a bit longer than usual to run.

Pick Columns to determine what detail you want to see in results

Once you’ve made your change filter selections, you’ll want to make sure you’re also choosing columns that will display the most important information related to the report results. There are a number of column selection options that go hand-in-hand with change filters.

Building on our example of changes to the Significance field, you’ll probably want to include a column selection of Change Details. In most cases, this will show you what the previous value in the field was. So within that column in our report results, we’ll see if a particular Page’s Significance changed from Key to Non-key, or from Secondary to Key, and so on. When applicable, this column will also display any notes that a user entered at the time they made a change.

Let us help!

Would you like some help creating a particular change report? Don’t forget that the policyIQ support team is here to assist. Send us an email any time at support@policyIQ.com, and we’ll be happy to help you out!