1. Dale
  2. Report Editor
  3. Среда, Сентябрь 25 2019, 05:54 AM
  4.  Подписаться через email
How to create a group band from a MySQL stored procedure datasource which I placed in the pre_build script of my report?
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
Hello Dale,

Please add more details.

In the pre_build script, you call a stored procedure - which creates a temporary table, or...?
Комментарий
There are no comments made yet.
Dale Ответ принят
Yes, Sergey, you are correct. A stored procedure generates a cursor (temporary table). The grouping of rows is handled in the stored procedure already.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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
Вложения
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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?
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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?
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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?
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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?
Комментарий
There are no comments made yet.
Dale Ответ принят
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.
Комментарий
There are no comments made yet.
Sergey Pashkov Ответ принят
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 ] ]; " )
Комментарий
There are no comments made yet.
Dale Ответ принят
"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!
Комментарий
There are no comments made yet.
  • Страница :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories