Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: CrystalReports
Hi Experts,
 
I am newbie in crystal report world Smile | :)
 
I need some help regarding creating record selection formulas:
 
1. How to filter records on the base of date [dd/mm/yyyy] range? The report will get two DATE parameters "fromDate" & "toDate". Can you please tell how to make this formula?
 
2. How to filter records on the base of time [mm:hh] range? The report will get two DATE parameters "fromTime" & "toTime". Can you please tell how to make this formula?
 
3. I have one parameter "CompanyName" which if user provides then the data should be selected for that specific company otherwise all companies data should appear. How to create this formula?
 
4. I want to create summary field which will be count of specific value lets say "XYZ" in some particular column of report. How to create such formula?
 
Thankx
BR
SC
Posted 8-Jan-11 10:07am
Comments
thatraja at 9-Jan-11 4:59am
   
Check my updated answer
thatraja at 9-Jan-11 6:13am
   
I have appended some more things in answer, so check my answer again.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

OP wrote:
1. How to filter records on the base of date [dd/mm/yyyy] range? The report will get two DATE parameters "fromDate" & "toDate". Can you please tell how to make this formula?

Try this C# Crystal Reports - Date to Date [^]
OP wrote:
2. How to filter records on the base of time [mm:hh] range? The report will get two DATE parameters "fromTime" & "toTime". Can you please tell how to make this formula?

This will help you
How to Correct this selection Formula to show time in AM or PM[^]
This one is great
Creating Advanced Record Selection Formulas[^](It contains all type of things)
OP wrote:
3. I have one parameter "CompanyName" which if user provides then the data should be selected for that specific company otherwise all companies data should appear. How to create this formula?

Try this Creating a Report with a Selection Formula[^]
Send the selected company value to the selection formula, that's all.
OP wrote:
4. I want to create summary field which will be count of specific value lets say "XYZ" in some particular column of report. How to create such formula?

Here you go Crystal Reports Summary Field[^]
 
[Edit]
What error you are getting?
I think you need to put block() for If block.
{EMPLOYEE.BIRTHDATE} >= {?FromDate} and {EMPLOYEE.BIRTHDATE} <= {?ToDate} and 
//(
// If (StrCmp({?DeptName},"") = -1) Then 
// (  
   {EMPLOYEE.WORKDEPT} = {?DeptName} //Append this by condition in front-end.
// )
//)

Then comment the if block like above, Actually in front end(java) check the condition if DeptName has selected or not. If yes then append the DeptName thing in selection formula else stop with fromdate & todate things.
If DeptName selected then
{EMPLOYEE.BIRTHDATE} >= {?FromDate} and {EMPLOYEE.BIRTHDATE} <= {?ToDate} and
   {EMPLOYEE.WORKDEPT} = {?DeptName}
else
{EMPLOYEE.BIRTHDATE} >= {?FromDate} and {EMPLOYEE.BIRTHDATE} <= {?ToDate} 
Send this selection formula to report at runtime in java.
Let us know.

Pending query

 
Need help in Crystal Reports. (how to sum by conditions)[^]
 
[/Edit]
  Permalink  
v4
Comments
Sandeep Mewara at 9-Jan-11 2:55am
   
Good answer. 5!
SmoothCriminel at 9-Jan-11 4:28am
   
Hi thatraja, thanks for response. Although I am working in java but even then its helpful. Thanks again.
It will be great if you can tell me how the formula will look, lets say I have fields EMPLOYEE.BIRTHDATE (type = date) & EMPLOYEE.WORKDEPT (string). Report has three parameters 1. FromDate (Date), 2. ToDate (Date) and 3. DeptName (String).
Now user will select dates and it can either enter or leave the DeptName field empty. If its empty then all departments should be selected otherwise the one it entered.
I have tried creating this formula:
{EMPLOYEE.BIRTHDATE} >= {?FromDate} and
{EMPLOYEE.BIRTHDATE} <= {?ToDate} and
If (StrCmp({?DeptName},"") = -1) Then
(
// Add statement here
{EMPLOYEE.WORKDEPT} = {?DeptName}
)
I know this is not correct... Can you please create it?
BR
SC
SmoothCriminel at 9-Jan-11 6:02am
   
The issue is with last "and" I believe. What ever input I give for the "DeptName" there is no where clause when I see the generated SQL.
SmoothCriminel at 9-Jan-11 6:48am
   
Aha... I got it now. So, like this I don't even need to create any parameters. And on the java side I will create the selection formula and just send it to report. Will that be good option? Or it wont work. Thanks a lot for help.
thatraja at 9-Jan-11 7:01am
   
Yeah, that's it. It will surely work. Like I mentioned in my answer try the selection formula based on condition. Over. If you still facing issue please let us know.
Dalek Dave at 9-Jan-11 11:48am
   
Good Answer.
SmoothCriminel at 9-Jan-11 7:50am
   
Great thankx... I will try and let you know. Now the only pending thing is last question. I know how to create summary field but the thing is how to have distinct count of specific value in a field? BR. SC.
thatraja at 9-Jan-11 8:00am
   
Check my updated answer again follow the link dude, that's all

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 BillWoodruff 330
1 Mathew Soji 309
2 Maciej Los 295
3 Afzaal Ahmad Zeeshan 257
4 DamithSL 225
0 OriginalGriff 6,369
1 Sergey Alexandrovich Kryukov 5,973
2 DamithSL 5,183
3 Manas Bhardwaj 4,673
4 Maciej Los 4,045


Advertise | Privacy | Mobile
Web02 | 2.8.1411019.1 | Last Updated 9 Jan 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100