2229489 - How to make a field filterable in a custom SFAPI Adhoc entity and how to use it

1. Custom Adhoc Report is defined via Adhoc Report Builder in SuccessFactors UI
2. The report is exposed as SFAPI Adhoc Report entity with naming convention AdhocReport_<Report Id> in SFAPI Data Dictionary
3. A request is to set up a filter for a field of an SFAPI Adhoc Report entity and use this field as a filter in a SFQL statement in a query for the entity
4. Field of the Adhoc Report entity has property 'filterable' set to 'false' in SFAPI Data Dictionary
5. When you try to query Adhoc Report entity using WHERE or CONSTRAIN BY clause in SELECT statement of the query in SOAP request, you get one of the below error messages in SOAP response:
• <ns2:errorMessage>Query failure! Error: Field employee_USERS_SYS_USERNAME is not allowed to appear in WHERE clause</ns2:errorMessage>
• <ns2:errorMessage>Query failure! Error: Field employee_USERS_SYS_USERNAME is not allowed to appear in CONSTRAIN BY clause</ns2:errorMessage>

SuccessFactors BizX

1. For user defined Adhoc report entity with naming convention like AdhocReport_<Report Id>, property filterable for an adhoc report entity field will be always set to 'false' by default. A field which is supposed to be used as a filter in a query has to be set as constrainable instead (constrainable has to be set as 'true'). 
   
   Constrainable property cannot be found in SFAPI Data Dictionary as there is no corresponding column. The only option to verify constrainability for a field is via SFAPI DescribeEx operation (see KBA 2229893).
   
   
2. If you want to set a filter for a field, you need to edit Adhoc report definition in Adhoc report builder on UI. Add a filter for the field and check “User prompted” checkbox. 
   
    
   
   
3. This will expose corresponding field with "df_" prefix in SFAPI Data dictionary with constrainable set to true.
   
   For example, if there is a filter on employee_USERS_SYS_USERNAME set in Adhoc Report Builder on UI, there will be corresponding constainable field df_employee_USERS_SYS_USERNAME in SFAPI Data Dictionary.
   
   
   
   
4.  Use constrainable field with df_" prefix in CONSTRAIN BY clause in the SELECT statement of the query as follows:

        <urn:queryString>
           SELECT
             employee_USERS_SYS_USERNAME,
             employee_USERS_SYS_FIRSTNAME,
             employee_USERS_SYS_LASTNAME
           FROM AdhocReport_1542
           CONSTRAIN BY df_employee_USERS_SYS_USERNAME = 'cgrant'    
         </urn:queryString>

2229893 - How to find out if a field in SFAPI Adhoc Report entity is constrainable

SFAPI, ad hoc, report, entity, dictionary, filter, filterable, constrain, constrainable, constrain by, adhoc report builder , KBA , LOD-SF-INT-API , API & Adhoc API Framework , LOD-SF-INT , Integrations , How To