Crosstab columns by year/month/day 1

[Dominican1979]

Hello everyone,

Thanks in advance for reading my post. So I have a report with a crosstab on the report header and thats all I have in there. This report is to keep track of customer service reps and the count of return authorization numbers they give out. This is how my data is arranged on the crosstab:

Rows: Employee Name
Columns: a formula @summarize (described below)
Summarized Fields: count of RA numbers

@Summarize

Select {?SummarizeBy}
case "Year" : date(year({RADate}),1,1)
case "Month" : date(year({RADate}),month({RADate}),1)
case "Week" : {RADate}-dayofweek({RADate})+1
case "Day" : {RADate}

I also have a parameter field called: SummarizeBy which has a list of the following values:

Year
Month
Week
Day

When I run the report, I do get the correct number of columns based on the dates and broken down correctly depending on the option I choose (year, month, week, day). However, the column label shows the date no matter what option I pick.

for example if I run it from Jan 2011 to June 2011 by year, I would get one column which is correct, but it would say 1/1/2011 instead of saying 2011. If i run the same dates by month, I would get the first date of each month for the columns like: 1/1/2011, 2/1/2011, 3/1/2011.... and so on. If I do it by month, the same story picking the first date in each week. The only one that actually does what is supposed to is picking by day, shows the actual dates for each day

Now, what I would like to have happen is this:

If I run the report and pick year from the parameter list, I would like it to say the actual year for each column. If i run it by month, the name of the month, by week: the week number within a month and by day just the date like it does now.

I'm using Crystal XI

Thanks a lot for your help in advance

 

[lbass]

Change your column formula to:

select {?Summarize by}
case "Year" : totext(year(date(year({table.date}),1,1)),"0000")
case "Month" : totext(date(year({table.date}),month({table.date}),1),"yyyy-MMM")
case "Week" : totext({table.date}-day({table.date})+1,"yyyy-MMM")+" Week "+totext(datepart("ww",{table.date}) -
datepart("ww",{table.date}-day({table.date})+1)+1,"00")
case "Day" : totext({table.date},"yyyy-MM-dd")

-LB

 

[Dominican1979]

Hey LB thank you so much this works like a charm

 

[Dominican1979]

Hi again LB. I was just doing some more testing and realized that when I choose summarize by week, the order of the columns isn't right. For example: If I do a date range of 07/01/11 to 08/31/11
I get them in this order:
2011-Jul Week 5 | 2011-Aug Week 02 | 2011-Aug Week 01

In crosstab expert, I have set the group options for the summary field to descending so I would think it would look like this instead:
2011-Aug Week 02 | 2011-Aug Week 01 | 2011-Jul Week 05

Any idea what may be going wrong here?

Thanks a lot again

 

[Dominican1979]

I think it may be because its sorted as text and not dates

 

[lbass]

Actually, now that I think about it, the months won't sort correctly either. If you are willing to display months as month numbers, then change the formula to:

select {?Summarize by}
case "Year" : totext(year(date(year({table.date}),1,1)),"0000")
case "Month" : totext(date(year({table.date}),month({table.date}),1),"yyyy-MM")
case "Week" : totext({table.date}-day({table.date})+1,"yyyy-MM")+" Week "+totext(datepart("ww",{table.date}) -
datepart("ww",{table.date}-day({table.date})+1)+1,"00")
case "Day" : totext({table.date},"yyyy-MM-dd")

-LB

 

[Dominican1979]

Hi LB,

Just made the changes and works perfect, I don't mind displaying the months as numbers, thanks a lot again.