Month Year in separate fields, need to combine them in CR 20

building reports crystal reports
1

I am using Crystal Reports 2008, and CR 2008 Server. One of the Views we are using has different fields for Month and Year, both Calendar and Fiscal. I need to be able to combine the Month Year, when the user wants to select a date range she will be able to select 11/2011 through 6/2012 right now she has to select Month 1-12 and then Year 2000 - 2012. Doesn’t get the data she needs.

Any Help would be very much appreciated.

Thanks

Don

dhillsr (BOB member since 2011-03-14)

2

Part of the problem is that when you have the date in the format mm/yyyy as a string, it sorts like a string. For example:

1/2012
1/2013
10/2012
2/2012

So, using “between” with month and year in that format won’t get you the results you need.

One option to get around this would be to format the parameters as yyyy-mm and force a leading 0 on the month when it’s prior to October. The above sample would then sort like this:

2012-01
2012-02
2012-10
2013-01

Or you could set up the parameters in the mm/yyyy format you’re looking for and create a formula to convert them to yyyy-mm.

You would then also create a SQL Expression (assuming you’re using just views and/or tables and not any commands) that looks something like this:

“MyView”.“Year” || ‘-’ || Right(‘0’||“MyView”.“Month”, 2)

Use this SQL Expression in your selection criteria along with the parameters to get what you’re looking for. If you can’t create a SQL Expression, you could use a formula to do the same thing. The problem with using a formula is that Crystal will bring all of the data in the view into memory and filter it there instead of letting the database do the work.

-Dell

hilfy :us: (BOB member since 2007-04-16)

3

Hi, thanks for your input, but I am still learning how to create new formulas.
Is there a formula that can be used to combine the month and year fields?

dhillsr (BOB member since 2011-03-14)

4

If they’re number fields, you’ll do something like the following:

ToText({table.month}, 0, ‘’) + “/” + ToText({table.year}, 0, “”)

if they’re strings, take out the ToText() syntax above.

-Dell

hilfy :us: (BOB member since 2007-04-16)

5

Hi,

Consider using 2 parameters string:

From
set the edit mask to -> 00/0000 – Prevents users from entering in other format

To
set the edit mask to -> 00/0000

create 2 formulas in crystal to process paramaters

FromDate = right(From,4)+left(2)
ToDate = right(To,4)+left(2)

An additional formula for database year month
ReportDate=year+month

IN record select
ReportDate in [FromDate to ToDate]

dame (BOB member since 2007-07-30)