Print Page | Close Window

paremeters for month and year

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17234
Printed Date: 06 May 2024 at 10:21am


Topic: paremeters for month and year
Posted By: jnnpvan
Subject: paremeters for month and year
Date Posted: 07 Aug 2012 at 10:49am
I am working on a report and need to view the report by month and year.
The idea is:
- when the user wants to view a report by a month, just select the month from a parameter.
- when the user wants to view a report by a year, just select the year from a parameter.
How can I set this parameter in Crystal Report 2011 ?


Replies:
Posted By: DBlank
Date Posted: 07 Aug 2012 at 10:59am
1 option...
you can add 2 params
1 called 'Type' as string for that has 'month' and 'year' as the 2 options
the other as a date field
do you mean they enter a date and you want
 
({?type}='month' and year({table.date})=year({?date}) and month({table.date})=month({?date}))
or
({?type}='year' and year({table.date})=year({?date}))

Posted By: jnnpvan
Date Posted: 07 Aug 2012 at 12:18pm
Thanks,
I tried it, but when I run the report, it didn't link the correct parameter.
For example:
I type month is 1
year is 2011
but the data didn't run in January 2011.
What I need to set more in Crystal Report?

Posted By: DBlank
Date Posted: 08 Aug 2012 at 4:11am

I was recommending you create 1 paramter that is a string with static LOV of the values

Month
Year
 
the next parameter would be of a date type and allow the user to enter any date. you then use either the year and month or only the year fromt hat entered value.
 
You can go the direction of entrering month numbers and year numbers but I think it makes it more complicated and less clean. One down side to the date entry is users may be confused to why they are pick ing a 1 day(date) when they wanted a whole month or a whole year.
 
 

Posted By: DBlank
Date Posted: 08 Aug 2012 at 4:26am

Another approach closer to what I believe you had in mind would be

 
Create one parameter called 'Month'
set it to numeric
set it as a static LOV
set the values as 0 to 12
set the descriptions as the corresponding month names but make 0='All months'
 
Create another parameter called "Year"
set it as numeric.
set the minimum and maximum values to the years you want users to be able to enter. This will help avoid users leaving off the lead 2 characters or entering typos.
 
change the select expert to
 
({?month}=0 or {?month}=month({table.datefield})
and
{?Year}=year({table.datefield})

Posted By: jnnpvan
Date Posted: 08 Aug 2012 at 4:48am
Hi,
 
Thanks for your help.
 
I used the second hint with created 2 seperate parameters "month" and "year"
I got it, but one thing, when I set it to numeric, the number to display on the Range view report has the format like this:
 
This is my formula:
'Report view in  ' + totext({?month}) + ' / ' + to text({?year})
 
and the otuput is:
Report view in 1.00 / 2011.00 
 
It mean Report view in January 2011,
 
How can I get a nice format of the month and yeat like this
 
Report view in 1 / 2011 

Posted By: DBlank
Date Posted: 08 Aug 2012 at 4:51am

(if {?month}=0 then 'All of ' else monthname({?month})) + totext({?Year},0,'')

 
 
Edit: oops ... I see you wanted the number not the month name.
Not sure what you want for no month selection (e.g. 0) ...
 
(if {?month}<>0 then totext({?month},0,'')+ ' / ' else '') + totext({?Year},0,'')

Posted By: jnnpvan
Date Posted: 08 Aug 2012 at 5:07am
Thank you  DBlank.
 
One thing I want to do like to view the report with a quarter, 4 months, 6 months.
How can I set the parameter together with the parameter every month and one year that I just done ?

Posted By: DBlank
Date Posted: 08 Aug 2012 at 5:27am
you can create another param for the quarter selection by using datepart()
 
(?quarter)=0 or ?quarter=datepart('q',table.datefield)

Posted By: jnnpvan
Date Posted: 08 Aug 2012 at 6:56am

But I don't know how to combine with the quarter condition.

In Select Expert, I did:
( ( {?Month} = 0 )  or ( {?Month} = month({Command.createddatetime}) ) ) and
( ({?Year} = 0 ) or ({?Year} = year({Command.createddatetime})) 
 
 
and in formula I did:
'Working Hours In ' + (if {?month}= 0 then 'year'
                          else if {?Month} <> 0 then monthname({?month})
                          else if {?Quarter} = 0 then 'year'
                          else if {?Quarter} <> 0 then 'Quarter ' + totext({?Quarter}) + ' of '
                        ) + ' ' + totext({?Year},0,'')
 
But how can run when select Quarter, then the data only run with Quarter condition, not run with month and year condition?

Posted By: DBlank
Date Posted: 08 Aug 2012 at 7:21am

I assume you will always need the year param.

If you do not use it and a user selects say quarter 1, it would select all quarter 1's for all the years in your original data set.
You have the same issue with month param and that is why I did not recommend using a 0 in the year param.
Your original post wanted either a full selected year or a full month of 1 year so I assume the quarter is also for only one year.
 
Maybe you are limiting your data set to only one year in soem other way?
 
Under the condition that you have multiple years of data to deal with your select statment should be something like:
 
({?month}=0 or {?month}=month({table.datefield}))
and
({?Quarter}=0 or {?quarter}=datepart('q',{table.datefield}))
and
{?Year}=year({table.datefield})
 
If a user select both a month and a quarter and the month is in the selected qurter only the month records will be shown.
 IF they pick month that is outside the q then no records would be returned.
 
your display string would be
 
 
'Working Hours In ' +
(
if {?month}= 0 then and {?quarter}=0 then 'year' else
if {?Month} <> 0 then monthname({?month}) else
if {?Quarter} <> 0 then 'Quarter ' + totext({?Quarter},0,'') + ' of '
) + ' ' + totext({?Year},0,'')
 
 
 

Posted By: jnnpvan
Date Posted: 08 Aug 2012 at 8:57am
Yeah, I got perfect output.
Thank you very much.
 


Print Page | Close Window