locked
SSRS report Primary XML dataset now needs to query SQL for secondary information RRS feed

  • Question

  • Have a long running report that now needs to present a "new" piece of data from a separate data source.  In this company well-used report only one customer needs this specific data as of now.  I created <g class="gr_ gr_269 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="269" id="269">an if()</g> in the expression to call it if needed.  

     

    ="PO# "+First(Fields!pur_orno.Value )
    + if(len(Fields!whinh431_adin)> 0, "Release Number: " + getRelease(),"")

    How do I use the Code segment of the report to fetch the data?

    Function getRelease()
    ' Sproc on <g class="gr_ gr_312 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="312" id="312">db</g> that the Shared data Source points to is getRelease() the data in element whinh431_adin is the key/param that is

    ' needed to find the release number.  I have this so far but cannot find a way to make the call for data before I return it.

      return "release-number"
    End function

    Any ideas or a better way to do this?  

    Wednesday, May 31, 2017 1:51 PM

All replies

  • Hi Stephen.Russell.Ring,

    According to current description, I was confused about your actual requirement, especially the usage of custom code. In this scenario, I want to confirm below questions:

    • I noticed that you use an IIF expression to display data, so, you want to dynamically show the new piece of data?
    • The fields [pur_orno] and [whinh431_adin] are two existing fields returned by the primary XML dataset? You have created a report parameter whose available values are returned by field [whinh431_adin]?
    • You want to control whether execute the SQL dataset or not via custom code? In other words, if whinh431_adin <0, there is no need to execute SQL query and return new piece of data, right?

    First, you should be aware that all the dataset queries are executed before report processing. If you want to refer to any data, you have to fetch it in a dataset before calling it in report body. Second, it is not supported to fetch data from DB in custom code. 

    In your scenario, You could create a report parameter (in my test, it's @ReportParameter1) whose available values are returned from field [whinh431_adin]. Then, you can create a dynamic dataset based on parameter selection. For example,

    If (@ReportParameter1)>0
    SELECT        tableName.*
    FROM            tableName
    else
    select null

    Then, the expression in report body could be like this:

    ="PO# "+First(Fields!pur_orno.Value )+ First(Fields!release_number)

    Another workaround is create a new dataset (not a dynamic one) which fetched all data records, including corresponding release_number from SQL DB. Then, the expression could be:

    ="PO# "+First(Fields!pur_orno.Value )
    + if(Parameters!ReportParameter1.Value> 0, Fields!ReleaseNumber.value, nothing)

    If I have something misunderstood, please correct me.

    Best regards,
    Yuliana Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Yuliana Gu Thursday, June 8, 2017 8:42 AM
    Thursday, June 1, 2017 3:44 AM