Can I use a variable from a user dialog in a dataset definition

[ham13]

Hello,

I have a dataset built on a sql query. The query contains a where clause that selects records based on a certain date. The user enters the date in a user dialog. The date is stored in a variable. I would like to use this variable in my sql query. Something like SELECT * FROM <Table> WHERE Date = 'variable'. Is this possible? I think the connection/dataset definition occurs before the dialog script runs.

What is the correct syntax for the expression (COUNT(x..) * 0.15). I want to take the count of the records in the databand and multiply it by 0.15 and display the result in a text item. Can I get the value of a text item?

Thanks

[Subst]

Hi!
You have some dialog in your report?

So, you can just access to needed element of diaog

Try do like this

first define the datasource

Code: [Select]

Source as

Code: [Select]

select * from Table where id=$V{id}then define in script page some scripts

Code: [Select]

YourDlg.exec()==1

function beforeDataPrint() {
 setVariable("id", YoourDlg.YourLineEdit.Text)
 reopenDatasource(Source)
 }


[ham13]

OK. I'll give it a try. Finally figured out the syntax to do the calculation. Will post results.

Thanks

[ham13]

OK followed your suggestion but, it appears not to work.

Datasource tblneighborhood = select * from tblNeighborhood where `Paid Year` = $V{ib};
   Produces no data as expected when the query is tested. Query select * from tblNeighborhood where     `Paid Year` = '2019';. Works. There is data for the year 2019.

Script:
    Dialog.exec() == 1

var Year = Dialog.txtYear.text
var Date = Dialog.txtDate.text

function beforeDataPrint() {
 setVariable("id",  Dialog.txtYear.text)
 reopenDatasource(tblneighborhood)
 }

Where var Year is used in the heading. var Date is also used in the heading.

Dialog.txtYear.text is the value that should be in "id". It is a text field in tblneighborhood as Paid Year ( In mysql you 'txt' text fields).

When I try to render the report there is no data. See attached pdf file.

What am I missing? I can see the logical approach. It should work. It also prints without data.

Thanks

[ham13]

I did catch the error in the datasource variable. I tested with $V{id}. Sorry bad typist.

THanks

[ham13]

Got it finally. I'm used to adding '..' to user defined number fields in mysql queries. In this case they were the problem! When I removed them in the source dataset the report worked with no problem.

What is the scripting language used in QtRptDEsigner and would you suggest a good reference source.

So far, I have not found any report that I could not get done with this project. The more you use it the more you come to appreciate its power. Titles in the forum could benefit from translation to English to make it more user friendly.

Thanks for your help and this amazing product

[Subst]

finally original trouble has been solved? or not yet?
 (when you fix the variable name from 'ib' to 'id')

Got it finally. I'm used to adding '..' to user defined number fields in mysql queries. In this case they were the problem!

Can U type here your query?
I did not fully understand your problem here

[ham13]

I want to get data from a dialog and use that data in the datasource mysql query.

The query in the datasource tblneighborhood is select * from tblNeighborhood where `Paid Year` = 2019;
That works as you would expect.

The query suggested above: select * from tblNeighborhood where `Paid Year` = $V{id};

Produces no data when the query is tested and produces no data when run from the script.

I think mysql is looking for a text item formated like '2019'. So can we convert $V{id} to a text value? $S{id} also does not work.
 
function beforeDataPrint() {
 setVariable("id",  Dialog.txtYear.text)
 reopenDatasource(tblneighborhood)
}

It appears that the variable is not evaluated by mysql or it is empty. I have been unable to visualize the variable id in a text item on the report to check it's contents.

Unfortunately I was a bit premature when I said "subst' suggestion worked. It must have been a fluke.

I still think QtRptDesigner is a good product and I will continue to use it.

Hope this helps.

Thanks
 

[ham13]

When I run the function line "reopenDatasource(tblneighborhood)" it produces error "Reference error tblneighborhood not defined". My connectio is defined as Mydata and the datasource is defined as tblneighborhood in the initial connection to the database.

Marty

[Subst]

Sorry
I've forgot to tell about this

Code: [Select]

Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint);
after beforeDataPrint definition

But some thing wrong
I told Alex, let's wait

[ham13]

OK. I'll add the new line in the function Thanks

[Subst]

No!
not IN the function
OUTSIDE them

Code: [Select]

function beforeDataPrint() {
  setVariable("id",2)
  }

Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint);
and

Code: [Select]

setVariable("id",2) has "brake down"... or

Code: [Select]

connect may be
Alex has promised to look

[Subst]

OK. I'll add the new line in the function Thanks

Well we've tested again (Alex and Me after)
All looks fine

I've attached report (test.lrxml), dialog (test.ui) and database (322_ифф.sqt)

Let's try

[ham13]

OK. I'll download them and see where I went wrong. Should script files be written using QtScript or Javascript? Is there a good source to learn each?

Thanks

[Subst]

it's all about that recommended use the JS but Qt 5.15, for example, still supports the QrScript
I've used JS? but each likes another and described fundamentally in ECMA-262 standart