13 Replies Latest reply: May 13, 2013 3:38 PM by user7967487 RSS

    Cross tab with BI Plug-in

    180271
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                Ok, got it. Thank you so much for your help!
                                • 13. Re: Cross tab with BI Plug-in
                                  user7967487
                                  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.