This discussion is archived
13 Replies Latest reply: May 13, 2013 1:38 PM by user7967487 RSS

Cross tab with BI Plug-in

180271 Newbie
Currently Being Moderated
I am trying to do a cross-tab report with the BI plugin (see sample XML below).
I need 'Student Name' down, 'Period' across and the 'Name' filled into the appropriate spot.
When I use the plug-in, select pivot table, and pick the fields, it creates the report... but it is making
the 'Name' field a number and showing garbage. I need it to be a text field so the 'name' data shows in
the appropriate spot of the table. What am I missing?


- <ROWSET2>
- <ROWSET2_ROW>
<GROUP_FIELD>857393303P</GROUP_FIELD>
<STUDENT_NAME>Farley, Charlie</STUDENT_NAME>
<PERIOD>09</PERIOD>
<NAME>10th Grd Phys Ed and Health</NAME>
</ROWSET2_ROW>
- <ROWSET2_ROW>
<GROUP_FIELD>857393303P</GROUP_FIELD>
<STUDENT_NAME>Farley, Charlie</STUDENT_NAME>
<PERIOD>09</PERIOD>
<NAME>10th Grd Phys Ed and Health</NAME>
</ROWSET2_ROW>
  • 1. Re: Cross tab with BI Plug-in
    734422 Newbie
    Currently Being Moderated
    There is a good example in the documentation.
    Please check this out:
    http://download.oracle.com/docs/cd/E12844_01/doc/bip.1013/e12187/T421739T481157.htm#4535399

    regards

    Jorge A.
  • 2. Re: Cross tab with BI Plug-in
    673776 Newbie
    Currently Being Moderated
    Thank you very much for the suggestion. I have seen that documentation and it provides good instructions. We have a cross tab report that is displaying incorrectly. Would like data to display down a column instead of across.
  • 3. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    I had looked at that example before, but it is doing a sum ( <?sum(current-group()//SALES)?> ) for the cell. When I build it in BI plug-in, it does the same thing. My cross-tab is not based on sums or counts.

    ie. employees down the page, months across the page and the appropriate Job Name in the cells

    ______SEP_______OCT______NOV
    BOB___job1______job2______job3
    TIM___job4______job5______job10
    JOE___job7______job8______job9

    How do you do that?
  • 4. Re: Cross tab with BI Plug-in
    524753 Guru
    Currently Being Moderated
    Send me the xml and sample output file.

    or
    have a look at these.

    http://winrichman.blogspot.com/search/label/cross%20tab
  • 5. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    Thanks, but I don't see any samples on your blog that match. Does the BI plug-in not support crosstab with cell data (not numeric summaries)? Every time I try to create a simple matrix, it puts the sum in the middle cells and I just want the actual value. Anybody have a simple example?
  • 6. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    This is what the plug-in created for the pivot table. My across (PERIOD) and down (DEPARTMENT_DESC) are correct.
    I need to have the middle cell be the value of my 'CLASS_CODE' field (not sum etc.). The pivot table only lets you pick sum, count, average.


    C H___________________________G PERIODE

    G DEPARTMENT_DESC_______________X 999E E



    My 'C' field in the word editor is set to:
    <?crosstab:c594;"//ROWSET2_ROW";"DEPARTMENT_DESC{,o=a,t=t}";"PERIOD{,o=a,t=t}";"CLASS_CODE";"sum"?>

    My 'H' field is set to: <?horizontal-break-table:1?>

    My 'X' is set to: <?for-each@cell:.//R1C1?>

    My '999' is set to: <?./M1?>

    Anybody?

    Edited by: bobmagan on Nov 18, 2009 5:06 AM
  • 7. Re: Cross tab with BI Plug-in
    673776 Newbie
    Currently Being Moderated
    There are some great examples on this page:

    http://apps2fusion.com/at/kr/345-matrix-cross-tab-report-bi-publisher

    These you can set up manually, which might be easier than the plug-in. However, if you use the plugin, delete the summary row if you don't want the calcs.
  • 8. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    Thanks, but still isn't what I am looking for. I am not using summaries, but I need my center cells to show character data (not sums, counts). Similar to below:

    _________01______02_______03________04
    Name1___ABC_____SSS______NNN_______123
    Name2___XYZ_____XXX______BBB_______ABX
    Name3___123_____123______CCC_______ZZZ


    C H___________________________G PERIODE

    G DEPARTMENT_DESC_______________X 999E E
    C H___________________________G PERIODE

    G DEPARTMENT_DESC_______________X 999E E

    My XML is like this:
    <?xml version="1.0" encoding="UTF-8" ?>
    - <DATA>
    - <ROWSET1>
    - <ROWSET1_ROW>
    <PERIOD_LABEL>Period</PERIOD_LABEL>
    <TEACHER_LABEL>Teacher</TEACHER_LABEL>
    </ROWSET1_ROW>
    </ROWSET1>
    - <ROWSET2>
    - <ROWSET2_ROW>
    <DEPARTMENT_DESC>Mathematics</DEPARTMENT_DESC>
    <STAFF_NAME>Baker, Ned</STAFF_NAME>
    <PERIOD>01</PERIOD>
    <CLASS_CODE>316CHS</CLASS_CODE>
    <SHORT_NAME>Geometry</SHORT_NAME>
    <SECTION>001</SECTION>
    <ROOM>100</ROOM>
    <ROOM_LOCATION>First floor</ROOM_LOCATION>
    <ASSIGNED_SEATS>0</ASSIGNED_SEATS>
    <BEGIN_SEATS>30</BEGIN_SEATS>
    </ROWSET2_ROW>
    - <ROWSET2_ROW>
    <DEPARTMENT_DESC>Physical Education and Health</DEPARTMENT_DESC>
    <STAFF_NAME>Baker, Ned</STAFF_NAME>
    <PERIOD>09</PERIOD>
    <CLASS_CODE>919CHS</CLASS_CODE>
    <SHORT_NAME>PE10/Healt</SHORT_NAME>
    <SECTION>002</SECTION>
    <ROOM>GYM-W</ROOM>
    <ROOM_LOCATION>West Wing Gymnasium</ROOM_LOCATION>
    <ASSIGNED_SEATS>2</ASSIGNED_SEATS>
    <BEGIN_SEATS>25</BEGIN_SEATS>
    </ROWSET2_ROW>




    My 'C' field in the word editor is set to:
    <?crosstab:c594;"//ROWSET2_ROW";"DEPARTMENT_DESC{,o=a,t=t}";"PERIOD{,o=a,t=t}";"CLASS_CODE";"sum"?>
  • 9. Re: Cross tab with BI Plug-in
    673776 Newbie
    Currently Being Moderated
    The examples on that site are correct you just have to remove the summary row if you don't want that. Creating these manually is easier than the plug-in in most cases. Hopefully this helps:

    Create a two row, two column table in Word.

    Column 1 Row 1 Cell 1: <?horizontal-break-table:1?>
    Column 2 Row 1 Cell 2: <?for-each-group@column:current-group();PERIOD?><?PERIOD?><?end for-each-group?>
    Column 1 Row 2 Cell 1: <?for-each-group:current-group();./STAFF_NAME?><?STAFF_NAME?>
    Column 2 Row 2 Cell 2: <?for-each-group@cell:current-group();CLASS_CODE?> <?CLASS_CODE?><?end for-each-group?><?end for-each-group?>


    Note: Two end for each group tags in the last cell.
  • 10. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    Thanks, that helped some. I created the 2X2 as you described. I was getting an error when using 'current-group' so
    I had to use this:
    <?for-each-group@column:ROWSET2_ROW;PERIOD?>

    I have 2 issuues:
    1) My middle cells are not showing in the appropriate 'PERIOD' column.

    For example 'Name2' only has info for period '04', but its showing in the period '01' columns

    2) If the 'Name' doesn't have any detail info for the specific 'PERIOD' column, it doesn't draw a blank box

    See my sample below.
    ________PERIOD
    NAME____01______02_______03________04
    Name1___01
    Name2___04
    Name3___01______02________

    Am I missing something?
  • 11. Re: Cross tab with BI Plug-in
    673776 Newbie
    Currently Being Moderated
    On this web page: http://apps2fusion.com/at/kr/345-matrix-cross-tab-report-bi-publisher is a paragraph that reads: "But the output we get using the template mentioned in the user guide is not proper, we didn’t have the quarter sales results for the year 2004 but the value 1500 which has to be shown under 2003 column is actually shown under 2004." This section may resolve that issue. There is a sample "template2.rtf" file to download.

    This may also help you: http://winrichman.blogspot.com/2009/03/crosstab-issue.html. This shows you how to display columns that might ordinarily be skipped because of zero or null data.

    Hope this helps.
  • 12. Re: Cross tab with BI Plug-in
    180271 Newbie
    Currently Being Moderated
    Ok, got it. Thank you so much for your help!
  • 13. Re: Cross tab with BI Plug-in
    user7967487 Explorer
    Currently Being Moderated
    Can you post the final solution (XML tag syntax)? I tried starting with the Pivot Table builder and then changing the tags from there, but that got messy. I saw the earlier posts in this thread on the required XML tags, but then it seems some changes were needed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points