0

I've an XML file that comes from the DB stored proc and from that I need to generate a PDF using XSL FO.Net with Grouping as shown in below image. Can anyone please guide me how to group the columns and the grouped column should not come in the HEADER columns.

enter image description here

Like that i can have 2 levels of grouping. I'm using the below XML and XSL FO to generate it.But not able to group by columns.

<PdfPrinter>
    <Reports>
    <Header>
    <PrintedDate>2016-07-01T15:16:09.473</PrintedDate>
    <PrintedBy>Pavan</PrintedBy>
    </Header>
    <Report>
    <Name>S</Name>
    <UserName />
    <Remarks />
    <IPAddress>192.168.1.83</IPAddress>
    <DateCreated>2015-10-07T17:48:35.243</DateCreated>
    <AppID>OS</AppID>
    <AppVersion>0.0.0.2</AppVersion>
    <LoginDate>2015-10-07T17:48:05.380</LoginDate>
    <LogoutDate>1900-01-01T00:00:00</LogoutDate>
    </Report>
    <Report>
    <Name>S</Name>
    <UserName />
    <Remarks />
    <IPAddress>192.168.1.83</IPAddress>
    <DateCreated>2015-10-07T17:49:36.107</DateCreated>
    <AppID>OS</AppID>
    <AppVersion>0.0.0.2</AppVersion>
    <LoginDate>2015-10-07T17:49:26.287</LoginDate>
    <LogoutDate>1900-01-01T00:00:00</LogoutDate>
    </Report>
    </Reports>
</PdfPrinter>

XSLT:

    <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:ext="http://exslt.org/common"
