Record based selection with parameter 2

[ClowneUser]

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.

 

[IanWaterman]

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

 

[lbass]

(
{?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

 

[lbass]

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

 

[IanWaterman]

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

Ian

 

[ClowneUser]

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.