| Use Multiple Dataset For One Table [message #935154] |
Sat, 06 October 2012 18:54  |
 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  |
|
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 348 times)
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.02016 seconds
] 
Search
Help
Register
Login
Home
