Filter report with multiple parameters

[Medic1196]

I want to be able to filter a report by a date range and possibly one or more other parameter fields. I want the filters to be optional, and if left blank, the report will show all records. What do I need to do to accomplish this?

1.jpg

[Foodtester]

In the Action JavaScript to open a report you have the option of defining arguments and building a report filter. You could set the argument values to those of your criteria, and build the report filter based on the arguments.

[Medic1196]

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.

[Foodtester]

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.

[Medic1196]

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.

[Al Buchholz]

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]

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.

[Stan Mathews]

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

[Al Buchholz]

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.

[Charlie Tye]

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.

[Stan Mathews]

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.

[Progytech]

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.

[Medic1196]

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

[Medic1196]

Are stored procedures created locally or on the server?

[Progytech]

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 OR @PersonnelPK = 0) AND
	(s.ClassName = @ClassName OR @ClassName = '')

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]

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.

[J.R. Noe]

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.

[Charlie Tye]

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

[Medic1196]

Thank you Al. Once I build the string, as you demonstrated, how do I pass that to the report and open it? I don't want to save it in a session folder, I just want to open it.

[Stan Mathews]

the xbasic code to even open a report

I don't do mobile/web so this may be completely wrong but the xbasic to open a report is

report.preview()

The only required parameter is the report name.

[Charlie Tye]

I tried it different ways but it does not work.

report.preview("boltorders.a5rpt")
report.preview("boltorders")
report.preview(boltorders)

[Foodtester]

Alternative Approach. Here is an approach I have used used.

In a UX create a list component based on the table or query you want to search(SQL), then add all the search boxes you want (Watch videos UXL-V12--22 and 23). Set up filters for the list.

When you have got the list filtering how you want, add buttons or hyperlinks to open your various reports. Use action JavaScript with your buttons to open reports (Action is- Open Report, Label or Letter), the dialog to to enter your report details allows you to filter the report based upon currentListFilter("ListName")

This should open the report/s based on filter criteria.

Of course you may not want to have the list cluttering your page- so put it in a container and hide it on the client side. (I have my list visible, because I want my customers to have some indication of what will be included in the report/s and also to have access to reports based on individual rows).

I would add that the reports have to be Layout Reports (I think).

I had to do a load of experimenting and the list filter I have built is very complex, but I think you could take this approach and there is minimal self coding involved.

I would think that a hidden grid could also used (currentGdidFilter).

[Charlie Tye]

that is not an acceptable solution robin. I would have to have a different ux for this dataset and reports, and then another ux for others and so on. I will have very many reports so it would take more than a dozen ux's and that would make it hard for users to find the report they need.

[Progytech]

Hi Charlie,
The problem is not AA report but probably the lack of knowledge on using stored procedure in your case. As I explained you before, I do this all the time, for your report, you have to build the same arguments or parameters you will use in your UX dialog and using a Stored Procedure is a straight forward process without building strings.

Don't be intimidated by the use of Stored Procedures (SP), it is basically an SQL Select you build to grab your data the way you want it, passing blank or null parameter values to test your WHERE condition as I posted, then once that is working as expected, you can build your report using the SP as the source, creating the parameters required by the SP and then build your UX, pass the values of your UX controls to the report arguments and you are done.

Try it!!!

[Medic1196]

Found a way that works great for us.....so far. We changed the way the filter is applied to the action javascript, and we added an additional choice to each and every drop down field "<ALL>". Then we used this in the SQL Filter:

companyid = :cid AND CdDate >= :argDate1 AND ClassDate <= :argDate2 AND (CsName = :argCsName OR :argCsName = "<ALL>") AND (PersID = :argPers OR :argPers = "<ALL>") AND (ClName = :argClName OR :argClName = "<ALL>") AND (CoType_text = :argCoType OR :argCoType = "<ALL>") AND (PK9_text = :argPK9 OR :argPK9 = "<ALL>") AND (ClTopic_text = :argClTopic OR :argClTopic = "<ALL>") AND (LSkV = :argLSkV OR :argLSkV = "<ALL>") AND (IsReq = :argIsReq OR :argIsReq = "<ALL>")

