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

Designing subreports

dsrikanth

How to design a report based on this oracle query. My query is very big but i am giving sub query of the Total query. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT A.COMP_CODE,A.MATCODE,B.UNIT_CODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,COST_PRICE,GRN_DATE FROM

(
SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,B.COST_PRICE,B.GRN_DATE FROM

(
SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,B.MAT_NAME,B.SUP_PROD_CODE,
B.SUP_CODE,B.BRAND_CODE,B.CAT_CODE,B.SGRPCODE,B.SUB_SGRPCODE FROM
MAT_LIST A,
MATERIAL_MASTER B
WHERE A.COMP_CODE=B.COMP_CODE
AND A.MATCODE=B.MATCODE
--AND A.MATCODE='168847'
)A,

(SELECT A.COMP_CODE,A.MAIN_CODE,A.MATCODE,NVL(A.ATTRIB_CODE1,0) ATTRIB1,NVL(A.ATTRIB_CODE2,0) ATTRIB2,
A.MAT_TYPE,MAX(A.MAT_COST) COST_PRICE,GRN_DATE
FROM INV_GRN_DTL_V A
WHERE a.grn_date=(select max(b.grn_date) from inv_grn_dtl_v b
where b.comp_code=a.comp_code and
b.main_code=a.main_code and
b.matcode=a.matcode and
nvl(b.grn_status,'P')='A' and
nvl(b.auth_status,'P')='A' and
b.supcode<>'GDS1' and
b.grn_date<=:TO_DT)
AND NVL(A.GRN_STATUS,'P')='A'
AND NVL(A.AUTH_STATUS,'P')='A'
GROUP BY A.COMP_CODE,A.MAIN_CODE,A.MATCODE,A.ATTRIB_CODE1,A.ATTRIB_CODE2,A.MAT_TYPE,GRN_DATE
) B
WHERE A.COMP_CODE=B.COMP_CODE(+)
AND A.MATCODE=B.MATCODE(+)
AND A.ATTRIB1=B.ATTRIB1(+)
AND A.ATTRIB2=B.ATTRIB2(+)
AND A.COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE)

A,(
SELECT COMP_CODE,MAIN_CODE,UNIT_CODE
FROM UNIT_MST WHERE COMP_CODE=56
AND UNIT_CODE IN (SELECT DISTINCT UNIT_CODE FROM STK_SALES_VU
WHERE ORD_DATE BETWEEN :FR_DT AND :TO_DT
AND COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE)
--UNION ALL
--SELECT DISTINCT COMP_CODE,MAIN_CODE,'STOCK' FROM UNIT_MST WHERE COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE
) B

