SELECT LAST MONTH ONLY

[grom71]

Hi all,
I think this is an easy one but I've confused myself...
All I want to do is run the report for the last month only using the month field NOT a date field as the report will run quicker by this method.
The month field is a link field called lnkmon and is a number basing on financial year starting in April.
e.g 04=April 05=May 06=June up to the following March=15

All I want is to do is run the report for the last full month only.
Can anyone help ????
Thanks in eager anticipation
Grom

 

[Madawc]

For Crystal 8.5, there is a function called DatePart. Try
if DatePart("m", (dateadd ("m", -1, currentdate))
= DatePart ("m", {your.date})

Madawc Williams
East Anglia, Great Britain

 

[grom71]

Madawc,
I've tried the formula substituting the "your.date" with my "lnkmon" field but is says the &quot" is missing ?
The lnkmon field is a number as described above, will this make a difference
I'm also using version 9
grom

 

[midearth]

There's also a LastFullMonth function in the date ranges which might be easier.

Try:

{Date.Field} in LastFullMonth

Sub {Date.Field} with your database field required for the search.

 

[grom71]

midearth,
Thanks for your suggestion but it didnt work
My {date.field} is a month number 04=April, 05=May, 06=June through to March=15 etc as its by fiscal year.
Grom

 

[earljgray]

A couple of points (if I understand the objective correctly)
LASTFULLMONTH can be used, IF the current date can be reset:

If you would like to base the date range on a date other than today's date, you must either change the date via the Date/Time option in the Windows Control Panel, or change the report print date via the Set Print Date/Time command in the Report Menu. (CR Help)

It seems that what the report wants to do is read a specific {date.field} then generate the previous month's info (? got it right)

IF that's true, you'll need to first get the Month/Year combo from the date field. If APRIL = 04, then you have to know what year that falls in... assuming that the year is ALWAYS the current year, that's not a problem. If, however, in FEB 2004 you want to run a report based on APRIL, then you'll have to tell the system that the year is currentyear - 1. In other words, you'll have to restate the incoming 2 digit monthcode to a month/year value, assigning (presumably) the date as 1, giving you a DATE(2002, 04, 01) for the APRIL example just preceding...

Once you have a date, you can then create a date range (if you have DATEs in the record somewhere) or, if LAST FULL MONTH is defined by {date.field} then simply select ALL records where the {date.field} = CURRENT MONTH - 1. You have to calculate, first, what the current month is by looking at the Month Value for the current month - which is OK until you get past 12 where everything has to be adjusted again to compensate for the year boundary being crossed. Normally, MARCH = 03; but in your DATE FIELD it's 15, so once you cross 12 you'll have to subtract it to get the 'real' month, and adjust accordingly.

You could also create a Parm where the user enters the month value to be reported (or a month name which you can programmatically 'convert' to a month value) then use the method described above to generate a one line formula to select the appropriate {Date.Field} from your table...

Of course, if I misunderstood the objective, then you can totally disregard everything I've said!!




Crystal Reports Design/training/Consultation
ecsdata@ecsdata.com

 

[bdreed35]

The way you posted that your financial month field is, I have assume that it is a text field (ie April="04", etc) by the way you described it.

Try this in your Record Selection Criteria:

ToNumber({table.lnkmon}) = //change this field to your field
switch
(
    Month(DateAdd("m",-1,CurrentDate)) IN [1 to 3], Month(CurrentDate) + 11,
    Month(DateAdd("m",-1,CurrentDate)) IN [4 to 12], Month(CurrentDate) - 1
)

Once you get it working, I would create a SQL Expression field for the conversion of ({table.lnkmon} to a number. There is a whitepaper that discusses the SQL Expression Convert function on CD's site. You should read it because the parameters are database dependent.

http://support.crystaldecisions.com/library/kbase/articles/c2002571.asp

Of course, if I was wrong in my assumption that the field is a string data type, then you can just drop the ToNumber function in the code above.

~Brian

 

[bdreed35]

Made a small mistake. Use this code instead:

ToNumber({Text_Months.lnkmon}) = //change this field to your field
switch
(
    Month(DateAdd("m",-1,CurrentDate)) IN [1 to 3], Month(DateAdd("m",-1,CurrentDate)) + 12,
    Month(DateAdd("m",-1,CurrentDate)) IN [4 to 12], Month(DateAdd("m",-1,CurrentDate))
)

I am sure this code be streamlined, but I don't have the time right this moment to do so.


~Brian

 

[grom71]

Hi Brian
It worked ok but it was slow.
If I use the selection to hardcode the month number it takes 1-2 minutes to run. If I use the formula it takes about 10mins !
As its a boolean expression I used the selection to say formula name = TRUE. Is this how it should work ?
Sorry for my ignorance
Grom

 

[bdreed35]

Did you read the whitepaper in my last post? You can possibly create a SQL Expression to use in the formula to speed it up.

~Brian

 

[bdreed35]

To go a little further here, I created a SQL Expression called Convert_Month. I am using a CR 8.5 with an ODBC connection to SQL Server 2000. You may have different parameters depending on what database you are using. Reference the previously posted whitepaper for syntax.

Convert_Month:

{fn Convert(Text_Months."lnkmon", SQL_SMALLINT)}

I replace your field in the Record Selection Criteria with the SQL Expression.

{%Convert_Month} = 
switch
(
    Month(DateAdd("m",-1,CurrentDate)) IN [1 to 3], Month(DateAdd("m",-1,CurrentDate)) + 12,
    Month(DateAdd("m",-1,CurrentDate)) IN [4 to 12], Month(DateAdd("m",-1,CurrentDate))
)

This now passes the it to the database as part of the SQL. This should speed up your report significantly.

~Brian

 

[synapsevampire]

Check the Database->Show SQL Query,m the reason it's slow is because it isn't passing it to the database due to not testing.

Try creating a formula, as in:

if month(currentdate)-1 = 0 then
"12"
else
totext(month(currentdate)-1,0,"&quot

Now use the formula in your record selection formula.

Anything that you add to the record selection should be reflected in Database->Show SQL Query, or Crystal will be doing the work, and your performance will suffer.

-k

 

[synapsevampire]

BTW, a SQL Expression would work too, but why bother...

-k

 

[bdreed35]

sv-

Good point. I just took the opposite approach and converted the database field in SQL Expression. I am sure they will both work fine. In your experience, besides not having to use a SQL expression, is there any benefit to one technique or the other?

Also, I took the liberty here of modifying your formula to work in this situation.

if month(currentdate)-1 IN [1,2,3] then
    ToText((month(currentdate)-1) + 12)
else
    ToText(month(currentdate)-1,"00")

~Brian

 

[synapsevampire]

Hey Brian.

I don't understand your formula.

I thought that the requirement was last month compared to a month column, in which case the only time it is 12 is if the current month is 1... I guess there must be something funny there, but I didn't care about formatting so much as making the point to make sure that the SQL is passed. The whole premise of using a month for speeds sake is likely wrong anyway, especially because it's stored as a string.

I'd reread the thread but I have a cold, and it seemed to be a rehash of untested theory from the start, and not a new problem (there are other posts here along these lines that I've responded to).

Since Crystal will pass the SQL in the above instance, there's no need to create a SQL Expression.

I use SQL Expressions often, but only if I must as the net effect is that you're adding another column to be returned dataset by the database, which means more data, hence more time.

-k

 

[bdreed35]

The requirement was that the fiscal year is skewed by three months. April(04) is first month of fiscal year and March(15) of the next year is the last month. Apparently, they do not use 1, 2, or 3. My formula is just accomodating this requirement. I guess the DBA didn't think that one through.

Thanks for the info regarding SQL Expressions. It makes sense to not use in this circumstance.

Hope you feel better!

~Brian

 

[grom71]

Hi guys
I seem to have missed whats going on !
Thanks for all your help...

The following formula worked best:

if month(currentdate)-1 IN [1,2,3] then
ToText((month(currentdate)-1) + 12)
else
ToText(month(currentdate)-1,"00&quot

However... When I refreshed it the report generated all months "09" from the database NOT last month only...

Any further suggestions..?
Grom

 

[grom71]

Oh one more thing...
If I try and run the report in Jan to pull Decembers data the month in the query is "00" instead of "12"
All other months are fine
grom

 

[bdreed35]

This should fix it for your last post:

if month(currentdate)-1 IN [0,1,2,3] then
    ToText((month(currentdate)-1) + 12)
else
    ToText(month(currentdate)-1,"00")

As far as your second to last post...you probably need to provide information regarding a database field that specifies which year the month is for. The current setup will pull all "09" months regardless of the year, as you found out.


~Brian

 

[tilakrajsingh]

You can use following statement in record selector :-
DatePart("m", (dateadd ("m", -1, currentdate))) **************

or

{datasource.lnkmon} = datepart("m", (dateadd("m"-1 ,currentdate)))