13,095,658 members (52,053 online)
Rate this:
See more:
Hi Experts,

I am newbie in crystal report world :)

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 9:07am
thatraja 9-Jan-11 4:59am

thatraja 9-Jan-11 6:13am

I have appended some more things in answer, so check my answer again.

Rate this:

## 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?

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[^]

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]
v4
Sandeep Mewara 9-Jan-11 2:55am

SmoothCriminel 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
(
{EMPLOYEE.WORKDEPT} = {?DeptName}
)
I know this is not correct... Can you please create it?
BR
SC
SmoothCriminel 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 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 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.
SmoothCriminel 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 9-Jan-11 8:00am

Dalek Dave 9-Jan-11 11:48am

Top Experts
Last 24hrsThis month
 OriginalGriff 210 Karthik Bangalore 65 Jochen Arndt 60 Mohibur Rashid 59 RickZeeland 55
 OriginalGriff 4,131 Graeme_Grant 2,232 ProgramFOX 2,057 Jochen Arndt 1,735 ppolymorphe 1,735