1

I have a report made in report builder 3 that will find order lines based off of an order number. I am trying to have the row repeat based on the quantity value of that row like this:

Part   Qty
001     5
002     2

I am trying to get it to repeat the 001 row 5 times and the 002 row 2 times. (This is for printing off labels) I cannot create or update tables in the database.

Thanks

Trevor
  • 11
  • 3
  • Can you include the query used to return this data? –  May 22 '13 at 19:12
  • 1
    This isn't a feature available in SSRS. You would need to write a function that makes the multiple entries in the initial query then send that to the report. – StevenWhite May 23 '13 at 04:16

2 Answers2

0

This can be addressed in the underlying SQL statement. See this answer.

The basic idea is to create a view with a list of numbers (N). Then join to that view with N <= Qty:

CREATE VIEW [dbo].[vwNumbers] AS SELECT N=1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 GO

SELECT Part FROM PartList INNER JOIN vwNumbers ON N <= Qty

Community
  • 1
  • 1
Ken Mc
  • 1,268
  • 15
  • 12
0

I have the same problem and after searching a lot I could do it this way, I have to repeat and display a product label based on a number, for example in my table label that I have:

ItemId | Barcode | CustName | LabelQty
001    | 123abc  | Jhon     |    3

So in this case I need to repeat the same label 3 times. I achieved this by grouping the table by a value that count the number of times to be repeated.

ItemId | Barcode | CustName | LabelQty | Counter
001    | 123abc  | Jhon     |    3     |    1
001    | 123abc  | Jhon     |    3     |    2
001    | 123abc  | Jhon     |    3     |    3

In my case the 3 labels are the same so I fill only the first row with label data in order to reduce the data to process, like this: 

ItemId | Barcode | CustName | LabelQty | Counter
  001  | 123abc  |    Jhon  |     3    |    1
  null |   null  |    null  |  null    |    2
  null |   null  |    null  |  null    |    3

in the field expresion I use =first(field.value, 'datasource') to find the row with data.

if you have to repeat different values I suppose that you have to fill all rows.

hope this helps.

sorry for bad english.

Felipe Sierra
  • 181
  • 5
  • Could you describe how you achieved that? I can't see the solution in your answer, only the result. – pieeetr Jul 26 '17 at 12:54
  • I think it's clear, suppose you have a row, and you need to repeat it 3 times, so you need to add a column to 'identify' that row ("Counter" in the answer), the number of records must be the same of the number of times to be repeated (3 in this case), of course you just insert the counter value not all the columns the result something like the last 'table' in the answer, remember to group your table in SSRS and set the value with 'first' – Felipe Sierra Jul 28 '17 at 17:52
  • the way you implement the counter is missing, please explain – Bacon Jun 12 '19 at 09:13