Different kind of crosstab

[danasellers@udayton.edu]

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

[dlindsey]

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.

[danasellers@udayton.edu]

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?

[danasellers@udayton.edu]

Hello all,

Does anyone have other techniques for this table layout in a crosstab?

Thanks so much!

[Naven]

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

[danasellers@udayton.edu]

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.

[danasellers@udayton.edu]

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!

[TheDarkKnight]

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.

[danasellers@udayton.edu]

Thanks TDK.  I'll give these a shot and see what happens.  I appreciate your time!

Dana

[danasellers@udayton.edu]

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!