0

Is it possible to easily distribute data ('subject's in my case) into different columns based on the value of another field ('block' in my case) so I could have a kind of timetabling grid report, i.e. if my data looks like:

Subject   |   Block

----------


English   |   A 

French    |   B 

Science   |   C 
 ----------

x         |   A  

y         |   B

z         |   C

How might I produce a table / matrix that looks like:

Block A | Block B | Block C

English |  French | Science 

   x     |    y    |    z

(forgive the formatting!) I can't help thinking this must be straight forward, but I can't seem to find the appropriate technique. Something like a pivot, but listing rather than aggregating values? I thought maybe filtered columns, but that doesn't seem very efficient. Many thanks for any advice!

Raghavendra
  • 3,530
  • 1
  • 17
  • 18
Mikey
  • 3
  • 2

1 Answers1

0

Using the following as a basis

https://stackoverflow.com/a/9007678/2311633

(I have copied the relevant sections so the complete answer is on this page...)

You can create a horizontally expanding table by:

  1. First create a Tablix by dragging the Matrix Report Item onto the design surface. The Tablix will have a RowGroup and a ColumnGroup by default.
  2. Delete the Row Group by right clicking on it and selecting "Delete Group" In the Delete Group prompt, delete both just the group. (Not related rows and columns; you'll probably want these as left label for your rows.)

At this point right click the Column Group and "Add Group -> Child Group...". Keep adding child groups for each of the rows you require. For each child group select 'Group by' and choose each of the series you wish to display on each individual row.

I am unable to post images at this time, but have been able to recreate what you have requested above. Once I'm able to I'm can post screenshots for further clarification if required.

Update

Alternatively, If you are able to edit the SQL source could you add another field to define a row number for each item. Using ROW_NUMBER() and PARTITION_BY you could add a new column such as

SELECT ROW_NUMBER() OVER(PARTITION BY [Block] ORDER BY [Block]) as rownum

Then you could just create a simple Matrix as shown here https://www.flickr.com/photos/135805284@N08/20883237722/

Community
  • 1
  • 1
Jonnus
  • 2,988
  • 2
  • 24
  • 33
  • Thanks for your help! Unfortunately I can't seem to make the 'subjects' populate rows below the column headings - they seem to make a a whole new column for each instance rather than list vertically. Am I missing something? – Mikey Aug 25 '15 at 13:12
  • I've uploaded an example of the setup i used, and the output it gave to flickr -> https://www.flickr.com/photos/135805284@N08/20684997468/ <- . Note that in this I have set column1, row1 and row5 to have Hidden = True to make it look cleaner. Is this the sort of thing you're after? (I realise this example is upside down but I'm sure you can rejig it as necessary) – Jonnus Aug 25 '15 at 14:44
  • Ah, I think I see what you have you have done. However, what I mean by x,y,z is 3 more subjects, such that Block (column A) might have let's say 'English' and 'Business' -, block B, 'French' and 'Geography', Block C with 'Science' and 'History' below. – Mikey Aug 25 '15 at 22:26
  • All subjects - let's say around 20, are different records in the 'subjects' field of the database, each with a corresponding block, A through E, thus the aim would be to to have 4 subjects in each of 5 columns (although the initial query will actually be dynamic, so both the number of blocks and the number if subjects is liable to change). Forgive me if your solution achieves this and I'm not doing it right! Thank you so much for support :) – Mikey Aug 25 '15 at 22:26
  • Mikey, I have updated the answer as a result of your comments above, to use a new column populated using ROW_NUMBER() – Jonnus Aug 26 '15 at 08:43
  • Ohhh, that's great! Nice technique :) That will help me no end, thanks so much! – Mikey Aug 26 '15 at 13:38
  • Good stuff, if it's what you're after can you mark as Answer (click the tick by the ratings arrows) – Jonnus Aug 26 '15 at 13:41