Enumerations in Reports

[Jaspreet_Kaur1]

Hello friends,

A very basic question:

I am building a report in AC and in Page activity I am trying to get the data of a column which is of type enumeration.

Instead of getting the label of the enumeration, I am getting its value.

For example : I am getting values like 0,1,2 instead of getting Approved, Rejected,Cancelled.

I have tried using Format = Enumeration in the cell format too, it fails to work and throws blank value in this case.

Can someone help pls?

[Adhiyan]

Hi there,

There should be an option like "use label instead of enumeration" while you are building the report.

Regards,

Adhiyan

[Jaspreet_Kaur1]

Thanks Adhiyan for the quick response.

There ain't any option for the Label. Pasted is the screenshot for your reference.

Note : If I use text, it shows me 0,1,2 only.

[Jean-Serge_Biro]

Hi Jaspreet,

Enumeration format is unfortunately here for xtk:enum (itemized lists, node Administration>Platform>Enumerations (itemized lists) not for sysEnum (enumerations defined in schema, XML source schema).

And yes, you pointed out a limitation of Adobe Campaign for such report, with sysEnum values.

The only solution for sysEnum, as far as I know, is to add the label corresponding to the value of sysEnum for each line, in the context for each line retrieved by your query.
So it means a bit of Javascript for this 2nd pass of adding data.

See for example this  JS code to understand the JS function and object to use:

var schema = application.getSchema("nms:delivery")

for each(var e in schema.enumerations)

{

  logInfo(e.name)

  if (e.name == "nms:delivery:deliveryState")

    for each(var v in e.values) // the .EnumerationValue is not to mention

    {

      logInfo(v.name + " = " + v.value)

    }

}

In your case, you must loop on the ctx.query childs, (or whatever the collection you got, replace by rightname such as ctx["delivery-collection"] for instance), and append attribute value for the array of sysEnum you got by using the getSchema("nms:delivery").enumerations statement.

You can find out many standard factory reports that use this technique of double pass to construct, enrich a data set.

Hope this work-around helps, and good luck.
Regards

J-Serge

[Jaspreet_Kaur1]

Thanks Jean for your inputs.Its helpful !

I was also trying to create an expression (CASE WHEN/IF) in the query activity(Just before the Page activity) while fetching this field, However, on doing so, the column is completely coming blank in the report.Unsure, why!

Can you name some standard factory reports where such case is handled, as you mentioned in your earlier reply, so that I can refer it?

Thanks,

[Jean-Serge_Biro · Accepted solution]

Hi Jaspreet,

Yes, your solution to generate label values from numeric values in Query expression editor (equivalent of decode SQL function) is probably enough for your need, easier and more understantable for not programmers, you are right; though it is often useful for a few values possible, otherwise it becomes quites unreadable because the Query expression editor doesn't support formatting styles (line breaks etc) so it is often misleading.

So if you do it frequently, you should define the replacement by a SQL User Function (see other posts and documentation about to proceed), in that way you will get a concise expression.

The main issue I see is that this is not dynamically change when there are products upgrades (builds can introduce new values of sysEnum).
Anyway, please test numeric values not strings, take care to the right syntax.

Regarding the solution of double-pass for filling context objects, yes, it is more useful for adding more complex data than this sysEnum replacement.
You can have a look into this report to see the idea of double-pass to add information in the context:

budgetEvolution
But take time to have a look on many other reports that do more complex things, it is useful to understand possibilities of context manipulation. Most of time it is done in JS activity so you can notice them easily. But sometimes it is done directly in JS script inside the Page activity, such as:

budgetOperationExpenses

in which you see the Expenses context enrichment in the script within the last Page activity of the report.

Regards
JS