how to get future 11 months based on current month and group on it

[toblerone65]

Hello friends, I been trying to create a formula to get all the months based on current date and I want to group on it. I know in SQL i create this, but wondering if there is a shortcut way to do this Crystal Reports too. Ia using XIr2.

Thanks in advance,
Toby

 

[lbass]

if {table.date} in dateserial(year(currentdate),month(currentdate)+1,1) to dateserial(year(currentdate),month(currentdate)+12,1)-1 then "Next 11 Months" else
"Other Dates"

You should provide more explanation about the grouping you want, as I doubt this is really what you are looking for.

-LB

 

[toblerone65]

hello LB , sorry if i was not clear, I want to create a fake data which will show months from Jan to dec all 12 months, is there way I can crate a formula in crystal which will show months from Jan to dec in separate rows?, I would like to group on it then.
Jan
Feb
March
.
.
.
Dec

Thanks a lot,
Tob

 

[lbass]

I think you should create a table in Excel or Access that includes all dates in the year and then link your existing table to that wiht a left join FROM the new table to your existing table, with no selection criteria on the right hand table. Then group on the new date field on change of month.

-LB

 

[toblerone65]

LB, what is i dont link the two tables? , will it affect anything I want to new table colum only to use as group and nothing else.

 

[toblerone65]

Sorry for mis-spell, I meant is it okay if dont link two tables at all?

 

[lbass]

That would create a cross join, with every row in one table repeating for every row in the other.

-LB