| Subject | Re: Passing parameters from form to report |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Tue, 9 Feb 2016 09:50:10 +0200 |
| Newsgroups | dbase.getting-started |
On 02/09/2016 01:05 AM, Bob grimes wrote: > I am struggling with sending / making available parameters to a report in Order to filter the report. > I have successfully run a number of reports by using a public memory variable set up and given a value in the calling form using an SQL property statement such as the one below > > Sql = ' select * from "feedetails.dbf " where feedetailsid = ' + feedetailsidx > > Where feedetailsidx is a public memory variable . The above works fine without any problems. > > My problem is that I cannot however get it to work with more than one memory variable in one SQL statement. For instance when I want to filter an account statement from the bought ledger for one supplier and with a start date and an end date. > I can filter a table by typing the following in the command window It's messy and I don't recommend this method of passing parameters into a report but something like the following should work. sql = "select * from feedtails where feedtailsid = " + feedtailsidx + " and supplier_id = " + supplieridx If the supplier_id is character then you would need to wrap the parameter in quotes. sql = "select * from feedtails where feedtailsid = " + feedtailsidx + " and supplier_id = '" + supplieridx + "'" I can't test this at the moment but SQL normally prefers string literals to be wrapped in single quotes. Double quotes may work here but just to be sure I've used single quotes. > > Select * from bought_ledger where supplier_id = : supplieridx and date >= : startdate and date <= : end date > Where supplieridx , startdate and enddate are all memory variables. > > But it will not work in a report. I have tried saving all of the text following 'where' into one memory variable called say parameter_string and then using > Select * from " bought_ledger" where ¶meter_string and that works fine in the command window but again it does not work in the report. > I have also tried setting up a parameters array and using that in the do statement with a 'with' . > But have failed to make that work. > I have scoured both of Ken mayers books , dbl language reference and the user guide and failed to find an explicit and complete explanation of how to pass memory variables from a form to a report. > There are many places where it is said that parameters can be passed from forms to reports but I can't find an explanation Of how to do it. > I found kens method of opening an instance of the report in the form and then controlling the report from within the form as also suggested in other postings but that seems a complicated way to do it. > Please can anybody point me in the right direction As is often the case in dBASE, there is more than one way to do things. If you have twelve programmers you are likely to get a dozen different answers. :-) The main thing is, however, that if it works it's not wrong. In my case, I avoid public variables completely. As a last resort I will use user-defined properties of the _app object but this is usually limited to where I need user names or security levels in every form. There are programmers who will disagree with this vehemently but in the end we each make our own choices. DATE is a reserved word in SQL and using it as a fieldname is going to give problems. These can be dealt with (and we can discuss this if you are interested) but it can become messy. A better option is to change your fieldname to, say, idate and avoid the problem. I appreciate that this may have ramifications throughout your application but you really should address this. My preference, and I stress that others have other preferences, is for parameter driven queries especially in a report. Something like the following will give you the flexibility to select a range of dates, or by setting the startdate and enddate to the same value, a single date. this.BOUGHT_LEDGER1 = new QUERY() this.BOUGHT_LEDGER1.parent = this with (this.BOUGHT_LEDGER1) left = 2940.0 top = 4620.0 sql = 'select * from bought_ledger where supplier_id = :supplieridx and idate between :startdate and :enddate' requestLive = false params["supplieridx"] = "1" //use numeric if field is numeric params["startdate"] = {01/01/1900} params["enddate"] = {01/01/2020} active = true endwith dBASE is not normally case-sensitive but when using parameters in a query the parameter names in the params assocArray must match those in the SQL statement. Whatever method you choose, passing parameters to a report involves some work. Loading the report into memory from the calling form, setting parameters and then launching the report gives me the most flexibility and so that's the way I do it. In the form's onOpen event handler function form_onOpen set procedure to myform.rep form.oRep = new myformreport() return In a pushbutton's onCLick event handler, assuming the supplier has been selected from a grid datalinked to a query that uses the suppliers table function pustbutton1_onClick form.orep.bought_ledger1.params["supplieridx"] = form.suppliers1.rowset.fields[supplier_id"].value form.orep.bought_ledger1.params["startdate"] = form.entryfield1.value form.orep.bought_ledger1.params["enddate"] = form.entryfield2.value form.orep.bought_ledger1.requery() form.orep.render() return. If you don't set the start and end dates here the report will use the hard-coded values which should give you all the records for a given supplier. Using this method requires a bit of once-off coding but it's not really complicated and it gives you enormous flexibility. It has the advantage that you can change any text on a report in memory before you render it. This allows you to use, say, one report for several different departments or to use the same report for quarterly reports Mervyn. | |