How to use advanced grids

The Workforce Report and Timesheets Report use advanced grids to provide flexible querying of the data.  These grids can seem a little overwhelming at first but once you get used to them they are easy to work with and can ensure you have access to the data you need.

Before jumping into the different actions you can take let's get the lay of the land:

• The red area in the top left is the group by bar.  You can drag column headers in this area to group by them.
• The yellow area in the top right is the button bar where there are different actions available.  The Workforce Report and the Timesheets Report have some similar and some different actions you can take.
• The filter icon in the green area in each column bring up the filter menu for each column.
• The search box in the purple area in each column is the search box for each column.

Let's review some of the different things you can do and how to do them:

• Add/Remove columns with the column chooser
• Reorder columns
• Filter columns
• Sort columns
• Group columns
• Export the grid to excel
• Reset the columns back to default view
• Keep the current columns but equally space them

Add/Remove columns with the column chooser

In the button bar hit the column chooser button.

That will bring up the column chooser and you can check and uncheck the different columns you want to be included in the data grid.  Once you check a column it will be added to the data grid.  To remove a column simply uncheck it.

Reorder columns

To reorder visible columns simply left click and hold on a column header and then drag to the position you want that column in the data grid.

Filter columns

There are two way to filter columns: using the filter menu or the search area.  Generally speaking the filter menu is better when there is a small set of values for the columns (i.e. Yes/No) and the search area is better when there are many values for a column like a person's name.

To use the filter menu click the filter icon in the header.

This will bring up the filter options based upon the type of column the data is.  Dates will have a slightly different look than string values but the use is the same.  Simply check which values you want to be included in the data grid.

To use the search menu the first thing to consider what type of search you want to do.  You can do a contains, greater than, less than, etc.. based upon the type of column.

Hovering over the magnifying glass for a numeric column will bring up a dialog like the following.

From there you can pick what type of search you want to do.  Simply write your text in the text area and the grid will perform the search as you type.

Sort columns

To sort columns simply click the column header.  If you want to sort ascending or descending click the same column header multiple times.  

If you want to sort by company name and then sort by last day seen click company name and then while holding the shift key hit last date.  Holding shift while clicking a column header will apply a secondary sort.

Group columns

If you want to group the grid by a certain column left click and hold the column header then drag the header up to the group by bar.

You can group by multiple columns, the following screen shot groups by sub contract and then by last seen.

You can change the order in the group by bar.  The following groups by last seen and then sub contractor.

When you have data grouped you can use the carrot to toggle if all the rows are collapsed or expanded.

Export the grid to excel

In the button bar use the export button to bring the export dialog.

Export all data will export data for the entire report while Exported selected row will just export the rows that are selected.  Most of the time when you are doing an export you are going to want to choose Export all data.

When exporting grouped data and you just want the summary data, follow the steps below in MS Excel.

1. Open the exported file in MS Excel
2. Choose Find
3. Select Go to Special...
4. Select Visible cells only
5. Copy data selected and Paste into a new tab
6. Optional: Use Find & Replace to remove extraneous labels from data as needed for downstream reporting 

Reset the columns back to default view

Sometime if you have been playing with the data and just need to reset to the default view.  To do so click the reset icon.

Keep the current columns but equally space them

Sometimes when adding/removing/reordering columns a column might get squished.  If you want to keep the current columns but reset their width and layout use the layout button.