OfficeConnect has many helpful features that enable detailed report creation tailored to customer needs. Below are a handful of advanced features worth pointing out.
Absolute vs Relative Time
Most often you will want to open a report and view it as of a certain date without manually updating the report. By properly building your report with OfficeConnect’s dynamic time options, you can do just that. Under Workbook Properties in the ribbon, there is a checkbox to make any new time elements added to the report relative to the selected report date. This functionality can be turned on and off throughout the creation of the report.
One can also manually update a time element’s relativity by selecting the appropriate row or column, right clicking on the time element in the Review tab of the Reporting Task Pane and clicking either “Switch to Absolute” or “Switch to Relative”.
Once all time elements are added correctly, simply select the desired report date in the Workbook Properties and refresh the report to dynamically update your data.
Time Components & Context
Components and Context time elements are additional options to the individual periods, available to satisfy different reporting requirements. Keep in mind, to be a valid time element it must include a year, so when utilizing weeks, months, or quarters they must be added in combination with a valid year.
A common use case is the need to display January through December for a given year, or Q1 through Q4. To effectively do this, start by un-checking the dynamic time option in Workbook Properties to ensure your month or quarter time elements will be absolute. Under the appropriate stratum component, select either January through December or Q1 through Q4 and drag them to the desired locations on the report. Back in Workbook Properties, re-enable the dynamic time option as this will keep the year of the data relative. Select the desired year in relation to your report date and overlay the time periods you previously added. Now your report will always display January through December or Q1 though Q4 for the desired year relative to your chosen report date.
Time contexts are very helpful in quickly displaying a beginning balance or consolidated-to-date value. Keep in mind, these functions need to overlay an existing, valid time element in the report to properly connect to an instance.
Filters
Filtering data is a necessity and OfficeConnect provides the ability to filter on accounts, time, levels, versions, currencies, attributes, and custom dimensions. Filters can be applied either to an entire workbook or individually to each worksheet. Workbook filters exist on the Filters tab of the Workbook Properties and worksheet filters exist as a separate icon in the ribbon or on the Filters tab of the Reporting Task Pane.
Repeating Reports
Ever find yourself needing to create a report multiple times to account for one filter difference? Maybe you need to generate a P&L for each department. OfficeConnect makes this effortless with just a few clicks. There is no need to manually make copies of the report and adjust the data to reflect each department. Simply create your report with no reference to departments (essentially it should assume all departments), this will act as your starting template. Click Repeating Reports in the OfficeConnect ribbon, select the appropriate element type from the drop-down (departments in this scenario could be a custom dimension or part of your level structure), under the Selected Elements section choose the departments you want to generate a P&L for, select the desired Delimiter and Sheet name format, which together control the worksheet tab titles, and click Create. Multiple tabs in your workbook will auto-generate based off the starting template and each worksheet will include data filtered by one department. One thing to note, any element types already in use as a filter will not be available as an Element type in Repeating Reports.
Labels
If you change an account name or dimension value in your instance and want to ensure the names reflect properly on your reports, utilize Labels to stay dynamic. As long as the element is incorporated on your report, you can apply a label for it. System Variable Label types are very handy as well, especially Report Date and Repeating Report Element. Get creative and store labels in hidden rows and columns and reference them via Excel formulas to drive tailored headers.
