nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

BIRT Stored Procedure Data Sets and Label Printing

bappoo

The complete version of this guide, in PDF format, including screeen shots is available free of charge from http://www.BIRTReporting.com
 
STORED PROCEDURE DATA SETS AND LABEL PRINTING
 
A member of BIRTReporting.com recently asked a question that had me scratching my head for a while. They were producing a report consisting of labels that were to be printed onto physical sticky label sheets and then attached to physical products in a store room.
In the database they had a single record for each product and in that record a quantity of items currently in stock. 
What they needed to do was to have BIRT produce 1 label for each individual item in stock. 
I could see that there may be a way to do this in BIRT alone, perhaps with the addition of a piece of javascript, but I figured this may put an unnecessary load on the report engine, so I elected to create a stored procedure in my database and use this as the source data for the report. 
The procedure was to iterate through all the records in the database and record the quantity for each into a variable. Then it would iterate from zero to the quantity value and with each iteration, insert a record into a temporary table, using the data from the original record. So this was essentially a nested loop, the outer loop running through the records and the inner loop creating a record for each item in stock.
The final part was to run a select query over the temporary result set and feed this to the output. 
CREATING A DATABASE TABLE
The first thing to do was create a database table to contain the base data. Since this is not a lesson in how to create tables in SQL server, I only include it here for completeness so you can follow the process from the beginning. 
CREATING THE STORED PROCEDURE
To create a stored procedure in SQL 2005 you first need to navigate to: Programmability / Stored Procedures. Then right click on Stored Procedures and select New Stored Procedure.
SQL Server automatically generates a template for you to start with. Now all we have to do is fill in the blanks!
To start with, if you click on the Query menu option, then select Specify Values For Template Parameters, SQL server give you a screen in which to enter some of the primary parameters for your stored procedure.
You can just modify the code to enter these details but here is the screenshot of the entry form. I have entered my name as the author and “usp_ItemLabels” as the name of the procedure. The “usp” bit at the beginning is a convention and signifies that the procedure is a User stored procedure, as opposed to a system one. 
On clicking the OK button the procedure is modified as follows:
 
-- =============================================
-- Author: Paul
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE usp_ItemLabels 
Next we need to add 2 variable parameters which will contain the ID from the source record and a counter for the second loop to iterate through until the counter matched the record quantity of the record indicated by the ID. These were entered as follows, with the variable type set as integer and seeded to zero.
-- Add the parameters for the stored procedure here
 @Counter int = 0, 
 @Record int = 0
Next we need to start on the procedure itself by declaring the temporary table and a cursor which will be used to iterate the source table. This was achieved as follows, notice how the cursor only returns the ID field from the source records:
 -- Insert statements for procedure here
Declare @MyData table(ID int, ItemDescription varchar(50),Qty int)
DECLARE items_Cursor CURSOR FOR
SELECT ID 
FROM table_1;
 
Following on from that we initiate the cursor, fetch the first record into our @Recordvariable and set up a while loop based on the Fetch Status. Because the cursor only returns the ID, then that is all that is entered into the @Record variable.
OPEN items_Cursor;
FETCH NEXT FROM items_Cursor into @Record;
@FETCH_STATUS = 0
 BEGIN
Within the loop we first set the @Counter variable to zero, since this section will be repeasted for each record in the source table. Then we start another while loop which runs for as long as the @counter is less than the Quantity value returned from the table, where the record ID matches the current ID we have in memory in the @Record variable. 
 
 set @counter = 0
 while @counter < (select qty from table_1 where ID =@Record )
 begin
 
Of course during the loop we need to increment the counter and insert the current source data record into the temporary table.
 
 set @counter = @counter + 1
 Insert into @MyData select ID,ItemDescription,Qty from table_1 where 
 
ID=@Record;
 end
 
Finally we jump back to the outer loop and select the next record to begin the sequence again. 
 FETCH NEXT FROM items_Cursor into @Record;
 END;
Before ending the procedure we do a little housekeeping on our cursor and run the select statement to produce the records from the temporary table as the ultimate result of the procedure.
CLOSE items_Cursor;
DEALLOCATE items_Cursor;
select * from @MyData
 
The full version of this report contains a full listing of the code. If you read through the code you will notice that it is a “Create Procedure” procedure – in other words it instructs SQL Server to generate a stored procedure, containing the code that is detailed after the first BEGIN statement.
Also, note that the variables used within the procedure (@Counter and @Record) are declared as stored procedure parameters and the table and cursor are declared within the procedure itself.
You can check your syntax by pressing CTRL & F5 and when you are happy that everything is correct you can execute the procedure by pressing F5.
All being well a new stored procedure will be created and displayed under the stored procedures branch of the SQL Server object explorer.
TESTING YOUR PROCEDURE
To test if this works, all you have to do is launch a new query and execute the stored procedure with the following command line
Exec usp_ItemLabels
CREATING THE BIRT REPORT
This stored procedure makes creating the BIRT report itself very straightforward. All we have to do in fact is use the stored procedure as a data set and we can then use the data as if it were a normal data set.
First the data source, this is just a normal data source to the database.
Now, lets see just how complicated it is to use a stored procedure as a data set:
Enter the SQL statement: Exec usp_ItemLabels 
That’s right, all you need to do is place the execute command into the query section and that’s it! The returned result set is the same as the result set returned when executing the procedure within SQL Server.
LAYING OUT THE REPORT
The concept of laying out a report consisting of labels would be very straightforward if the labels were the full width of the page, but if the data for each label is not really enough to fill the entire width of the page then it would make sense to use a sheet of labels where there are 2 or 3 labels across the page. 
Achieving this is not as simple as it sounds and we need to use a little trickery to do it. Essentially we are going to set up a 2 column report, with a table in each column. Then we are going to filter the records of the left hand column by rownum, showing only the odd numbers and filter the records in the right hand column to show the even row numbers.
Here is the basic report layout. You can see that there is a grid with 3 columns. In the left had column there is a table that is fed from the usp_ItemRecords data set and in the right hand column, a table fed from the same data set. I have left the quantity field in at this point so that we can see if we are generating the correct number of labels for each item.
Now we must add the filter to each table so that the left hand column only shows records with even row numbers and the right hand column only shows records with odd row numbers.
To do this, highlight the detail row from the first table and select the Visibility property.
Next enter the following expression:
(row.__rownum % 2 != 0)
The Java % operator divides one operand by another and returns the remainder as its result. So dividing the rownum by 2 will produce a remainder if the rownum is odd and will not if the rownum is even.
The != means “does not equal” so in this statement we are saying
Return true if rownum divided by 2 does not return a remainder of 0
So for the first row of the report, rownum =1 
1 divided by 2 = 0.5 (which has a remainder) so the expression returns false.
Because the visibility property for the row only hides the row if the expression returns true, the row is displayed. Of course the opposite is true for even rows, so in the right hand tables the expression would be:
(row.__rownum % 2 == 0)
RUNNING THE REPORT
Here we can see that output from the report and using the Qty values, we can count up the instances of labels for each item, to ensure they match.
As you can see, for item 1, which has a Qty of 1 we only have 1 label but for item 2, which has a quantity of 4 we have 4 labels. Also the labels read left to right across and down the page.
Finally we need to space out our labels down the page, so that they fit onto our printed labels appropriately. We achieve this by using the row height property. Select the row you want to set the height for and in the general properties set the height to a certain number of units, e.g. 2 cm.
Do this for all rows as appropriate, to match your label stationery and remove any unnecessary data items (in my case the Qty field) and the report is complete.
MORE INFORMATION
If you would like the full version of this report in PDF format with screen shots or to find out more about BIRT reporting and the BIRT User Group UK then please visit http://www.BIRTReporting.com
Please feel free to share this address with your colleagues and inspire them to use BIRT to create great looking reports.
I look forward to your feedback so please feel free to send me an email and let me know how you get on with BIRT, provide feedback on this guide, share your tips and tricks, or request help for specific problems. I can’t guarantee to personally solve everyone’s problems but there are some great BIRT related forums out there and you can find a growing list of links and resources on my site. 
Paul Bappoo
Paul@BirtReporting.com
http://www.BIRTReporting.com
<div></div>
http://www.BIRTReporting.com

Find more posts tagged with

Comments

There are no comments yet