Skip to Content

If you’ve ever wanted to duplicate rows of data then this blog post should help. There are 2 methods outlined here, one for existing reports, and one for new reports. The second method will provide better performance as described below.


Method 1: Adding a Command to an Existing Report


I believe that this technique comes from Cathy Michitsch of Michitsch Systems from a while ago and she definitely deserves credit for a pretty neat method.

To do this, you need to add a Command object to your report. This Command object and a not equal join to it will ensure that the records are duplicated. It’s normally advised not to link two Commands or to link a Command to a table object for performance reasons. It will slow things down, but if you want the duplicated records then the performance loss is probably okay.

There is also a sample report attached to this blog post. Extract the contents and change the .txt extension to .rpt.

In this example we’ve already got a Customers table and want to repeat the customer address N times so that we can have more than one label per customer. To make these repeated labels / details records, then you would add a Command to your report similar to

SELECT TOP {?NumberOfLabels}

-1  AS LINKVALUE

FROM Customer

Where NumberOfLabels is a numeric prompt / parameter created in the Command object. Note that you don’t have to use a parameter should you just want to hard code the number of repeated records. i.e. set the first line to SELECT TOP 10. You need to ensure that there is not going to be a “LINKVALUE” in your existing dataset that is equal to “-1”..if there is, then change “-1″ to something that you know is not in the database…”-9999999999999″.

You may also wish to use a large table if you want to repeat the records many times. In the attached report I used the xtreme sample database which has 269 customers. In this case, I would only be able to repeat the labels 269 times, unless I changed the Command object to use a different table, such as Orders.

Now in the Database Expert the Command is linked to the existing table, using a Not Equals Join.

Untitled.jpg

One important step here is to ensure that you place the LINKVALUE field from the Command object anywhere on your report. You can suppress it if you want.

Now when you run the report you will get N number of repeated records in your report.

Method 2: Creating a New Report Using a Command

The second method builds off of the idea from the first method but uses one Command instead of tables and a Command. Since all of the record processing occurs at the database then performance will be much better.

To do this, create a new report based on a Command object using syntax similar to

SELECT

`Customer`.`Customer Name`,

`Customer`.`Address1`,

`Customer`.`City`,

`Customer`.`Country`,

`Customer`.`Postal Code`

FROM   `Customer` `Customer`,

(SELECT TOP {?N} `Customer ID` FROM `Customer`) AS C1

WHERE  `Customer`.`Customer ID` <> `C1`.`Customer ID`

Where N is a parameter created in the Command itself and is a numeric type. In this method the Top N is brought in using a table derived in the FROM clause and linked in the WHERE clause. A ‘not equal’ join is used to ensure that the records are duplicated in conjunction with the top N value.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply