Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 33

Thread: Filter report with multiple parameters

  1. #1
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Filter report with multiple parameters

    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

  2. #2
    Member
    Real Name
    Robin Jones
    Join Date
    Sep 2009
    Location
    Wetherby West Yorkshire
    Posts
    92

    Default Re: Filter report with multiple parameters

    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.

  3. #3
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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.

  4. #4
    Member
    Real Name
    Robin Jones
    Join Date
    Sep 2009
    Location
    Wetherby West Yorkshire
    Posts
    92

    Default 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.

  5. #5
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default 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.

  6. #6
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default 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
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default 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.

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter report with multiple parameters

    Something like

    Code:
    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
    Last edited by Stan Mathews; 02-27-2015 at 09:44 AM.
    There can be only one.

  9. #9
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,628

    Default Re: Filter report with multiple parameters

    Quote Originally Posted by Medic1196 View Post
    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.

    Code:
    '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.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    Member
    Real Name
    Charlie Tye
    Join Date
    Feb 2015
    Posts
    6

    Default 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.

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter report with multiple parameters

    If you can use xbasic the general format is

    Code:
    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.
    Last edited by Stan Mathews; 02-27-2015 at 11:43 AM.
    There can be only one.

  12. #12
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: Filter report with multiple parameters

    Hi Travis,
    Quote Originally Posted by Medic1196 View Post
    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.
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  13. #13
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

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

  14. #14
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    Are stored procedures created locally or on the server?

  15. #15
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: Filter report with multiple parameters

    Quote Originally Posted by Medic1196 View Post
    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:
    Code:
    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.
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  16. #16
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default 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.

  17. #17
    Member J.R. Noe's Avatar
    Real Name
    J.R. Noe
    Join Date
    Feb 2011
    Location
    Brandenburg, KY.
    Posts
    398

    Default 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.

  18. #18
    Member
    Real Name
    Charlie Tye
    Join Date
    Feb 2015
    Posts
    6

    Default 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

  19. #19
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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.

  20. #20
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Filter report with multiple parameters

    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.
    There can be only one.

  21. #21
    Member
    Real Name
    Charlie Tye
    Join Date
    Feb 2015
    Posts
    6

    Default Re: Filter report with multiple parameters

    I tried it different ways but it does not work.

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

  22. #22
    Member
    Real Name
    Robin Jones
    Join Date
    Sep 2009
    Location
    Wetherby West Yorkshire
    Posts
    92

    Default Re: Filter report with multiple parameters

    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).

  23. #23
    Member
    Real Name
    Charlie Tye
    Join Date
    Feb 2015
    Posts
    6

    Default Re: Filter report with multiple parameters

    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.

  24. #24
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: Filter report with multiple parameters

    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!!!
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  25. #25
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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.

  26. #26
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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.

  27. #27
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: Filter report with multiple parameters

    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:
    Code:
    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
    Last edited by Progytech; 03-02-2015 at 07:31 PM. Reason: Added a Stored Procedure sample
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  28. #28
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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?

  29. #29
    Member
    Real Name
    Edhy Rijo
    Join Date
    Sep 2012
    Location
    Bronx NY
    Posts
    348

    Default Re: Filter report with multiple parameters

    You are welcome Travis.

    Quote Originally Posted by Medic1196 View Post
    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
    Last edited by Progytech; 03-02-2015 at 07:55 PM. Reason: Added new video link for how to create stored procedures in MySQL.
    Edhy Rijo
    Progytech
    (Computer Consultants)
    The makers of CardTracking.Net
    www.progytech.com

  30. #30
    Member
    Real Name
    Travis Parker
    Join Date
    Feb 2011
    Posts
    117

    Default Re: Filter report with multiple parameters

    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.

Similar Threads

  1. Showing report filter parameters on report
    By Ckirkpatrick in forum Alpha Five Version 11 - Desktop Applications
    Replies: 15
    Last Post: 12-04-2014, 02:15 PM
  2. How do I create a report based on the parameters of the filter date
    By r.akabane25 in forum Mobile & Browser Applications
    Replies: 18
    Last Post: 09-29-2014, 07:06 AM
  3. How do I show selected filter parameters for a report in the report
    By Robert Rough in forum Alpha Five Version 7
    Replies: 14
    Last Post: 10-03-2006, 03:36 PM
  4. Multiple Parameters when Building Grids
    By Rich Hartnett in forum Archived Wishlist
    Replies: 0
    Last Post: 06-01-2006, 01:36 PM
  5. Passing Filter Parameters to Report
    By Jim Stephenson in forum Alpha Five Version 5
    Replies: 2
    Last Post: 06-21-2004, 06:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •