I need to limit number of columns in a matrix report

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  February 24, 2017 at 4:48 am

  #327960

  hi guys
  I have a matrix report and the query used on the report

   

  DECLARE

  @table TABLE

  (

  STATUS varchar(50),

  
  

  RequiredByDate DATETIME,

  
  

  WorkOrder VARCHAR(20)

  )

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '1-Jan-2017','S04886')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '2-Jan-2017','S04901')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '3-Jan-2017','S04902')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '4-Jan-2017','S04903')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '5-Jan-2017','S04904')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '6-Jan-2017','S04905')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '7-Jan-2017','S04906')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '8-Jan-2017','S04907')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '9-Jan-2017','S04908')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '10-Jan-2017','S04909')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '11-Jan-2017','S04910')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '12-Jan-2017','S04911')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '13-Jan-2017','S04912')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '14-Jan-2017','S04913')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '15-Jan-2017','S04914')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '16-Jan-2017','S04915')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '17-Jan-2017','S04916')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '18-Jan-2017','S04917')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '19-Jan-2017','S04918')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '20-Jan-2017','S04919')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '1-Jan-2017','S05042')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '2-Jan-2017','S05046')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '3-Jan-2017','S05097')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '4-Jan-2017','S05134')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '5-Jan-2017','S05139')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '6-Jan-2017','S05148')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '7-Jan-2017','S05149')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '8-Jan-2017','S05150')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '9-Jan-2017','S05151')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '10-Jan-2017','S05212')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '11-Jan-2017','S05213')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '12-Jan-2017','S05214')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '13-Jan-2017','S05215')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '14-Jan-2017','S05216')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '15-Jan-2017','S05217')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '16-Jan-2017','S05218')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '17-Jan-2017','S05219')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '18-Jan-2017','S05270')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '19-Jan-2017','S05271')

  
  

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '20-Jan-2017','S05272')

  
  

  SELECT t.STATUS,t.RequiredByDate,t.WorkOrder FROM @table AS t

  I need to limit the number of columns to at least 10 so that it won't go to the next page.
  I've tried creating a list and used vb code that I got from one of the site but the it's still does not work

  Please help

• Thom A

  SSC Guru

  Points: 99133

  More actions

  February 24, 2017 at 5:04 am

  #1930123

  Nomvula - Friday, February 24, 2017 4:48 AM

     hi guys

  I have a matrix report and the query used on the report

  DECLARE @table TABLE(
  STATUS varchar(50),
  RequiredByDate DATETIME,
  WorkOrder VARCHAR(20));

  INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '1-Jan-2017','S04886');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '2-Jan-2017','S04901');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '3-Jan-2017','S04902');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '4-Jan-2017','S04903');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '5-Jan-2017','S04904');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '6-Jan-2017','S04905');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '7-Jan-2017','S04906');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '8-Jan-2017','S04907');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '9-Jan-2017','S04908');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '10-Jan-2017','S04909');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '11-Jan-2017','S04910');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '12-Jan-2017','S04911');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '13-Jan-2017','S04912');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '14-Jan-2017','S04913');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '15-Jan-2017','S04914');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '16-Jan-2017','S04915');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '17-Jan-2017','S04916');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '18-Jan-2017','S04917');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '19-Jan-2017','S04918');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Overdue', '20-Jan-2017','S04919');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '1-Jan-2017','S05042');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '2-Jan-2017','S05046');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '3-Jan-2017','S05097');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '4-Jan-2017','S05134');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '5-Jan-2017','S05139');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '6-Jan-2017','S05148');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '7-Jan-2017','S05149');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '8-Jan-2017','S05150');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '9-Jan-2017','S05151');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '10-Jan-2017','S05212');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '11-Jan-2017','S05213');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '12-Jan-2017','S05214');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '13-Jan-2017','S05215');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '14-Jan-2017','S05216');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '15-Jan-2017','S05217');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '16-Jan-2017','S05218');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '17-Jan-2017','S05219');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '18-Jan-2017','S05270');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '19-Jan-2017','S05271');
INSERT INTO @table ( STATUS, RequiredByDate,WorkOrder ) VALUES ( 'Due', '20-Jan-2017','S05272');

  SELECT t.STATUS,t.RequiredByDate,t.WorkOrder FROM @table AS t;

  I need to limit the number of columns to at least 10 so that it won't go to the next page.
  I've tried creating a list and used vb code that I got from one of the site but the it's still does not work

  Please help

  Wow, that's a lot of white space. I've fixed that post of yours first.

  Secondly, what is your display meant to look like, what are you using as your column heading? What 10 should you be displaying? Can you supply what you want your output to look like with your results above? You could likely achieve this easily by using ROW_NUMBER, but I don't want to guess what you want to omit.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Nomvula

  Hall of Fame

  Points: 3778

  More actions

  February 24, 2017 at 6:16 am

  #1930139

  I forgot to mention
  the RowGroup  = Status
  ColumnGroup = RequiredByDate
  Data = Count(WorkOrder)

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 24, 2017 at 6:34 am

  #1930142

  What do you want to do with the extra values? Discard them or group them?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99133

  More actions

  February 24, 2017 at 7:16 am

  #1930153

  Maybe..?
  WITH RN AS (
  SELECT t.[STATUS],
        t.RequiredByDate,
        t.WorkOrder,
        DENSE_RANK() OVER (ORDER BY t.RequiredByDate DESC) AS RankNumber
  FROM @table t
)
SELECT RN.[STATUS],
       RN.RequiredByDate,
       RN.WorkOrder
FROM RN
WHERE RN.RankNumber <= 10;

  Your explanation didn't actually tell me what you need to eliminate, so I've guessed that you only want the 10 most recent dates. I've used DENSE_RANK in case one status doesn't have a value for that date, so that the matrix is still limited to 10 group columns.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk