Adding GroupNumber

• xanthos

  SSCrazy

  Points: 2510

  More actions

  November 28, 2007 at 10:03 am

  #181578

  I've got a simple table in SSRS 2005 pulling data from SQL server 2000.

  The table holds invoice information and is grouped by invoice number. Details holds line-items information. On the groupheader level I want to put an consecutive number for each invoice.

  Since the tablefilter filter on parameters I want to do this in the table rather than in the data set.

  The table would look like this:

  InvoiceA1SalesCost

  ItemX

  ItemY

  InvoiceB2

  ItemY

  ItemX

  ItemZ

  InvoiceC3

  ItemZ

  Anyone any ideas?

• xanthos

  SSCrazy

  Points: 2510

  More actions

  November 28, 2007 at 10:18 am

  #755039

  Table should like the attachment.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 28, 2007 at 10:41 am

  #755047

  Normally I would do something like this

  Invoices

  Invoice_ID indentity(1,1) int or bigint primary key,

  Customer_ID (fk to purchasers information)

  and a table for items

  Invoice_Details

  Item_ID (FK to purchased item where decription comes from),

  Quatity,

  Price_Per_Item,

  Discount_Applied

  Then you can join both for the invoice to get your results as expected with a bit of tweaking in the presentation layer.

• xanthos

  SSCrazy

  Points: 2510

  More actions

  November 29, 2007 at 2:08 am

  #755278

  Thanks,

  I can't change the source tables. The only way I can get it to work is create a temp table with a identity column in the dataset and join this to my original query.

  I would like to number the groups in the reports table. Is there any way to do this in VB?