nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Not able to retreive the mulitple resulset from stored procedure

RepBIRT

The stored procedure have mulitple resultsets. When it is accessed from the report, we are able to retreive only the first result set. Rest of the resultsets are not retreived. Any ideas/references would be of great help.

Database used: DB2

Find more posts tagged with

Comments

mwilliams

If you look at the last section of the pdf in this first link, you'll see that, at least originally, BIRT only supported stored procedures with 0 or 1 result sets. If a procedure had more, only the first was taken.

http://www.eclipse.org/birt/release20specs/BPS24-DataSetOutParamSpec.pdf

According to this bug report, in 2.3.0, the other result sets from a multi result set procedure could be accessed, but the result set had to be specified by an index number.

https://bugs.eclipse.org/bugs/show_bug.cgi?id=109128

Not sure if this is still the case, but it seems to explain the result you're getting.

RepBIRT

Is there any way, the mulitple dataset values are retrieved by the script by changing the option
"Select Result set By Name / Number" for different tables.

mwilliams

I don't have a stored procedure to test this on. Where does it have you select which result set you want to return? Is it in the sp call query? If so, you might be able to use dataSet parameter binding to change the passed value, so you can display all the result sets in your report. Let me know!

mwilliams

I talked to a colleague and they said that you could possibly put a cursor on an output parameter and try to iterate the results in script, but that they hadn't tried it.

bgbaird

I have just encountered the same issue. My legacy report called an sp with another sp nested inside. I attempted to re-engineer with a cursor, but ended up having to maintain only one level in the sp then joining with another result set in my design.

It worked fine, just a different way around.

RepBIRT

Thanks for all the alternative idea.

Main intention is to retreive the result set through scripting
BIRT will allow to fetch only one result set in case of multiple result sets from the stored procedure.

The choice of result sets can be changed in Data Set Setting by using either any one of the options
Select Result sets by Name / Number.

Is there any possibility to use the all the result sets through scripting.

There are around 12 result sets and each should be used for each tables.
So when creating each table whether individual result set can be used in those tables.