How to pick up current DELIVERY id in a DELIVERY report workflow

salvdangelo

22-09-2017

Hi everyone,

I am using a delivery report and in particular a query activity with a custom schema (containing delivery iDs in particular).
In "where condition" I would filter the records for the current delivery (SCHEMA.deliveryId = <currentDeliveryId>)

Someone could tell me how to do this?
Maybe it's a simple issue.


Thanks for helping!

Note: I am not using cube

Vipul RaghavAmit_Kumar

Jean-Serge Biron

Adhiyan

@nkur

Adobe Campaign

Replies

Highlighted

Jean-Serge_Biro

MVP

28-09-2017

Hi Salvatore,

So, do you have included in your Query activity filters/criteria something like this (example taken from a report using context filtering in a Query activity, not only in a Page activity: deliverySending report, "Exclusion" Query activity):

1313081_pastedImage_0.png

Regards.

J-Serge

Highlighted

salvdangelo

28-09-2017

I don't know what is its goal, but when I put as additional filter:

SCHEMA.deliveryId equal to  [@_selection]

I immediately get back "attribute '_selection' unknown"

Highlighted

Jean-Serge_Biro

MVP

29-09-2017

Hi Salvatore,

You wrote in previous posts that:

  1. the Filter by context checkbox in the Query activity has been checked
  2. you run the report either being on a delivery and selecting your report through the dashboard, or in the preview mode by selection Selection checkbox and selecting the delivery

So you should have in the ctx (debug mode) the useful elements indicated by Amit.

Please confirm that way.

BR
JS

Highlighted

salvdangelo

29-09-2017

Hi Jean-Serge,

In debug mode, if selection type = "single selection" I have:

<where>

  <condition expr="@id = $noescaping(@_selection)" enabledIf="$(@_context) = 'selection' and $(@_hasFilter) = false" />

  <condition expr="@id" enabledIf="$(@_context) = 'selection' and $(@_hasFilter) and $(@_locationName) != 'descriptiveAnalysis'" subQuery="$(whereCond)" setOperator="IN" />

</where>

that is equal to that one that wrote Amit above.

Salvatore

Highlighted

Jean-Serge_Biro

MVP

29-09-2017

To sum up:

using debug mode, selecting the Selection check box, selecting one delivery in the folder (or listbox), you should have on the URL address bar something like this:

http://...&_context=selection%7CdeliverySending&_selection=1080026525&_selectionCount=1&lang=&_debug

where _selection argument value is the delivery id (primary key) of the delivery selected.

Now, for your Query activity, there are 2 cases:

1. either it is based on Delivery schema, and you have nothing to do, because Filter on context checkbox will automatically filter Delivery on the _selection argument value.

2. or it is based on your specific customer schema (I thought it was your use case...) and you have to inspired from the exemple of deliverySending report, Exclusion activity that I mentioned before for the foreign key used.

Then check in your context in debug mode the data retrieved and please share some screenshot for it. Or perhaps the issue is the relationship between your customer schema and the delivery schema, but I don't think so.

Please check also your parameter syntax, it seems you forgot the / for getting the _selection attribute from the root XML element :

SCHEMA.deliveryId equal to  [@_selection] (wrong)

instead of

SCHEMA.deliveryId equal to  [/@_selection] (right)

BR
JS

Highlighted

salvdangelo

29-09-2017

To sum up:

using debug mode, selecting the Selection check box, selecting one delivery in the folder (or listbox), you should have on the URL address bar something like this:

http://...&_context=selection%7CdeliverySending&_selection=1080026525&_selectionCount=1&la ng=&_debug

where _selection argument value is the delivery id (primary key) of the delivery selected.

Correct.

.../report/deliverySegmentsReportOTB?_preview=true&standalone=1&reportId=0&_uuid=975a1104-94cc-4e1e-ab48-cc7a07de2708&_reportLabel=Segmentation%20Tracking%20indicators%20CUSTOM%20BLOCKS&_lastModified=2017-09-29 08:23:36.071Z&_context=selection%7CdeliverySegmentsReportOTB&_selection=1341861&_selectionCount=1&lang=&_debug

Now, for your Query activity, there are 2 cases:

1. either it is based on Delivery schema, and you have nothing to do, because Filter on context checkbox will automatically filter Delivery on the _selection argument value.

2. or it is based on your specific customer schema (I thought it was your use case...) and you have to inspired from the exemple of deliverySending report, Exclusion activity that I mentioned before for the foreign key used.

CUSTOM SCHEMA (DELIVERY SEGMENTS STATISTICS):

<key internal="true" name="dimensions">

      <keyfield xpath="@deliveryId"/>

      <keyfield xpath="@dimension"/>

      <keyfield xpath="@valueIndicator"/>

</key>

<attribute label="Delivery Id" name="deliveryId" type="long"/>

