cross-tab group option

[Former Member]

How can I create a cross tab group option to get reports for a group of 2 months of each year?

we have the option for a day, week, month, quarter or year..

I need an option for November and December together as a group.

Thanks in advance.

Claudio.

[Former Member · Accepted solution]

I think by using specified order we can get it.

When you go to group options select specified order and add each month from JAN to OCT by giving the range from 1/1/2008 to 1/31/2008 -->JAN

from 2/1/2008 to 2/29/2008-->FEB

.

.

.

.

.

from 11/1/2008 to 12/31/2008--> Group Name

Regards,

Raghavendra

[Former Member · Accepted solution]

I just need sales information for the months of november and december for the last 8 years.

[former_member260594 · Accepted solution]

If you only want to display data for Nov and Dec for the past 8 years then filter the other months out in the record selection;

month() in [11,12]

Then group on year.

[Former Member · Accepted solution]

If you want only november and december then as explained before use the specified order and create new group for month >10 and all others by default comes under Others group. In the options tab check the option discard Others.

Regards,

Raghavendra

[Former Member · Accepted solution]

Raghavendra,

This is the information for the date I'm using,

It's working fine for year comparison but I would like to include just november and december for the last 8 years.

stringvar DT:= totext({pos_hist_lin_fil.PosHistLinInvcDat},0,'');

numbervar yr:= tonumber(left(DT,4));

numbervar mo:= tonumber(mid(DT,5,2));

numbervar dy:= tonumber(right(DT,2));

Date (yr,mo,dy)

How can extract just the last 2 months of the year and grouped by year?

I appreciate all your help,

[Former Member · Accepted solution]

Did you try using record selection formula like

month(datefield)=11 or month(datefield)=12

Regards,

Raghavendra.G

[Former Member · Accepted solution]

How can I create the formula with the information provided?

[Former Member · Accepted solution]

No need to create a formula. You need to apply filter condition to the data to extract only nov and dec data from database. For that click on menu option

Report-->selection formulas --> records and write

month(datefield)=11 or month(datefield)=12

Regards,

Raghavendra.G

[Former Member · Accepted solution]

My date fiel is:

Date (yr,mo,dy)

I tried the following but I get an error.

month(date(mo))= 11 or month(date(mo))=12

[Former Member · Accepted solution]

try this

month(Date (yr,mo,dy))= 11 or month(Date (yr,mo,dy))=12

[Former Member · Accepted solution]

Still getting the error.

[Former Member · Accepted solution]

What is the error that you are getting?

[Former Member · Accepted solution]

There is an error in the formula,

The remaining text does not appear to be part of the formula.

here is the text I put in the record selection,

stringvar DT:= totext({pos_hist_lin_fil.PosHistLinInvcDat},0,'');

numbervar yr:= tonumber(left(DT,4));

numbervar mo:= tonumber(mid(DT,5,2));

numbervar dy:= tonumber(right(DT,2));

Date (yr,mo,dy)

month(Date (yr,mo,dy))= 11 or month(Date (yr,mo,dy))=12

[Former Member · Accepted solution]

Try this

stringvar DT:= totext({pos_hist_lin_fil.PosHistLinInvcDat},0,'');

numbervar yr:= tonumber(left(DT,4));

numbervar mo:= tonumber(mid(DT,5,2));

numbervar dy:= tonumber(right(DT,2));

month(Date (yr,mo,dy))= 11 or month(Date (yr,mo,dy))=12

[Former Member · Accepted solution]

That works well,

but I need to group them as a total for the year.

[Former Member · Accepted solution]

select the group option yearly instead of each day or monthly

[Former Member · Accepted solution]

I got it,

I really appreciate all your help,

Thank you very much,

Regards.

[Former Member · Accepted solution]

Since I made the change,

I lost the selection of the year,

I need to select records that are after year 2000.

how can I include it in the same formula?

[Former Member]

Yes, I'm using CR2008, how can I do it?

[former_member260594]

Claudia,

I'm not familiar with your data enough to tell you exactly how to do this so I would suggest you got to thee Help File > Contents > Crosstab Objects > Advanced Crosstab Features > Calculated Members.

[former_member260594]

If you are using CR 2008 you could create a calculated member to do this

[Former Member]

i would do a formula to cast your date/time field to the month name (e.g. 01/05/2008 4:45PM = January)

formula: MonthName (month({Command.Create Date/Time}))

You can then use that formula in your cross-tab...then do "Specified Order"...selecting Jan..Feb...Mar........etc..etc...Oct. Then use the "Put All Others, with the name:" = "November/December". (in this case that would be Nov and Dec)

This will keep you from having to do a range when years change..etc. Hope this helps.

Level:

1. {Command.Create Date/Time} - row printed for each year

2. *formula* - to show as "Jan..Feb............Nov/Dec"

output:

2001

Jan

Feb

.

.

.

.

Nov/Dec

2002

Jan

Feb

.

.

.

.

Nov/Dec

2003

Jan

Feb

.

.

.

.

Nov/Dec

etc.

etc.

Edited by: Roberto Andrade Jr on Sep 5, 2008 5:41 PM

[Former Member]

Thank you for your help,

I need to do it for every year, for the last 8 years.

Regards,

[Former Member]

I'm not sure whether this works for you. Try creating two formulas like year(date field) and month(date field).

Now insert both the formulas as columns in cross tab and use the group options for the month column

1-->JAN

.

.

.

.

8-->OCT

greater than 10-->group name

Regards,

Raghavendra.G