You're welcome!

My Dear Sergey,

Well, I'll be damned! You hit the nail on the head by suggesting to pass an explicit value instead of a variable. I just found out that I did not handle well the variable used to pass for the parameter to catch. Now my report works! You are a God-send, Sergey! Loads of thanks to you!!!

Hi Dale,

Thank you for the details, I think I recreated your structure but it still works for me from Xojo.

The report query is not executed in the MakeNewReport, but only when it is necessary - like getting the number of pages or printing, so it should not be a problem.

Have you tried also to pass a constant value to parameter, like:
mReport.SetParameterValue("pHeaderID", "1" )

I created an example to do it entirely in the Valentina Studio.

1. I added the stored procedure.
It creates a temporary table with the same structure as original and fills it.

CREATE PROCEDURE create_source_table()

BEGIN

	DECLARE v_finished INTEGER DEFAULT 0;

	DECLARE id varchar(100) DEFAULT "";

	DECLARE quantity INTEGER DEFAULT 0;

	DECLARE i INTEGER DEFAULT 0;

 	DECLARE items_cursor CURSOR FOR SELECT * FROM tblItems;

 	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;



	DROP TABLE IF EXISTS tblLabelsSource;

	CREATE TEMPORARY TABLE tblLabelsSource SELECT * FROM tblItems LIMIT 0;



	OPEN items_cursor;

	get_item: LOOP

		FETCH items_cursor INTO id, quantity;



		IF v_finished = 1 THEN LEAVE get_item; END IF;

	

		SET i = 1;

		WHILE i <= quantity DO

			INSERT INTO tblLabelsSource VALUES( id, quantity );

			SET i = i + 1;

		END WHILE;

	END LOOP get_item;

	

	CLOSE items_cursor;

END;

2. Checked that it works:

CALL create_source_table();

3. Created a query based on the original table: SELECT * FROM tblItems

4. Created a report with this query

5. Changed query to SELECT * FROM tblLabelsSource

6. Changed "pre_build" script of the report to call the procedure (screenshot)

Now before building each report tblLabelsSource is filled with data.

Yes, it is possible with JavaScript, just switch the type of pre_build script to JavaScript and write the following code:

report.datasource.sqlSelect( "CALL genqrlabels(" + report.parameterValue( "pHeaderID" ) + ");" );

To test it in the Valentina Studio, you can add this parameter on the left panel and make it interactive.

Sergey, thank you for bearing with me all this time. The parameter now works in VS Pro but from Xojo, it shows a blank report. I guess there's a problem with my parameters calling the MakeNewReport project method. This is how I do it:

mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)

This works on other simple report that I already made but not on this one. No error generated, except it shows nothing. Probably there is a problem with the strSelect. At runting strSelect has this value: "select * from tmpQRLabels". Is this correct?

Update: I tried changing the parameter pHeaderID value to "1" in Valentina Studio,still the result shows a blank report.

It is working from Xojo if I revert to [SQL] in the report's pre-build with the statement "Call genqrlabels(1)". The thing is, I have to provide an explicit value as a parameter for the stored proc. If I switch to [Javascript] with this statement:

report.datasource.sqlSelect( "CALL genqrlabels(" + report.parameterValue( "pHeaderID" ) + ");" );

... this one works from Valentina Studio because I provide the value of pHeaderID at runtime. From xojo, it shows nothing. I suspect that the parameter passing from Xojo does not get into consideration during the initialization of the report in the statement:

mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)

This is an excerpt from my xojo code:

    mReport = mProject.MakeNewReport(strReportFile, vconnect, strSelect)

    

    mReport.SetParameterValue("cCompany",ReportModule.CompanyName)

    mReport.SetParameterValue("cAddress",ReportModule.CompanyAddress)

    mReport.SetParameterValue("cEmail",ReportModule.CompanyEmail)

    mReport.SetParameterValue("cWebsite",ReportModule.CompanyWebsite)

    mReport.SetParameterValue("cLandLine",ReportModule.CompanyLandline)

    mReport.SetParameterValue("cMobile",ReportModule.CompanyMobile)

    mReport.SetParameterValue("cTIN",ReportModule.CompanyTIN)

    mReport.SetParameterValue("pHeaderID",Str(me.HeaderID))

pHeaderID is passed AFTER the creation of the report not before, so I think the pre-build takes the default value instead and use it and cannot find the HeaderID value of "333" (your given default). Thus the report is blank.