WHERE A.COMP_CODE=B.COMP_CODE
AND A.COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE
AND UNIT_CODE=DECODE(:_UNIT_CODE,'ALL',UNIT_CODE,[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_UNIT_CODE)
AND CAT_CODE BETWEEN DECODE(:FR_CAT,'ALL',CAT_CODE,:FR_CAT)
AND DECODE(:TO_CAT,'ALL',CAT_CODE,:TO_CAT)
AND SUP_CODE=DECODE([img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_SUP_CODE,'ALL',SUP_CODE,[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_SUP_CODE))
</pre>
 
Here each :param(: P_UNIT_CODE) should be replaced with the Report parameter, I created the report parameters but i don't know how to assign them to sub queries especially at between operator and DECODE function. The parameters are -> (:FR_DT,:TO_DT, : p_COMP_CODE, :FR_CAT, :TO_CAT, : p_SUP_CODE) which need to be replaced with '?' while writing the query in dataset. But i don't know how to replace same parameter which is occurring at multiple places with the query parameters. Pls Help Me.

Find more posts tagged with

Comments

Hans_vd

Hi Dsrikanth,

Read this great article about re-using parameters in BIRT datasets by using the WITH clause: http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/

You'll have to add a reference to the "params" view in every from clause.

Hope this helps

Regards
Hans

dsrikanth

<pre class='_prettyXprint _lang-auto _linenums:0'>
WITH tmp_parms AS (
SELECT ? as fr_dt, ? as to_dt, ? as p_comp_code,
? as fr_cat, ? as to_cat, ? as p_sup_code, ? as p_unit_code, ? as p_fnyr
FROM dual
)
SELECT
C.COMP_CODE,C.MATCODE,C.ATTRIB1,C.ATTRIB2,C.MAT_NAME,C.SUP_PROD_CODE,
C.SUP_CODE,C.BRAND_CODE,C.CAT_CODE,SGRPCODE,SUB_SGRPCODE,C.UNIT_CODE,
NVL(SUM(D.SALES_QTY),0)SALES_QTY,
NVL(SUM(D.SALES_VAL),0) SALES_VAL,
MAX(COST_PRICE) GRN_COST_PRICE,GRN_DATE,'sales qty' a, 'sales val' b,'stock' c,'stock val' d
FROM
tmp_parms tr,
(
SELECT A.COMP_CODE,A.MATCODE,B.UNIT_CODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,COST_PRICE,GRN_DATE FROM

(
SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,MAT_NAME,SUP_PROD_CODE,
SUP_CODE,BRAND_CODE,CAT_CODE,SGRPCODE,SUB_SGRPCODE,B.COST_PRICE,B.GRN_DATE FROM

(
SELECT A.COMP_CODE,A.MATCODE,A.ATTRIB1,A.ATTRIB2,B.MAT_NAME,B.SUP_PROD_CODE,
B.SUP_CODE,B.BRAND_CODE,B.CAT_CODE,B.SGRPCODE,B.SUB_SGRPCODE FROM
MAT_LIST A,
MATERIAL_MASTER B
WHERE A.COMP_CODE=B.COMP_CODE
AND A.MATCODE=B.MATCODE
--AND A.MATCODE='168847'
)A,

(SELECT A.COMP_CODE,A.MAIN_CODE,A.MATCODE,NVL(A.ATTRIB_CODE1,0) ATTRIB1,NVL(A.ATTRIB_CODE2,0) ATTRIB2,
A.MAT_TYPE,MAX(A.MAT_COST) COST_PRICE,GRN_DATE
FROM INV_GRN_DTL_V A
WHERE a.grn_date=(select max(b.grn_date) from inv_grn_dtl_v b
where b.comp_code=a.comp_code and
b.main_code=a.main_code and
b.matcode=a.matcode and
nvl(b.grn_status,'P')='A' and
nvl(b.auth_status,'P')='A' and
b.supcode<>'GDS1' and
b.grn_date<=tr.to_dt)
AND NVL(A.GRN_STATUS,'P')='A'
AND NVL(A.AUTH_STATUS,'P')='A'
GROUP BY A.COMP_CODE,A.MAIN_CODE,A.MATCODE,A.ATTRIB_CODE1,A.ATTRIB_CODE2,A.MAT_TYPE,GRN_DATE
) B
WHERE A.COMP_CODE=B.COMP_CODE(+)
AND A.MATCODE=B.MATCODE(+)
AND A.ATTRIB1=B.ATTRIB1(+)
AND A.ATTRIB2=B.ATTRIB2(+)
AND A.COMP_CODE=tr.p_comp_code)

A,(
SELECT COMP_CODE,MAIN_CODE,UNIT_CODE
FROM UNIT_MST WHERE COMP_CODE=56
AND UNIT_CODE IN (SELECT DISTINCT UNIT_CODE FROM STK_SALES_VU
WHERE ORD_DATE BETWEEN tr.fr_dt AND tr.to_dt
AND COMP_CODE=tr.p_comp_code)
--UNION ALL
--SELECT DISTINCT COMP_CODE,MAIN_CODE,'STOCK' FROM UNIT_MST WHERE COMP_CODE=tr.p_comp_code
) B

WHERE A.COMP_CODE=B.COMP_CODE
AND A.COMP_CODE=tr.p_comp_code
AND UNIT_CODE=DECODE(tr.p_unit_code,'ALL',UNIT_CODE,tr.p_unit_code)
AND CAT_CODE BETWEEN DECODE(tr.fr_cat,'ALL',CAT_CODE,tr.fr_cat)
AND DECODE(tr.to_cat,'ALL',CAT_CODE,tr.to_cat)
AND SUP_CODE=DECODE(tr.p_sup_code,'ALL',SUP_CODE,tr.p_sup_code))

C,(
SELECT COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0) ATTRIB_CODE1,NVL(ATTRIB_CODE2,0) ATTRIB_CODE2,
NVL(SUM(SALES_QTY),0) SALES_QTY, SUM(COST_VAL) SALES_VAL
FROM
(

SELECT COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(B.ATTRIB_CODE1,0) ATTRIB_CODE1,NVL(B.ATTRIB_CODE2,0) ATTRIB_CODE2,
NVL(SUM(B.SALE_QTY),0) SALES_QTY, SUM(B.VAL) COST_VAL
FROM STK_SALES_VU_ATT B
WHERE ORD_DATE BETWEEN tr.fr_dt AND tr.to_dt
AND UNIT_CODE=DECODE(tr.p_unit_code,'ALL',UNIT_CODE,tr.p_unit_code)
AND COMP_CODE=tr.p_comp_code
GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,NVL(B.ATTRIB_CODE1,0),NVL(B.ATTRIB_CODE2,0)

UNION ALL

SELECT COMP_CODE,MAIN_CODE,'STOCK' UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0),SUM(INC_QTY)-SUM(DEC_QTY) OB_QTY, 0 SALES_VAL
FROM INV_TRN_DAY_SUM_VU_ATT
WHERE TRN_DATE BETWEEN tr.fr_dt AND tr.to_dt
AND UNIT_CODE=DECODE(tr.p_unit_code,'ALL',UNIT_CODE,tr.p_unit_code)
AND COMP_CODE=tr.p_comp_code
GROUP BY COMP_CODE,MAIN_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0)

UNION ALL

SELECT COMP_CODE,MAIN_CODE,'STOCK' UNIT_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0),SUM(QTY)QTY, 0 SALES_VAL
FROM MATERIAL_DETAIL
WHERE SERIAL=tr.p_fnyr
AND UNIT_CODE=DECODE(tr.p_unit_code,'ALL',UNIT_CODE,tr.p_unit_code)
AND COMP_CODE=tr.p_comp_code
GROUP BY COMP_CODE,MAIN_CODE,MAT_TYPE,MATCODE,NVL(ATTRIB_CODE1,0),NVL(ATTRIB_CODE2,0)

)
--WHERE MATCODE='168847'
GROUP BY COMP_CODE,MAIN_CODE,UNIT_CODE,MAT_TYPE,MATCODE,ATTRIB_CODE1,ATTRIB_CODE2
) D

