Filter report with multiple parameters

[Charlie Tye]

Re: Filter report with multiple parameters

alpha will not perform as I had hoped. I have spent the last few days trying to run reports and filter them by drop down controls on a ux, and I cant find any way to do it. if one of the fields is blank the report will not show any records. I have gone through so many suggestions on here and I cannot even find how to use the xbasic code to even open a report. all I find is things like SaveAs, but not to just open it. rapid development? not quite. seems like I am not the only one looking to do this, and they have more experience than me. so discouraging

[J.R. Noe]

Re: Filter report with multiple parameters

Nothing I have tried works. Hard to believe there is no way to open a report utilizing xbasic with parameters from fields on a UX, and I have been trying for a very long time. I finally gave up. If you figure it out, please let me know.

[Medic1196]

Re: Filter report with multiple parameters

Ummmm...I feel like a monkey that just got handed a smart phone. lol
I will have to google and try to absorb this information.

[Progytech]

Re: Filter report with multiple parameters

Are stored procedures created locally or on the server?

They are created in the server. Look at your MySQL help file on google.

I am not familiar with MySQL but here is a pseudo SQL code you can use in your Stored Procedure:

DECLARE @PersonnelPK INT = 10
DECLARE @ClassName VARCHAR(20) = ''

SELECT
	s.*
FROM
	Students s
WHERE
	(s.PersonnelPK = @PersonnelPK [B][COLOR="#FF0000"]OR @PersonnelPK = 0[/COLOR][/B]) AND
	(s.ClassName = @ClassName [B][COLOR="#FF0000"]OR @ClassName = ''[/COLOR][/B])

The key for this SQL statement, is to test for the value of the parameters (or arguments in AA) for a default value to ignore them, I use 0 or -1 for integers or blank space '' for varchars.

Hope this help.

[Medic1196]

Re: Filter report with multiple parameters

Are stored procedures created locally or on the server?

[Medic1196]

Re: Filter report with multiple parameters

I use MySQL and the reports utilize views. Guess I need to learn about stored procedures.

[Progytech]

Re: Filter report with multiple parameters

Hi Travis,

I have that now Robin, but if one of the filters is not set the report does not show any records. If one of the parameters is blank, I want the report to run utilizing the other parameters.

What database are you using for your report?

The way I handle this is as follow:

1. I use MS SQL database, so I create a Stored Procedure with the SQL statatement I need to grab all my records. I may even use a view if I want, but AA handle Stored Procedures as the source data for reports very well.
2. Define the data for the report using this Stored Procedure and create a report argument for each parameter defined in the stored procedure.
3. Run my report from a grid or UX dialog and pass the expected parameters values as arguments.

The key to all this is building your Stored Procedure properly to handle empty or NULL values.

[Stan Mathews]

Re: Filter report with multiple parameters

If you can use xbasic the general format is

query.filter = ""
query.order = ""



:Report.SaveAs("reportnameinquotes","PDF",query.filter,query.order,"",.T.)

So you would use the procedure outlined above to create your own query.filter variable.

Probably sloppy thinking here. You won't know if the user enters an argument until the report is called so the advice is no good.

I think you'll have to gather the arguments via some data entry method first and then build the query.filter.

[Charlie Tye]

Re: Filter report with multiple parameters

I am opening a pdf report using parameters too but not all will have a parameter value. where do I put your filter?
my first try at this was opening a pdf report using a button that called an action javascript. does the filter go where it says 'sql filter'
my first try at this was this,
comid = :cid AND CDate >= :argStartDate AND CDate <= :argEndDate AND PID=:argPersonnel
OR comid = :cid AND CDate >= :argStartDate AND CDate <= :argEndDate AND :argPersonnel=""

which will work but each time I add a parameter I have to add another OR and then cover all the different variations.
there has got to be an easier way. if I have a dozen or so parameters, then my way would be ridiculous to use.

[Al Buchholz]

Re: Filter report with multiple parameters

Well that was like playing "Where's Waldo".....and I still never found him. I spent a lot of time searching through all that Al, but I could not find how to show all records for parameter fields which have no value.

Travis

The concept is that you build a filter string that contains the parameters that have values and ignore the ones that don't.

'so if 


p1 = "a"
p2 = " "
p3 = "x"


'the filter would be 

'f1 = "a" .and. f3 = "x"

'to accomplish this the code looks something like this:

myfilter = ""

if p1 > "" then
myfilter = myfilter + "f1 = "+s_quote(p1) + " "
end if

if p2 > "" then
	if myfilter > "" then
	    myfilter = myfilter + ".and. "
	end if
myfilter = myfilter + " f2 = "+s_quote(p2) + " "
end if

if p3 > "" then
	if myfilter > "" then
	    myfilter = myfilter + ".and. "
	end if
myfilter = myfilter + "  f3 = "+s_quote(p3) + " "
end if

ui_msg_box("My filter " , myfilter)

Then you can use this filter to build a query.

The report record selection then should be set to use the currently selected records.

What I indicated previously will help in the filter building process.

Or as Stan has shown there are many ways to build a string that contains the filter and make the filter as efficient as possible.

[Stan Mathews]

Re: Filter report with multiple parameters

Something like

filt_string = ""
filt_string = filt_string+if(start_date>{} .and. end_date > {} .and. (end_date>start_date),"between(date_field,"+s_quote(start_date)+","+s_quote(end_date)+")","")
filt_string = filt_string+if(alltrim(personnel)>""," .and. person_field = "+quote(personnel),"")
filt_string = filt_string+if(alltrim(course)>""," .and. course_field = "+quote(course),"")
'etcetera
filt_string = ltrim(filt_string,".and. ")'removes leading .and. if the dates are not valid

[Medic1196]

Re: Filter report with multiple parameters

Well that was like playing "Where's Waldo".....and I still never found him. I spent a lot of time searching through all that Al, but I could not find how to show all records for parameter fields which have no value.

[Al Buchholz]

Re: Filter report with multiple parameters

You can build a filter as a string. Then apply the string as the filter in the call to a report.

How are your skills building strings with xbasic?

http://wiki.alphasoftware.com/~alpha...nd+Expressions

Here are some programming examples using xbasic..

http://www.alphasoftware.com/alphafo...amming-Puzzles

[Medic1196]

Re: Filter report with multiple parameters

That is the purpose of my question, to save hours of experimenting. I am sure someone has done this in the past, and can point me in the right direction.

[Foodtester]

Re: Filter report with multiple parameters

If a dropdown filter is not set then you presumably want to see all the records in the report. So I think you will need to set a default value for each argument such that the default is a wildcard eg : "*". Not sure of the syntax so you may need to experiment a bit.