Data filtering

[swetham]

I have created a reprot using command (SQL query) which retrieves all the employee details.Now i need to filter the data based on the conditions,

1) Employees joined in the Week numbers will be selected (example: 13,15,19) and the data should be filtered.
2) Employees joined in the selected months. (Exacmple : 2,5 months) and the data should be filtered.

Can anyone help me on how to filter the data? the months & weeks will be selected in the user interface.

 

[Madawc]

Depending on your version of Crystal, it should be possible to add parameters within the command and then add SQL code to test.

Alternatively you can apply selection rules to the output, just as if it were a database table. This is wastelful since the SQL will collect data you then discard, but would be simpler if you have trouble with SQL.

It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

When using a command, you should always build parameters into the command, otherwise the filtering occurs locally. However, unless you have CR 2008, you will not be able to use a parameter that accepts multiple values.

-LB

 

[swetham]

I am using crystal reports 2008 SP3. My sql query is ,

Select e.emp_id,e.first_name,e.last_name,d.dept_name from employee_details e, department_Details d where e.dept_id=d.dept_id

User will be selecting the week numbers or month numbers from the interface and i will be passing the week or month numbers as the parameters. How to use those multiple week or month numbers in crystal reprots?

 

[Madawc]

In Crystal, you can match using values like Year({your.date) or Month({your.date). You should be able to do the same in SQL, which is more efficient.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

What you can use in a command depends upon your database. Check in the SQL expression area of a new report (it won't show if you are using a command) just to see if there is a week function and/or a month function for your database/datasource. Then use those in the where clause of your query, along with parameters that you have created within the command (on the right side of the screen).

-LB

 

[swetham]

THere are week() and month() functions in MYSQL. Now it is working fine. Thank a lot