WHERE C.COMP_CODE = D.COMP_CODE (+)
AND C.UNIT_CODE = D.UNIT_CODE(+)
AND C.MATCODE = D.MATCODE(+)
--AND C.MATCODE='168847'
AND C.ATTRIB1 = D.ATTRIB_CODE1(+)
AND C.ATTRIB2 = D.ATTRIB_CODE2(+)
AND C.COMP_CODE=tr.p_comp_code
AND C.UNIT_CODE=DECODE(tr.p_unit_code,'ALL',C.UNIT_CODE,tr.p_unit_code)
GROUP BY
C.COMP_CODE,C.MATCODE,C.ATTRIB1,C.ATTRIB2,C.MAT_NAME,C.SUP_PROD_CODE,
C.SUP_CODE,C.BRAND_CODE,C.CAT_CODE,SGRPCODE,SUB_SGRPCODE,C.UNIT_CODE,GRN_DATE
order by c.unit_code</pre>
 
I declared the parameters as fr_dt, to_dt, p_comp_code and i linked them to the report parametes. is it right? I am getting this error "TR"."P_COMP_CODE":invalid identifier . What's wrong

Hans_vd

It's all about scope

You've added the params in the from line of the most outer query, but it is not known in the subqueries. You'll have to add it to the inner queries in order to be able to read the values from it.

Regards
Hans

dsrikanth

Thanks Hans It is working fine, 
I have one more query that is in my query i have code of the country where i need to get the name of that country while preparing the report. I have a stored procedure which returns the name of the country if we pass the code but how to call that procedure with code at each row. 
 
This is not my exact requirement but i need something in that way. 
 
Please give any suggestion in both ways 
<ul class='bbc'><li>How to call procedure at each row by passing code.</li><li>How to call dataset which returns name or bind the data for each row.</li></ul>

Hans_vd

Is it a procedure or a function?

If it is a function, you can add a call to it in your SQL.
That would be the most simple solution.

But, unless there is some complex code in the procedure, even better would be to incorporate the code in the stored procedure in your query. It will certainly perform better.

Does one of these solutions work for you?

Regards
Hans

dsrikanth

The query that what i posted in this topic taking lot of time to execute.
What is the problem?

If it fetching thousands of records why it is not fetching them directly from database while paging?
Can i store that query inside the database as procedure or function? If I do like that it may perform well i think?

Hans_vd

<blockquote class='ipsBlockquote' >The query that what i posted in this topic taking lot of time to execute. 
What is the problem?</blockquote>
Performance tuning is a complex process. 
Take a look at your query. It's huge. 
Without knowing your datamodel.. without knowing the data itself.. without knowing what indexes are in place.. without knowing what you're trying to achieve.. without knowing your business.. ... not a chance that anyone can help you speeding up this query. 
 
