automatically display rptng mo nbr 1

[John1Chr]

Hi all,

I'm working in crystal and I would like to write a formula that does this based on the current date:

mo/day/year give me YearMo (Format month with 0X)
for example, 3/25/2009 gives me 200903

I'm thinking of automatially feeding the report based on this formula.

 

[IanWaterman]

This should return what you want

totext(Year(currentdate),0,"")&totext(month(currentdate),"00",0,"")

If you use shuch a formula in select statement it will not be very quick as all data will have to be retrieved from DB and then filtered in Crystal.

Ian

 

[John1Chr]

I'm haveing trouble putting it in the select statement. It's expecting a number. I wonder if there's a better way to feed the automatic date parameter?

 

[CR85user]

Why don't you explain in more detail the following:

1) Crystal version
2) Datasource Type
3) Current selection criteria
4) What you are trying to acheive

automatically feeding the report sounds as if (In my twisted mind) you are trying to get the report to run automatically without parameter input. As long as there is a clear rule to define the requred periods this is usually pretty easy.

Please make sure though that you give enough information so that we can help you..... Abbreviated titles and vague posts will not help you to acheive your aim.

'J

CR8.5 / CRXI - Discovering the impossible

 

[John1Chr]

1.) Crystal 11.5
2.) ODBC warehouse table
3.) I'm selecting by project number;however, every month has a different reporting month number.
4.) I'm trying to automate the date parameter in the selection criteria so that the user just needs to enter the project number.

Yes, I'm trying to automatically feed the date only.

 

[CR85user]

Excellent, that helps us all understand much more.

When you select by project number AND date at the moment what does it look like?

E.g. is your selection criteria something like:

{table.project} = {?Project} and {table.datemonth} = {?Monthparam}

Or is it structured differently?

Have you tried swapping the formula posted by Ian above in place of the parameter field in your selection criteria?

'J


CR8.5 / CRXI - Discovering the impossible

 

[John1Chr]

This is what I had in it intially:

{IA_PROJECT.PROJ_5_NBR} = "AJ089" and
{IA_PROJECT.RPTG_MO_NBR} = 200903

I tried Ian's formula and I got that error.

 

[IanWaterman]

Change my formula to

tonumber(totext(Year(currentdate),0,"")&totext(month(currentdate),"00",0,""))

Ian

 

[lbass]

Or you could use:

tonumber(totext(currentdate,"yyyyMM"))

-LB

 

[John1Chr]

I'm using the formula -

tonumber(totext(Year(currentdate),0,"")&totext(month(currentdate),"00",0,""))

to automatically insert rptng mo nbr like YYYYMM based off the current date. I have the problem that on the first of the month,like today, I really need 200905 and not 200906 like the formula states. Everytime I hit the first day of the month the warehouse is still behind a day. Is this easily modified?

 

[lbass]

tonumber(totext(Year(currentdate-1),0,"")&totext(month(currentdate-1),"00"))

-LB

 

[John1Chr]

LB,

Thanks...it was pretty simple yet escaped me!