Crosstab by Column (Years) Rows (Months) Comparison 2

[Dominican1979]

Hello Everyone,

Thanks a lot in advance for reading my post. So I have a new report I started to work on, but I'm having trouble figuring out how to put it together.

Basically this is the deal. I need to be able to see how customers are doing in terms of sales given a date range last year compared to this year broken down by months and within the months broken down by order type as shown on the sample below:

2011 2010
January
phone "qty" "$ amt" "qty" "$ amt"
travel "qty" "$ amt" "qty" "$ amt"

February
phone "qty" "$ amt" "qty" "$ amt"
travel "qty" "$ amt" "qty" "$ amt"

March
phone "qty" "$ amt" "qty" "$ amt"
travel "qty" "$ amt" "qty" "$ amt"



I am not sure if I need two sets of date ranges, one for each year or not, I'm a bit confused at this point. Thanks a lot again for your help.

Crystal Reports XI

 

[MCuthill]

Dominican1979,

You should just need a couple formula fields:
{@Month}

Month({YourDateField})

{@Year}

Year({YourDateField})

Setup a crosstab as follows:
Rows:
- {@Month}
- {OrderType}
Columns:
- {@Year}

Your record selection would be the earliest date to the most recent. If you want to see all of 2010 to current, your selection criteria would be:

{YourDateField} in [Date(2010,01,01) To CurrentDate]

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."

 

[Dominican1979]

Hi Mike,

Thanks a lot for your prompt response, I'm still setting up my query that will feed the report and as soon as I try what you suggested I will let you know, thank you so much again.

 

[Dominican1979]

Hi again Mike,

I did what you suggested and the report looks great. I just have a question, is there a way to arrange the months in order? using the month name. Right now its sorting them alphabetically. I expanded the formula you gave me to show the month name like this:
MonthName(Month({YourDateField})) but they're not in the order they're supposed to be. I know I can have them sorted correctly with the month number like your formula says

 

[MCuthill]

Dominican1979,

You can structure your cross tab as follows and supress the outer LH column, if needed:

Rows:
- {@Month} -- my original formula
- {@MonthName} -- your formula for MonthName()
- {OrderType}

Columns:
- {@Year}

As it will sort numerically, and only one month name exists for each number, won't add any rows to the cross tab.

Alternately, change your formula to as follows:

ToText(Month({YourDateField}),00,"") & " - " & MonthName(Month({YourDateField}))

This will return text such as:
"01 - January"
"02 - February" etc

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."

 

[lbass]

You could use:

month({table.date}) as your row field, and then select group options->options tab->customize groupname->use a formula:

monthname(month({table.date}))

-LB

 

[MCuthill]

Thanks LB!

I was thinking there was an option like that, but without Crystal in front of me yesterday I didn't want to send anyone down the garden path.

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."

 

[Dominican1979]

Hello, thanks a lot Mike and LB for your valuable input, they both work great, you guys are awesome