parameter date field with only mm-yyyy mask

[Former Member]

hi

i want to create a parameter field with only mount/year.

it's possible ?

i'm in Crystal 2008

[Former Member · Accepted solution]

thanks to Shwu Hua Gan and Deepravs ,

yes i understand that.

but what i want is for the user only have mm-yyyy not dd-mm-yyyy.

i have 1 option making like Shwu Hua Gan said. and if i want lov for that ? i can make 1 text parameter with 12 values for the mounth and 1 for the year with 10/20 years from 2010 to 2030 for instance.

any other solution ?

[Former Member · Accepted solution]

Hi,

Not sure what do you really mean.

1. Do you want to user to select a list of LOV, mm-yyyy; or

2. You want to user to select 2 from 2 parameters, one for month and one for year?

If you are looking for option 1, then you might be able to achieve with writing the report based on Command within crystal report or based on SQL view, where you have a column with mm-yyyy and set a dymanic parameter for user selection.

[Former Member · Accepted solution]

If you want to create a pick list for your parameter you have a couple of options...

1) The 2 parameter option:

Create 2 static parameters... 1 for month and 1 for year. (both will have numeric values but set them up as string type parameters)

Then in you selection criteria do something like this...

{TableName.FieldName} = ({?MonthParameter} & "-" & {?YearParameter})

2) The single parameter option:

~ Place the following SQL code in a CR SQL Command (Written for and tested in MS SQL Server)

~ Create a dynamic parameter using {Command.MonthYear} and the LOV source of the parameter.

IF object_id('tempdb..#Month') IS NOT NULL
BEGIN
   DROP TABLE #Month
END
CREATE TABLE #Month (MonthNum INT)
DECLARE @Num INT
SET @Num = 1
WHILE @Num <= 12
BEGIN
INSERT INTO #Month (MonthNum) Values(@Num)
SET @Num = @Num + 1
END
-------------------
IF object_id('tempdb..#Year') IS NOT NULL
BEGIN
   DROP TABLE #Year
END
CREATE TABLE #Year (YearNum INT)
DECLARE @NumY INT
SET @NumY = 2010
WHILE @NumY <= 2030
BEGIN
INSERT INTO #Year (YearNum) Values(@NumY)
SET @NumY = @NumY + 1
END
-------------------
SELECT
CASE WHEN Len(Cast(m.MonthNum AS VarChar(2))) < 2 
	 THEN '0' + Cast(m.MonthNum AS VarChar(2))
	 ELSE Cast(m.MonthNum AS VarChar(2)) END 
	 + '-' + Cast(y.YearNum AS VarChar(4))AS MonthYear
FROM #Month AS m
CROSS JOIN #Year AS y
ORDER BY y.YearNum, m.MonthNum

Then in you selection criteria do something like this...

{TableName.FieldName} = {?MonthYear}

HTH,

Jason

[Former Member]

what i have done :

create a table dimension time with year_mounth_string

create a procedure who populates the table from 2008 until 2023

in crystal a LOV from that table

thanks for all your answer

[Former Member]

I have a solution for you.

it is a litle mesed up but works

1. create parameter type string

2. enter mask 00\.0000

3. enter formula field whit this

if right(left(totext(month(DATE FIELD)),2),1)="." then 
("0"+left(left(totext(month(DATE FIELD)),2),1))+"."+ left(totext(year(DATE FIELD)),1)+mid((totext(year(DATE FIELD))),3,3) else
(left(totext(month(DATE FIELD)),2)+"."+ left(totext(year(DATE FIELD)),1)+mid((totext(year(DATE FIELD))),3,3))

DATE FIELD is date field from table to which you compare parameter

4. in record selection type

{?date}={@date}

works just fine

[Former Member]

Solution could be..

Create a Date Parameter(?Date),

and wirte a formula(Month_Year) to convert this date to the mm-yyyy format

ToText({?Date},"MM-yyyy")

Note: MM-yyyy is case sensitive

Use this formula in mapping with the desired column or in selecting records.

Hope this helps.

Thanks,

[Former Member]

Hi,

I don't think you can set a mask for Date Parameter.

However, you can set a string parameter, which allow Edit mask, so that user can enter it in the format you want.