Eclipse Community Forums: BIRT » Use Multiple Dataset For One Table

Home » Archived » BIRT » Use Multiple Dataset For One Table

Use Multiple Dataset For One Table [message #935154]

Sat, 06 October 2012 14:54

Eclipse User

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 14:57] by Moderator

Re: Use Multiple Dataset For One Table [message #937334 is a reply to message #935154]

Mon, 08 October 2012 18:30

Eclipse User

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 386 times)

Re: Use Multiple Dataset For One Table [message #937730 is a reply to message #937334]

Tue, 09 October 2012 04:06

Eclipse User

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.