Recent

Author Topic: Adding a pseudo column to a dataset  (Read 2756 times)

stoffman

  • Jr. Member
  • **
  • Posts: 67
Adding a pseudo column to a dataset
« on: September 24, 2017, 07:42:42 pm »
Hi All,

My users needs to be able to show data in a TDBGrid as well as viewing the data in a Chart. For that I was hoping to use the following setup:

TSQLQuery -> TDataSource -> TDBGrid
                                         -> TDBChartSource -> TChart

The problem is that the Chart requires me to define a pseudo color column (for the pie series) and my users will not be able to ass such a thing in the query.

The only option I was able to come up with is to create a memory dataset and copy all the data to it and the column color or define my own chart source.   I don't like either solution as it needs to copy a lot of data. Any other ideas?

Thanks,


taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Adding a pseudo column to a dataset
« Reply #1 on: September 24, 2017, 07:53:36 pm »
a couple of questions.

1) Do you define static or dynamic columns on the query?
2) why are your users writting sql?
3) what do you know about calculated columns?
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

stoffman

  • Jr. Member
  • **
  • Posts: 67
Re: Adding a pseudo column to a dataset
« Reply #2 on: September 24, 2017, 10:10:28 pm »
Hi,

1 + 2) Very sophisticated users that need to access to many different data sources.
3) now I know!  :) I'll investigate

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Adding a pseudo column to a dataset
« Reply #3 on: September 24, 2017, 10:30:52 pm »
TDbChartSource has an event OnGetItem. You can use it to override the data passed to the chart. In the event handler you must first call the method DefaultGetItem of the DbChartSource to get access to the data used in the series for the currently plotted record (AItem: TChartDataItem). Then you can modify the field "Color" of the TChartDataItem. You could check the values of the series label or the x value to determine a color for the corresponding pie sector.

Please note that there is a bug (which I did not notice before) related with this event: Don't assign the event handler at designtime (i.e., don't double-click on "OnGetItem") because you will not be able to remove it again (unless you edit the lfm file manually and rebuild the project). Better to assign the event handler at runtime, e.g. in the OnCreate event of the form. I'll have to look into this issue...

In the attachment there's a modified version of the TAChart demo "db" which shows all aspects mentioned.

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Adding a pseudo column to a dataset
« Reply #4 on: September 24, 2017, 10:47:55 pm »
Hi,

1 + 2) Very sophisticated users that need to access to many different data sources.
3) now I know!  :) I'll investigate
In delphi world there is the notion of dynamic and persistent fields, you can use either of them but not both. Dynamic fields are the fields that are created by the query at runtime and they are bound to the sql, persisten fields are defined at design time and are expected to be present on the query result set when opening it (unless they are calculated). Cant your users add an extra column in their query named colour with a value of 0 and then you provide your own value on the field's ongetvalue event? Or try WP's suggestion first and see if that is enough.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

wp

  • Hero Member
  • *****
  • Posts: 11855
Re: Adding a pseudo column to a dataset
« Reply #5 on: September 25, 2017, 11:42:02 am »
Please note that there is a bug (which I did not notice before) related with this event: Don't assign the event handler at designtime (i.e., don't double-click on "OnGetItem") because you will not be able to remove it again (unless you edit the lfm file manually and rebuild the project). Better to assign the event handler at runtime, e.g. in the OnCreate event of the form. I'll have to look into this issue...
Fixed in r55919 (will be backported to Laz 1.8 ).

There is also a new demo in lazarus/components/tachart/demo/db-barseries demonstrating usage of the OnGetItem event of the DBChartSource.
« Last Edit: September 25, 2017, 12:08:47 pm by wp »

 

TinyPortal © 2005-2018