7
Views
5
Comments
Solved
How do I rearrange aggregate table for multi-dataseries barcharts widget
Question

I would like to create a multiple bar chart.  Is there an easy way to convert the table below to a multiple bar chart format where the dataseries is identified by a cell on the record and not by the column?  The first table is returned from the database - the second is the easiest to manage in the widget.  (I was unable to find a way using the first table).

 BudgetSpent
Cash10010
Expenses20040
Travel30060
   
   
ValueLabelDataSeriesName
100CashBudget
10CashSpent
200ExpensesBudget
40ExpensesSpent
300TravelBudget
60TravelSpent
Rank: #64
Solution

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

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

Rank: #64
Solution

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

Rank: #1135

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

Rank: #1135

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