But apart from that I have two tips (which may or may not improve performance, I really can't say for sure): 
- Do you have indexes on comp_code in the base tables you are querying? 
- In the three inner queries that are in the query that is aliased as 'D', the group by parts and the summing can disappear as you are already doing that in the outer query 
 
<blockquote class='ipsBlockquote' >If it fetching thousands of records why it is not fetching them directly from database while paging?</blockquote>
It does fetch the records directly from database.. as soon as the first records is found. But in a query as complex as yours it might take a while before the database comes back with the first record. 
 
Storing the query in the database might make it a little bit faster, but I don't think you'll notice the difference. 
 
Regards 
Hans

dsrikanth

Thanks for the reply Hans_vd, 
 
I have a simple query 
<pre class='_prettyXprint _lang-auto _linenums:0'>select cat_code, SGRPCODE,SUB_SGRPCODE,COST_PRICE from table1</pre>
 
 
how to get cat_desc from category_master where above query cat_code = master table cat_code 
 
By that i can create a report like 
 
cat_code || cat_desc || sgrpcode || sub_sgrpcode || cast_price 
 
Is it required to join two tables to get cat_desc value. Is it possible to get that value while designing the report by binding the category_master dataset. 
 
Or is it possible to get that cat_desc value by calling the database function which takes the cat_code value and returns respected cat_desc

Hans_vd

<blockquote class='ipsBlockquote' >Is it required to join two tables to get cat_desc value</blockquote>No, but it is the best way to do it. 
 
<blockquote class='ipsBlockquote' >Is it possible to get that value while designing the report by binding the category_master dataset</blockquote>
I don't know what you mean by that. 
 
<blockquote class='ipsBlockquote' >Or is it possible to get that cat_desc value by calling the database function which takes the cat_code value and returns respected cat_desc</blockquote>
If you do it in your query: yes, it's possible. Your query would then look like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>select cat_code, function_to_get_cat_desc (cat_code) as cat_desc, SGRPCODE,SUB_SGRPCODE,COST_PRICE from table1</pre>
But if you're selecting a lot of records, this might have an impact on performance. So joining the tables would be the best way. Something like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>select table1.cat_code, table2.cat_desc, table1.SGRPCODE,table1.SUB_SGRPCODE,table1.COST_PRICE
from table1, table2
where table1.cat_code = table2.cat_code
</pre>
 
Regards 
Hans

dsrikanth

For Second Quote 
 
In my query i got 30 as code for that query can i get description by calling a dataset which returns description for the associated code. 
 
 
Or i have a function which returns description for the code like 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>BEGIN
SELECT UNIT_NAME FROM UNIT_MST
WHERE COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE
AND UNIT_CODE=:UNIT_CODE;
EXCEPTION WHEN OTHERS THEN NULL;
END;</pre>
 
How to call this function through birt. data of pallete window.

Hans_vd

You can't use a dataset with parameters that are filled with values from the outer table
You would have to create a dataset that selects all unit_name's and create a filter to bind it with the outer table. That is, as I mentioned earlier, not the most efficient way to do this.

Do you have any specific reason to do it that way? Why don't you just build a query that joins both tables together?

Off topic: "EXCEPTION WHEN OTHERS THEN NULL;" is very very very bad practice. This will hide whatever exception occurs and you'll never know it happened. If you are trying to avoid TOO_MAY_ROWS or NO_DATA_FOUND exceptions, then do "EXCEPTION WHEN NO_DATA_FOUND THEN NULL" or "EXCEPTION WHEN TOO_MAY_ROWS THEN NULL".

Regards
Hans

dsrikanth

I want to call a procedure to calculate the rate of the material by passing the some of parameters which are resulting values of query. How to do it? 
 
My procedure is: 
<pre class='_prettyXprint _lang-auto _linenums:0'>BEGIN
SELECT DISTINCT RATE FROM inv_daily_stk
WHERE COMP_CODE=[img]http://www.birt-exchange.org/org/forum/public/style_emoticons/<#EMO_DIR#>/tongue.gif[/img]_COMP_CODE
AND MATCODE=:MATCODE
AND NVL(ATTRIB1,0)=NVL(:ATTRIB1,0)
AND NVL(ATTRIB2,0)=NVL(:ATTRIB2,0);
END;</pre>
 
Here : p_COMP_CODE, :ATTRIB1, :ATTRIB2 are the values of resulting query. I need to place that rate along with these parameters.