Alpha DevCon 2018
Results 1 to 8 of 8

Thread: Xbasic filtering for SQL Datasource report

  1. #1
    Member
    Real Name
    Norm Kelson
    Join Date
    Dec 2010
    Posts
    29

    Default Xbasic filtering for SQL Datasource report

    I want to pass a filter to a report based on a named datasource to select on record using xbasic to select the report from a form. The :report command with the filter doesn't work. I tried the USING ARGUMENTS but don't know how to pass the :variable to the report.


    Of there is a way in xbasic to to modify the named source that would work, otherwise how to pass the argument to the report command.

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

    Default Re: Xbasic filtering for SQL Datasource report

    I don't know much about the process but the documentation suggests you pass the argument(s) in the .print() command.

    DIM myargs as SQL::arguments
    myargs.add("whatcity","London")
    report.print("report1", "", "", .f., .f., myargs)

    Printing based on the current record visible on a form where there is a field for the city and the objectname of that field is city_name (for instance)

    myargs.add("whatcity",city_name.value)


    would seem to be proper.
    There can be only one.

  3. #3
    Member
    Real Name
    Norm Kelson
    Join Date
    Dec 2010
    Posts
    29

    Default Re: Xbasic filtering for SQL Datasource report

    I tried that and it didn't filter. Here is the SQL datasource:

    SELECT contact.contactid, contact.firstname, contact.lastname, contact.follow_up_date, contact.telephone, contact.extension, contact.callfreq, contact.email, contact.title, contact.city, contact.state, company.companyname, activity.activitydate, activity.activityresult, activity.discussion
    FROM (contact contact
    INNER JOIN activity activity
    ON contact.contactid = activity.activityid
    INNER JOIN company company
    ON contact.companyid = company.companyid )

    The report request:
    DIM myargs as SQL::arguments
    myargs.add("contactid",26839)

    report.preview("SQLContactDetail","","",.f.,.f.,myargs)

    I also tried myarges.add("contact.contactid",26839)

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

    Default Re: Xbasic filtering for SQL Datasource report

    Again professing my ignorance but if you are using something like

    Code:
    qry_string = <<@a@
    SELECT contact.contactid, contact.firstname, contact.lastname, contact.follow_up_date, contact.telephone, contact.extension, contact.callfreq, contact.email, contact.title, contact.city, contact.state, company.companyname, activity.activitydate, activity.activityresult, activity.discussion
    FROM (contact contact
    INNER JOIN activity activity
    ON contact.contactid = activity.activityid
    INNER JOIN company company
    ON contact.companyid = company.companyid )
    @a@
    You should be able to use strtran() to supply a Where value to the string.

    Code:
    qry_string = <<@a@
    SELECT contact.contactid, contact.firstname, contact.lastname, contact.follow_up_date, contact.telephone, contact.extension, contact.callfreq, contact.email, contact.title, contact.city, contact.state, company.companyname, activity.activitydate, activity.activityresult, activity.discussion
    FROM (contact contact
    INNER JOIN activity activity
    ON contact.contactid = activity.activityid
    INNER JOIN company company
    ON contact.companyid = company.companyid )
    WHERE contact.contactid = ~~~putvaluehere~~~
    @a@
    
    qry_string = strtran(qry_string,"~~~putvaluehere~~~",alltrim(str(26839)))
    
    ? qry_string
    = SELECT contact.contactid, contact.firstname, contact.lastname, contact.follow_up_date, contact.telephone, contact.extension, contact.callfreq, contact.email, contact.title, contact.city, contact.state, company.companyname, activity.activitydate, activity.activityresult, activity.discussion
    FROM (contact contact
    INNER JOIN activity activity
    ON contact.contactid = activity.activityid
    INNER JOIN company company
    ON contact.companyid = company.companyid )
    WHERE contact.contactid = 26839
    There can be only one.

  5. #5
    Member
    Real Name
    Norm Kelson
    Join Date
    Dec 2010
    Posts
    29

    Default Re: Xbasic filtering for SQL Datasource report

    That didn't work. I appreciate your response.

    If there are any sql gurus out there, i'd appreciate your assistance.

  6. #6
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,044

    Default Re: Xbasic filtering for SQL Datasource report

    I don't know that I qualify as a SQL Guru, but

  7. #7
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,005

    Default Re: Xbasic filtering for SQL Datasource report

    Import the data to a temp file. Print from there once you are satisfied you have the appropriate content.

    Hey Greg, bits missing?
    Ted Giles
    Example Consulting - UK
    .

    https://exampleltd.example-software.com/
    See our site for Alpha Support, Conversion and Upgrade.

  8. #8
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,044

    Default Re: Xbasic filtering for SQL Datasource report

    Horrible things happen thanks to multi-tasking.

    Assuming the following for arguments:
    DIM myargs as SQL::arguments
    myargs.add("contactid",26839)

    Code:
    qry_string = <<%txt%
    SELECT C.contactid, C.firstname, C.lastname, C.follow_up_date, C.telephone, C.extension, C.callfreq, C.email, C.title, C.city, C.state, M.companyname, A.activitydate, A.activityresult, A.discussion
    FROM contact C
    INNER JOIN activity A ON C.contactid = A.activityid
    INNER JOIN company M ON C.companyid = M.companyid 
    WHERE C.contactid = :contactid
    %txt%
    should work.
    If this doesn't work, maybe https://www.alphasoftware.com/docume...ed%20arguments
    will guide you to the best answer.

    P.S.
    I changed the aliases to one letter to make things easier for me to read.

Similar Threads

  1. Report - Change DataSource
    By soonerjj in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 12-27-2017, 12:15 PM
  2. Unable to Change DataSource for Report
    By soonerjj in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 08-24-2017, 06:27 PM
  3. Filtering a grid based on a AlphaDAO datasource with Action buttons
    By CampnJim in forum Mobile & Browser Applications
    Replies: 3
    Last Post: 01-07-2014, 10:04 AM
  4. Dates in SQL Datasource Report
    By jacsolutions in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 08-02-2010, 04:22 PM
  5. (Report) SQL Datasource to Local Table
    By Gman in forum Alpha Five Version 8
    Replies: 3
    Last Post: 08-24-2007, 01:22 PM

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
  •