<element externalJoin="true" label="Delivery" name="delivery" revIntegrity="normal" target="nms:delivery" type="link">

      <join xpath-dst="@deliveryId" xpath-src="@id"/>

</element>

ReportOTB.JPG

Then check in your context in debug mode the data retrieved and please share some screenshot for it. Or perhaps the issue is the relationship between your customer schema and the delivery schema, but I don't think so.

<query>

  <deliverySegmentsOTB language="" table_id="table117" webClick="0" valueIndicator="OTHER" toSend="0" sent="0" recipientOpen="0" recipientClick="0" percSent="0" percOpen="0" percClick="0" mobileClick="0" mailClick="0" dimension="language" deliveryId="1507661" delivery-id="1507661" country="" />

  <deliverySegmentsOTB language="ICB.undefined" table_id="table117" webClick="0" valueIndicator="ICB.undefined" toSend="1" sent="1" recipientOpen="1" recipientClick="0" percSent="100" percOpen="100" percClick="0" mobileClick="0" mailClick="0" dimension="language" deliveryId="4970930" delivery-id="4970930" country="" />

....

</query>

<query2>

  ...

  <dlvExclusion label="Exclude domains" count="0" />

  <dlvExclusion label="Address in quarantine" count="0" />

  <dlvExclusion label="Exclude addresses" count="0" />

  <dlvExclusion label="Address not specified" count="0" />

  <dlvExclusion label="Double" count="0" />

  <dlvExclusion label="Blacklisted address" count="0" />

</query2>

Please check also your parameter syntax, it seems you forgot the / for getting the _selection attribute from the root XML element :

SCHEMA.deliveryId equal to  [@_selection] (wrong)

instead of

SCHEMA.deliveryId equal to  [/@_selection] (right)

I tried in both ways but as error I receive "Attribute '_selection' unknown"

Highlighted

Jean-Serge_Biro

MVP

29-09-2017

Hi Salvatore,

It can't work with deliveryId, as is, because the _selection value is made for automatic use on the primary key of the Query activity target dimension selected. And in your custom schema, you have a compound key dimensions.

I don't know at all how it could work with delivery to be selected; except if you could select a line of your custom schema data deliverySegmentsOTB instead of selecting a delivery?
In that case, the Filter by context would replace the filter on vars/dimension, it would be equivalent, it would filter on key dimensions.

If you want to execute your report coming from a delivery line (dashboard of reports) or in preview mode, from a selection on delivery, you must have a 1st query on Delivery target dimension, then a second one with the trick that I mention for foreign key.

Please, may you confirm what is your source line? Delivery/DeliveryId or deliverySegmentsOTB /Dimensions key ?

Sorry for the time taken, it is not easy to fully understand a context by posts discussion sometimes.

Regards
JS

Highlighted

Jean-Serge_Biro

MVP

29-09-2017

Other report interesting, perhaps a bit similar to your need:

deliveryForward

The 1st activity has no Filter on context (which is applied on 2nd Activity on Delivery dimension), but the 1st activity has the forey key to reconciliate on the delivery-id.

BR
JS

Highlighted

salvdangelo

29-09-2017

I used the deliveryForward report as base but things aren't changed, rather I get even errors and I cannot see anything.Report 1.JPG

1st activity:

Report 2.JPG2nd activity

Report 3.JPG

" Please, may you confirm what is your source line? Delivery/DeliveryId or deliverySegmentsOTB /Dimensions key ? "

Don't know what are you referring to, but the report schema is DELIVERY.
deliverySegmentsOTB is the schema whose records will be shown in the table/page
delveryId is a field of deliverySegmentsOTB and is linked to DELIVERY through a link in the schema (look previous messages).

I simply want that
deliverySegmentsOTB woud be filtered for delveryId = DELIVERY.id

Highlighted

Jean-Serge_Biro

MVP

29-09-2017

I meant by "source" the schema dimension selected in Property/Document Type.
I still don't understand your error and wonder about any misleading somewhere, so sorry in advance if I ask for too much checks.

So, in debug & preview mode, selecting Selection one, are you on delivertSegmentsOTB dimension, and does it list records from that schema/table and you select one? Or are you on nms:delivery  dimension, and you select a specific delivery?
In your  URL, it was: _selection=1341861

case of Document type is Delivery:
it should work with _selection value / filter by context exactly as you did. In that case I wonder why it gives error.
case of Document type is deliverySegmentsOTB, the filter checkbox must be on the Query activity of the same and not on the Delivery activity.

Well, see another way to proceed on same principle, report "throughput":

1313726_pastedImage_0.png

This report can be called on 2 selection context. See carefully the operation one, closed of your deliverySegmentsOTB: as you can see, the filter criteria is based on @_selection and of course in that case, the checkbox Filter with the context is not activated.

One more tip: force to a static variable the value of a delivery-id to see if you get good results and highlight some issue somewhere, if filtering by context is a headache for you.


Good luck and nice week-end.
Best Regards.
JS