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™
on
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

thanks,

Rich
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
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

Author

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