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

Procedure in BIRT

Ronak

Hii,

Can you give me an example of the birt report using procedure as query?

Find more posts tagged with

Comments

JasonW

Do you mean a stored procedure? When you create the dataset select SQL Stored Procedure Query instead of
SQL Select Query in the Data Set Type dropdown. Call your procedure like:

{call test()}

Where test is the stored procedure name.

Jason

Ronak

Hii,

I have one scenario and not getting what actually to be done.
Please help me in this case.

Scenario is :
The birt report should always call one procedure and it will return two ref cursor. one ref cursor with column title and 2nd ref cursor with data and the main tricky part is that these refcursor will return different columns. means for one excel export ref cursor returns 10 items and for 2nd excel export it returns 5 items.

Thanks

JasonW

BIRT datasets only can process one cursor currently. You can call a procedure that returns multiple ones and set the ref cursor to use, but only one per dataset.

Jason

Ronak

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="77420" data-time="1306169630" data-date="23 May 2011 - 09:53 AM">
BIRT datasets only can process one cursor currently. You can call a procedure that returns multiple ones and set the ref cursor to use, but only one per dataset. 
 
Jason </blockquote>
 
 
 
Hi Jason, 
 
I didn't get exactly. Can you elaborate it more or can you give example for that?

Ronak

Hello,

I have one different scenario here.
I have one procedure and from that I have to generate report.
I have attached a procedure.
EG:

If user_id = 1
then the column should be dispayed from table_A
elsif user_id = 2
then the column should be dispayed from table_B
end;

Also the condition is that I have to display only those columns which are in REF cursors.

I have no idea of how to doing it
Please answer me and if possible then give any demo example.

JasonW

In your procedure you return one of three result sets. The problem is they all have different column numbers and types. In this case I would create three datasets in birt and add a table to the report for each dataset. Then in the beforeFactory event get the userid say from session and depending on it drop two of the tables. This will prevent the other two datasets from running. For example suppose you name each table: table1, table2, table3 (in general properties of the report table item). Also suppose you have the user id in session. The beforeFactory would look something like:

var userid = reportContext.getHttpServletRequest().getSession().getAttribute("userid");
if( userid == "test1" ){
reportContext.getDesignHandle().findElement("table2").drop();
reportContext.getDesignHandle().findElement("table3").drop();
}else if( userid == "test2" ){
reportContext.getDesignHandle().findElement("table1").drop();
reportContext.getDesignHandle().findElement("table3").drop();
}else if( userid == "test3" ){
reportContext.getDesignHandle().findElement("table1").drop();
reportContext.getDesignHandle().findElement("table2").drop();
}

You could also make userid a report parameter or a url parameter. If a report parameter check it like:
params["userid"].value
If a url parameter
reportContext.getHttpServletRequeset().getParameter("userid");

see attached screenshot for setting the ref cursor in a dataset.

Also I have attached an example of dropping a table.

Jason

Ronak

Hii,

Can you tell me how to create master-master- detail or master-detail-detail report in birt.
I am getting problems of arrangement. In many cases entire detail row starts from next page, also in some case the data of particular table get spread among pages i.e half column on end of first page and other on the start of second page.

How to overcome such problem.
If there is any example then plz forward me .

Thanks
Ronak

JasonW

Do you mean like the attached?

Jason

Ronak

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="77633" data-time="1306430326" data-date="26 May 2011 - 10:18 AM">
Do you mean like the attached? 
 
Jason </blockquote>
 
 
Yes, but I want master-master-detail not just master-detail. I am also attaching one file and mentioning here all problems I am facing. 
In my report the customer number value comes from the first i.e main master table. 
And then the order number comes from the second master table i.e main order table. 
 
When I generated report, What I wants is that the data didn't get spread among different pages. 
i.e half data on the end of first page and rest next to the first page. Data should be on first or its next page. 
 
Lets take an EG: 
Let customer No. be : 1 
Now for customer Number 1 there are 3 order number generated say - 11,12,13. 
Now for order number 11 two Order Details Number are generated say - 21,22 and for order number 12 one order detail number is generated say = 31. 
 
Thus the report to be generated as 
 
1 --> Customer Number 
11 --> Order Number 
21 --> Order Detail Number 
22 --> Order Detail Number 
 
12 --> Order Number 
31 --> Order Detail Number 
 
13 --> Order Number 
 
 
I am not getting the perfect report as I want. Getting small such errors like certain time the entire order Number displays and report look likes 
 
1 --> Customer Number 
11 --> Order Number 
12 --> Order Number 
13 --> Order Number 
 
21 --> Order Detail Number 
22 --> Order Detail Number 
11 --> Order Number 
12 --> Order Number 
13 --> Order Number 
 
31 --> Order Detail Number 
 