Sergey, this is the stored proc:

CREATE DEFINER=`root`@`localhost` PROCEDURE `genqrlabels`(IN pHeaderID INT(11))

BEGIN

	DECLARE v_finished INTEGER DEFAULT 0;

	DECLARE vitemcode varchar(50) DEFAULT "";

    DECLARE vdescription varchar(100) DEFAULT "";

    DECLARE vuniqueid varchar(20) DEFAULT "";

    DECLARE vexpiry varchar(10);

	DECLARE vqtyreceived decimal(12,4);

    DECLARE vqrlabel varchar(250) DEFAULT "";

    

	DECLARE i INTEGER DEFAULT 0;

 	DECLARE rrdetails_cursor CURSOR FOR

    SELECT itemcode,description,uniqueid, DATE_FORMAT(expiry, '%Y-%m-%d') AS expiry,qtyreceived,Concat('Item Code: ',itemcode,'\n','Desc: ',Description,'\n','Batch ID: ',uniqueid,'\n','Expiry: ',DATE_FORMAT(expiry, '%Y-%m-%d')) AS qrlabel FROM bw01.vwrrdetails Where HeaderID = pHeaderID;

 	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;



	DROP TABLE IF EXISTS tmpQRLabels;

    CREATE TEMPORARY TABLE tmpQRLabels

    (itemcode VARCHAR(50),description varchar(100),uniqueid varchar(20),expiry varchar (10),qtyreceived decimal(12,4),qrlabel varchar(200));

	#CREATE TEMPORARY TABLE tmpQRLabels SELECT * FROM tblItems LIMIT 0;



	OPEN rrdetails_cursor;

	get_item: LOOP

		FETCH rrdetails_cursor INTO vitemcode,vdescription,vuniqueid,vexpiry,vqtyreceived,vqrlabel;



		IF v_finished = 1 THEN LEAVE get_item; END IF;

	

		SET i = 1;

		WHILE i <= vqtyreceived DO

			INSERT INTO tmpQRLabels VALUES(  vitemcode,vdescription,vuniqueid,vexpiry,vqtyreceived,vqrlabel);

			SET i = i + 1;

		END WHILE;

	END LOOP get_item;

	

	CLOSE rrdetails_cursor;

END

Oh, sorry, please ignore the latest post I made above. I wanted to edit one of my previous posts but instead it created a new one. Sorry.

My code (and my approach) works with simple reports. It doesn't in this particular case. The only difference is that, with this one I used stored proc plus other instructions that you suggested. This app I am developing is a web app so a temporary table is a must unless I can find another way how to print multiple qr codes from a simple parent - children tables.

I am near to accomplish this thing since it is already working in Valentina Studio.

And what is the difference with the simpler report?

I'll check what could be wrong.
Also, if you need this report as soon as possible, you can call this procedure in Xojo code before MakeNewReport, but the table should not be TEMPORARY in that case.

Yes, I followed your screenshots, en toto.

Have you defined a default value for this parameter in the Valentina Studio?

tmpQRLabels is the temporary table that was created using your code that I customized.

Hello Dale,

Looks like the only way to do it is to use a stored procedure and a temporary table:
1) Stored procedure fills the temporary table with records - one record for each label
2) The report is printed from this temporary table

Hi Dale,

Only the string is accepted in this method.
I think a bit of JavaScript will be necessary, I’ll give an example

I tried doing this from Xojo:

mReport.SetParameterValue("pHeaderID",me.HeaderID)

I've got this error:
ModalPrint.GenerateReport, line 139
Parameter "inValue" expects type String, but this is type Integer.
mReport.SetParameterValue("pHeaderID",me.HeaderID)

If I placed a literal value, like, "call genqrlabels(2)" now the report runs. How can I pass a header ID from Xojo?

Yes, Sergey, the quantities are stored from the source table... and your code, wow! Really, thanks to you I am making huge improvements. I customized your code and I already managed to run the script from mysql itself and also from Valentina Studio Query Builder. I even made the stored proc parameterized so that only the detail records of a particular receiving report can be retrieved. However, I still cannot run the report. What is the syntax for including a parameter in the pre-build? I put "call genqrlabels(pHeaderID)" where gengrlables is the stored proc name and pHeaderID should be the ID of the header/parent record.