Crosstab question

[peterb58]

I am trying to produce a crosstab report to show the total number of calls from areas in the system.

What I want to see is
Friday Saturday Sunday ......... Thursday
January 01/01/2013 02/01/2013 03/01/2013 31/01/2013
Area 1 total total total ........ total
Area 2 total total total total
.
.
.
February
Area 1 total total total ........ total
Area 2 total total total total
.
.
.
March
Area 1 total total total ........ total
Area 2 total total total total
.
.
.


What I see it that the columns are also showing the days of each month, so across the columns I would see entries for 1st Jan - 31st Mar. I would like to restrict the columns to show only the days of the months

Friday Saturday Sunday ......... Thursday Friday ..............Friday .........Sunday
January 01/01/2013 02/01/2013 03/01/2013 31/01/2013 01/02/2013 01/03/2013 31/03/2013
Area 1 total total total ........ total total total total
Area 2 total total total total total total total
.
.
.
February
Area 1 total total total ........ total
Area 2 total total total total
.
.
.
March
Area 1 total total total ........ total
Area 2 total total total total
.
.
.


How can I get the Columns to only show the values I want?

IS this going to have to be a Running Totals job?

thanks

 

[MCuthill]

Hi PeterB58,

I am unsure what you are asking (though could be due to a case of Monday Morning). Are you looking only for specific days (consistenty across the entire report), for example: Friday, Saturday, Sunday. Your reference to Thursday, after the days for the weekend, puzzled me. Can you take a swing, word-wise to explain the intent of your report? Looks like 1st, 2nd 3rd, Month end... but I assume you are looking for entire month, grouped on day of the week (ex: all Mondays together, all Tuesdays, etc), but not certain.

From looking at the above, I *think* what is needed is to move your CrossTab into the Group Footer. The Group for said footer being based on the Month of the Date field you are using (can right-click group and set "on Months").
Assuming you are looking for all "Mondays" to be together / to replace the date with the day of the week: use the [blue]DayOfWeek[/blue] function, which will return a value 1-7; [blue]WeekDayName[/blue]([blue]DayOfWeek[/blue]({YourDateField})) will return "Sunday" through "Saturday", if no other parameters are defined in the DayOfWeek function.

Other than this starting step, I am thinking I need a better handle on the overall intent prior to giving any guidance as to how to proceed further.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[peterb58]

Mike

Agreed, it is not as clear as I would have liked. The formatting has not carried over as I hoped.

The target is to have the Month and Areas down the left side and the days of the month across the top. Each of the cells will show the total calls for the day for each of the areas.
So I hope to see 31 columns in total for the max number of days in a month. Those months which as less than this will just show zero calls for that day.

Problem is, if I run the report to cover a quarter, I see 1st Jan to 31st March as the columns. A few more than I want!
I suspect that as soon as I try and identify the columns as Monday 01/01/2013, I will run into problems.

I am working from, what I suspect is, a hand crafted document and I am trying to reproduce as closely as possible. If that is not possible, then they will have to have the closest option available.

Working on CR XI with Sybase.

Thanks

Pete

 

[MCuthill]

G'Day Pete!

I think that helps... but if I am out in left field, please feel free to give me a cuff and set me in the right direction.

Under the assumption I understand what you are seeking, the only issue I can see is that if no calls are placed on a given day for the entire period (in your example, we will say that there were no calls Jan 6, Feb 6 or Mar 6) -- a column for "6" will not be created. That being said... if exported to Excel, not too tricky to add a quick column in with zeros (there are likely other solutions, but I beleive they would require some DB access; I think a table with all calendar dates or some such thing to left outer join to... but that is beside the point haha).

The above is based on the understanding you would like "Day", not "Date" across the top of your cross tab. To acheive this, you need to create a formula field - let's say {@Day_CallDate} for ease of reading.

{@Day_CallDate}

[blue]Day[/blue]({table.CallDate})

*where {table.CallDate} is your Date Field

You can then use this as the Column Header for your Cross Tab.

I think this should get you going, please advise if the direction you were looking to take the report and/or any issues you encounter and we can see about tackling them for you.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."