Why do I do it to myself?

rbuser

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

nicocizik

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.

--

rbuser

Hi Nice,
Thank you for your reply.
I will have a look at the reference and have a go.
Will advise.

Regards,
Ian

rbuser

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

rbuser

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

rbuser

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?

rbuser

OK. Gotta get new glasses. Found it.
All working as desired now.
Thank you very much for your support & guidance.
Regards,
Ian