Autosearch filtering using calculated fields in dataset

rbuser

Hi All,

I have and end user reporting solution that returns fields that are
calculated by the OnCalc event of the dataset. This to the end user is
seamless and I would like them to be able to filter on this calculated value
using autosearch criteria as if it were any normal value coming from the
database.

For example I have a report that shows a clients investment portfolio that
they can evaluate at any point in time. I prompt them for a valuation date
and would like to allow them to filter out any investments that are less
than a certain value at the given point in time. The value of the
investment is calculated and I would like the end user to be able set the
minimum calculated value as autosearch criteria.

Obviously my problem is that the SQL that is generated adds a WHERE clause
to say FieldValue > X when X will always be NULL or 0.0 in the sql database
view before it is calculated by the OnCalc event.

Is there a way to do this? Possibly through RAP?

Many thanks in advance for your brilliance ;-)

Jarrod

nicocizik

Hi Jarrod,


Are you using DADE to create your datasets? I am unclear what you mean by
the "fields that are calculated by the OnCalc event of the dataset". Where
is the OnCalc event of the dataset?

Would this be easier if you created the calculated field inside your dataset
and made the calculations using SQL code? This way you will be able to
create an autosearch criteria on fields already in your dataset.

--

rbuser

All the objects in rb_table in my DataDictionary are actually SQL Views. I
have fields in that view that just return NULL. What I do is access the
TADOQuery object of the TADODataview that is created within the end-user
reporting solution I have created.

I assign an OnCalc event and a BeforeOpen event on the dataset. In the
BeforeOpen event I change all the NULL fields that I know are to be
calculated fields to FieldKind fkCalculated... Therefore firing off my
OnCalc event. I have to do the calcs through the OnCalc event as the
calculation formulas etc are impossible to construct in SQL

nicocizik

Hi Jarrod,

After consulting another engineer about this, there is unfortunately no way
to accomplish this using RAP. RAP does not contain a proper event handler
to update a dataset as you need.

As an alternative, you might consider preprocessing your data in an "in
memory" dataset such as kbmMemTable by Components4Developers
(http://www.components4programmers.com). This way you could pull the
calculated data directly off the table in memory. I believe they have
developed a plugin for DADE as well.

--