SAPCodeMonkeyHow to Build a Custom Analytical Query (Step by Step)
A hands-on, no-code walkthrough: building a real-time custom analytical query in S/4HANA embedded analytics, from the Fiori app to a published report — the report that used to need a developer.
This is a step-by-step walkthrough for building a custom analytical query in SAP S/4HANA embedded analytics — the kind of real-time report that used to require a developer and a transport, now built by a functional consultant or power user in a Fiori app. No Eclipse, no code. It pairs with the concept guide on CDS & OData for functional consultants; this page is the hands-on version. Prefer to just try it? Open the interactive query builder →
One clarification on wording. You build the query in the Custom Analytical Queries Fiori app, not in HANA directly. The data lives on HANA and the query runs against it in real time, but you never touch the database — that is the whole point. Everything below happens in a browser.
Before you start
Two things need to be in place. First, the app must be on your launchpad — it lives in the KPI Design group. If you cannot see it, that is a role and catalog assignment your security administrator handles, not something you fix in the app. Second, you need to know which CDS view holds your data, because every query is built on a data source: a delivered view (these start with C_) or a custom one. If you are not sure which view, open the View Browser app first and search for it — finding what already exists is the highest-value habit in this whole area.
Step 1 — Open the app and start a query
Open Custom Analytical Queries from the launchpad. To build fresh, choose New. To adapt something that already exists, search for it, select it, and click Edit. If you are starting from a delivered SAP query, copy it and edit the copy rather than the original — see the warning below on why that matters.
Step 2 — Name it and set the data source
Give the query a name. Custom objects conventionally start with Z or Y so they are easy to tell apart from SAP-delivered ones. Then set the data source — the CDS view you identified before you started. This is the single most important choice on the page: everything the query can show comes from this view, so if a field you need is missing here, you have the wrong source (or you need to extend the view first, using the Custom CDS View app).
Step 3 — Select your fields
Go to field selection and tick the checkboxes for the fields you want. As you select them, they appear on the right in the Free section. At this stage you are only choosing what is available to the query — you arrange how it is displayed in the next step. Select a little more than you think you need; unused fields left in Free do no harm and give users something to slice by.
Step 4 — Lay out the report on the Display tab
This is where a list of fields becomes a usable report. On the Display tab, assign each field to one of three places: Rows, Columns, or leave it in Free characteristics. Use the Up and Down buttons to set the order. A field left in Free is available for slicing but not shown by default — which is exactly what you want for a dimension like Sales Organization that some users need and others do not. A typical finance layout puts Fiscal Year and Fiscal Period in the rows and the key figures in the columns.
Step 5 — Add calculated and restricted measures
This is the real power of the app, and it is still code-free — just a formula editor. Two types are worth knowing:
A restricted measure is a measure limited by a dimension. “Gross Margin,” defined as the amount measure restricted by Functional Area (Revenue minus Deductions minus COGS), is the classic example. A calculated measure is a formula across measures — “Gross Margin %” as Gross Margin divided by Gross Revenue — and any measure from the data source can be used in the formula.
To build one, select the measure on the Display tab and click Edit to open the expression editor in a popup. You get supported functions and content assistance by pressing Ctrl + Space, so you do not need to memorize syntax.
Step 6 — Set filters and prompts
In the Filter area you decide, per field, how selection works: the selection type, any default values, whether the field is mandatory, and — importantly — whether it is a user-input value that the user is prompted for when they open the report. This is how you control the experience: prompt for Company Code and Fiscal Year, for instance, and quietly default everything else. Good prompt design is the difference between a report people use and one they abandon.
Step 7 — Preview before you commit
Click the preview button to see the actual result before publishing. Always do this. Preview is the cheapest place to catch a wrong join, an unexpected aggregation, or an empty result — far cheaper than a user finding it after go-live.
Step 8 — Publish
Click Publish. A published query becomes available to be consumed by KPIs and Reports, by SAP Analytics Cloud, and by SAP Analysis for Office. Publishing is what turns your draft into a reusable object the rest of the organization can build on.
Optional — put it on the launchpad as a tile
If end users should launch the report directly, create a Fiori tile that points at the query. A field tip from practitioners: copy the identifier between the # markers and the query name from the report URL — you will need that when you create the tile.
The one rule that saves projects
If you start from a delivered SAP query, copy it and edit the copy — never modify the SAP original. Changes to delivered content get overwritten at the next update, and only queries based on released CDS views can be copied safely. Building on released objects is the functional-side equivalent of clean core: work with what SAP commits to keeping stable, and your report survives the upgrade.
Know where the app stops
Custom Analytical Queries is deliberately simple and covers most operational reporting, but it has a ceiling. Some time-based comparisons — a clean “same measure, prior year” column, for example — are awkward in the app and are often better handled as prompt parameters, or moved to a BW query in the Query Designer where you have more flexibility. Knowing where the app stops is part of knowing the tool. For most day-to-day reporting, though, this eight-step path is all you need — and it replaces what used to be a development request with something you can build, preview and publish yourself in an afternoon.
Sources & further reading
- SAP Community — Using Custom Analytical QueriesThe end-to-end step sequence: field selection, Display layout, publish. Verified live.
- SAP Developers — Custom Analytical Queries with calculated measuresThe expression editor, Ctrl+Space assistance, and publishing for KPI/report consumption. Verified live.
- SAP Community — Creating reports with Custom Analytical QueriesRestricted vs calculated measures, filter/prompt setup, and the tile-identifier tip. Verified live.
App layout, tab names and available functions vary by release and edition. Verify against SAP Help and your own system before following this as a script. This how-to is independent commentary and is not affiliated with SAP.