Reports : How to pass parameter captured in page to query activity

[ckumari12]

Hi,

I have created one sample report to list all campaign created in this year. I was able to successfully create it.

Now, I want my report to be more flexible.

I want to get results based on the year selected while running the report.

If I select 2016, it should return all campaign created in 2016. For this, I have created a page, created combo box variable, input 3 years 2015,2016,2017.

But I am unable to see this variable in my next activity which is a query activity so that it can dynamically pick that value and return campaign list as per the year selected.

Attached are the images.

[Jean-Serge_Biro · Accepted solution]

Hello ckumari,

I don't see any screenshot images attached.

Besides the Adobe Campaign documentation (report section) there are the different standard reports that you should consult (there are a few examples with list boxes and text field for criteria filtering).

But please find some hints below (for Adobe Campaign v6, I don't know at all ACS Adobe Campaign Standard version).

First of all, use the Preview mode with the checkbox Debug value, so you can see easily the context of your report (ctx nodes and values).

If you use a list/combo box with static values (years values hard-coded), you set the storage as a variable in the Page v5 form, so the variable is for instance ctx.vars.yearSelected.

Then, in your Query activity, in the criteria tab section, you set your test as is:

in "Value" column of the expression line:
$([vars/yearSelected])

in "Taken into account if" column:

$([vars/yearSelected]) != ''

If you need to use a Javascript activity with queryDef (for instance if you need a where clause with IN operator, and that the criteria of the reports allows to enter multiple values to search for, in that case queryDef is mandatory), you must test/filter the values by testing undefined or empty values, such as is:

if ( ( ctx.vars.filter_date_debut != undefined ) && ( ctx.vars.filter_date_debut != '' ))

  filterDateDebut = "@DATE_PARTICIPATION >= " + "'" + ctx.vars.filter_date_debut + "'";

And if you need to use a list box value dynamically set from your database, custom or standard tables values, you can set the XPath it as is in the Advanced tab of the Web page form (compatibility v5):

Such a report with a dynamic list box values:

Regards
J-Serge

[ckumari12]

Thanks J-Serge for your detailed hints.

I have used static combo box and in my query used $([vars/yearSelected]) in value column to pull my report.

I am able to create this report.

JavaScript approach I might try it later for another requirement.

[davidh2892249]

Hi Jean-Serge Biron​

I have a report that i'm trying to build as per the below, but I'm struggling with the filtering by a variable - even though it looks quite clear with your instructions.

So... I have a custom schema that stores some webApp response data pro:clientFeedback

My Report is built like this:

Start -->

Query (using pro:clientFeedback schema, where @id > 0, to return all data) -->

Page (with a List with Group element)

This is working as expected, displaying the data from the schema and grouping by the @client field and performing the necessary counts/sums.

I now want to be able to allow the user to filter the data that is displayed on the page.

So I have added a text input control. Storing as a variable called "campaignName"

I assume the correct thing to do next is to add a query after the next transition, which is the same as the original query

but with the extra condition to only retrieve data from the schema where the campaign name contains the value of the variable (e.g. that was entered in the text box)

I have then made a copy of the first page activity, as I want it to be the same, but display less data (only show records where @campaignName contains the text from the text box variable).

Here is my report diagram.

Is there anything that i am missing?

The report loads fine (the first page activity) but when I enter a value in the input text box and click next, i get an error (e.g. the second page).

When I enable debug mode, I get the following error message (Easter was the value I entered in the text box):

Element 'Easter' unknown (see definition of schema 'Client Feedback (pro:clientFeedback)'). Unable to parse expression '@campaignName LIKE '%' + ''Easter'' + '%''. Error while executing the method 'ExecuteQuery' of service 'xtk:queryDef'.

[Jean-Serge_Biro]

Hi David,

Regarding the parameter vars/campaignName, it is a common "feature"/bug of Adobe Campaign user interface, it is quite difficult to type the expression and gets the right result; you should delete the line and start again, or click on the button to be sure to get the "value of parameter" in the result, and not $([var]) itself. The best way is to use the Expression advanced editor so you are sure of to copy/paste correctly the $([var]) and gets the right result:$([var]).
See below:

Result:

Regarding the workflow itself:

You achieved a usable workflow.

Even though you can optimize the workflow by using a loop, so only 1 page activity and 1 query activity. You can see many examples in the factory reports. See the screenshot above, for a usual Query activity / List page results with criteria that are used to run again the query with the values selected by the user.

In case of your filter criteria contains multiple choices in a list box, for instance, so a IN where clause, you won't be able to use a Query activity but you must use a Script with queryDef (due to a AC limitation of IN values management in a Query activity).

So typically, the loop below is done with a script queryDef instead of Query activity. But basically the principle is the same.

Regards
JS

[davidh2892249]

Hi Jean-Serge Biron​

Thank you so much for your response. I now have it working by using the expression editor.

Makes sense why it caused it to fail, I will look out for this in future.

Good to know these little anomalies :-)

Thanks for your help

David