Search This Blog

Tuesday, December 28, 2010

Matrix Report in XML Publisher

This report is number of JVs created per month per source wise.

Query:
select je_source,to_char(date_created,'Mon-YY') Source_Month,count(je_header_id) je_count from gl_je_headerswhere to_char(date_created,'Q') = :p_quarter and to_char(date_created,'YYYY') = :p_year group
 by
je_source,to_char(date_created,'Mon-YY')
RDF report:
    
Total Number of JVs Quarterly


Source Name
GSource_MonthE
Total
GJE_SOURCE
G If exists: JE_COUNT end; E
999E
Total
G If exists: 999 end; E

O               G Close to JE_SOURCE - <?for-each-   group@section:G_SERVICE_NAME;./JE_SOURCE?><?sort:JE_SOURCE;'ascending';data-type='text'?>
 JE_SOURCE - <?JE_SOURCE?><?variable@incontext:JS;JE_SOURCE?>
      
 G close to Source_Month :
<?for-each-group@column:G_SERVICE_NAME;./SOURCE_MONTH?>
Source_Month - <?SOURCE_MONTH?>

E  - <?end for-each-group?>
G Near If Exists -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?
IF Exists –
<?if:count(current-group()[JE_SOURCE=$JS])?>

JE_COUNT - <?current-group()[JE_SOURCE=$JS]/JE_COUNT?> 
End; - <?end if?>

E  - <?end for-each-group?>
G in Total -  <?for-each-group@cell://G_SERVICE_NAME;./SOURCE_MONTH?

IF Exists in Total –
<?if:count(current-group())?>
999 in Total
<?sum(current-group()/JE_COUNT)?>
End if in Total:
<?end if?>
E in Total –
<?end for-each-group?>

999 in Total Column - <?sum(//G_SERVICE_NAME[JE_SOURCE=$JS]/JE_COUNT)?>
E in Total Column:
<?end for-each-group?>


2 comments:

  1. Hello. I am creating a similar matrix report but I need the report to create a page break every 10 lines and have the header be present for every page. Can you please help me with this? Appreciate your response.

    ReplyDelete
    Replies
    1. Ninjovee,

      Please check http://bipublisher.blogspot.com/2009/06/bi-publisher-conditionally-limiting.html

      He has put one example there. Download and check if it helps you.

      Cheersss...
      Sandip

      Delete