Help me out to solve all this problems. 
I created a demo report as per the sample database. I am attaching that report.

Ronak

<blockquote class='ipsBlockquote' data-author="'JasonW'" data-cid="77502" data-time="1306261740" data-date="24 May 2011 - 11:29 AM">
In your procedure you return one of three result sets. The problem is they all have different column numbers and types. In this case I would create three datasets in birt and add a table to the report for each dataset. Then in the beforeFactory event get the userid say from session and depending on it drop two of the tables. This will prevent the other two datasets from running. For example suppose you name each table: table1, table2, table3 (in general properties of the report table item). Also suppose you have the user id in session. The beforeFactory would look something like: 
 
var userid = reportContext.getHttpServletRequest().getSession().getAttribute("userid"); 
if( userid == "test1" ){ 
reportContext.getDesignHandle().findElement("table2").drop(); 
reportContext.getDesignHandle().findElement("table3").drop(); 
}else if( userid == "test2" ){ 
reportContext.getDesignHandle().findElement("table1").drop(); 
reportContext.getDesignHandle().findElement("table3").drop(); 
}else if( userid == "test3" ){ 
reportContext.getDesignHandle().findElement("table1").drop(); 
reportContext.getDesignHandle().findElement("table2").drop(); 
} 
 
You could also make userid a report parameter or a url parameter. If a report parameter check it like: 
params["userid"].value 
If a url parameter 
reportContext.getHttpServletRequeset().getParameter("userid"); 
 
see attached screenshot for setting the ref cursor in a dataset. 
 
Also I have attached an example of dropping a table. 
 
Jason </blockquote>

Ronak

I am getting problem in script as multiple exception occured.

I think that I created problem in passing parameter.
Help me in that

Ronak

Hey Jason,

The answer you gave is good but just wants to know that it is the best solution, where the case is of 200or more tables??

JasonW

I think the solution will still work, but a report with 200+ tables in it is going to be difficult to maintain. You will add a little to the runtime to load such a large report, but after all the drops the report should be small and run fine. But when you have to make changes to the report it could be difficult.

Jason

Ronak

Hi Jason,

Thanks for reply. But I wants to know that can't it would be easy with the help of stored procedure.

I have to do same thing with the help of stored procedure. I attached a file containing the stored procedure.
So please let me know that is it possible, if yes then how. I dont have any idea regarding that.

Thanks,
Ronak

Hans_vd

Again this is looks very much the same question as in http://www.birt-exchange.org/org/forum/index.php/topic/22687-generating-dynamic-report/

But are you seriously planning to build a report containing 200 tables? On the other hand, in the stored procedure I see only three ref cursors, so you'd need only three tables, isn't it?

Ronak

Hi Sir,

Thanks for reply.
Now can you please tell me that if in a report I want to display on four columns out of 10 then how can I do. It should be dynamic

Ronak

Hii,

I created on procedure and it contains a cursor with query retriving 25 columns. Now I want that the retrival of columns should be dynamic i.e the user enter the no. of columns and that much columns only being displayed. I am not getting any idea regarding this. How can I do this????

Thanks,
Ronak

JasonW

Attached are two examples that use the de api in script to either drop a column or add columns dynamically.

Jason

Ronak

Hii..

Thanks for the Reply. The attached file (example) was good, but can you give me same or such type example working with stored procedures having ref cursors...please.

Another Question:-

CREATE OR REPLACE PROCEDURE doExcelExport( pvModuleName Varchar2,
rcColList OUT ERM.RC ) AS
BEGIN
If pvModuleName = 'TEST1' then
OPEN rcColList FOR
SELECT USER_ID,
LOGIN_DATE,
LOGIN_DAY,
DAY_TYPE,
LOGIN_TIME,
LOGOUT_TIME,
WORK_HOURS,
BREAK_HOURS
FROM SR_1189
ORDER BY Login_Date;
Elsif pvModuleName = 'TEST2' then
OPEN rcColList FOR
SELECT DOMAIN_NAME,
ATTRIBUTE,
ATTRIB_VALUE
FROM T_DOMAIN;
Elsif pvModuleName = 'TEST3' then
OPEN rcColList FOR
SELECT LANG_CODE,
LANG_NAME,
LANG_KEY
FROM T_LANG;
End If;
Exception
When Others Then
Null;
END doExcelExport;
_______________________________________________ As in this procedure there is two parameters. In this procedure the input parameter is pvmodulename.

Now What I wnat is I just have one table with max 200 columns and as per the value of pvmodulename I wants to generate table.

i.e if pvmodulename = test1 then the table will be filled with data of query SR1189 as shown in procedure. if test2 then t_domain and so on.

I am trying from many days but not getting it. please reply me

Thanks,
Ronak

JasonW

I do not have an example like that.

Jason