ESM’s Purchase, Sourcing and Contract Repository contains a reporting tool which allows Admin users to assign the ability to create and run reports to themselves and other users. Here are some tips and tricks that are useful when running reports.
Tips and Tricks
- Suppressing Entire Duplicate Rows - Often times you will need to remove duplicates that although distinct when considering all fields, are duplicates for the fields you are interested in. This can be achieved by using the cell-level suppress duplicates, and then introducing a conditional formatting that suppresses the entire row when that cell is blank.
Duplicate cells can be suppressed by clicking the cell and pressing the Suppress Entire Duplicate Rows Button
Then, Right click on the cell and click “Format Cells.” Form the Format cells window, click the conditional tab and then click the action “Suppress Row”. Then click the Formula button. In the formula area, enter the following formula:
Len(CellValue()) = 0.
This formula will ensure that when there is no text in the cell (because the cell has been suppressed) the entire row will be suppressed.
Note: This may require additional ordering to ensure that the right rows are suppressed, possibly switching from Ascending to Descending as well.
- Page Breaks - Even if a Header segment is unnecessary for a particular report, adding a suppressed row to the header section with a page break will ensure that groupings are displayed correctly.
- Line Numbers - Line numbers can be generated by using the RunningSum() function in a suppressed row. This function also accepts a second argument, that allows the RunningSum() to respect groupings.