Welcome, Guest. Please login or register.
Did you miss your activation email?

Author Topic: Can I use a variable from a user dialog in a dataset definition  (Read 1294 times)

ham13

  • Newbie
  • *
  • Posts: 27
    • View Profile
Can I use a variable from a user dialog in a dataset definition
« on: November 20, 2022, 07:49:14 PM »
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

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #1 on: November 21, 2022, 01:14:57 PM »
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #2 on: November 21, 2022, 06:51:36 PM »
OK. I'll give it a try. Finally figured out the syntax to do the calculation. Will post results.

Thanks

ham13

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #3 on: November 21, 2022, 08:00:09 PM »
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #4 on: November 21, 2022, 08:11:05 PM »
I did catch the error in the datasource variable. I tested with $V{id}. Sorry bad typist.

THanks

ham13

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #5 on: November 21, 2022, 11:17:45 PM »
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

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #6 on: November 22, 2022, 06:30:46 AM »
finally original trouble has been solved? or not yet?
 (when you fix the variable name from 'ib' to 'id')

Quote
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #7 on: November 23, 2022, 06:33:51 PM »
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #8 on: November 24, 2022, 05:40:57 PM »
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

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #9 on: November 25, 2022, 06:19:34 PM »
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #10 on: November 25, 2022, 06:39:03 PM »
OK. I'll add the new line in the function Thanks

Subst

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #11 on: November 26, 2022, 05:42:04 AM »
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

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #12 on: November 28, 2022, 06:26:21 PM »
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

  • Newbie
  • *
  • Posts: 27
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #13 on: November 28, 2022, 06:57:15 PM »
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

  • Sr. Member
  • ****
  • Posts: 442
    • View Profile
Re: Can I use a variable from a user dialog in a dataset definition
« Reply #14 on: November 28, 2022, 07:20:59 PM »
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