What's New: Server and Database - New Reporting Capabilities (2023 release wave 2)
New reporting capabilities in Business Central 2023 release wave 2: data analysis on query objects, Excel layouts with multiple data sheets for parent-child data, Power Query and Power Pivot in Excel layouts, and teaching tips and help links for reports and queries. Data analysis on lists and queries is stated as generally available.
- Data Analysis on Queries
- Excel Layouts for Parent-Child Relationships
- Excel Power Query and Power Pivot Integration
- Teaching Tips and Help Links for Reports and Queries
- Reports Documentation and Resources
- Analytics Strategy and Query Design Approach
- Relevant Sessions and Closing
Overview
The session covers server and database reporting changes in the 2023 release wave 2. The query object now has an analysis view attached and can be opened from the UI with Run object, for example as an action on a page, with grouping and pivoting. Drill-downs run as separate queries at the server level, so large record counts can be summed and explored.
For Excel layouts, parent-child (overview-detail) data can be generated as multiple root data items in separate sheets instead of one flat data set. Power Query and Power Pivot can be used to transform data and model relationships. The video also shows teaching tips and help links on report and query request pages, and ends with advice to design around what the user is trying to do with analytics, with Power BI named as the tool of choice for KPIs.
Key points
- Query objects now have an analysis view and can be run from the UI with Run object, for example as an action on a page, with grouping and pivoting.
- Server-side drill-down: each drill-down is a query by itself, which lets users sum millions of records and drill into them.
- Excel layout multiple data sheets is available in version 23. You enable it with a property, and it applies only to reports that target the Excel layout type.
- Excel layouts support Power Query and Power Pivot, but users may need to learn Excel modeling techniques.
- Teaching tips appear on report and query request pages. They can be dismissed and recalled by hovering over the title.
- Developers can create help links for reports and queries that show on request pages and in the help pane.
- Design guidance: understand what the user is trying to do with analytics, and a good report data set with a good first layout often lets customers add what they need.
Features
| Feature | Status | At | Evidence |
|---|---|---|---|
| Data Analysis on Queries | generally available, demoed | 0:18 | “this data analysis this feature that is now generally available on lists and queries” (11:12) |
| Server-Side Analysis Views on Queries | status not stated | 1:39 | |
| Excel Layout Multiple Data Sheets | status not stated, demoed | 2:39 | |
| Excel Power Query and Power Pivot Support | status not stated, demoed | 3:52 | |
| Teaching Tips on Reports and Queries | status not stated, demoed | 5:46 | |
| Help Links for Reports and Queries | status not stated, demoed | 5:46 |
AL objects mentioned
As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.
Not found in BC28-30: query “item hero query”, query “analyze inventory query”.
Quotes
- 0:18 “yes the query object has been there for 10 years and it’s time that it gets a bit more prominence so now you’ve always”
- 1:59 “the ability to handle large amounts of data the way we do these queries is that each drill down is a query by itself”
- 5:08 “Excel layout multiple data sheets available in version 23 if you set it to true you get these new multiple data sheets in your”
- 8:19 “you need to to stop just developing reports and instead understand what is the user trying to do with analytics and based on that”
- 9:20 “powerbi is here to stay and it’s going to be the tool of choice especially for for for kpis on business Central”
- 9:40 “they are very powerful to actually join data together from multiple sources and allow the user to the freedom to slice and dice as”
- 10:00 “very often just having a good report data set and a good first layout allows the customer to add what they need in the”