Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Why do I do it to myself?

edited February 2017 in End User
Hi Team,
Another old report. Updating an old app.

The report is no longer defined by fields, search, filter, etc, it has converted to SQL.
It works OK except that the dates for the report are hard coded into the SQL.

"select Invoice.job_type as [Job Type], count(Invoice.MSN) as [Total Units], count(distinct Invoice.MSN) as [Unique
Units], count(Invoice.msn)-count(distinct invoice.msn) as [Repeats]
from Invoice Invoice
where (trim(Invoice.msn) <> '') and (Invoice.Date_in >= '01/01/2006') and (Invoice.date_in <= '01/04/2016')
group by Job_Type;"

I need now to be able to feed the SQL Start & End Dates for Date_in.

Is possible? If so, how please?

Regards and thanks for your support,
Ian

Comments

  • edited February 2017
    Hi Ian,

    Take a look at the following article on the fundamentals of using Report
    Parameters.

    http://www.digital-metaphors.com/rbWiki/End-User/Fundamentals/Report_Parameter_Fundamentals

    It is possible to use report parameters in your SQL statements so
    instead of the hard coded dates, you will replace them with the colon
    syntax followed by the report param name:

    where (trim(Invoice.msn) <> '') and (Invoice.Date_in >= :StartDate) and
    (Invoice.date_in <= :EndDate)

    StartDate and EndDate would be report parameters you create and populate
    either in code or via the AutoSearch dialog.

    --
    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited February 2017
    Hi Nice,
    Thank you for your reply.
    I will have a look at the reference and have a go.
    Will advise.

    Regards,
    Ian
  • edited February 2017
    Hi Nico,

    Parameters are not something I have ever played with.

    I have added the Parameters to the Report and the SQL although I am unsure what goes into each of the Parameter
    properties under User Interface.

    I added the following to the calling code but I'm not confident this is the right way to populate Parameters although
    intuitively it seems right.

    [code}
    ppR1.AutoSearchFields[0].SearchExpression := '01/01/2006';
    ppR1.AutoSearchFields[1].SearchExpression := '01/04/2016';
    ppR1.Print;
    [code]

    Your advice please.
    Regards & TIA,
    Ian
  • edited February 2017
    Perhaps this?

    [code}
    ppR1.Parameters.Items['StartDate'].AsString := '01/01/2006';
    ppR1.Parameters.Items['EndDate'].AsString := '01/04/2016';
    ppR1.Print;
    {code}

    The compiler likes it anyway. :-)
  • edited February 2017
    Got it working from the Application OK.
    How/where do I set some values in the designer for the Parameters so I can test during the design phase in RB?
  • edited February 2017
    OK. Gotta get new glasses. Found it.
    All working as desired now.
    Thank you very much for your support & guidance.
    Regards,
    Ian
This discussion has been closed.