Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Numbering columns in matrix Tablix Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 3:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 11, Visits: 41
Update: Added attachment (PDF)

Disclaimer: Noob in the house. I'll try to phrase the question correctly.

SSRS Picking Report uses dataset in matrix. Data Set is a Pick List with varying numnber of Sales Orders having multiple lines per order. Lines contain Bin location and Item Quantity. Item Quantity is aligned with each corresponding column header(order).

The Orders are grouped in columns which expand dynamically to the right. The Line Items comprise the rows. The Tablix is sorted by the rows (Bin location).

I would like to autonumber, dynamically, the columns (distinct orders). "1" for the first column (order), "2" for the second column, "3" and so on.

I've tried several suggestions online, generally involving getCount in Code, and expression referencing the code. None work. If I have 10 columns, columns are ordered non-sequentially based on logic I'd spin my wheels trying to figure out.

I also tried CountDistinct(OrderNumber).

I'm throwing darts, blindfolded, at this point. One will surely hit me on the head without your help.

Thanks!


  Post Attachments 
AutoNumberingColumnsInMatrixWithSortingByRows.pdf (3 views, 36.27 KB)
Post #1463338
Posted Thursday, June 13, 2013 4:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 1,796, Visits: 5,804
Are you trying to number the columns in order to just put a number in the column title? or some other purpose?

Generally, I would say just add a row_number over(partition by product order by whatever) column to your dataset.

Then you can display the row_number value in the column title - you will need to sort the matrix column group by the row_number to make sure the numbers come out in order.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1463342
    Posted Friday, June 14, 2013 8:47 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 8:44 AM
    Points: 11, Visits: 41
    Thanks for your reply, appreciate it.

    Yes, I'm trying to "auto-number" number, left to right, the columns in the order in which they appear. The columns (Sales Orders) are ordered by the Row sort (Bin Location). I'm not sure this can have anything to do with changing the matrix sort must follow the rows.

    Does this explain more? Will row_number over still work to dynamically number however many columns appear (generally 1 to 8 columns).



    Post #1463622
    Posted Friday, June 14, 2013 11:48 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:38 PM
    Points: 1,796, Visits: 5,804
    Yes, it sounds like adding something like

    ROW_NUMBER() OVER(PARTITION BY ZN,Bin,Item ORDER BY [Order Number]) as [Column Number]

    ...but you still haven't said what order the columns (order numbers) are in, so I had to guess at order number order...remember there is no such thing as "Row order" unless you specify one.

    EDIT: just saw the PDF so edited...


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1463719
    Posted Friday, June 14, 2013 1:20 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 8:44 AM
    Points: 11, Visits: 41
    Thanks again.

    I will give that a look, though admittedly I'm not exactly sure how\where this goes in the report.

    I am also attaching a second PDF with Tablix and Group Properties which may explain what I was trying to!



      Post Attachments 
    AutoNumberingColumnsInMatrixWithSortingByRows_page2.pdf (2 views, 27.90 KB)
    Post #1463757
    Posted Friday, June 14, 2013 5:19 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:38 PM
    Points: 1,796, Visits: 5,804
    Sorry, I see now - you will have orders that span multiple rows and rows that span multiple orders...so the row_number won't help...but I have an idea...

    I will let you know if I come up with something...


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1463790
    Posted Friday, June 14, 2013 5:39 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:38 PM
    Points: 1,796, Visits: 5,804
    I got something to work - try this:

    Select your "SalesOrder" column/field in the matrix and insert a row "inside group - above" and add this expression (edited to suit your dataset)

    =RunningValue(CountDistinct(Fields!SalesOrder.Value),SUM,"DataSet1")

    My dataset was called DataSet1 and my Sales Order column was called SalesOrder - just change to suit.

    This will (it does on mine) add a running count to that new field:



    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw



  •   Post Attachments 
    Column Numbers.PNG (40 views, 7.23 KB)
    Post #1463793
    Posted Friday, June 14, 2013 10:29 PM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 8:44 AM
    Points: 11, Visits: 41
    Thank you so much! I'll give this a shot and let you know how it goes.
    Post #1463812
    Posted Tuesday, June 18, 2013 8:55 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 8:44 AM
    Points: 11, Visits: 41
    I received an error when building the report. However, I am not certain whether or not I executed your suggestion the way you intended. Please see the attached PDF.

    Also, I'm wondering if\how the following report components factor into things.

    Order number expression:
    =replace(Fields!SOPNUMBE.Value,"DTCI","") --strips "DTCI" from SOPNUMBE

    Row groups:
    matrix1_BinNumber (group/sort on BinNumber),
    matrix1_ItemNumber (group/sort on ItemNumber),
    matrix1_ItemDescription (group/sort on ItemDescription)

    Column groups:
    matrix1_SITE (groups on SOPNUMBE, sorts by Location)


    Thanks again!


      Post Attachments 
    RunningTotalExpression_Error.pdf (5 views, 67.76 KB)
    Post #1464710
    Posted Tuesday, June 18, 2013 9:41 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:38 PM
    Points: 1,796, Visits: 5,804
    Ah, it may be a new feature of 2012 then...sorry hadn't noticed that.

    Maybe there will be another way...


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1464741
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse