Can use CrossTab Report to create "Time Table Report"?

[cazellnu]

I had attempt to create the report with the following format with cross-tab but seems doesn't work:

Mon Tue Wed Thr Fri Sat Sun
MORNING xxx xxx xxx xxx xxx xxx xxx
xxx xxx xxx xxx xxx xxx
xxx xxx xxx
xxx xxx
xxx xxx
AFTERNOON xxx xxx xxx xxx xxx
xxx xxx xxx
xxx xxx
xxx
"x" are the information I draw from the database and I group the time with either AM or PM on the row and group the column by the week date. The problem is cross-tab had automatically summarized the "summary" section. I had also attempt to create this report with multi-column support but I had no clue how to control which column should these "x" data should be appear. Can anyone give me some hands? Thank You

 

[synapsevampire]

Please provide example data and expected output.

-k

 

[lbass]

More information would be helpful. If you have a datetime field, and you are using the columns to group on date, then in addition to using a group on AM or PM, add a row that is just based on times to get something like this:

Mon Tues Weds Thurs Fri
9:00 Eng Eng --- Eng Eng
AM 10:00 Phil Phil Phil Alg Alg
11:00
1:00
PM 2:00 etc.
3:00

If your data changes from week to week, you would want to make your columns date-specific. So the question is, is this close to what you are looking for, and if so, what problems are you running into?

-LB

 

[cazellnu]

lbass:

This is pretty close to what I am looking for and I am develop this report base on information on each row of that contains fields including Bound_Date, Bound_Time, Bound_Number and etc... All I am trying to do is to group all data (such as just the Bound_Number) in each column depending on the Date of the Week of the Bound Date and group all datas in each sessions depends on the Bound_Time. The report will only report event on 1 week since user will require to specific the range of the report with parameters.

The problem is I been trying to use both cross-tab report and multi-column reports but I don't know which approach was right since I have experienced difficulties in both approaches.

 

[lbass]

I would try the crosstab. For the date group, go to format crosstab, enter {table.bound_date} as your column and then select it and choose group options, group on day, and select customize name using a formula and add:

["Sun","Mon","Tues","Weds","Thurs","Fri","Sat"] [DayofWeek({table.bound_date})]

Do the same thing for your row, using {table.bound_time}, maybe using "for each hour" as your grouping basis. I'm not sure if you would need to format it using a customized name, since I'm not sure about your datatype or how it is currently formatted in your database.

Let me know if this works for you.

-LB

 

[cazellnu]

lbass:

I had did the same as mentioned. However the summerized field had count the number of bound in each session automatically instead of display all the bound number. In addition, I am using version 7 of crystal report. Please help

 

[lbass]

A couple of suggestions:

To display a non-numeric field (text or date), choose "Nth Most Frequent" as the summary. If you have no more than a few non-numeric fields you want displayed per row (time), you could add the field several times more to "summarize" and keep choosing a different "n"--1, 2, up to the maximum number of summaries you want displayed.

You could also try not grouping on the row field. If your summarized field occurs at distinct times, this will automatically increase the number of rows, and the number of summarized fields displayed.

I'm sorry I'm not familiar with CR v.7--so I'm unsure of what features you have available in your crosstab.

-LB

 

[cazellnu]

lbass:

The problem is I don't know how many bound_number would possible to include in specific section there maybe be none and there can be more than hundreds. Is there any alternative solution?

Cazellnu the Miner

 

[lbass]

Let's say that the bound_time is 2:00 PM and the day of the week is Monday. Are you saying there could be hundreds of strings in that one cell? How is this crosstab being used? It would really be helpful if you could give a more specific description of what you would like your results to be.

Another approach could be a manual crosstab where your columns are based on individual formulas like:

if dayofweek({table.bound_date}) = 2 then {table.bound_number}//column 1 for Monday--place in details section

if dayofweek({table.bound_date}) = 3 then {table.bound_number}//column 2 for Tuesday

Create a total of seven formulas like this for your columns and place in the details section.

You might want to look at your results without any groups to begin with, but sorting on {table.bound_time}. Then decide what intervals make the most sense for your display. You could then use one of the grouping choices for time intervals (hours, minutes, etc.) or choose specified groups and narrow the possibilities.

Creating a manual crosstab like this might give you more flexibility.

-LB