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 first 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.
You can download a sample report here.
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.
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 might be better depending on your data situation.
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.
You can download a sample report here.
- SAP Managed Tags:
2 Comments
