Hello Dale,

Please add more details.

In the pre_build script, you call a stored procedure - which creates a temporary table, or...?

Yes, Sergey, you are correct. A stored procedure generates a cursor (temporary table). The grouping of rows is handled in the stored procedure already.

So now it is necessary to group by another field and add a group band for it? An example would help a lot.

Usually, the grouping is set on the Data tab, Grouping button

Sergey, you can only do this if you have a datasource, right? The stored procedure is parameterized. I would like to supply the parameters at runtime from a xojo app. The report editor does not allow you to have a group band unless you assign a field from the datasource.

Yes, but you can call this function manually in the SQL Editor.
It creates a temporary table. Then you add a query to select data from this table and a report where you can define grouping.

This stored procedure does something that can't be done with report parameters (VReport.SetParameterValue) and usual grouping in the report, right?

May I post the stored procedure here? So you may guide me where to put it in the report designer.

CREATE DEFINER=`root`@`localhost` PROCEDURE `genstocksallitems`(In pDateFrom varchar(10),pDateTo varchar(10),pCustomCond varchar(1000),pCount varchar(1),pTotalOnly varchar(1),pOrderBy varchar(1000),pLimit varchar(1000))

BEGIN



	Declare vstarthour varchar(8) DEFAULT "00:00:00";

    Declare vendhour varchar(8) DEFAULT "23:59:59";

    Declare vDateFrom varchar(19) default Concat(pDateFrom," ",vstarthour);

    Declare vDateTo varchar(19) default Concat(pDateTo," ",vendhour);

	Declare vCustomCond varchar(1000) Default pCustomCond;

    Declare vCount varchar(1) Default pCount;

	Declare vTotalOnly varchar(1) Default pTotalOnly;

    

	DECLARE v_finished INTEGER DEFAULT 0;

 

    DECLARE vitemcode varchar(50) DEFAULT "";

    DECLARE vbarcode varchar(50) DEFAULT "";

    DECLARE vstockcode varchar(50) DEFAULT "";

    DECLARE vdescription varchar(100) DEFAULT "";

    DECLARE vunit varchar(20) DEFAULT "";

    DECLARE vqty decimal(12,2);

	DECLARE vamount decimal(12,4);

    DECLARE vItemID Int(11);

    DECLARE vStockID Int(11);

    DECLARE vclassification varchar(100) DEFAULT "";

    

    DECLARE stockallitems_cursor CURSOR FOR

		SELECT ItemCode,barcode,stockcode,itemfulldesc,unitcode,SUM(remaining) As totalqty,SUM(remaining * costprice) As totalamt,ItemID,StockID,Classification From bw01.vwstocklog Where ItemFullDesc is not null And (DateDoc BETWEEN vDateFrom AND vDateTo) group by StockID,ItemID;



 	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

    

   	DROP TABLE IF EXISTS tmpStockAllItems;

    CREATE TEMPORARY TABLE tmpStockAllItems

    (itemcode VARCHAR(50),barcode VARCHAR(50),stockcode VARCHAR(50),description varchar(100),unit varchar(20),totalqty decimal(12,4),totalamt decimal(12,2),ItemID Int(11),StockID Int(11),Classification varchar(100));



	OPEN stockallitems_cursor;

	get_item: LOOP

		FETCH stockallitems_cursor INTO vitemcode,vbarcode,vstockcode,vdescription,vunit,vqty,vamount,vItemID,vStockID,vclassification;



	IF v_finished = 1 THEN LEAVE get_item; END IF;

	

	

        If vqty > 0.0000 Then

			INSERT INTO tmpStockAllItems VALUES(vitemcode,vbarcode,vstockcode,vdescription,vunit,vqty,vamount,vItemID,vStockID,vclassification);

		End If;

	

	END LOOP get_item;

	

	CLOSE stockallitems_cursor;

    

	If pCustomCond Is Not Null Then

		SET @whereclause=CONCAT(vCustomCond);

		        

        If Upper(vCount) = 'Y' Then

			SET @stmt= "Select Count(*) As 'Count' FROM tmpStockAllItems WHERE";

        ElseIf Upper(vTotalOnly) = 'Y' Then

			SET @stmt= "Select Sum(TotalAmt) As 'GrandTotal' FROM tmpStockAllItems WHERE";

        Else

			SET @stmt= 'SELECT * FROM tmpStockAllItems WHERE';

        End If;

        

		SET @stmt1=CONCAT(@stmt,' ',@whereclause); 

    

		If pOrderBy Is Not Null Then

			Set @orderclause=CONCAT(pOrderBy);

                   

			SET @stmt3=CONCAT(@stmt1,' ORDER BY ',@orderclause); 

        End If;

        

        If pLimit Is Not Null Then

			Set @limitclause=CONCAT(pLimit);

            

              SET @stmt3=CONCAT(@stmt3,' LIMIT ',@limitclause); 

        End If;

        

		PREPARE stmt2 FROM @stmt3;

		EXECUTE stmt2;

		DEALLOCATE PREPARE stmt2; 

      

    Else

		

		If Upper(vCount) = 'Y' Then

			Select Count(*) As 'Count' From tmpStockAllItems;

		ElseIf Upper(vTotalOnly) = 'Y' Then

			Select Sum(TotalAmt) As 'GrandTotal' FROM tmpStockAllItems;

        else

	

			If pOrderBy Is Not Null Then

				Set @orderclause=CONCAT(pOrderBy);

          

         

				SET @stmt=CONCAT('select * from tmpStockAllItems ORDER BY ',@orderclause); 

            

			End If;

          

           

			If pLimit Is Not Null Then

				Set @limitclause=CONCAT(pLimit);

            

				SET @stmt=CONCAT(@stmt,' LIMIT ',@limitclause); 

			End If;

          

          	PREPARE stmt1 FROM @stmt;

            EXECUTE stmt1;

			DEALLOCATE PREPARE stmt1; 

		

		End If;

    End If;

    

 

