locked
SSRS 2008 R2: Using a value returned by SP as a parameter to another SP with Oracle backend RRS feed

  • Question

  • Hi,

    First to state, I am not a master in SSRS yet, only have been using it for last three months.

    I have stored procedure in Oracle which I would need to execute twice, second one having parameter needed from the first procedure.  One of the values returned from the second should be used as a base for detail lines calculations. With every little workaround I could have figured out so far, I always end up getting similar error message, all bound to the same issue, fields cannot be used as parameters. I have tried expressions, report variables and lookup function, every time ending up having the same error message.

    Tried to search and apply many techniques already found on the forums about the similar problems people have had, but none of them being exactly as my issue, nor was the answers that solved their problems applicaple to my problem.

    The reason why I decided to use the stored procedure is that the main database is using financial periods in the column heading and only storing the start and end date of each period, so it is not possible to refer to a certain column in the table with available period value. So I ended up writing dynamic SQL in Oracle SP to get the right value, in this case financial period's starting date. My first procedure is in its own dataset and is first started based on user input of start and end dates, it works without any problems. This procedure is finding the correct period start date and returning it in a reference cursor to dataset, amongst with other calculated values. I should use this start date returning value as a parameter to the same procedure, but in another dataset, and use it as a start date, while end date is the user supplied start date - 1. So far SSRS hasn't complained about that, since I am using an expression on existing report parameter, but it is complaining about using field value from the other dataset.

    I saw in some forums subreports as suggestions, but i cannot use subreports due to the fact that I need to use the second SP calculated return values on detail lines calculations. So I am running out of ideas. Any ideas are welcome, as long as it helps to solve this issue. :)

    Thanks in advance.

    Jan

    Wednesday, September 4, 2013 9:43 AM

Answers

  • Hi John, thanks for your reply. Despite all the efforts I did, SSRS keeps on complaining to me that fields cannot be used as parameters. I overcame the problem with modifying the procedure to do some extra calculation for what I tried to gain from the second call, and  the procedure is now returning the wanted figures in the reference cursor. All works well with this solution. Thanks for your effort anyway.

    • Proposed as answer by Charlie Liao Monday, September 9, 2013 8:56 AM
    • Marked as answer by Charlie Liao Monday, September 9, 2013 8:56 AM
    Saturday, September 7, 2013 10:50 AM

All replies

  • You can use a dataset to provide the value for a parameter.  That can be a hidden parameter, so the user doesn't have to see it.  So your first procedure call can provide a field, which then becomes the value of a parameter.  So we have turned a field into a parameter.  The new parameter can be used as input for a procedure in a second dataset.

    But your underlying problem seems to be converting a user's input parameters for the start and end dates (e.g., mm/dd/yyyy) into financial period keys.  Usually there is a FinancialPeriod table, or something like that, that lists each key value together with the corresponding beginning and end dates.  So you need to join the Target table you're looking at with this FinancialPeriod table.  Then you can apply the user's start and end dates directly to the FinancialPeriod table, and the join will return the records from the Target table that you need.  If I have understood the problem correctly?

    Wednesday, September 4, 2013 5:05 PM
  • Hi John, thanks for your reply. Despite all the efforts I did, SSRS keeps on complaining to me that fields cannot be used as parameters. I overcame the problem with modifying the procedure to do some extra calculation for what I tried to gain from the second call, and  the procedure is now returning the wanted figures in the reference cursor. All works well with this solution. Thanks for your effort anyway.

    • Proposed as answer by Charlie Liao Monday, September 9, 2013 8:56 AM
    • Marked as answer by Charlie Liao Monday, September 9, 2013 8:56 AM
    Saturday, September 7, 2013 10:50 AM