Issue with data output in Bi publisher using pivot tables — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Issue with data output in Bi publisher using pivot tables

Received Response
394
Views
4
Comments

Summary

Issue with data output in Bi publisher using pivot tables

Content

Currently, I have created a pivot table using RTF template in using BI Publisher. During run time pivot table can generate 10 columns or more columns based on data stored in the table  In the pdf output report, the end user needs only 4 columns per page in pdf output. I have been able to achieve that. But, the user also wants the data to be completely published for the first 4 columns and then it should start with the remaining new columns in subsequent pages  and so on. Please note that in the cross tab layout I have one row column which remains static. 

But, during my testing it displays the first 4 columns in 1st page and the next 4 columns in the 2nd page and so on. It alternates till it reach the end of columns in the pivot table.

Please advise which XML script or option in BI Publisher will help me achieve the above requirement.

For ex. if data in pivot table is rendered in below format

Row0 Col1 Col2 Col3 Col4 Col5 Col6 Col7

ph        ab    cd      ef    gh      ij      kl    mn

cell       tt     gg     ff     qq      ll      pp   qq

..............till 100 rows

I want output as

Row0 Col1 Col2 Col3 Col4

ph        ab    cd    ef      gh

cell       tt     gg   ff       qq

..............till 100 rows

Then it should publish

Row0 Col5 Col6 Col7

ph        ij      kl     mn

cell       ll      pp    qq

..............till 100 rows

and so on.

Answers

  • Venkata Allam-Oracle
    Venkata Allam-Oracle Rank 4 - Community Specialist

    We have a feature to achieve fixed no.of rows per page using RTF in BIP. I don't think so, we achieve fixed no.of columns using pivot table.

    These blogs might help you.

    http://bipublisher.blogspot.in/2009/06/bi-publisher-conditionally-limiting.html

    https://blogs.oracle.com/xmlpublisher/entry/row_filler

    https://blogs.oracle.com/xmlpublisher/entry/anatomy_of_a_template_i_fixed

    Regards,

    Venkat

  • Ravi Chamarty
    Ravi Chamarty Rank 4 - Community Specialist

    Is there some kind of logic for the columns like given below in XSL/XML coding?  Step1: I can declare a variable. Step2: The value will increment by 1. Step 3: Use mod function divide by 4 for fetching 4 columns at a time. Step 4: In this step it should loop through the data for the first 4 columns till end of data Step 5: repeat the step for fetching the next 4 columns Step 6: loop through the data for the next set of 4 columns till end of data step 7: end of inner loop and end of outer loop step 8: exit My thoughts -- 1)Columns per page: Define 'column counter','row counter' variable in a form field

    2) Increment counter for each column (again in a form field)

    3) Break if the number of columns is reached (say 4 rows in this example)

    1)Columns per page: Define column counter variable in a form field

    2) Increment counter for each column (again in a form field)

    3) Break if the number of columns is reached (say 4 rows in this example)

                  

        for looping through the data <?end if?

  • Ravi Chamarty
    Ravi Chamarty Rank 4 - Community Specialist

    Hi,

    I needed the report output in PDF format. So, though the pivot table rendered 13 columns, I achieved 4 columns per page just by a adjusting the column width in the RTF template. So, am good with that.

              The actual requirement here is it should publish the data for the first 4 columns from the beginning row till the last row in subsequent pages before proceeding to display the next 4 columns and so on.But, the current behavior of BI Publisher is if there are 13 columns, first page displays first 4 columns(along with the data that fits the page) and in the second page the next 4 columns are shown(along with data that fits the page) and the next 4 columns, this behavior repeats till it reaches the 13th column. Then again the columns repeat till we reach  the end of the data. User doesn't not want this. How to achieve this functionality?

               horizontal-line-break feature I already explored and it doesn't fit my requirement

  • Ravi Chamarty
    Ravi Chamarty Rank 4 - Community Specialist

    I declared a variable outside the pivot table and initialized it to 0.

    The, in the pivot section defined the below if condition before the for-each clause. Then, it restricts the columns to only 4. So far so good.

    bit for the grid where data is published am unable to pass this condition to display the data for the above selected 4 columns. Though in column it restricts only to 4 in the pivoting, in the grid it is still showing all the data for all the columns. Below is the condition I tried hoping it will show on the specific data.

    Alas! ,not working and again trying further.