Technical Articles
BEx Query based Fiori App
We all know BEx queries have been the traditional Sap BW reporting method to analyze the dataset of the BI system. Info Objects are combined in a query and data is evaluated in a tabular report.
Now Enterprise Reporting can be taken to the next level with the advent of Fiori and SAP UI5. Here are a few points why you should consider creating a custom SAPUI5 app to analyse BI data.
- Single query can yield multiple reports
- Attractive graphical representations of BI data
- On the go slicing and dicing and much more…
Steps to consume Bex queries in Fiori Analytical applications
- Create an ODATA compatible BEx Query.
- Activate ODATA service for the Query. This ODATA service will expose the BI data.
- Develop a custom UI5 app consuming query ODATA Service
And the final output will look something like this :
First chart represents Preventive Maintenance Count by District.
Second chart represents PM Count ACTUAL and PLAN values with respect to district.
Step 1 : Create ODATA compatible BEx Query
There are a few points to remember when creating a query in query designer with the motive of generating an ODATA for the same
- Key Figures can go only in the columns section.
- Characteristics have to be in rows section.
- At least one characteristic in rows.
- No additional structures are allowed.
- To keep it simple, we are not going to add any filters in query level, rather implement all filter related details (like filter defaults, select options etc) at the app level
– To get the above output, we need to create a query ZFIORI_SAMPLE with
- PM Count in Column
- District and Value Type in Rows
and then save the query as seen below.
- Now go to extended tab of query and select By OData option and hit SAVE again. You can see a message that Gateway service has been generated.
- Now change the technical name of PM Count and save.
Step 2 : Activate ODATA service for Query ZFIORI_SAMPLE
- Login to SAP GUI and go to tcode /n/iwfnd/maint_service and Follow the instructions as seen in the screenshots.
- Now to see the metadata, replace ?$format=xml with $metadata
Query ODATA Service is ready to be consumed in Fiori App!
Step 3: Develop Custom SAPUI5 app in WebIDE
This step is explained in detail in my next blog post-
https://blogs.sap.com/2019/10/14/simple-sapui5-app-to-display-charts/
Good one!
Thank you 🙂
indeed a good quick reference Naseera .
Nice Blog Naseera
Hi Naseera,
Good one and will be quick reference guide. The blog is more towards ready only bex query. Can we consume input enable bex query in Odata as well?. Please share your experience.
Hi Vetrivelan, yes we can consume input enabled BEx Query in Fiori App. A separate Entity Type - 'Parameters' is created for the variables, and we can directly bind this entity type to a Filter Bar in the app. Another approach, and a way more simpler one, is to configure filters (variable input) at Fiori app level instead of adding them in query level.
Sorry for the late reply.
Thanks for this brilliant article
Can you please clarify the version of NW required, any other components required and any limitations
Huge thanks
Great Blog - thanks !
Hello Naseera,
I am trying to add the service.
But, we are getting the message – backend ‘LOCAL’ – ZBAL_Q0001: Two-Structure query cannot be used for an ODATA query.
Please find attached screen shot.
Regards,
Sunil
Hello Nasera,
thanks for the great article. Will this still work in BW4/HANA?
Hello Nasera,
I know this article is old. Just wondering have you tried using smart table with Bex Query odata service. I couldn't get it to work.
Thanks
Vinitha
Thank you very much. Could you please describe the Query filter for Bex Query. I habe two variables filter in the query and how to pass variable input during Ui5 startup?
Hi,
Where to Activate ODATA service for Query ZFIORI_SAMPLE in BW system or in SAP Gateway system? while adding the service with local no service found. Could you please help
Thank you !