INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Record based selection with parameter

Record based selection with parameter

(OP)
Hi,

I'm looking for a solution to try to save me from writing multiple reports, what i need is to be able to choose which data is included in the report depending on the report type ( if that makes sense )

For example..

report 1 would need to include part numbers beginning with M and have fault codes 1, 2 and 3

report 2 would need to include part numbers beginning with A and have fault codes 4, 5 and 6

and so on...

what I've tried to do is create a parameter and added static entries ie. report 1, report 2 etc..

so when I run the report I can choose report 1 from the parameter and it will give me Part M123 with the correct fault codes..

but I'm struggling on the correct formula to get this working.. or even if this is the best way to handle this.

Any ideas?

Thanks,

Mick.

RE: Record based selection with parameter

Try (replace with your parameter and field names)

If {?ReportTypeParam} = 'Report1' then
(Partnumbers startswith 'M*' and faultcodes in (1,2,3))
Else
If {?ReportTypeParam} = 'Report2' then
(Partnumbers startswith 'A*' and faultcodes in (4,5,6))
Else
....
These filters might not get passed to database and thus all filtering will be done in report and therefore might be slow to run.
Ian

RE: Record based selection with parameter

(
{?Which Report}='Report1' and
{table.partno}[1]='M' and
{table.faultcodes} in 1 to 3
) or
(
{?Which Report}='Report2' and
{table.partno}[1]='A' and
{table.faultcodes} in 4 to 6
)
//etc.

-LB

RE: Record based selection with parameter

Ian, didn't see your post.

ClowneUser, you could use a SQL expression to replace {table.partno}[1] which would allow the selection formula to pass to the SQL query. The syntax depends upon your database, but might look like:

Substring("table"."partno",1,1)

Or maybe:

Substr("table"."partno",1,1)

Look in the functions list in the SQL expression editor to see the appropriate syntax for your database/connectivity and then add your field by double clicking on it in the field list in order to get the correct punctuation. Then use the new {%partno} in my earlier formula.

-LB

RE: Record based selection with parameter

LB
Probably typing at same time.
I think yours is probably better and more likely to be included in Report SQL.

Ian

RE: Record based selection with parameter

(OP)

Thanks LB and Ian, that worked great...

I was going down the route of an if then else... so if report_type = 'normal', then part_no like 'M%' else if.... etc...

so was more like saying if its this report type then only show Parts beginning with M.... obviously this isn't how it works.... it was a bad day yesterday brain had died....

Thanks again guys, LB nice to see you helping out still after all these years.

Mick.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close