Calculated Values

Applications Grid / Reports
#1

Good morning,

In my database design I am trying to have all calculated filed calculated by the application when it runs and not store the in the database. I can get this to work great in my grid app but I was hoping to be able to use this same method to create chart application so that I can drill down into the date. In my original look into this I am not seeing a way to accomplish this. (the Chart section of the grid app is not providing me what I need)

I can modify my database to store these values but I was hoping to avoid.

Has anyone done anything like this and if so can you offer tips?

Thanks,

Keith

#2

Is there any reason why you are not using db views to generate the calculated fields?

#3

No I was looking at implementing that too. However, the issue I have is this:
calculated field is the change in revenue
I need to be able to retrieve the previous week’s revenue (different row in the same view) and subtract that from the current week’s revenue

creating a view like this is what is giving me trouble and after googling most fo the afternoon I am not much further along and perhaps in the mysql 5.7.26 version I am using it is not possible?

#4

and of course I just managed to figure out how to construct the view properly… SUCCESS!!!

#5

now my only question is this. Is is better to one a view with complex queries in them or multiple views and then going them together.

#6

In general this is a hard question to answer as there are many ways to Rome. If you have many views and combining them then this might be less efficient, but more easy maintainable. But complex queries can be time consuming too as the query optimizer of the database can make ‘mistakes’. If the used data is quite static, I would not opt for a dynamic construction of the numbers, but use a special table to store intermediate results like in BI tools uses. But it all depends…

#7

Thanks for the info Albert. That is exactly what I was finding in my search across the internet. It really just depends.