How does one Calculate Opening Balances

rbuser

We have a table which contains both purchase and sales data. This table
pertains to buying and Selling of US Dollars. So the Purchase items is
identified by PP and Sales By SP.

Now for a given Period say from 01/August/2005 to 31/August/2005 i want to
seen the Purcase and Sales data, but any data prior to 01/August should be
summed as Opening Balance.

So how does one do this in the Calc space. I have set the report, set up
the Auto Search Dialog via the Query, but cant figure out this.


TIA
Sameer



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Sameer,

Using the autosearch simply changes the underlying SQL code to filter your
data. Once it is filtered, you are not able to access the rest of the data
to perfom calculations. One work-around may be to create an identical query
that filters out the autosearched data and calculates the sum of that.
Primarily the best way to do this would be in the BeforeOpenPipelines event
in RAP and using the SQLBuilder object. This object allows you to manually
create search criteria on the fly at runtime fairly easily. See the
TdaSQLBuilder topic in the RBuilder.hlp file for more information and
examples.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

First thanks for the info. Got a understanding of what is required. We are
new to the Delphi world as well as to ReportBuilder. So you will have to
bear with us.

I forgot to mention we are using RB V7. So what i wanted to know is it
possible that my team lead has removed some of the events, becasue i dont
get to see the BeforeOpenPipelines.

Also do we have to Use delphi to code, cant i just use RB and its features
,like the calc part. what i do is use the Delphi form to drop the
Componets and then use the RB designer to desing my reports.

One more question, how do i get the AutoSearch Values. I mean when i
enter between dates, i want to retrive the first Date entry in the
Autosearch dialog and use it to compare for calculations.

Also what is the advantage or disadvantage of the first method to
calculate over the other as suggested by you in your rply.

also do you have more advanced examples other than the the PDF manual.

TIA
Regards
Sameer







--- posted by geoForum on http://delphi.newswhat.com

nardmoseley

1. I think you need to create one query dataview to calculate the opening
balance and a second query dataview to list the sales and purchase item
detail.

2. The Report.OnGetAutoSearchValues event can be used to access the
Report.AutoSearchFields[]. The AutoSearchField.SearchExpression contains the
value entered by the user in string format. If you use the Between search
operator, the SearchExpression will contain a comma between the two values.
The AutoSearchField.Value and Values[] array can also be used to access the
search field values as a variant data type.

3. The BeforeOpenDataPipeline and the TdaSQLBuilder class are new for
ReportBuilder 9. These enhancements were added to RB 9 specifically to
enable the Calc workspace to be used to prepare the SQL queries of a
dataview.


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com