[SOLVED] Group by and calculated fields

Applications Grid / Reports
#1

Bonsoir :slight_smile:

Would need your help as I am stuck on a grid since a couple of times…

In this grid i get datas from a view of the db, ok no problem.

Then i tried to add a calculated field : no problem SC helps me to do it for each row.

And now here is the problem : I would like to group the data by this new field (to then make some graphs).
But I just can’t succeed to do it.
When i tri to group datas, just the initials fields (coming from the db) are proposed…

It seems that i need to add this calculated field on the view ? That’s right ?
Because i tried on another grid : a calculated field is in the view and effectively proposed when i want to group by.

But in my last case, adding this calculated field in the view is something too complicated (without this is field the view is already complicated…) ! I tried and tried without success… A lot of tables are used to make this new field.

Thanks for your help.

#2

Afaik you cannot do that. I’m not sure if it will work, but you might try to add a field from your select of a field you don’t need/use. In the onrecord you can set this field to any value (as you cannot update this field in the db, it should be safe). Perhaps this value will be used in the group-by. I’m not sure. It’s a dirty method, but I cannot see how it should be done otherwise than using the view. Perhaps this brings you on ideas to work-around. Let me know if it works.

#3

The idea is pretty interesting !
… But unfortunately this is the initial value (contained in the db) that is used to group datas.

merci quand m?me.

#4

Hello, guys!

This situation can be solved by creating this field on the SQL statement, using an alias:

SELECT
   product_ID,
   quantity,
   unit_price,
   quantity * unit_price AS sub_total
FROM 
   tbl_sales

This code will give you a new calculated field. this field may be used on ScriptCase’s GroupBy.

#5

Great Henriqueb !
i was not aware about that !
That will be very useful in some cases. Like that I will not have to create an other view it is cool.
I tested it and it works well.

For my initial problem; the calculated field is complicated and needs several tables : so i created two views and joined them in my grid and it seems to be good :wink:

thanks guys !

? bient?t