What is Worksheets: Driving it home

In our first blog of this series, we started the journey with “What is Worksheets?” and we looked at the setup of Drive, the security setup of Worksheets, the example audit report called Duplicate National ID and how to enable a report for Worksheets.

In this second blog, we focus on:

• The next steps of how to add our example report Duplicate National ID to a Workbook;

• Adding all the other audit reports to the Workbook and add pivot tables; and,

• How easy the Overview Tab will show the user a one-pager to help troubleshoot potential issues.

Add “Duplicate National ID” to a Workbook

To re-cap – ensure the report is enabled for Worksheets.

Navigate to the report in question – in this case, the Duplicate National ID report and Edit the report. Navigate to the Advanced tab of the report and enable the following 2 items for the report to be added as a Workbook on the Worksheet:

• Check the Enable As Web Service checkbox under Web Services Options.

• Check the Enable for Worksheets under Worksheets.

See below:

To add the report to a Workbook, navigate to your Profile, and then select Drive:

Once in Drive, access the New button at the top left-hand corner to create the Workbook. And then select Workbook:

Once you are in the Workbook, access the Add Live Data button and search for the reports. As we discussed, in this example, we are adding the Duplicate National ID report to the Workbook:

Then add the columns or “fields” from the Duplicate National ID report to the Workbook by dragging them from the Available Columns to the right-hand side. Repeat for all columns or “fields” needed:

Then structure the table that will become a tab in the Workbook and give the table a name. In the example below it is called Duplicate_Social_Test – see the picture below on the left. You can also schedule when you want to refresh your live data – see the picture below on the right:

We have now added our first report to the Workbook we have created.

Repeat the process above to add all the reports needed. See below a snapshots of what we used:

Add Pivot Tables

Once all our reports are in our Workbook, highlight the data on each tab/report and insert a Pivot Table for EACH tab/report in the Workbook by using the task Create Pivot Table. The idea is to add each Pivot Table to its corresponding tab/report in the Workbook. See picture below on the left.

And for the Values on Pivot Table, we are counting the number of errors. To achieve this – add the Worker field as it will return the number of errors. See the Pivot Table settings picture below on the right:

Remember: Repeat this action of adding a Pivot Table for each tab/report in the Workbook and then link it back to the Overview Tab that will display the number of errors per audit report. The Overview Tab will summarize all the different reports.

To link the different reports back to the Overview Tab, select a cell on the Overview Tab and type in the “=” sign and then go back to the tab/report with the Pivot Table error and select that value. Upon doing this, hit Enter to return that Pivot Table value back to your Overview Tab.

The result will look like the screenshot below:

The Overview Tab

The last step in the process is to use Conditional Formatting and Rules on our Overview Tab to ensure the cell turns red whenever a number greater than 0 is returned. Or the cell turns green whenever a number of less than or equal to 0 is returned.

See below:

In essence – audit reports WITHOUT issues will return a value of 0. And audit reports WITH issues will return a value of 1 or more. And because of the Conditional Formatting and Rules used – it’s a quick visual check to see where red cells appear as any report not equal to 0 indicates that report has outstanding issues that needs to be investigated.

So at-a-glance you can tell which audit reports even need to be run and reviewed!

Conclusion

The benefit from implementing this into our Workday tenant has been huge time savings. Sometimes reports can take a while to run depending on its configuration and data source. But because of this easy-to-use Overview Tab using Conditional Formatting and pivot table logic, it outputs the errors on the Overview Tab and isolates the reports that need to be reviewed.

Looking forward to seeing you in the Worksheet session of the 7/11 Share-a-thon (registration required).

Author: Scott from Texas