END

Then I call this stored proc for report purposes like this:

call genstocksallitems('2018-09-01','2019-09-30',Null,Null,null,'Classification',Null)

Where the 6th parameter -- "Classification" rearranges the order of rows by classification.

Thank you in advance.

1) genstocksallitems procedure returns records, so it looks like you can call it not in the pre_build script, but in the report query itself and pass this query to VReport.MakeNewReport method with necessary parameters

2) You need to create a report with grouping by some field from the tmpStockAllItems table, right?

1) genstocksallitems procedure returns records, so it looks like you can call it not in the pre_build script, but in the report query itself and pass this query to VReport.MakeNewReport method with necessary parameters --- I think I will do this after I finish the design of the report and I can preview it from the report IDE.

2) You need to create a report with grouping by some field from the tmpStockAllItems table, right? -- Yes, sir, and I have to do this first.

So you create a query with
CALL genstocksallitems ...
in your project.

And then design a report with this query.

Should work fine. Or any problem with it?

I can create a datasource for my report and call the query from there. Sure I can make a group band then, but when I preview it, the report engine automatically adds a "group by" clause that generates errors. Basically my query statement goes like this:

call genstocksallitems('2018-09-01','2019-09-30',Null,Null,null,'Classification',Null) As foo group by Classification

The last part is added by Valentina itself.

Ah, yes, so that's the problem, and there is no SELECT FROM CALL in MySQL.

What versions do you use - Valentina Studio and V4RB plugin?

Valentina Studio Pro 8.3.3. For the Xojo plugin, I'm not sure. I think it's version 8. The installer filename is v4rb_8_win.EXE.

Looks like you can avoid the temporary table and stored procedure at all if I am not missing something

SELECT

  ItemCode,

  barcode,

  stockcode,

  itemfulldesc,

  unitcode,

  SUM( remaining ) As totalqty,

  SUM( remaining * costprice ) As totalamt,

  ItemID,

  StockID,

  Classification

FROM

  bw01.vwstocklog

WHERE ItemFullDesc is NOT null

AND ( DateDoc BETWEEN vDateFrom AND vDateTo )

GROUP BY StockID , ItemID HAVING totalqty > 0.0000;

Filter and limit can be added later to the query of report.
But the other problem is sorting - you need to change sorting in your Xojo code, right?
With grouping in report, it is impossible to change it in the query using ORDER BY, it will be just ignored.

But in the latest version, it is possible to change it using new VReport.RunScript method, which executes JavaScript

For example, the following sets descending sorting by "field_name.

mReport = mProject.MakeNewReport(  reportIndex + 1, ds_str , "SELECT * FROM T1"  )

dim rs_res as string = mReport.RunScript( "report.sortFields = [ [ 'field_name', false ] ]; " )

"Filter and limit can be added later to the query of report." -- Okay, I will try your code and get to designing the report first and hopefully I will be able to run it. I will worry the filtering and other matters later but I will sure to come back to this thread.

Thanks, Sergey!