oraclereports - oracle matrix report filling null cells -


i have made matrix report in oracle report builder this

enter image description here

and here query report being calling

select a.p_date,        l.sup_name,        decode(a.perc_typ, 1, 'buff',                           2, 'cow')     perc_type,        a.sup_rate                       rate,        decode(a.perc_typ,        1, round(( nvl(a.fat_perc, 0) * nvl(a.gross_vol, 0) ) / 6, 5),        2, round(           ( nvl(a.fat_perc, 0) + (             ( nvl(a.fat_perc, 0) * 0.22 ) + (             nvl(a.lr_perc, 0) * 0.25 ) + 0.72 ) ) *           nvl(a.gross_vol, 0) / 13, 5)) vol   mlk_purchase a,        supplier l  a.sup_cod = l.sup_cod        , a.p_date <= trunc(sysdate)        , a.p_date >= trunc(sysdate) - 7 order  1

problem there are showing empty cells no data coming query. want show 0 cells instead of empty space. there way in oracle report builder.

there @ least 2 solutions.

solution 1 -- in oracle reports, create boilerplate text object displays zero, , arrange object displays behind matrix field. way, boilerplate hidden when field displayed, revealed when field not displayed. solution described in documentation.

solution 2 -- rewrite query return rows 0 values combinations of row , column fields have no data. example, might find possible combinations of matrix row , column fields (supplier , date in case), outer join data combinations, , use nvl convert null values zeroes. might this:

select     l.p_date,     l.sup_name,     decode(a.perc_typ, 1, 'buff', 2, 'cow') perc_type,     a.sup_rate rate,     nvl     (         decode         (             a.perc_typ,              1,              round             (                 (nvl(a.fat_perc, 0) * nvl(a.gross_vol, 0)) / 6,                 5             ),             2,             round             (                 (nvl(a.fat_perc, 0) +                      (                         (nvl(a.fat_perc, 0) * 0.22) +                          (nvl(a.lr_perc, 0) * 0.25) + 0.72)                     ) * nvl(a.gross_vol, 0) / 13,                 5             )         ),         0     ) vol     mlk_purchase a,     (         select             l1.sup_code,             l1.sup_name,             l2.p_date                     (                 select distinct                      supplier.sup_code,                      supplier.sup_name                                       supplier             ) l1,             (                 select distinct                     mlk_purchase.p_date                                     mlk_purchase                                     mlk_purchase.p_date <= trunc(sysdate)                     ,                     mlk_purchase.p_date >= trunc(sysdate) - 7             ) l2     ) l      a.sup_cod (+) = l.sup_cod     ,     a.p_date (+) = l.p_date order     1

a more efficient (and simpler) way rewrite query same effect might use partitioned outer join between mlk_purchase , supplier partitions sup_code, don't know extent version of oracle reports supports syntax.


Comments

Post a Comment

Popular posts from this blog

ios - iPhone/iPad different view orientations in different views , and apple approval process -

i force app's home screen portrait , other views landscape. apple rejects app if this? if not , how force first screen launch in portrait mode? , force other screens launch in landscape. this not duplicate question search every possible answer in website. i force app's home screen portrait , other views landscape. apple rejects app if this? no apple not reject app this. have lot of apps doesn't support orientation. forcing on 1 screen won't cause app reject. if not , how force first screen launch in portrait mode? , force other screens launch in landscape. can on write : -(nsuinteger)supportedinterfaceorientations; function.
Read more

java Extracting Zip file -

i'm looking way extract zip file. far have tried java.util.zip , org.apache.commons.compress, both gave corrupted output. basically, input zip file contain 1 single .doc file. java.util.zip: output corrupted. org.apache.commons.compress: output blank file, 2 mb size. so far commercial software winrar work perfectly. there java library make use of this? this method using java.util library: public void extractzipnative(file filezip) { zipinputstream zis; stringbuilder sb; try { zis = new zipinputstream(new fileinputstream(filezip)); zipentry ze = zis.getnextentry(); byte[] buffer = new byte[(int) ze.getsize()]; fileoutputstream fos = new fileoutputstream(this.tempfolderpath+ze.getname()); int len; while ((len=zis.read(buffer))>0) { fos.write(buffer); } fos.flush(); fos.close(); } catch (filenotfoundexception e) { // todo auto-generated catch block ...
Read more

C# WinForm - loading screen -

i ask how make loading screen (just picture or something) appears while program being loaded, , disappears when program has finished loading. in fancier versions, have seen process bar (%) displayed. how can have that, , how calculate % show on it? i know there form_load() event, not see form_loaded() event, or % property / attribute anywhere. all need create 1 form splash screen , show before main start showing landing page , close splash once landing page loaded. class splashform { //delegate cross thread call close private delegate void closedelegate(); //the type of form displayed splash screen. private static splashform splashform; static public void showsplashscreen() { // make sure launched once. if (splashform != null) return; thread thread = new thread(new threadstart(splashform.showform)); thread.isbackground = true; thread.setapartmentstate(apartmentstate.sta); thread....
Read more