xmlns:utilityExtension="pdfprinter:extensions:utility"
exclude-result-prefixes="msxsl utilityExtension">
  <!--<xsl:output method="xml" indent="yes" omit-xml-declaration="yes" encoding="utf-8"/>-->
  <xsl:template match="/">

    <xsl:variable name="columnFontSize">12pt</xsl:variable>
    <xsl:variable name="columnFontType">Helvetica</xsl:variable>

    <xsl:variable name="headerPrintedBy">Printed By</xsl:variable>
    <xsl:variable name="headerPrintedDate">Printed Date</xsl:variable>
    <xsl:variable name="headerReportID">Report ID</xsl:variable>
    <xsl:variable name="logo" select="utilityExtension:MapPath('~/App_Data/Resources/IMAGES/logo.jpg')"/>

    <fo:root xmlns:fo="http://www.w3.org/1999/XSL/Format">
      <!-- defines the layout master -->
      <fo:layout-master-set>
        <fo:simple-page-master master-name="all-pages" page-width="1600pt" page-height="1190pt">
          <fo:region-body region-name="xsl-region-body" column-gap="0.250in" margin="0.7in" margin-left="0.7in"/>
          <fo:region-before region-name="xsl-region-before" display-align="after" extent="0.700in" />
          <fo:region-after region-name="xsl-region-after" extent="0.700in"/>
        </fo:simple-page-master>
        <fo:page-sequence-master master-name="default-sequence">
          <fo:repeatable-page-master-reference master-reference="all-pages"/>
        </fo:page-sequence-master>
      </fo:layout-master-set>
      <!-- starts actual layout -->
      <fo:page-sequence master-reference="default-sequence">
        <fo:static-content border-bottom-width="1pt" border-bottom-style="solid" border-bottom-color="rgb(192,192,192)" flow-name="xsl-region-before" font-size="10pt" font-family="Helvetica">
          <fo:block color="rgb(105,105,105)" text-align-last="justify">

          </fo:block>
        </fo:static-content>
        <fo:static-content border-top-width="1pt" width="1000pt" border-top-style="solid" border-top-color="rgb(192,192,192)" padding-top="2pt" flow-name="xsl-region-after" font-size="10pt" font-family="Helvetica">
          <fo:table table-layout="fixed" width="500pt">
            <fo:table-column column-width="proportional-column-width(4)"/>
            <fo:table-column column-width="proportional-column-width(1)"/>
            <fo:table-body>
              <fo:table-row>
                <fo:table-cell>
                  <fo:block text-align="right" color="rgb(105,105,105)">&#160;</fo:block>
                </fo:table-cell>
              </fo:table-row>
            </fo:table-body>
          </fo:table>
        </fo:static-content>
        <fo:flow flow-name="xsl-region-body" font-size="10pt" font-family="Helvetica">
          <fo:block>
            <fo:table border-collapse="collapse" width="1200pt"  table-layout="auto" margin-top="-0.002in">
              <fo:table-column column-width="proportional-column-width(55)" column-number="1"/>
              <fo:table-column column-width="proportional-column-width(45)" column-number="2"/>
              <fo:table-body>
                <fo:table-row>
                  <fo:table-cell>
                    <fo:block color="rgb(0,0,128)" font-size="16pt" font-weight="bold">&#160;</fo:block>
                    <fo:block color="rgb(0,0,128)" font-weight="bold"></fo:block>
                    <fo:block color="rgb(0,0,0)" font-weight="bold" font-family="{$columnFontType}" font-size="{$columnFontSize}">
                      <fo:external-graphic src="{$logo}"/>&#160;&#160;&#160;&#160;
                      <fo:block>
                        <xsl:value-of select="$headerOrgName"/>
                      </fo:block>
                    </fo:block>
                    <fo:block color="rgb(0,0,128)" font-weight="bold">&#160;&#160;&#160;&#160;</fo:block>
                    <fo:block color="rgb(0,0,0)" font-weight="bold" font-family="{$columnFontType}" font-size="{$columnFontSize}">
                      <xsl:value-of select="$headerReportID"/>
                    </fo:block>
                    <fo:block>
                      <xsl:value-of select="/PdfPrinter/Reports/Header/ReportID" />
                    </fo:block>
                  </fo:table-cell>
                  <fo:table-cell>
                    <fo:block text-align="left">
                      <fo:inline font-weight="bold" font-family="{$columnFontType}" font-size="{$columnFontSize}">
                        <xsl:value-of select="$headerPrintedBy"/>  :
                      </fo:inline>
                      <fo:block>
                        <xsl:value-of select="/PdfPrinter/Reports/Header/PrintedBy" />
                      </fo:block>
                    </fo:block>
                    <fo:block text-align="left">
                      <fo:inline font-weight="bold" font-family="{$columnFontType}" font-size="{$columnFontSize}">
                        <xsl:value-of select="$headerPrintedDate"/>  :
                      </fo:inline>
                      <fo:block>
                        <xsl:value-of select="/PdfPrinter/Reports/Header/PrintedDate" />
                      </fo:block>
                    </fo:block>
                  </fo:table-cell>
                </fo:table-row>
              </fo:table-body>
            </fo:table>
            <fo:block/>
          </fo:block>
          <fo:block text-align="center">
            <fo:table border-bottom-width="5pt" font-weight="bold" inline-progression-dimension="auto" table-layout="auto" border-bottom-color="rgb(51,51,153)" border-collapse="collapse">
              <fo:table-column column-number="1"/>
              <fo:table-body>
                <fo:table-row border-bottom-color="rgb(0,0,255)" display-align="before">
                  <fo:table-cell border-bottom-width="3pt" border-bottom-style="solid" border-bottom-color="rgb(51,51,153)" padding="2pt" text-align="left">
                    <fo:block text-align="center" font-size="12pt" font-weight="bold" font-family="Helvetica" border-bottom="10pt  "></fo:block>
                    <fo:block>
                      <xsl:value-of select="/PdfPrinter/Reports/Header/ReportTitle" />
                    </fo:block>
                    <fo:block border-bottom-width="10pt" text-align="center"/>
                  </fo:table-cell>
                </fo:table-row>
              </fo:table-body>
            </fo:table>
          </fo:block>
          <fo:block font-size="18pt"
                font-family="sans-serif"
                line-height="24pt"
                space-after.optimum="15pt"
                background-color="blue"
                color="white"
                text-align="center"
                padding-top="3pt">
           Users Report
          </fo:block>
          <fo:block text-align="center">
            <!-- table start -->
            <fo:table border-bottom-width="5pt" width="1200pt" border-bottom-color="rgb(0,51,102)" border-collapse="collapse" background-color="rgb(255,255,255)">
              <!--table header-->
              <xsl:for-each select="/PdfPrinter/Reports/Report[1]/*">
                <fo:table-column column-width="proportional-column-width(4.77)"/>
              </xsl:for-each>
              <fo:table-header>
                  <fo:table-row height="20.81pt" display-align="center" overflow="hidden">
                    <xsl:for-each select="/PdfPrinter/Reports/Report[1]/*">
                    <fo:table-cell text-align="center" border-left-color="rgb(0, 0, 0)" border-left-style="solid" border-left-width="1pt" border-right-color="rgb(0, 0, 0)" border-right-style="solid" border-right-width="1pt" border-top-color="rgb(0, 0, 0)" border-top-style="solid" border-top-width="1pt" border-bottom-color="rgb(0, 0, 0)" border-bottom-style="solid" border-bottom-width="1pt" padding-left="2pt" padding-right="2pt" padding-top="2pt" padding-bottom="2pt">
                      <fo:block color="rgb(0,0,0)" text-align="center" font-weight="normal" font-family="{$columnFontType}" font-size="{$columnFontSize}">
                        <xsl:value-of select="name()"/>
                      </fo:block>
                    </fo:table-cell>
                    </xsl:for-each>
                  </fo:table-row>
              </fo:table-header>
              <!--table body-->
              <fo:table-body>
                <xsl:for-each select="PdfPrinter/Reports/Report">
                  <fo:table-row display-align="before">
                    <xsl:for-each select="*">
                    <fo:table-cell text-align="center" border-top-color="rgb(0, 0, 0)" border-top-style="solid" border-left-width="1pt" border-right-width="1pt" border-top-width="1pt" border-bottom-width="1pt" padding-left="2pt" padding-right="2pt" padding-top="2pt" padding-bottom="2pt">
                      <fo:block>
                          <xsl:value-of select="."/>
                      </fo:block>
                    </fo:table-cell>
                    </xsl:for-each>
                  </fo:table-row>
                </xsl:for-each>
              </fo:table-body>
            </fo:table>
            <!-- table end -->
          </fo:block>
        </fo:flow>
      </fo:page-sequence>
    </fo:root>
  </xsl:template>
