We help IT Professionals succeed at work.

How do I pass in 2 parameters to a SSRS report, that's based on a SQL function query?

Richard Gouette
Richard Gouette used Ask the Experts™
I'm trying to design a report based on a function query in our ERP. (as in, under Database/Programmability/Functions/Table-valued Functions)

The FROM clause is this:

FROM p21_fnt_invoice_hdr('COMPNAME', @ls_begin_invoice_no,@ls_end_invoice_no, 0, 9999, '0', 'ZZZZZZZZZZ', 0, 999999999, ' ', 'ZZZZZZZZZZ', ' ', 'ZZZZZ', 0,999999999, {ts '1900-01-01 00:00:00.000'}, {ts '2019-12-05 23:59:59.000'}, 'Y', 'N', 'N','B','Y', 'N','','N')

Open in new window

If I run this in SSMS, providing the two parameters (@ls_begin_invoice_no  and  @ls_end_invoice_no),  it returns the data as expected.

But, I cannot figure out how to make SSRS work?

I get no results if I try & apply parameters..
No errors, just no results


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Please have a look here https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-parameters-report-builder-and-report-designer?view=sql-server-ver15 at how to add SSRS Report Parameters to your report and read the "Note" under "Creating Parameters" paragraph that says "Not all data sources support parameters."
Richard GouetteIT Manager


abandoned that particular query, but good thanks for the direction.