Report Designer is an Excel add-in that allows users to create reports within Solver. It is a full-blown Excel-based report writer enabling the user to report on top of their organization’s data. Business users with basic Microsoft Excel skills can quickly create presentation-quality financial statements, operational reports, and KPI reports with charts and graphical indicators.
These reports can be used by the creator only or may be shared across the organization via Categories. Report Designers can also choose to run reports in Excel. Additionally, Reporting licensed users get a lightweight version of the application, allowing them to run reports in Excel, without design capabilities.
For more training, check out the Solver Academy.
Common Terminology
The following terminology is commonly used when working with Report Designer and referenced in this article.
- RD – The acronym for Report Designer
- Designer – a shortened name for someone who will use Report Designer
- Solver Ribbon – the add-in found within Excel
Report Designer Install
The first time you use Report Designer, you must install the Solver Reporting add-on for Excel. Make sure your computer meets the technical requirements listed below.
NOTE: You must have administrator rights on your pc to be able to install Report Designer. If you don’t, you must contact your IT department to get help.
Instructions on installing Report Designer can be found in our Report Designer Installation article.
Report Designer Tab
Once successfully installed, the Solver Reporting tab will appear in your Excel.
The Solver Reporting add-in has the following features.
- Create a Report
- New Blank: A legacy button that does not function in Solver. This button will be removed in future versions of Report Designer.
- New Current: A legacy button that does not function in Solver. This button will be removed in future versions of Report Designer.
- Upload Report: Upload your current changes to Solver. Click this button only after click Save within Excel.
- Configuration
- Data Connection: Displays information on what tenant this report is connected to.
- Report Properties: Properties that can be viewed and configured for this report including
- General: properties of the report including who created the report and when.
- Protection: If you are using Excel protection, itis required to provide the password so that Solver can paste the report data into a protected sheet.
- Reporting:
- Keep formulas in run report: enabled by default, this feature allows users to view the formulas when a report is ran in the web.
- Budgeting: these properties only apply to Input Templates and Assignments
- Force refresh when opening report in Portal: disabled by default, enabling this feature will refresh the report automatically when this report is opened in the web.
- Protect non-input cells: Enabled by default, this setting will prevent users from typing into cells that do not have budgeting settings.
- Allow line item details: Enabled by default, this setting allows users to store Line Item Details on the template.
- View: Click any of these icons to display or hide the respective menus.
- Report Designer: enabled by default, this is the report designer navigation pane
- Layout Editor: Enabled by default
- Report Parameters: hidden by default and typically not shown while designing reports.
- Design
- Auto Parameters: enabled by default, this option will automatically create parameters for non-custom dimensions.
- Layout Comments: Click this button to see details of the entire report as comments. This is the same information that is displayed in Layout Editor.
- Options:
- Designer
- Always open Report Designer in Run mode: Disabled by default, this option will open designer on the Run tab.
- Insert filed title above cell on drop: Disabled by default, this option will paste in text of the field dragged into the report one cell above where the field was dragged in.
- Show tooltips in Module: enabled by default, this property shows the underlying column name of the highlighted field within the Navigation Pane.
- Create Group dialog
- Automatically create parameter when creating a new group: Enabled by default, this setting is the same setting as enabling/disabling Auto Parameters.
- Language: change the language to select, supported languages, to be displayed in Report Designer
- Application: change the language shown within RD
- Integration Dictionary: Change the language of the integration fields. For Data Warehouse reports, this is set to English as the language shown is defined within the Data Warehouse Configuration.
- Application
- Auto-connect: Legacy feature that does not function. Will be removed in future version of Report Designer
- Auto-Start Report: Report will automatically load Report Designer
- Sign in Automatically: Legacy feature that does not function. Will be removed in future version of Report Designer.
- Portal URL
- License
- Licensed To: This will display the logged in users name
- About: Display the installed version number of Report Designer.
- Designer
- Help: A quick link to Solver’s Help site.
Working in Report Designer
Once successfully installed, a Solver Reporting tab will appear in your Excel.
When working within Report Designer, it is important to note that all actions must begin within the web interface including
Report Designer Interface
Once you have opened a report in Excel, the Report Designer interface has three main features
- Navigation Pane
- Layout Editor
- Excel Grid
Report Designer Navigation Pane
Found on the left-hand side of your Excel grid, the navigation pane will show all fields that your administrator(s) has granted data access to. When creating a report, you will drag fields from this pane to the desired cell within Excel.
Navigation Pane Terminology
The navigation pane has the following features
- Factsets: A factset is a set of data such as your General Ledger Summary, General Ledger Detail and other data. Typically, one type of data goes into a factset. For example, you will typically not combine General Ledger and General Ledger Detail into one factset, but instead put each data set into different factsets.
- Modules: A module is a grouping of relevant data. When using the Data Warehouse, the General Ledger “module” will have your General Ledger and General Ledger Detail factsets. All other modules list in the navigation pane when working with the Data Warehouse will only contain one factset and cannot be changed.
- Because of this, the terms factset and module are sometimes used interchangeably with the Data Warehouse
- Dimensions: Dimensions help define a transaction. While dimensions and attributes, described below, both define a transaction, the difference between the two is that a dimension does not change. For example, account is considered a dimension, because on a single transaction that account will never change. The amount associated to that account can change which would make amount an attribute.
- Attributes: Attributes help define a transaction. Attributes of a transaction can change, such as the account category or a monthly amount.
The navigation pane will list all modules you have access to and will include a couple more features.
- System
- Current Date: gives you the current date in UTC format
- User: gives you the logged in user’s name
- Currency: Allows you to report off monthly rate information that has been imported into your tenant database.
- Period Functions: Displays a list of common period functions that can be applied to your row or column filters. This list is also available when you drag period into layout editor.
- Trees: Display s list of row trees that have been created in the Data Warehouse. This menu item will show all trees that have been created in your Data Warehouse along with allowing users to see the hierarchy. However, users cannot see data associated to the hierarchy unless that have permissions to that module’s data.
- Expressions: Use expressions to create a filtered grouping.
- KPIs: In combination with expressions, KPIs allow you to create mathematical formulas based on expressions to calculate a KPI such as gross margin.
- Drill-To Definitions
- Report Parameters: sometimes referred to as “at parameter” because its underlying syntax is @parameterName, the report parameters menu lists all parameters that have been created on the report. You can drag this field into a report to display the user’s selection at run time on the sheet.
When creating sheet per value reports, use the parameter found in the report parameters menu to dynamically get the right parameter to appear on each sheet.
Layout Editor
Layout Editor is a popup within the Excel grid that displays key properties of the current sheet that you are on. One workbook can contain many worksheets with report properties, layout editor only shows the properties of the sheet you are currently on.
The Layout Editor will show you the
- Row and column expansion properties
- Sorting and grouping properties
- Filters on the sheet, rows and columns
- Drilldown and Drill-To Properties
- Budgeting setup for budget Reports
Expansion Groups
Commonly referred to as Row Expansion and Column Expansion groups, expansion groups define whether or not a row will expand for a range of data, grouped by a specific value or will remain as a single row/column displaying an aggregation of the data being filters and grouped on.
- The most common example of a row expansion group would be Accounts. In many financial statements, accounts are displayed on the rows.
- The most common example of a column expansion group would be Periods. In many financial statements, each periods worth of data is shown across columns.
By default, dimensions are set to expand by default.
This can be changes by clicking the Parameters
Nested Expansion Groups
Nested expansion groups is a design concept where you “nest” or “stack” expansion groups on top of one another.
This reporting concept allows you to expand by multiple attributes. In combination with dimension attributes, users can achieve advanced reporting concepts that take into account your changing account structure.
Groupings
Groupings, commonly referred to as “group by”, play an integral part in how data is presented within your report and help make reports dynamic and simpler to maintain. Groupings define what value a row/column will expand by. For example, instead of creating a row for each account in your Chart of Accounts (CoA), simply drag account in once and define the grouping to be Account. In doing so, each unique account code that meets the criteria of the row filter, will appear on its own row within excel.
Without defining this grouping, your accounts will not expand that and min/max account code will appear on the single row.
While groupings are great and help you achieve your reporting goals, only apply the attributes that are needed. Excessive groupings will slow down your reports.
Best Practices for Groupings
There are a couple best practices for groupings.
- Too many groupings will cause performance issues. As a starting point, attributes that are unique such as Code are all that need to be brought into the groupings tab.
- At least one grouping should remain on each expansion group to ensure drilldown data is correct.
Sheet Level Filters
Sheet-level filters are any filters that apply to the entire sheet. These are visible by clicking the square box in the upper left-hand corner of the Layout Editor. Sheet-level filters typically are
- Parameters that the user defines when the report is being executed
- Advantageous from a performance perspective.
By utilizing sheet-level filters, users can achieve dynamic reports that are performance-friendly. It is recommended to have as many sheet level filters as possible.
Row Level Filters
Row level filters are filters that are defined on a specific row.