</xsl:stylesheet>

I'm able to generate columns dynamically, But need to group by columns and display the result as shown in the above attached image. Please help me out.

Thanks in advance!

Silk Road
  • 13
  • 11
  • I ran your provided XML and XSLT and got an empty `fo:table-header/fo:table-row` and empty `fo:table-body`. Either the XML needs an outer `PdfPrinter` or your XPaths need to change. – Tony Graham Jul 05 '16 at 08:37
  • @ Tony, Yep.. I'm sorry, doing that programmatically.. You need to add on top. Thank you.. – Silk Road Jul 05 '16 at 09:00
  • Your graphic shows 4/5 columns, but your XML produces 9 columns. What is the grouping column in your XML? – Tony Graham Jul 05 '16 at 09:16
  • Graphic is just for visualisation, I just depicted it for experts to understand how exactly i need the output. Can you use "Name" column to group by? So, The column5 in the graphic should be replaced by COlumn value : Name and row value by "S" followed by other rows and columns.I hope I'm clear.. – Silk Road Jul 05 '16 at 09:24

1 Answers1

0

Since you're using XSLT 2.0, you can use xsl:for-each-group to group the Report. You also need to not consider the Name elements when generating fo:table-column and fo:table-cell and when spanning the whole row.

<fo:table border-bottom-width="5pt" width="1200pt" border-bottom-color="rgb(0,51,102)" border-collapse="collapse" background-color="rgb(255,255,255)">
  <!--table header-->
  <xsl:for-each select="/PdfPrinter/Reports/Report[1]/(* except Name)">
    <fo:table-column column-width="proportional-column-width(4.77)"/>
  </xsl:for-each>
  <fo:table-header>
      <fo:table-row height="20.81pt" display-align="center" overflow="hidden">
        <xsl:for-each select="/PdfPrinter/Reports/Report[1]/(* except Name)">
        <fo:table-cell text-align="center" border="rgb(0, 0, 0) solid 1pt" padding="2pt">
          <fo:block color="rgb(0,0,0)" text-align="center" font-weight="normal" font-family="{$columnFontType}" font-size="{$columnFontSize}">
            <xsl:value-of select="name()"/>
          </fo:block>
        </fo:table-cell>
        </xsl:for-each>
      </fo:table-row>
  </fo:table-header>
  <!--table body-->
  <fo:table-body>
    <xsl:for-each-group select="PdfPrinter/Reports/Report"
      group-adjacent="Name">
      <fo:table-row>
        <fo:table-cell number-columns-spanned="{count(*) - 1}">
          <fo:block><xsl:apply-templates select="Name" /></fo:block>
        </fo:table-cell>
      </fo:table-row>
      <xsl:for-each select="current-group()">
        <fo:table-row display-align="before">
          <xsl:for-each select="* except Name">
            <fo:table-cell text-align="center" border-top-color="rgb(0, 0, 0)" border-top-style="solid" border-width="1pt" padding="2pt">
              <fo:block>
                <xsl:value-of select="."/>
              </fo:block>
            </fo:table-cell>
          </xsl:for-each>
        </fo:table-row>
      </xsl:for-each>
    </xsl:for-each-group>
  </fo:table-body>
