Live Data Reports in Worksheets
Q: What should I do if a Workbook freezes while running a Live Data report?
A: Unfortunately, there is no way to stop the report from running once it starts, most Live Data reports should time out after 30 minutes and then unfreeze the Workbook.
Q: How can I view all my schedule Live Data Reports?
A: Scheduled Refreshes expire after 1 year. You can run the Workday report “View My Workbook Details”. This shows a summary of all the Workbooks you own and any associated Live Data Reports. If the reports are on a refresh schedule, the frequency, next scheduled date, and expiration date are given.
Q: How can I view all values for a specific field from a Live Data Report?
A: “To add all values that may be present in a field, use the “Enable Multi-instance Values” check box. The ‘Enable Multi-instance Values’ check box is found on the final screen before running a Live Data Report.”
Q: Why doesn’t a comment added on a cell move with the transaction after refreshing the live data?
A: Comments are only linked to the cell they are added to, not the transaction line. A note column can be added to a live data report and tied to a specific transaction line.
Q: What happens if I add my live data as Static Values instead of as a Live Data Area?
A:
- You will no longer be able to edit or refresh the live data.
- Multi-Instance Values will be automatically enabled.
- You cannot use formula or notes columns.
Q: What is the maximum size my Workbook can be?
A: “Max # of Rows in a Worksheet is 1,048,576.
Max # of Cells in a Workbook is 20 Million.”
Q: What does the error “Web Socket connection failed” mean when I try to refresh Live Data?
A: This error can occur due to network connection or firewall issues. Please try clearing your cache first. If the issue persists, Workday may have connectivity issues.
Q: Does the output from a live data Workday report truncate/summarize if you exclude certain columns from the output?
A: No, all underlaying rows will be retrieved, regardless of which columns are selected. Users can summarize data using formulas or pivot tables.
Q: What Workday Reports are available for use in Worksheets?
A: In Drive a user will see the reports that have been Enabled for Worksheets, that they have access, when choosing to ‘Add Live Data’ to a Worksheet. If a user already does not have access to a Workday report, they will not have access to that report in Worksheets.
Note: - “If ‘Include reports not enabled for Worksheets’ is selected, a list of reports a user has access to will display This option is not recommended for Worksheets.”
Q: How can I maintain my formatting, such as decimals or dollar signs, in a live data cell after the dataset has been refreshed?
A: In Worksheets, any formatting which is applied directly to cells within a Live Dataset will be lost once that dataset has been refreshed. One way to keep applied formatting within a Live Dataset is to apply conditional formatting to the row or column desired. Conditional formatting will continue to populate within the appropriate cells even after a Live Dataset refresh.
Q: What are the differences in the colors of the columns within a Live Data Array?
A: Worksheets color codes the columns within a Live Dataset to more easily identify what columns can be edited and what type of edits can be performed in the columns. Blue columns indicate live data from the Workday report being brought into the Worksheet. Blue columns within a Live Dataset cannot be edited. Green columns indicate where formulas can be utilized/edited within the Live Dataset. Finally, Yellow columns indicate where notes and comments can be placed/edited for reference within a Live Dataset.
Q: Is it possible to update prompts for all Live Data reports at once?
A: The use of Global Prompts within a Workbook will allow users to tie the same prompt fields from multiple Live Data reports to one interface. (Example: Mapping the Cost Center Hierarchy Prompt for two different Live Data reports to Global prompts will direct both reports to run off of the same value).
Q: Why do certain fields appear differently in Worksheets compared to the corresponding Workday report?
A: Literal Boolean values appear in Worksheets reports but can be customized in Workday Report output. For Example, an anchor report may have “Yes” or be Blank. In the Worksheets version, they show up as “True” or “False”.
Q: Can I refresh a single Live Data Report instead of all my reports?
A: Users with Report Edit access can manually refresh all or specific reports as desired. Scheduled refreshes will refresh all Live Data Reports.
Q: Why can’t I see the entire name for certain fields or resize the frame when adding live data?
A: The full column name can be seen by hovering over every value. Columns can be renamed once added to the live data.
Q: What does the “#SPILL!” Error mean when I try to add Live Data?
A: This error can occur if the data set being returned by the report will expand into a cell that currently contains data or contains blanks. Verify that no cells contain data or blank spaces in the desired range before adding live data or pivot tables to a sheet.
Collaboration and Sharing Worksheets
Q: Can I prevent a user from making their own copy if I share a worksheet with them and grant them view-only access?
A: Yes, you can go to advanced options when sharing the report and check the box allowing users to copy or download the report that has been shared with them. However, this should not be viewed as sufficient security when sharing sensitive information.
Q: How can I comment at somebody so they receive a notification to review a Worksheets cell?
A: User must share the file with the person they want to comment at. They can then add a comment to the desired location in the file and in the comment type @ and the user’s name. Once the comment is posted, the person notified will receive a Workday alert. Currently, this functionality is only available in comments and not supported in Notes columns.
Similarities / differences in Functionality between Worksheets & Excel
Q: How do I use filters on header rows within Worksheets?
A: In order to filter on specific values within a data table, you must first “Uncheck All Shown” values, and after this, you can search and check the values that you wish to see in the dataset.
Q: Can I easily copy down a value/formula into multiple cells, like using ctrl+d in Excel?
A: Worksheets provides the ability to drag down or copy and paste, it does not support ctrl+d.
Q: Does Worksheets show sums or counts in the Workbook?
A: Yes, if you highlight two cells containing numeric or text values the count/sum will appear in the top right corner of the Workbook.
Q: Why do my Excel keyboard shortcuts not work in Worksheets?
A: Worksheets keyboard shortcuts differ from Excel. Worksheets has a Keyboard Shortcut Guide available from the Help section on the toolbar at the top of their workbooks.
Q: Why does my Pivot Table convert to Text when downloading a Worksheets file to Excel?
A: Unfortunately, there is not currently a way to download Worksheets files and maintain Pivot table functionality in Excel.
Q: I downloaded my Workbook to excel with formulas, but my formula is not updating when I change my data. Why is this happening?
A: Worksheets has a number of proprietary formulas that are not available in Excel. If your workbook utilizes any Workday proprietary formulas, they will not work in Excel.
Q: Can I use Advanced filtering options on Live Data to filter by highlighted colors etc.?
A: Unfortunately, Advanced Filtering is not available in Worksheets.
Q: Freeze Panes only gives me the option to Freeze “Top Row”, “First Column”, or “Freeze All”. Can I Freeze panes starting on a Row or Column other than the top Row or First Column of my Workbook?
A: Yes, if you want to freeze Rows, you can Select Freeze “Top Row” and/or “First Column” and then you can dynamically drag to your desired row or column.
After you choose to freeze the Top Row and First Column, Grey Lines appear. after you select a freeze option indicating which rows or columns have been frozen.
You can then hover on the dark grey line between the columns or the rows until your curser changes to two black arrows separated by a line. You can now click and drag your curser to the desired column or row you’d like to freeze panes on.
Formulas in Worksheets
Q: What caused the formulas in my Workbook to stop working?
A: Adding /removing columns from your live dataset could result in reordering fields referenced in your formulas.
Q: Why don’t my formulas expand or contract with live data or pivot tables?
A: Worksheets contains the functionality to have a formula grow or shrink with datasets. You can choose to insert a Formula Column into your live dataset which will calculate next to each row of data within the live dataset, or you could use an array formula.
Q: My formula has made my Workbook spin, and I can’t get back in. What do I do?
A: The default time limit for how long a Formula might run in a Workbook is 4 hours. This can be changed in File >Settings and choose to change the recalculation time limit. If recalculating takes too long, switch to another tab, if possible, or delete the recalculating tab to free up the Workbook.”
Q: How do I link to business documents or transactions within my NotifyIF function?
A: Unfortunately, the ability to link to business documents and transactions in a notification spawned from NotifyIF is not currently possible.
Q: Can I use a person’s name with a NotifyIf formula?
A: NotifyIf only works with Net IDs.
Q: How can I get help with a Worksheets formula?
A: A User Guide and Function Reference document, containing a section on Formulas can be found under Help from the toolbar at the top of the workbook. A folder is also available in Drive for all Worksheets Users containing Tips & Tricks for useful Formulas.
Q: Why does my SQL formula throw an error?
A: SQL can be used in Worksheets, but the Syntax is different than in many common platforms. Some tips and tricks have been added to Drive.
Q: If a notification is sent using Notifyif, will the user only see the portion of the workbook that they have access to?
A: Users will receive the text notification with a link to the Workbook. That link will only be clickable if the Workbook has been shared with the user. The sharing rules apply here, so the user will be able to view the full Workbook based on the Owners access and comment or edit if they have those permissions.
Q: Is there a way to send a live link using Notifyif?
A: Notifications are received as text, even if the URL is spelled out. The only clickable link in a notification is to the Workbook if the user has access.
Q: Why do I get a date instead of numeric value when calculating the difference between two dates?
A: When attempting to find the difference in days between two date fields, an embedded text function is needed for the proper format to be achieved. The solution is to write the formula within the “TEXT()” function in order to have the results return as desired. For example: =TEXT(TODAY()-C2,).
PivotTables in Worksheets
Q: Within a pivot table, is there a way to manually sort data within a row or column?
A: Worksheets defaults to alphabetical sorting of columns and rows vs. numerical. You cannot manually select the order of the data in columns and rows like in Excel.
Q: How do I add a custom calculated field to my PivotTable?
A: In Worksheets, there is not functionality which allows you to add a calculated field within a PivotTable. The recommendation is to add a formula column to the underlying data.
Q: Can I dynamically filter values in a Pivot Table?
A: Worksheets does not allow the ability to filter on Pivot Table values. A formula can be added to the Source data and that field can be used as a Filter. For Example, Add an IF statement to calculate rows with totals greater than $20 in the source data then use that new field to filter on the True values.
Q: Why does my Pivot Table formatting reset after refreshing or adding rows?
A: Formatting on numeric values stays when formatting is added in the “Values” section of the Pivot Table Panel. Formatting will be lost when using the formatting options in the top Toolbar.
Q: Why is my pivot table not recognizing the headers from my live data?
A: This is likely due to the report type being used in the pivot table. By default, Composite reports have an extra row inserted at the top of the live data array in Worksheets. This blank top row makes the pivot table unable to detect the actual header rows when pivoting directly off the live data. Instead of the column labels, users will see Column A, Column, B etc.. In order to have headers recognized in the pivot table, users can create a new tab and enter the following formula: =REMOVEROWS(ARRAYAREA(YourLiveDataSheet!A1)) [you must replace the preceding highlighted text in the formula with the tab name from your workbook which holds the composite report’s live data]. A pivot can now be created using this secondary lookup tab.
Other Questions about Worksheets
Q: How do I create a new Folder in Drive?
A: Worksheets Users do not have the ability to Create Folders in Drive. Users can request folders by sending an email to finance.reporting@yale.edu.
Q: How do I upload files to Drive?
A: Worksheets Users do not have the ability to upload files to Drive. Sensitive Non-Workday data should not be uploaded or copied and pasted to Drive since this information is saved to the cloud and gives Workday access to information they do not already have access to.
Q: What does error message HTTP Status Code Error: 405 mean when attempting to export the Output of my Workday Report to Worksheets using the Export to Worksheets button?
A: There is a limitation when attempting to export data from reports with more than 8K rows to Worksheets. Workday is aware of the issue and users are advised to download to excel and upload or copy and paste the data, unless it is available as a live data report.
Q: Can I Use Worksheets on my Mobile Device?
A: “Yes, Worksheets can be accessed through the Workday Mobile App with limited functionality. Key Functionality of Mobile Device is listed below:
• Ability to See all Files and Folders on Drive
• Ability to Share, Copy, & Delete Files
• Ability to Open Worksheets and View Data (Files are view only but mobile users can add comments)
• Cannot Edit Worksheets from Mobile Device
• Data Values are not clickable to Workday transactions
Q: I have an external reference in my Workbook, but it did not automatically refresh when I reopened my primary Workbook. How can I refresh my Workbook?
A: Worksheets has changed the default for refreshing external references to require the user to manually select the green External Reference Refresh button located at the top ribbon of the Worksheets display.
Once selected, the external reference in the workbook will refresh to the latest data from the external source.
Q: I want my external reference to update automatically when I open my Workbook. How can I make my external reference update without having to use the External Reference Button?
A: To change to automatically update external references, click the File button in the top Ribbon and then selecting Settings from the drop down. Select the checkbox to “Automatically Update External References on Open” and then click OK.