Hello crosstab lovers,
I have what I personally think is a toughy - hopefully not so tough for those of you who love crosstab puzzles. Below is an example of the database record that i'm dealing with. I've also placed below the desired crosstab output. How can I make this happen?
DATABASE RECORDS:
CRS BLDG ROOM START TIME END TIME MON IND TUES IND WED IND THU IND FRI IND SAT IND SUN IND
EDT209 andr 103 900 950 M W F
EDT101 andr 103 1100 1150 M W F
EDT250 andr 103 900 950 M W F
EDT220 andr 103 1100 1150 T R
EDT102 andr 103 900 950 T R
DESIRED CROSSTAB OUTPUT:
Building: andr
Room: 103
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
EDT 209 EDT 209 EDT 209
900 950 EDT 250 EDT 201 EDT 250 EDT 201 EDT 250
1100 1150 EDT 101 EDT 220 EDT 101 EDT 220 EDT 101
First I would section the report by the building, room. I would make the meeting pattern (days) to be columns and the meeting time to be rows. The course id would be the measure. Let me know if that works.
thanks so much for getting back to me. i'm unsure what you mean by meeting pattern (days). each day is in a separate column in the course's table row. when I place each day field in the columns - how do I get the course id to show up under each column that has something in it's day indicator field in the measures?
Hello all,
Does anyone have other techniques for this table layout in a crosstab?
Thanks so much!
To get a structure similar to your desired output, Use a List object, Drag and drop all the columns in the order you wish.
Then select the first column > Go to Strucure on the Menu Bar > then click Section.
Again repeat the same step for second column.
Regards,
Naveen
Thanks for getting back with me. The problem isn't getting the bldg and room at the top of the page as a section, the problem is getting the CRS to show up under the appropriate day indicator in the 'measures' section of the crosstab. since the record is keyed by crs, each day indicator exists for every crs. I also want a blank to appear for this day indicators that are blank.
Ok, so I created a view to try to accomodate how the data looks coming into the crosstab. It seems closer but now the corsstab is placing things in the cells when there's nothing there. I have shown the data records from the view and also a sample of the crosstab output:
VIEW:
YEAR TERM BLDG BLDG DESC ROOM BTIME ETIME DAY SUB CRS# SEC
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Monday EDT 442L P1
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Wednesday EDT 442L P1
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Tuesday N/A N/A N/A
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Thursday N/A N/A N/A
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Friday N/A N/A N/A
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Saturday N/A N/A N/A
2009 200980 CHAMND Chaminade Hall 010F 0800 1150 Sunday N/A N/A N/A
CROSSTAB OUTPUT
CHAMND
010F
Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0800 1150 EDT-442L-P1 EDT-442L-P1 EDT-442L-P1 EDT-442L-P1 EDT-442L-P1 EDT-442L-P1 EDT-442L-P1
Why does EDT-422L-P1 show up under everyday when the data shows N/A for Tuesday - Sunday?
Thanks so much for any ideas!
Hey Dan,
u culd do is for debugging purpose, First chk whether the view u defined gives the correct output in a list in report studio. whether the measures are displayed properly. if yes, then try to use 'Convert list to crosstab' optiion.
i suppose if frame manager properities are set properly, u culd easily achieve ur desired output by discontinuous crosstab [refer the doc] and sections as other friends said
Cheers
TDK.
Thanks TDK. I'll give these a shot and see what happens. I appreciate your time!
Dana
Wierd. This works perfectly in the list, but when I convert it to crosstab - nothing then shows in the measure section. I modify the cell fact source type to be Data Item Value and assign Data Item Value to course and then all the cells are filled with the same course as displayed. I'll look into the discontinuous crosstabs.
Thanks again!