Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Use Multiple Dataset For One Table
Use Multiple Dataset For One Table [message #935154] Sat, 06 October 2012 18:54 Go to next message
Utkarsh Shinde is currently offline Utkarsh Shinde
Messages: 4
Registered: August 2012
Junior Member
Hi
I have issue in BIRT
I have two datasets I have to use both of them in one table how's it possible
Query of dataset1:
SELECT
HC.CASE_NO CASE_NO,
HC.NO_REVIEWS NO_REVIEWS,
RP.case_status_id rvw_prd_status,

HUSR0201_RPT.AWAITING_DECISION_CASE_PROD(HC.CASE_NO) CASE_PROD2,
HUSR0201_RPT.AWAITNG_DECISN_CASE_INCDNT_NO(HC.CASE_NO) CASE_INCDNT_NO2,
HUSR0201_RPT.AWAITNG_DECISN_ms_user(HC.CASE_NO) ms_user2,
HUSR0201_RPT.AWAITNG_DECISN_CASE_RVW_NO(HC.CASE_NO) case_rvw_no,
HUSR0201_RPT.AWAITNG_DECISN_RECEIVED_DATE(HC.CASE_NO) STATUS_DATE2,
HUSR0201_RPT.AWAITNG_DECISN_AP_CONT_ATT_RN(HC.CASE_NO) AP_CONT_ATT_RN,
HUSR0201_RPT.PEER_TO_PEER_CONTACT_DATE(HC.CASE_NO) LAST_AP_CONT_RN,
HUSR0201_RPT.REFERRAL_TYPE_ID(HC.CASE_NO) referral_type_id,
HUSR0201_RPT.GET_PATIENT_L(HUSR0201_RPT.AWAITNG_DECISN_CASE_INCDNT_NO(HC.CASE_NO)) LNAME,
HUSR0201_RPT.GET_PATIENT_F(HUSR0201_RPT.AWAITNG_DECISN_CASE_INCDNT_NO(HC.CASE_NO)) FNAME,
HUSR0201_RPT.GET_CLIENT_CN(HUSR0201_RPT.AWAITNG_DECISN_CASE_INCDNT_NO(HC.CASE_NO)) CLIENT_NAME,
HUSR0201_RPT.GET_CLIENT_ON(HUSR0201_RPT.AWAITNG_DECISN_CASE_INCDNT_NO(HC.CASE_NO)) ORG_NAME,
HUSR0201_RPT.GET_RVW_PRD_DATE(HUSR0201_RPT.AWAITNG_DECISN_CASE_RVW_NO(HC.CASE_NO),HC.CASE_NO) RECVD_DATE,
HUSR0201_RPT.GET_LOCATION(HUSR0201_RPT.AWAITNG_DECISN_MS_USER(HC.CASE_NO)) LOC_NO,
HUSR0201_RPT.GET_LOCATION_TN(HUSR0201_RPT.AWAITNG_DECISN_ms_user(HC.CASE_NO)) team_no
from case_rvw_period RP, hcx_case HC
where HC.inq_ind = 'N'
and HC.closed_id is NULL
and nvl(HC.ms_id, HC.rn_id) is not NULL
and HC.case_prod in ('OPR','PTR','CHR')
and RP.case_no = HC.case_no
AND RP.CASE_RVW_NO = HC.NO_REVIEWS
AND RP.CASE_STATUS_ID NOT IN ('CERT','CLTR','CMPL','DENY','OVER','UPHD')
ORDER BY 5
and
Query for dataset2:

select count(HC.ms_id) as total_user from HCX_CASE HC,case_rvw_period RP
where HC.inq_ind = 'N'
and HC.closed_id is NULL
and nvl(HC.ms_id, HC.rn_id) is not NULL
and HC.case_prod in ('OPR','PTR','CHR')
and RP.case_no = HC.case_no
AND RP.CASE_RVW_NO = HC.NO_REVIEWS
AND RP.CASE_STATUS_ID NOT IN ('CERT','CLTR','CMPL','DENY','OVER','UPHD')
group by ms_id having (count(ms_id)>=1)



I want to put total_user to same table which have already columns from dataset1
I used Joined dataset but its not working.
pl suggest.

[Updated on: Sat, 06 October 2012 18:57]

Report message to a moderator

Re: Use Multiple Dataset For One Table [message #937334 is a reply to message #935154] Mon, 08 October 2012 22:30 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

If data set two only returns one row you could always add it to the top of the report and use the visibility rule to hide the table. Then in the oncreate for the data item you could add a script like:

reportContext.setPersistentGlobalVariable("myave",this.getValue());

You could then get this value later(second table) like:

reportContext.getPersistentGlobalVariable("myave"); //this can be called from an expression or from a report item script. You could also call it from chart script like:

function beforeGeneration( chart, icsc )
{
var mytitle = "Avg QTY for Order is: " +icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("myave");

chart.getTitle().getLabel().getCaption().setValue(mytitle);
}

If you are going to manipulate this value further (ie use it in a calculation you may have to set it as a type when you save it) ie
//save it
importPackage(Packages.java.lang);
reportContext.setPersistentGlobalVariable("myave",new Integer(this.getValue()));

//use it
var myval =parseInt(reportContext.getPersistentGlobalVariable("myave"));

see example report attached.

Jason
  • Attachment: total.zip
    (Size: 5.92KB, Downloaded 116 times)
Re: Use Multiple Dataset For One Table [message #937730 is a reply to message #937334] Tue, 09 October 2012 08:06 Go to previous messageGo to next message
Utkarsh Shinde is currently offline Utkarsh Shinde
Messages: 4
Registered: August 2012
Junior Member
Hi,
I want total_user(actually it is total_cases) in Parallel of other Columns...pl look Screenshot.total_case in Screenshot.I have to count cases for Each User.other columns from query1.
Re: Use Multiple Dataset For One Table [message #938293 is a reply to message #937730] Tue, 09 October 2012 18:46 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Does total_cases change for every row? If so you could use a nested table.
Here is an older example:
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/subreport/index.php

Jason
Re: Use Multiple Dataset For One Table [message #939311 is a reply to message #938293] Wed, 10 October 2012 16:34 Go to previous messageGo to next message
Utkarsh Shinde is currently offline Utkarsh Shinde
Messages: 4
Registered: August 2012
Junior Member
yes total_case changes for every row.nested table not working there.
Re: Use Multiple Dataset For One Table [message #939860 is a reply to message #939311] Thu, 11 October 2012 05:09 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Do you get an error? What does the output look like?

Jason
Previous Topic:Looking for Excel Emitters
Next Topic:Issue with editing
Goto Forum:
  


Current Time: Sun Sep 21 20:23:08 GMT 2014

Powered by FUDForum. Page generated in 0.01832 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software