Quick Reports

[JD Kromkowski]

I’ve working through Examples in Chapter 24, I have question about the quickreports example. (The report example works fine and I’ve been able to modify to suit my needs – I had already written my own off version that does the samething thing without using a report1 object.) But I’ve had trouble with quickreport example: First, it won’t compile (unless I comment out: //if not (errmsg > “” or e != 0) //wxprocess(20000000) //s = “Success!{d}{a} //else //if errmsg > “” // s = errmsg + “{d}{a}” //else // s = “Error number ” + .tostr(e, 10) + ” running report{d}{a}” //end if //end if It tells me the problem is is on the line of wxprocess(), but just commenting out that line doesn’t fix problem. Second, after I comment out above, I get access 12 error on this line: ds1 =@ qr.adddatasource(sbmfile, “address.sbm”, error=e) I thought maybe it should be sbmfile.type, but alas that doesn’t help. I think the problem is that the example has already opened the datasource to start the program. Is this is why we are getting the access issue? If I just pair everything down and load a previously created report and run it qr =@ loadquickreport(“C:Program FilesSIMPOLProjectstutorialaddressbookbinaddresslist.sxq”, error=e, errortext=errmsg) It works “kind of”. Two different windows get openned up. So I can see the report but the windows are not responsive to commands (I have an idea as to why that is, but the whole thing is still kind of funky.

[JD Kromkowski]

Phil Alexander made a nice catch on that missing ". on s= "Success! line

It will compile now but I still have Access denied Error 12 on this line.

ds1 =@ qr.adddatasource(sbmfile, "address.sbm", error=e)

I'm not sure I understand the parameters for this method and since the
source was not included I can't really tell what is going on.

jdk

On 10/19/2012 3:46 PM, JDK wrote:
> I've working through Examples in Chapter 24, I have question about the
> quickreports example. (The report example works fine and I've been able
> to modify to suit my needs – I had already written my own off version that
> does the samething thing without using a report1 object.)
>
> But I've had trouble with quickreport example:
>
> First, it won't compile (unless I comment out:
>
> //if not (errmsg > "" or e != 0)
> //wxprocess(20000000)
> //s = "Success!{d}{a}
> //else
> //if errmsg > ""
> // s = errmsg + "{d}{a}"
> //else
> // s = "Error number " + .tostr(e, 10) +
> " running report{d}{a}"
> //end if
> //end if
>
> It tells me the problem is is on the line of wxprocess(), but just
> commenting out that line doesn't fix problem.
>
> Second, after I comment out above, I get access 12 error on this line:
>
> ds1 =@ qr.adddatasource(sbmfile, "address.sbm", error=e)
>
> I thought maybe it should be sbmfile.type, but alas that doesn't help.
>
> I think the problem is that the example has already opened the datasource
> to start the program. Is this is why we are getting the access issue?
>
> If I just pair everything down and load a previously created report and
> run it
>
> qr =@ loadquickreport("C:Program
> FilesSIMPOLProjectstutorialaddressbookbinaddresslist.sxq", error=e,
> errortext=errmsg)
>
> It works "kind of". Two different windows get openned up. So I can see
> the report but the windows are not responsive to commands (I have an idea
> as to why that is, but the whole thing is still kind of funky.
>
>

[Michael]

On 07/11/2012 19:34, John D Kromkowski wrote:
> I'm using the quick report example but taking short cut and just loading
> a quickreport qr to start.
> It works fine. But if I want to change the whereclause
> for example, using:
>
> qr.setwhereclause("EMPLOYEE.FullName = WAGES.FullName AND
> WAGES.FullName = 'Simon J. Kromkowski'", errmsg, erridx)
>
> qr.setwhereclause doesn't seem to do anything at all.
>
> The whereclause just stays the same. I think that this is because the
> setwhereclause method only goes with sqlqr objects not qr objects. Am
> I correct in my assessment of the problem?
>
> also bugs in Quick Report of Personal
>
> 1. cancel in the middle of adding to filter crash.
> 2. You can't edit the filters manually – real pia.
>
> jdk
>

Hi John,

The setwhereclause() call is being done at the sqlq1 object, it just appears to be part of the quickreport1 object because of
embedding/resolve.

As far as I can remember this works fine (I use it in one of my commercial products). A new release is literally days away, with
updates in many areas.

Quick Report in Personal is much more robust and the underlying engine is better. Unfortunately I don't have time to write a full
parser for the GUI for the where clause, so you can't edit it yet. It annoys me as well, so we will get there, eventually :).

Ciao, Neil

[JD Kromkowski]

Obviously (I presume this), the QuickReports in Personal still cannot do
calculated fields.

However, programmatically, I've been able to do "calculated" fields for
quickreports.

E.g, I can subtract, add, multiple and divide fields. For example,
this multiplies the AddressID and the Dummy field and puts it in a
column call "Test".

qr.setselectclause("AddRepOut.AddressID,AddRepOut.Dummy,AddRepOut.AddressID
* AddRepOut.Dummy AS Test", errmsg, erridx)

I can even use a Constant like

AddRepOut.AddressID * 10 AS Test

Concating strings fields however has proved elusive. I don't whether
its because I haven't hit upon the correct syntax or if there is another
reason?

Just before sending I tried one more thing and success:

qr.setselectclause("AddRepOut.FirstNames||' '||AddRepOut.Surname AS
Test", errmsg, erridx)

double pipes? Is there a list someplace. I think SQL has changed in the
ten years since I fooled around with it. (Or I've forgotten – more likely).

JDK

On 11/8/2012 4:18 PM, Neil Robinson wrote:
> On 07/11/2012 19:34, John D Kromkowski wrote:
>> I'm using the quick report example but taking short cut and just loading
>> a quickreport qr to start.
>> It works fine. But if I want to change the whereclause
>> for example, using:
>>
>> qr.setwhereclause("EMPLOYEE.FullName = WAGES.FullName AND
>> WAGES.FullName = 'Simon J. Kromkowski'", errmsg, erridx)
>>
>> qr.setwhereclause doesn't seem to do anything at all.
>>
>> The whereclause just stays the same. I think that this is because the
>> setwhereclause method only goes with sqlqr objects not qr objects. Am
>> I correct in my assessment of the problem?
>>
>> also bugs in Quick Report of Personal
>>
>> 1. cancel in the middle of adding to filter crash.
>> 2. You can't edit the filters manually – real pia.
>>
>> jdk
>>
>
> Hi John,
>
> The setwhereclause() call is being done at the sqlq1 object, it just appears to be part of the quickreport1 object because of
> embedding/resolve.
>
> As far as I can remember this works fine (I use it in one of my commercial products). A new release is literally days away, with
> updates in many areas.
>
> Quick Report in Personal is much more robust and the underlying engine is better. Unfortunately I don't have time to write a full
> parser for the GUI for the where clause, so you can't edit it yet. It annoys me as well, so we will get there, eventually :).
>
> Ciao, Neil

[Michael]

On 28/11/2012 23:05, John D Kromkowski wrote:
> Obviously (I presume this), the QuickReports in Personal still cannot do
> calculated fields.

The GUI cannot yet do calculated fields. The underlying engine always could.

> However, programmatically, I've been able to do "calculated" fields for
> quickreports.

Exactly.

> E.g, I can subtract, add, multiple and divide fields. For example,
> this multiplies the AddressID and the Dummy field and puts it in a
> column call "Test".
>
> qr.setselectclause("AddRepOut.AddressID,AddRepOut.Dummy,AddRepOut.AddressID
> * AddRepOut.Dummy AS Test", errmsg, erridx)
>
> I can even use a Constant like
>
> AddRepOut.AddressID * 10 AS Test
>
> Concating strings fields however has proved elusive. I don't whether
> its because I haven't hit upon the correct syntax or if there is another
> reason?
>
> Just before sending I tried one more thing and success:
>
> qr.setselectclause("AddRepOut.FirstNames||' '||AddRepOut.Surname AS
> Test", errmsg, erridx)

Have to admit, that surprised me too. I just checked and I am actually supporting + for concatenation in the calceval.sml, so I
will have to add || as an alternative.

> double pipes? Is there a list someplace. I think SQL has changed in the
> ten years since I fooled around with it. (Or I've forgotten – more likely).

Here is a page that lists the supported operators for SIMPOL's SQL92 subset. It definitely needs a more prominent location.

http://www.simpol.com/docs/progbook/usingdf1_specialfeatures.html#usingdf1_datagridrunquery

Ciao, Neil

>
>
>
>
> On 11/8/2012 4:18 PM, Neil Robinson wrote:
>> On 07/11/2012 19:34, John D Kromkowski wrote:
>>> I'm using the quick report example but taking short cut and just loading
>>> a quickreport qr to start.
>>> It works fine. But if I want to change the whereclause
>>> for example, using:
>>>
>>> qr.setwhereclause("EMPLOYEE.FullName = WAGES.FullName AND
>>> WAGES.FullName = 'Simon J. Kromkowski'", errmsg, erridx)
>>>
>>> qr.setwhereclause doesn't seem to do anything at all.
>>>
>>> The whereclause just stays the same. I think that this is because the
>>> setwhereclause method only goes with sqlqr objects not qr objects. Am
>>> I correct in my assessment of the problem?
>>>
>>> also bugs in Quick Report of Personal
>>>
>>> 1. cancel in the middle of adding to filter crash.
>>> 2. You can't edit the filters manually – real pia.
>>>
>>> jdk
>>>
>>
>> Hi John,
>>
>> The setwhereclause() call is being done at the sqlq1 object, it just appears to be part of the quickreport1 object because of
>> embedding/resolve.
>>
>> As far as I can remember this works fine (I use it in one of my commercial products). A new release is literally days away, with
>> updates in many areas.
>>
>> Quick Report in Personal is much more robust and the underlying engine is better. Unfortunately I don't have time to write a full
>> parser for the GUI for the where clause, so you can't edit it yet. It annoys me as well, so we will get there, eventually :).
>>
>> Ciao, Neil
>

[JD Kromkowski]

I think that you'll find that + does not actually work for concatenation.

At least in failed in my attempts to use it in the quick report sample.

qr.setselectclause("AddRepOut.FirstNames + ' ' + AddRepOut.Surname AS
Test2", errmsg, erridx)

fails with this error:

select clause: data type or value problem, or cyclic dependency within
columns

Likewise, even this fails (in the off chance that the space was messing
things up:

qr.setselectclause("AddRepOut.FirstNames + AddRepOut.Surname AS Test2",
errmsg, erridx)

but

qr.setselectclause("AddRepOut.FirstNames|| ' ' || AddRepOut.Surname AS
Test2", errmsg, erridx)

works, as was noted.

Why SQL-92, when that is 4 versions (and 20 years!) old. Aren't we
already at SQL:2011?

And this document doesn't even exist anymore except maybe in the wayback
machine.
|http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt|.

Wouldn't in make sense to support:

CONCAT ( field1, field2, … )

and make that the way to do this if you aren't using the double pipes.

BTW, kind of clunky that we can't use a space renaming field names,
e.g. AS This Field nor AS 'This Field' works. So instead you
have this work around which inserts a _ in place of spaces.

JDK

[Michael]

On 29/11/2012 17:56, John D Kromkowski wrote:
> I think that you'll find that + does not actually work for concatenation.

Not in the sql1 library, which is the heart of the report engine, but in the calceval library it does work.

> At least in failed in my attempts to use it in the quick report sample.
>
> qr.setselectclause("AddRepOut.FirstNames + ' ' + AddRepOut.Surname AS Test2", errmsg, erridx)
>
> fails with this error:
>
> select clause: data type or value problem, or cyclic dependency within columns
>
> Likewise, even this fails (in the off chance that the space was messing things up:
>
> qr.setselectclause("AddRepOut.FirstNames + AddRepOut.Surname AS Test2", errmsg, erridx)
>
> but
>
> qr.setselectclause("AddRepOut.FirstNames|| ' ' || AddRepOut.Surname AS Test2", errmsg, erridx)
>
> works, as was noted.

Yes, I realize that. I simply was commenting on the fact that the calceval library implements a runtime capable eval function and
it is meant to be compatible with the sql1 engine.

> Why SQL-92, when that is 4 versions (and 20 years!) old. Aren't we already at SQL:2011?

This engine was written many years ago. As far as standards go, SQL92 is still the supported form in many currently shipping SQL
products. Actually the SQL standard is the biggest joke on the planet, since it is anything but standard. Every manufacturer made
proprietary changes to SQL. SQL 92 was an attempt to get them to agree on something, and to some degree it worked. At the time we
decided on SQL92, the next version was actually SQL3, which was hopelessly mired in the design stage and had an enormous
definition, parts of which were mutually exclusive. This is what happens when you are creating a standard and the manufacturers as
interested parties are invited to participate in the creation of a new standard (each of them is trying to ensure that their
version gets into the final spec so they have to do as little as possible).

> And this document doesn't even exist anymore except maybe in the wayback machine.
> |http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt|.

Not so. I clicked on it in your post and it immediately took me to the document on the web.

> Wouldn't in make sense to support:
>
> CONCAT ( field1, field2, … )
>
> and make that the way to do this if you aren't using the double pipes.

CONCAT() is not part of the SQL92 standard. We got moaned at all the time in the past for not using industry standards (even
though Superbase's query language predates SQL), so this time around we decided to try and support industry standards as much as
possible. That is why the storage format for forms, etc. is XML. We support TCP/IP client and server. We include an SMTP email
client, and an HTTP client library.

> BTW, kind of clunky that we can't use a space renaming field names, e.g. AS This Field nor AS 'This Field' works. So
> instead you have this work around which inserts a _ in place of spaces.

True, but this appears to be a SQL limitation as well. We took the bad with the good. In the graphic report you can place any
string you want on the page in the header band, so it doesn't become a problem. The issue is that the space character is a term
separator, so it can't be part of an identifier. The only thing I could do is to extend the Quick Report engine to allow the
separate storage of an alias that could be used as the column name when outputting to window or printer.

Ciao, Neil

[JD Kromkowski]

that link was dead earlier in day. I'm glad its working.

once i take care of other technical difficulties in office I start
investigating the "where" clause a bit more closely.

[JD Kromkowski]

selectclause ="WAGES.EndTime AS End,WAGES.StartTime AS
Start,WAGES.EndTime – WAGES.StartTime AS Hours"

qr.setselectclause(selectclause, errmsg, erridx)

So I know how to concat strings, and perform operations on intergers and
numbers,

But how do I find time or date interval, but subtraction not so much
working.

On 11/29/2012 10:48 PM, jdk wrote:
> that link was dead earlier in day. I'm glad its working.
>
> once i take care of other technical difficulties in office I start
> investigating the "where" clause a bit more closely.
>

[JD Kromkowski]

string selectclause

selectclause ="(EXTRACT(MINUTE FROM WAGES.EndTime)+(EXTRACT(HOUR FROM
WAGES.EndTime)*60))TotE"

selectclause =selectclause +"," + "(EXTRACT(MINUTE FROM
WAGES.StartTime)+(EXTRACT(HOUR FROM
WAGES.StartTime)*60))TotS,((TotE-TotS)/60)hWorked"

qr.setselectclause(selectclause, errmsg, erridx)

On 12/8/2012 5:08 PM, kromkowski wrote:
> selectclause ="WAGES.EndTime AS End,WAGES.StartTime AS
> Start,WAGES.EndTime – WAGES.StartTime AS Hours"
>
> qr.setselectclause(selectclause, errmsg, erridx)
>
> So I know how to concat strings, and perform operations on intergers and
> numbers,
>
> But how do I find time or date interval, but subtraction not so much
> working.
>
>
>
> On 11/29/2012 10:48 PM, jdk wrote:
>> that link was dead earlier in day. I'm glad its working.
>>
>> once i take care of other technical difficulties in office I start
>> investigating the "where" clause a bit more closely.
>>
>

[Author]

Posts