How do I pass in 2 parameters to a report, based on a SQL function? RRS feed

  • Question

  • I'm trying to design a report based on a function query in our ERP.

    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')

    If I run this in SSMS, providing the two parameters(@ls_begin_invoice_no,@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..



    Friday, December 6, 2019 8:24 PM

All replies

  • My hunch is that you need to define a report project, possibly in C#, in Microsoft Visual Studio. It will give you a chance to build a connection string to the database you need and then there will be an option for you to choose between (1) tables, (2) Views (3) Stored procedures and (4) Functions.

    - MyCatAlex

    Saturday, December 7, 2019 5:20 PM
  • Probably easiest way is to create a data set that calls UDF  (with parameters)  , then you can use  the result anywhere in report.

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 8, 2019 6:33 AM
  • Uri, can you point me to a "basic" example of doing this?

    I've not yet created these within the SSRS world..


    Monday, December 9, 2019 4:11 PM
  • Rich,

    In SQL, are you just selecting from your function to get the data for the parameters?  

    Select <Fields_returned_by_function>
    From <function_call>

    If so, then you should be able to create a dataset in the SSRS report that runs the same select statement. That gets the values available to be used within the report.

    Once you've done that, create the two parameters that you can use in your main report query (that needs the two values) and set the default value to be provided by this new dataset:

    1) Open the parameter Properties
    2) On the Default Values tab, choose Get Values from a query.
    3) Specify the new dataset you created for the function call in the Dataset box, then choose the field that contains the data for that parameter as the Value Field.

    4) Repeat for the second parameter.

    All this will get the values returned from the functions into SSRS parameters that can be used anywhere on the report.

    Hope this helps.



    Monday, December 9, 2019 10:57 PM