Hi John,

With aggregates you cant achieve that type of data.

I would suggest you to create an action the transform the first table into the second type of list.

For example you would iterate the first table and append the two values of the current row to a new list.

Hope I could help.

Best Regards,

Cláudio

Hi John,

You can prepare the list after the query.

For instance, I would create a structure DataSeries for the data to feed the chart:

Then I would have a local variable of DataSeries List type:

And after the query, you just have to iterate each record and do two appends for your list: one for the budget, one for the spent (using ListAppend from System). Something like this:

Then you just need to feed the chart with the DataSeriesList variable instead of the query.

Hope it helps.

Cheers,
João

Thanks for your fast responses - I'll try these and respond

With an aggregate, can be done something like this, being "product" the entity to display in the chart:

 use the entity twice in the aggregate, and join them with the "with" operator and the condition

"Product.Id = NullIdentifier() or Product_2.Id = NullIdentifier()"

Create then the attributes to be used :

As you can see one instance from the entity is used to obtain one column value and the other instance to the other column value, the column used is identified in the DataSeries attribute . 

This attributes can be used to sort the result list . 

Well , that's other option. 

Stay safe

Graça

I have this working using the local variable solution but it ended up being handled on client side - although I had guessed there would be a way to handle it in SQL - but was beyond me at this stage.  

Thanks very much for your help