1 2 Previous Next 17 Replies Latest reply: May 17, 2013 1:42 PM by 1009634 RSS

    Cross tab reports

    Vishalaksha
      Hi
      I have a unique requirement. For a particular part number I have quantity available date wise. Now I also want to show sum of quantities on monthly basis and half yearly too. Below is the required output. I have tried with different grouping methods but I am not able to get it. The whole data is coming from a single table.

      Heading...*Jan Allotment*...4-Jan...11-Jan...*Feb Allotment*...1-Fen...8-Feb......*1H2010 Total*....
      ABCD................9...............5.........4...............7.............3.........4.............16......................
      CDEF................11..............6.........5...............10...........5.........5.............21......................
      EFGH................9...............5.........4...............7.............3..........4............16......................

      I can generate the data without monthly or half yearly grouping .
      Anyone please guide me how to proceed.

      Below is the sample XML data

      - <T03405180>
      - <LIST_G_BUILD_HALF_YEAR>
      - <G_BUILD_HALF_YEAR>
      <BUILD_HALF_YEAR>2</BUILD_HALF_YEAR>
      <BUILD_MONTH>Dec</BUILD_MONTH>
      <BUILD_MONTH_NUM>12</BUILD_MONTH_NUM>
      <BUILD_DATE>09-DEC-09</BUILD_DATE>
      <MDL>PC01500</MDL>
      <QTY>14</QTY>
      </G_BUILD_HALF_YEAR>
      - <G_BUILD_HALF_YEAR>
      <BUILD_HALF_YEAR>2</BUILD_HALF_YEAR>
      <BUILD_MONTH>Dec</BUILD_MONTH>
      <BUILD_MONTH_NUM>12</BUILD_MONTH_NUM>
      <BUILD_DATE>10-DEC-09</BUILD_DATE>
      <MDL>MA02500</MDL>
      <QTY>2</QTY>
      </G_BUILD_HALF_YEAR>
      -----------------------
      -----------------------

      Thanks
      Vishalaksha
        • 1. Re: Cross tab reports
          524753
          Data should support what you are askign for.

          if we can see the full data, then we can derive the logic for this.

          drop me the xml and expected output to my id in profile.
          • 2. Re: Cross tab reports
            Vishalaksha
            I have sent you the sample data and desired format to your id.
            Please let me know if you need other information.

            Thanks
            Vishalaksha
            • 3. Re: Cross tab report
              Vishalaksha
              Hi Vetsrini,

              Please share if you are successfull in deriving the logic.....

              I have tried with different options but I am not able to get the expected format.

              Thanks
              Vishalaksha
              • 4. Re: Cross tab reports
                Vishalaksha
                Hi,

                Any luck on this...

                Please guide me in some direction to resolve this issue.

                Thanks
                Vishalaksha
                • 5. Re: Cross tab reports
                  524753
                  Hi Vishalaksha,

                  Sorry for the delay, i worked on it and thought i sent it to you.
                  Just sent across {the working sample} to you,

                  try building on that, there are other ways to do this too, but this will be handy and simple.
                  • 6. Re: Cross tab reports
                    Vishalaksha
                    Hi Vetri,

                    I got below error while getting the output in EXCEL format.
                    __________________________________________________________________________
                    Font Dir: C:\Program Files\Oracle\XML Publisher\Template Builder\fonts
                    Run XDO Start
                    setLocale: en
                    java.lang.reflect.InvocationTargetException
                         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                         at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
                         at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
                         at java.lang.reflect.Method.invoke(Unknown Source)
                         at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(XSLT10gR1.java:580)
                         at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:378)
                         at oracle.apps.xdo.common.xml.XSLT10gR1.transform(XSLT10gR1.java:197)
                         at oracle.apps.xdo.common.xml.XSLTWrapper.transform(XSLTWrapper.java:156)
                         at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:916)
                         at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:178)
                         at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1507)
                         at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:913)
                         at RTF2PDF.runRTFto(RTF2PDF.java:535)
                         at RTF2PDF.runXDO(RTF2PDF.java:423)
                         at RTF2PDF.main(RTF2PDF.java:217)
                    Caused by: oracle.xdo.parser.v2.XPathException: Variable not defined: 'G'.
                         at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1526)
                         at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:517)
                         at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:485)
                         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:264)
                         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:150)
                         at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:187)
                         ... 15 more

                    ________________________________________________________________

                    Below are the cell formats provided by you.

                    Cell 1,1: Determination<?horizontal-break-table:1?> <?variable@incontext:G; /T03405180/LIST_G_BUILD_HALF_YEAR/G_BUILD_HALF_YEAR?>

                    Cell 1,2: JAN Total
                    Cell 1,3: <?for-each-group@column: $G[substring(BUILD_DATE,4,3)=’JAN’];BUILD_DATE?><?BUILD_DATE?> <?end for-each-group?>

                    Cell 1,4: FEB Total
                    Cell 1,5: <?for-each-group@column: $G[substring(BUILD_DATE,4,3)=’FEB’];BUILD_DATE?><?BUILD_DATE?> <?end for-each-group?>

                    Cell 2,1: <?for-each-group:/T03405180/LIST_G_BUILD_HALF_YEAR/G_BUILD_HALF_YEAR;./MDL?> <?variable@incontext:G1;current-group()?><?MDL?>

                    Cell 2,2: <?xdoxslt:sum($G1[substring(BUILD_DATE,4,3)=’JAN’]/QTY)?>
                    Cell 2,3: <?for-each-group@cell:$G[substring(BUILD_DATE,4,3)=’JAN’];BUILD_DATE?><?sum($G1[./BUILD_DATE = current-group()/BUILD_DATE and substring(BUILD_DATE,4,3)=’JAN’]/QTY)?>
                    <?end for-each-group?>

                    Cell 2,4: <?sum($G1[substring(BUILD_DATE,4,3)=’FEB’]/QTY)?>

                    Cell 2,5<?for-each-group@cell:$G[substring(BUILD_DATE,4,3)=’FEB’];BUILD_DATE?><?sum($G1[./BUILD_DATE = current-group()/BUILD_DATE and substring(BUILD_DATE,4,3)=’FEB’]/QTY)?>
                    <?end for-each-group?> <?end for-each-group?>


                    Please help me out in finding this error.



                    Thanks
                    Vishalaksha
                    • 7. Re: Cross tab reports
                      Vishalaksha
                      Hi Vetri,

                      It is working fine when I am attaching it to XML publisher resposibility as preview data. Thanks a lot for providing me the template. But still I am not getting the expected output as I need grouping on half yearly basis also.
                      I will work on this to get the final output. Please provide me help as you always do for all.

                      Thanks
                      Vishalaksha
                      • 8. Re: Cross tab reports
                        524753
                        Hi Vishalaksha,

                        Sent updated sample,with half year logic.
                        • 9. Re: Cross tab reports
                          Vishalaksha
                          Thanks a lot Vetri.
                          You are a genius in BI Publisher. This is working fine now.

                          I found this forum very useful which offers the interaction with experts.

                          Thanks
                          Vishalaksha
                          • 10. Re: Cross tab reports
                            Vishalaksha
                            Hi Vetri,

                            I was testing it for different data set and I came across two issues

                            1. I found that this will work only if data belongs to a single year. In case if xml has data for same month of different years then it will not show the correct sum on half yearly basis.

                            eg. 11-Dec-2009 and 11-Dec-2010 both belongs to the second part of the year so in output they will be summed up instead of showing on different column.

                            2. In some places there are some XPATH notations like "/T03405180/LIST_G_BUILD_HALF_YEAR/G_BUILD_HALF_YEAR?>" and the problem is that the first node T03405180 might not be fixed. There is a possibility that it will get changed.

                            Please help me....

                            Thanks
                            Vishalaksha
                            • 11. Re: Cross tab reports
                              Vishalaksha
                              Hi Vetri,

                              I have sent you the new XML data and the template.

                              Thanks
                              Vishalaksha
                              • 12. Re: Cross tab reports
                                Vishalaksha
                                Hi Vetri,

                                Did you get some logic for half year calculation....It was actually summing the quantities of same month of different years.

                                Looking forward to haring from you soon... :)

                                Thanks
                                Vishalaksha
                                • 13. Re: Cross tab reports
                                  524753
                                  Sent you the complete working template :)
                                  • 14. Re: Cross tab reports
                                    Vishalaksha
                                    Thanks a lot.....This is really awesome and working fine for me.....

                                    Thanks
                                    Vishalaksha
                                    1 2 Previous Next