How can I filter this report with user name?

Question

I am using below recipe to create a SAIL report. How can I filter this report with user name? Please note that I have added a column "Assign To" in the portal report that display's tp!owner information. I was able to add the user picker to the below recipe, but I am unable to filter the grid based on user that is being selected by the user picker control object. forum.appian.com/.../SAIL_Recipes.html Thank you for your help in advance! 
 OriginalPostID-147970 
 OriginalPostID-147970

sikhivahans · Accepted Answer

Hi zulfiqarp Though I haven't seen the recipe, here goes a few simple steps to add single query filter while querying the process analytics as desired by you:

Step - 1: Execute the a!queryProcessAnalytics() (with/without filters).
Ex: a!queryProcessAnalytics(
report: <your report>,
contextGroups: {},
contextProcessIds: {},
contextProcessModels: {
<your process model>
},
contextUsers: {},
query: a!query(
logicalExpression: null,
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: 1
)
)
)

Step - 2: Observe the 'columnConfigs' attribute from the output obtained from Step - 1. Identify the desired field which you want to apply the filter on.

For instance, [label:Owner,field:c3,drilldownField:dp3,configuredFormatting:NUMBER,configuredDrilldown:]; where label is the name of column in report and 'c3' is the corresponding field name which you should apply filter on.

Step - 3: Build a query filter from the field identified in Step - 2 and a value desired by you. You can reflect the same structure in the logicalExpression parameter of a!queryProcessAnalytics() or you can save the following into a local variable when you select a user from pick list.

Example: a!queryFilter(
field: "c3",
operator: "=",
value: local!selectedUser
)

Step - 4: Integrate the Step - 3 in a!queryProcessAnalytics definition on conditional basis.

Example:
a!queryProcessAnalytics(
report: <your report>,
contextGroups: {},
contextProcessIds: {},
contextProcessModels: {
<your process model>
},
contextUsers: {},
query: a!query(
logicalExpression: if(
                                                                      rule!APN_isEmpty(local!selectedUser),
                                                                      null,
                                                                      a!queryFilter(
                                                                                field: "c3",
                                                                                operator: "=",
                                                                                value: local!selectedUser
                                                                      )
                                                            ),
pagingInfo: a!pagingInfo(
                                                            startIndex: 1,
                                                            batchSize: -1
                                         )
)
)

Please do let me know if you have any follow up questions.

sikhivahans · Answer

No worries, feel free to ask as many questions as you want. As you are bit confused in identifying the field name, I would like to rephrase my explanation as follows: First of all, execute the step - 1 as specified above. This returns you an output. Now observe the value of columnConfigs in this output. This columnConfigs is nothing but the array of the columns in your portal report. If we look at the Appian report, we will be able to say what column does what. But when a!queryProcessAnalytics gets us the data, it helps us in identifying the each column by providing the attributes of each column(name of the column, field of the column etc) in the columnConfigs array. So every column in the portal report is identified by a!queryProcessAnalytics as c1, c2... and so on. That's the reason why we use the same in queryFilter as well. Similarly in order to access a column in final output, for ex. we read a field 'c1' in the final output as index(<datasubset_variable>.data,"c1",null).

sikhivahans · Answer

@zulfiqarp No worries :). Yep, so as per my previous explanation, it entirely depends on the number of columns that already exist in the Appian. The answer in short could be 'c' appended by . For ex., c5. Also please make a note that if you have already performed some additions or removals of columns, Appian won't reuse the ids. Let's say you have 5 columns in the report initially. Let's assume that thereafter you have added and removed 5 columns. Now if you add a new column, the field will be identified as 'c10' instead of 'c5'. Please do let me know if you have any follow up questions.

sikhivahans · Answer

No worries, I will go through the code. Mean while could you please provide me with following in order to proceed with debugging: 1. What is the category of report? 2. Could you please attach the results of queryProcessAnalytics (atleast with one row of data) executed without filters?