LimeReport Forum
General Category | Основное => Discussion | Обсуждение => Topic started by: ham13 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
-
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 Source as select * from Table where id=$V{id}then define in script page some scripts
YourDlg.exec()==1
function beforeDataPrint() {
setVariable("id", YoourDlg.YourLineEdit.Text)
reopenDatasource(Source)
}
-
OK. I'll give it a try. Finally figured out the syntax to do the calculation. Will post results.
Thanks
-
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
-
I did catch the error in the datasource variable. I tested with $V{id}. Sorry bad typist.
THanks
-
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
-
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
-
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
-
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
-
Sorry
I've forgot to tell about this
Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint);
after beforeDataPrint definition
But some thing wrong
I told Alex, let's wait
-
OK. I'll add the new line in the function Thanks
-
No!
not IN the function
OUTSIDE them
function beforeDataPrint() {
setVariable("id",2)
}
Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint);
and setVariable("id",2) has "brake down"... or connect may be
Alex has promised to look
-
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
-
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
-
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
-
Thank you for your help! I understand now that I can use JS in LPD scripts. How ever I cannot find a reference to the function reopenDatasource("Data") in either language. That's why I requested a source for the codeing used in LRD.
This code, when I add it to my project and use the proper variable, the program stops working.
txtYear is the line edit in my dialog. Year is the variable containing the data (Year).
Databand6 is the datband on the report page. I have defined the variable Year just aas you defined id.
Dialog.txtYear.text=getVariable("Year");
Dialog.exec()==1;
function beforeDataPrint()
{
setVariable("Year", Dialog.txtYear.text);
}
Reportpage1_DataBand6.preparedForRender.connect(beforeDataPrint);
This code, that I came to use after many tries, works. I do not know why but it does work.
Dialog.exec() == 1
var Year = Dialog.txtYear.text
var Date = Dialog.txtDate.text
var Check = ""
beforeDataPrint();
function beforeDataPrint() {
Check = "Function";
setVariable("Year", Dialog.txtYear.text);
reopenDatasource("tblneighborhood");
}
Thanks
-
I think you set the datasource for your DataBand6
It's affect to the program crash
I can't find the cause yet
-
Yes, that is the name of the band shown on the report page when I click on the data band. It shows DataBand6 in yellow. The datasource for the band is set to tblneighborhood. I'll set it to DataBand1 and see what happens.
Thanks
-
Setting Reportpage1_DataBand6.preparedForRender.connect(beforeDataPrint) to Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint) produces error "line 9 reference Reportpage1_DataBand1.preparedForRender.connect(beforeDataPrint)is not defined"
-
Reportpage1_DataBand6 - you have thi band
Reportpage1_DataBand1 - and don't have this
Reportpage1_DataBand6 means PageName_BandName
-
I'm not sure what you mean from your message. I know that the code should be "Reportpage1_DataBand6" because it is defined in the report and "Reportpage1_DataBand1" is not. That is why I used that reference in the code when I tested it. Hopefully you will find the cause of the crash.
Regards
Marty
-
I've asked Alex about crash.
We've discussed about and this behavior of band (crash in such case) it's right, normal.
I can quote our talk. but it's in Russian and I'm too lazy to translate it :) Sorry ;)
-
OK. So, unless I specify Reportpage1_DataBand1 in the code it will crash and this is normal behavior. That does not sound right. Well that leaves only one option; I'll use the code that works. I had hoped for a better solution. Let me know if I have misunderstood your comments.
Again thanks for your patience and efforts to resolve this problem. :)
Regards,
Marty
-
Well!
Good luck :)
-
ham13,
OK. So, unless I specify Reportpage1_DataBand1 in the code it will crash and this is normal behavior. That does not sound right
if we write somethig like this:
QObject* object = new QObject();
delete object;
qDebug() << object->objectName();
Will application crash be the normal behavior?
In your case something like this happens.
If you change on preparedForRender slot the variable linked with rendering datasource
this will invalidate datasource in rendering cycle.
Possible I have to make some additional checks for this case, but the behavior is normal :)