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; } } }

Execute a stored procedure before rendering?

megabri

I'm trying to find a way to execute a stored procedure before my report renders. How would I do that?

Find more posts tagged with

Comments

mwilliams

You're meaning before it's created, right? Can you explain more about what you're trying to do?

megabri

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="108340" data-time="1344467941" data-date="08 August 2012 - 04:19 PM">
You're meaning before it's created, right? Can you explain more about what you're trying to do? </blockquote>
Sure Mike. This is another one of those Actuate to BIRT things I'm trying to work through. In actuate I run the stored procedure below in the Start method of one of the ReportSections. This executes before any part of the report renders. Here's the actuate start method I use, let me know if you need more information! 
 
 
Sub Start( ) 
Super::Start( ) 
' Insert your code here 
' deptName = getUserSite() 
 
Dim aConnection as AcDBConnection 
Set aConnection = New MROConnection 
aConnection.connect() 
 
 
Dim str as String 
Dim dbStmt as AcDBStatement 
 
str = "DECLARE " 
str = str & " v_wonum workorder.WONUM%TYPE; " 
str = str & " BEGIN " 
str = str & " MAXIMO.lirr_rpt_es_brg_ins_proc(p_wonum => '" 
str = str & wonum 
str = str &"', p_status => ''); " 
str = str & " END; " 
 
Set dbStmt = aConnection.Prepare(str) 
dbStmt.Execute() 
 
End Sub 
 
 
 
- Mike

mwilliams

What are you specifically trying to do? Why does this stored procedure need to be ran first? Does it bring in data you need to pass to another query? Or what? If you just need this query to run first out of all the elements in your report, you'd just need to put an element bound to it, first, in your report. If you need to know the number of results returned, or you want to drop all of your elements, if there are 0 rows, or something like that, you'd need to connect to your database in the beforeFactory method and run your query in script. Let me know.

megabri

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="108377" data-time="1344523702" data-date="09 August 2012 - 07:48 AM">
What are you specifically trying to do? Why does this stored procedure need to be ran first? Does it bring in data you need to pass to another query? Or what? If you just need this query to run first out of all the elements in your report, you'd just need to put an element bound to it, first, in your report. If you need to know the number of results returned, or you want to drop all of your elements, if there are 0 rows, or something like that, you'd need to connect to your database in the beforeFactory method and run your query in script. Let me know. </blockquote>
The stored procedure effects what data is shown in the report. Does this mean I'd have to put the stored procedure in the beforeFactory method? If I do need to do that how would I code that in Birt?

mwilliams

If you're just passing a value from the result of the stored procedure to another dataSet to affect what data is shown in the report, you can just bind the stored proc dataSet to a text box at the top of your report. Then, in the onFetch of the stored procedure dataSet, you can grab the values you need, store them into global variables and then use the global variables in your other queries, via the beforeOpen script of their dataSets.

megabri

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="108383" data-time="1344527030" data-date="09 August 2012 - 08:43 AM">
If you're just passing a value from the result of the stored procedure to another dataSet to affect what data is shown in the report, you can just bind the stored proc dataSet to a text box at the top of your report. Then, in the onFetch of the stored procedure dataSet, you can grab the values you need, store them into global variables and then use the global variables in your other queries, via the beforeOpen script of their dataSets. </blockquote>
Wow that's a little confusing lol. Lets start with the basics... I know how to make a dataSet that uses a query. How do I make a dataSet that uses a stored procedure? Also, is there a way I can make one dataSet run before the others? 
 
The stored procedure isn't really going to pass anything after it's executed. It updates a table that I use in a later query. The only thing that is passed to the stored procedure is a work order number that I already have as a parameter.

mwilliams

Take a look at this, for using a stored procedure in BIRT.

http://wiki.eclipse.org/StoredProcedure_(BIRT)

If you create your stored procedure dataSet and then bind it to a hidden text box, at the top of your report design, it'll be the first dataSet executed. The first dataSet is the first to run.

Hope this helps.

megabri

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="108392" data-time="1344542160" data-date="09 August 2012 - 12:56 PM">
Take a look at this, for using a stored procedure in BIRT. 
 
<a class='bbc_url' href='http://wiki.eclipse.org/StoredProcedure_(BIRT'>http://wiki.eclipse.org/StoredProcedure_(BIRT</a>) 
 
If you create your stored procedure dataSet and then bind it to a hidden text box, at the top of your report design, it'll be the first dataSet executed. The first dataSet is the first to run.

Hope this helps. </blockquote>
I think I got it to work properly. I'm assuming that the things on the master page, such as headers, generate first right? I bound a text box to the first header that's generated.

megabri

Hope this helps. </blockquote>
 
Upon further investigation it doesn't seem like my stored procedure is firing before the report is rendered. I did as you said and created my stored procedure as a dataSet and bound it to a text box in the header. Any ideas why this isn't working? Below is what i have set up in my dataSet's open script: 
 
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName()); 
maximoDataSet.open(); 
 
var sqlText = new String(); 
 
sqlText = "DECLARE " + 
" v_wonum workorder.WONUM%TYPE; " + 
" BEGIN " + 
" MAXIMO.lirr_rpt_es_brg_ins_proc(p_wonum => '" + params["wonum"] + "', p_status => ''); " + 
" END;" 
 
maximoDataSet.setQuery(sqlText);