Skip to Content

SAP Sourcing/CLM: How to create a custom query – Supplier Login Monitor

This document shows you how easy it can be to create custom queries in the SAP Sourcing and Contract Lifecycle Management solutions.

I hope it could be a starting point and encourage you as an admin to try out building own queries that your business is asking for. The example is a real-life requirement from one of our customers for an easy monitoring of system activity of their supplier contacts.

The result can be seen on the screenshot below: A list of all supplier contacts with the time of their last login.

1 Result.png

     Figure 1: The result is a list of Supplier Contacts with their last login date

Step 1: Find a standard query that you can use as a starting point

You should always try to find a standard query that is already close to your given requirements. SAP Sourcing has hundreds of standard queries that you find in Setup->System Setup->Query Definition. After you found one, open and duplicate it. For this specific example, we found the query ‘Supplier and Contacts’ which contains almost all result fields that we needed. We could have named the new query definition to something more explanatory, like “Supplier Login Monitor”.

2. copy standard query.png

     Figure 2: Duplicate a standard query

Step 2: Change the collection of “Result Fields”

In your new query you can now add new fields and remove existing fields from the result set. It will depend on the origin of new fields if you will also need to change the SQL statement on tab ‘Query’. The only field that was missing in our example is the date of last login. We were lucky that this field is part of one of the database columns already used in the query, so that no changes to the SQL command was needed. The new field will be generated into the runtime query string as part of the <%RESULTS%> token.

The screenshots show how this field was added and how other fields were removed.

3. Add new field.png

     Figure 2: Add new field to the result set

4. new field details.png

     Figure 4: Details of new result field

remove fields.png

     Figure 5: Remove unnecessary fields from the result set

Last Step: Check result and publish your query

The new query is ready and can be tested now. You could make it available as a workbench channel, as part of a report or a search for your end users now.

Tip: Look at the online help. It is very well developed and quite helpful in questions around query and report design.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.