Custom queries

When no delivered query fits, a key user can build one with the Custom Analytical Queries app, without programming. The result behaves like any other analytical query: it can be opened as a multidimensional report, found in the Query Browser and, depending on setup, consumed by other SAP analytics tools. This lesson builds a simple one and then covers the governance that keeps custom queries from multiplying.

Good reasons to build one

• You need a different default layout of rows and columns.
• You need fixed filters, for example only company code 1710.
• You need a user-input filter (a prompt) for period or plant.
• You need a restricted measure, such as revenue for the current year only, next to the total.
• You need a calculated measure, such as margin percentage or variance between two measures.
• You need fields from a custom CDS view that a key user created.

Building a query, step by step

Defaults versus restrictions

An analytical query strictly defines which fields and filters exist, but many settings, such as totals and zero suppression, are only defaults: business users can change them at run time. Fixed filters, on the other hand, cannot be removed by the user. Use fixed filters for scope that must never change (the company code of a statutory report) and user-input filters for choices the user should make (the period).

Getting it to production

Key user objects such as custom queries are created in a system and then need to reach production in a controlled way. In SAP S/4HANA Cloud Public Edition this follows the key user transport process of the landscape; in on-premise and Private Edition the key user extensibility objects are recorded and transported with SAP's tooling for key user adaptations. Your project decides whether key users build in a development system and transport, or build directly in production for personal analysis. Either way, treat a shared query like any other change: specify, build, test, approve, transport.

Governance: stop the multiplication

Custom queries are easy to create, which is exactly why they multiply. Without rules, a company ends up with dozens of near-identical "revenue" queries that disagree in small ways, nobody knows which is right, and every upgrade means retesting them all.

• Check the Query Browser for an existing query before creating a new one.
• Use a naming convention that shows the owner and purpose, such as a "Contoso –" prefix and the process area.
• Document the definition: data source, filters, restricted and calculated measures.
• Give every shared query an owner who maintains it and answers questions.
• Review custom queries periodically and retire unused ones.