</fo:table>

To use Muenchian Grouping with an XSLT 1.0 processor:

Change version="2.0"' to 'version="1.0"' so there's less confusion about which XSLT version you are using.

Add this at the top level, e.g., after the xsl:output:

  <xsl:key name="Report" match="Report" use="Name" />

Use this:

<fo:table border-bottom-width="5pt" width="1200pt" border-bottom-color="rgb(0,51,102)" border-collapse="collapse" background-color="rgb(255,255,255)">
  <!--table header-->
  <xsl:for-each select="/PdfPrinter/Reports/Report[1]/*[local-name() != 'Name']">
    <fo:table-column column-width="proportional-column-width(4.77)"/>
  </xsl:for-each>
  <fo:table-header>
      <fo:table-row height="20.81pt" display-align="center" overflow="hidden">
        <xsl:for-each select="/PdfPrinter/Reports/Report[1]/*[local-name() != 'Name']">
        <fo:table-cell text-align="center" border="rgb(0, 0, 0) solid 1pt" padding="2pt">
          <fo:block color="rgb(0,0,0)" text-align="center" font-weight="normal" font-family="{$columnFontType}" font-size="{$columnFontSize}">
            <xsl:value-of select="name()"/>
          </fo:block>
        </fo:table-cell>
        </xsl:for-each>
      </fo:table-row>
  </fo:table-header>
  <!--table body-->
  <fo:table-body>
    <xsl:for-each select="PdfPrinter/Reports/Report[generate-id() = generate-id(key('Report', Name)[1])]">
      <fo:table-row>
        <fo:table-cell number-columns-spanned="{count(*) - 1}">
          <fo:block><xsl:apply-templates select="Name" /></fo:block>
        </fo:table-cell>
      </fo:table-row>
      <xsl:for-each select="key('Report', Name)">
        <fo:table-row display-align="before">
          <xsl:for-each select="*[local-name() != 'Name']">
            <fo:table-cell text-align="center" border-top-color="rgb(0, 0, 0)" border-top-style="solid" border-width="1pt" padding="2pt">
              <fo:block>
                <xsl:value-of select="."/>
              </fo:block>
            </fo:table-cell>
          </xsl:for-each>
        </fo:table-row>
      </xsl:for-each>
    </xsl:for-each>
  </fo:table-body>
</fo:table>
Tony Graham
  • 7,306
  • 13
  • 20
  • I tried with the above table and getting error with XSLT 2.0. (The element template in namespace 'http://www.w3.org/1999/XSL/Transform' has invalid child element 'for-each-group' in namespace 'http://www.w3.org/1999/XSL/Transform'. List of possible elements expected : 'apply-templates, call-template, apply-imports, for-each,value-of,copy-of,number,choose, etc in namespace 'http://www.w3.org/1999/XSL/Transform' as well as any element in namespace '##other'.) EDIT: I want to use XSLT 1.0.. If i need to use 2.0, Do i need to refer anything? Please clarify.. Thanks alot! – Silk Road Jul 05 '16 at 10:06
  • Your stylesheet includes '`version="2.0"`', so I gave an XSLT 2.0 solution. To use XSLT 2.0, you need an XSLT 2.0 processor, such as Saxon (http://www.saxonica.com). To do this sort of grouping using XSLT 1.0, look up how to do 'Meunchian Grouping', e.g., http://stackoverflow.com/questions/24411173/muenchian-grouping-with-xsl-fo-table?rq=1 – Tony Graham Jul 05 '16 at 11:09
  • Ok, so do i need to add any dll to my project to use xslt 2.0? How can make work with XSLT 2.0 in my project? Please suggest.. – Silk Road Jul 05 '16 at 11:24
  • That's a different question, and I don't have the answer. – Tony Graham Jul 05 '16 at 11:29
  • How can I make the grouping column dynamic? I would like to replace the Name with any column. Is it possible? Please guide me. – Silk Road Jul 22 '16 at 09:06
  • In the place of Name column, I would like to pass a variable(any column that is present in the Header section of my XML). How can i do this? I tried with – Silk Road Jul 23 '16 at 04:18