The arguments must be defined first for each of the fields and then the argument value set to the correct field.

It is a multi-company application, so we had to include the company id, which uses a session variable, and all the rest of the filter is acquired from the fields on the UX. Selecting "<ALL>" will naturally show all the records for that field, so you can use any number of them you need and not if you don't need them.

Utilizing (FilterValue = :argValue OR :argValue = "<ALL>") was the key and allowed us to use this without it being 6 miles long.
So far this seems to be working like we want but we are still testing it. If anyone can see problems using this, please let me know.

[Medic1196]

I forgot to include one quirk we found. Even though each of the fields has the default value set to "<ALL>", it throws weird errors unless each of the fields used in the report filter has its value set again. We have a button called "Reset Report Filter" which sets the values of each field to "<ALL>". And we also run that same code when the UX opens. It is really weird that the default value is ignored, but at least its an easy fix.

[Progytech]

Hi Travis,
Glad to here you are in the right path, I know how frustrating this kind of issues can be. AA is a powerful product but a good solid knowledge of your database is always a big PLUS.
This logic I suggested, will work for you even using it in the AA Query Genie, but now that you mentioned that your application is a Multi-Company one using your Company ID to filter out the records, you have to make sure that data optimization is used, so I encourage you to study the Stored Procedure approach this way your MySQL server will take advantage o it by pre-compiling the SP and running faster. Always use the combination of Views and Stored Procedure and your AA application will perform faster and more reliable.

Here is one of the Stored Procedure I use in one report:

CREATE PROCEDURE [dbo].[spGetRosterPaymentsReport]
	-- Add the parameters for the stored procedure here
	@OSISID VARCHAR(9) = '235390358', -- AGRAMONTE , JHAN
	@StartDate DATE = '07/01/2014',
	@EndDate   DATE = '06/26/2015'

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Select all payments for this roster period
	SELECT
		si.PK_SEITPaymentsImported,
		si.OSISID,
		si.StudentName,
		si.PaymentStartDate,
		si.PaymentEndDate,
		si.PaymentDate,
		si.PaymentUnits,
		si.PaymentAmount,
		si.VoucherNumber
	FROM
		SEITPaymentsImported si
	WHERE
		(si.OSISID = @OSISID OR @OSISID = '')
		AND (
			(si.PaymentStartDate BETWEEN @StartDate AND @EndDate) OR 
			(si.PaymentEndDate BETWEEN @StartDate AND @EndDate)
			)
	ORDER BY
		si.StudentName, si.OSISID, si.PaymentStartDate, si.PaymentEndDate, si.PaymentDate
END

AA Report DataSource using Stored Procedure.png
UX used for generating the report.png

[Medic1196]

Thank you Edhy, I think I can understand that much better than before. I will give it a shot and see what I can do once I get caught up. I am sure I will have more questions for you as I go along. Is a stored procedure along the same lines as a pass-through query?

[Progytech]

You are welcome Travis.

Is a stored procedure along the same lines as a pass-through query?

Yes and no. A Store Procedure is basically a SQL Query Statement you create in your database which you can then execute to return the cursor.
I know MySQL has a tool that will allow you to manage your database, look at the Store Procedure tree and right click to create a new one. you would actually start by creating your query and test it until you get the results you want, then you copy that SQL statement in your Stored Procedure so you can call it from AA report or Grids.
Look at these links from Google:
https://www.youtube.com/watch?v=yf7hZvUD1Lk
https://www.google.com/search?q=mysq...+example+video

This is a much better video for you:
https://www.youtube.com/watch?v=jRNZHWDEgIg

[Medic1196]

Let me throw something out there that has me confused on this. The SP is the reports datasourse? And the report can be opened by the usual action javascript?
I see the code above but nothing to actually open the report. This is what is a little confusing about everything we have tried the last few days. Like Charlie stated, I couldn't figure out how to open the report to see if the filter was working.
I will look at the links you have provided and work on figuring it all out.
Thank you again Edhy.