SAP CDS View 'Analytical Query'

Any custom report or a query can be easily generated via SQVI t-code as shown in the example below:

Suppose custom report needs to contain join of ANLA (Asset Master Record Segment), ANLZ (Time- Dependent Asset Allocations), T499S (Location) and ADRC(Addresses) tables. 

This can be achieved through creating table join via SQVI t-code:

Query output can be generated in ALV grid, classical view, Excel file and etc.:

Easy as that.

Now let's try utilizing CDS view called 'Analytical Query' in S/4 HANA for creating required query / or custom report. 

Step 1: Custom analytical query creation:

Go to 'Reporting' --> 'Query Design' option of the menu:

Select 'Custom Analytical Queries' tile:

Step 2: Create new query:

Step 3: Specify query name:

Step 4: Select query data source:

Step 5: Specify query output fields in 'Field Selection' tab of the view:

Step 6: Add filters and maintain fields status in 'User Input Values' tab of below view:

Step 7: Publish new query by pressing 'Publish' option:

Step 8: Go to Analysis for Office and select new query as data source:

Step 9: Maintain 'Asset Location Report' prompts: 

Step 10: Custom analytical query output validation:

Now in order to add T499S and ADRC table columns into the report, custom CDS view needs to be generated. 

Step 11: Custom CDS view generation: Go to 'Extensibility' --> 'Custom CDS Views' tile:

Press 'Create' option:

Step 12: Maintain primary and associated data sources:

Step 13: Select fields for custom CDS view output. Go to 'Fields selection' tab --> maintain fields in 'Selected Fields and Associations' tab of the below view:

Step 14: Publish custom CDS view:

Step 15: Creating a new query based on ZZ1_ASSET_LOCATION_03 view

Specify query name and select custom CDS view as data source:

Select fields for query output:

Maintain filters for Prompts screen of the report:

Save and publish new query:

Step 16: Go to ‘Query design’ tab and select ‘View browser’ app:

Step 17: Restrict view search by ‘Application component’ and by release status:

Step 18: Search results:

Step 19: Select ZZ1_FA_LOCATION query for fields validation:

Step 20: Show content of ZZ1_FA_LOCATION query:

Step 21: Adding dimensions into the view output:

Query output is correct as per below verifications:

Step 22: Save query as a tile:

Add new query into ‘My home’ tab of Fiori Launchpad:

Et voilà! :)