XSLT Muenchian method complex

0
votes

I was trying diferent options using the muenchian method but I don't get the result that I need.

This is my input:

<?xml version="1.0" encoding = "UTF-8"?>
<trailers>
    <ES_PRODUCT year="2014">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 1"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Erick"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2014">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 1"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Erick"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2014">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 1"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Erick"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2013">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 2"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Erick"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2013">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 1"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Pepe"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2014">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 1"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Pepe"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2014">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 2"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Pepe"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
    <ES_PRODUCT year="2013">
        <p_product_restrictions>
            <ES_PRODUCTRESTRICTION>
                <pr_channel>
                    <ESP_CHANNEL name="Channel 2"/>
                </pr_channel>
            </ES_PRODUCTRESTRICTION>
        </p_product_restrictions>
        <trailerProject>
            <wontrailerproject>
                <producer>
                    <ES_PERSON pe_fullname="Pepe"/>
                </producer>
            </wontrailerproject>
        </trailerProject>
    </ES_PRODUCT>
 <ES_PRODUCT year="2013">
    <p_product_restrictions>
        <ES_PRODUCTRESTRICTION>
            <pr_channel>
                <ESP_CHANNEL name="Channel 2"/>
            </pr_channel>
        </ES_PRODUCTRESTRICTION>
    </p_product_restrictions>
    <trailerProject>
        <wontrailerproject>
            <producer>
                <ES_PERSON pe_fullname="Thomas"/>
            </producer>
        </wontrailerproject>
    </trailerProject>
</ES_PRODUCT>
</trailers>

My code (the output is in excel)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:output encoding="utf-8" indent="yes"/>
<xsl:key name="producers" match="trailers/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON" use="@pe_fullname"/>
<xsl:key name="channels" match="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL" use="@name"/>
<xsl:key name="year" match="trailers/ES_PRODUCT" use="@year"/>
<xsl:key name="txid" match="trailers/ES_PRODUCT" use="mediaAssets/ES_MM2MEDIAASSET/@label"/>



<xsl:template match="/">
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
              xmlns:html="http://www.w3.org/TR/REC-html40">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

        </DocumentProperties>
        <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG/>
        </OfficeDocumentSettings>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>11595</WindowHeight>
            <WindowWidth>19200</WindowWidth>
            <WindowTopX>0</WindowTopX>
            <WindowTopY>0</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s70">
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
                <Interior ss:Color="#FFC000" ss:Pattern="Solid"/>
            </Style>
            <Style ss:ID="s74">
                <Borders>
                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                </Borders>
            </Style>
            <Style ss:ID="s75">
                <Borders>
                    <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </Borders>
                <Interior ss:Color="#92D050" ss:Pattern="Solid"/>
            </Style>
        </Styles>
        <Worksheet ss:Name="Hoja1">
            <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
                <Column ss:Width="66"/>
                <Column ss:AutoFitWidth="0" ss:Width="69"/>
                <xsl:variable name="ROOT_A" select="trailers"/>
                <xsl:for-each select="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]">
                    <xsl:variable name="thisChannel" select="@name"/>

                    <Row ss:Height="15.75">
                        <Cell ss:StyleID="s75">
                            <Data ss:Type="String">
                                <xsl:value-of select="$thisChannel"/>
                            </Data>
                        </Cell>
                        <xsl:for-each select="$ROOT_A/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON[generate-id() = generate-id(key('producers',@pe_fullname)[1])]">
                            <xsl:variable name="thisProducer" select="@pe_fullname"/>
                            <xsl:for-each select="p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]">

                                <Cell ss:StyleID="s75">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisProducer"/>
                                    </Data>
                                </Cell>
                            </xsl:for-each>
                        </xsl:for-each>
                    </Row>

                    <xsl:for-each select="$ROOT_A/ES_PRODUCT[generate-id() = generate-id(key('year',@year)[1])]">
                        <xsl:variable name="thisYear" select="@year"/>
                        <xsl:for-each select="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]">

                            <Row ss:Height="15.75">
                                <Cell ss:StyleID="s74">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisYear"/>
                                    </Data>
                                </Cell>
                                <xsl:for-each select="$ROOT_A/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON[generate-id() = generate-id(key('producers',@pe_fullname)[1])]">
                                    <xsl:variable name="thisProducer" select="@pe_fullname"/>
                                    <xsl:for-each select="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]">

                                        <Cell ss:StyleID="s74">
                                            <Data ss:Type="Number">
                                                <xsl:value-of select="count(/trailers/ES_PRODUCT[trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname = $thisProducer][@year = $thisYear])"/>
                                            </Data>
                                        </Cell>
                                    </xsl:for-each>
                                </xsl:for-each>
                            </Row>
                        </xsl:for-each>
                    </xsl:for-each>
                </xsl:for-each>
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <Print>
                    <ValidPrinterInfo/>
                    <HorizontalResolution>300</HorizontalResolution>
                    <VerticalResolution>300</VerticalResolution>
                </Print>
                <Selected/>
                <Panes>
                    <Pane>
                        <Number>3</Number>
                        <ActiveRow>6</ActiveRow>
                        <ActiveCol>2</ActiveCol>
                    </Pane>
                </Panes>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
    </Workbook>
</xsl:template>
</xsl:stylesheet>

and i need something like this: result

I don't know how is the corret way to make a group inside other group using the muenchian method. I appreciate any help. thanks!

1
xmlxsltmuenchian-grouping

1 Answers

0
votes

You were not far off, with your use of keys, but the main problem as far as I can see was you had some nested xsl:for-each statements, which weren't taking into account the context. For example

<xsl:for-each select="$ROOT_A/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON[generate-id() = generate-id(key('producers',@pe_fullname)[1])]">
   <xsl:variable name="thisProducer" select="@pe_fullname"/>
  <xsl:for-each select="p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]">

For the first xsl:for-each, you will be positioned on a ES_PERSON element, but the nested xsl:for-each starts off by looking for a p_product_restrictions as a child of the current ES_PERSON. Thus it selects nothing.

I am not sure the nested one is actually needed in this case.

Try this XSLT instead. Note that I have moved the use of keys into variable just to aid readability:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:output encoding="utf-8" indent="yes"/>
<xsl:key name="producers" match="trailers/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON" use="@pe_fullname"/>
<xsl:key name="channels" match="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL" use="@name"/>
<xsl:key name="year" match="trailers/ES_PRODUCT" use="@year"/>
<xsl:key name="full" match="trailers/ES_PRODUCT" use="concat(p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name, '|', 
                                                             trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname, '|',
                                                             @year)"/>

<xsl:template match="/">
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
              xmlns:html="http://www.w3.org/TR/REC-html40">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

        </DocumentProperties>
        <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG/>
        </OfficeDocumentSettings>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>11595</WindowHeight>
            <WindowWidth>19200</WindowWidth>
            <WindowTopX>0</WindowTopX>
            <WindowTopY>0</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <Styles>
            <Style ss:ID="Default" ss:Name="Normal">
                <Alignment ss:Vertical="Bottom"/>
                <Borders/>
                <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
            </Style>
            <Style ss:ID="s70">
                <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
                <Interior ss:Color="#FFC000" ss:Pattern="Solid"/>
            </Style>
            <Style ss:ID="s74">
                <Borders>
                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                </Borders>
            </Style>
            <Style ss:ID="s75">
                <Borders>
                    <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"/>
                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
                </Borders>
                <Interior ss:Color="#92D050" ss:Pattern="Solid"/>
            </Style>
        </Styles>
        <Worksheet ss:Name="Hoja1">
            <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
                <Column ss:Width="66"/>
                <Column ss:AutoFitWidth="0" ss:Width="69"/>
                <xsl:variable name="ROOT_A" select="trailers"/>
                <xsl:variable name="distinct_channels" select="trailers/ES_PRODUCT/p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL[generate-id() = generate-id(key('channels',@name)[1])]" />
                <xsl:variable name="distinct_producers" select="$ROOT_A/ES_PRODUCT/trailerProject/wontrailerproject/producer/ES_PERSON[generate-id() = generate-id(key('producers',@pe_fullname)[1])]" />
                <xsl:variable name="distinct_years" select="$ROOT_A/ES_PRODUCT[generate-id() = generate-id(key('year',@year)[1])]" />
                <xsl:for-each select="$distinct_channels">
                    <xsl:variable name="thisChannel" select="@name"/>

                    <Row ss:Height="15.75">
                        <Cell ss:StyleID="s75">
                            <Data ss:Type="String">
                                <xsl:value-of select="$thisChannel"/>
                            </Data>
                        </Cell>
                        <xsl:for-each select="$distinct_producers">
                            <xsl:variable name="thisProducer" select="@pe_fullname"/>

                                <Cell ss:StyleID="s75">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisProducer"/>
                                    </Data>
                                </Cell>
                        </xsl:for-each>
                    </Row>

                    <xsl:for-each select="$distinct_years">
                        <xsl:variable name="thisYear" select="@year"/>

                            <Row ss:Height="15.75">
                                <Cell ss:StyleID="s74">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisYear"/>
                                    </Data>
                                </Cell>
                                <xsl:for-each select="$distinct_producers">
                                    <xsl:variable name="thisProducer" select="@pe_fullname"/>
                                        <Cell ss:StyleID="s74">
                                            <Data ss:Type="Number">
                                                <xsl:value-of select="count(key('full', concat($thisChannel, '|', $thisProducer, '|', $thisYear)))"/>
                                            </Data>
                                        </Cell>
                                </xsl:for-each>
                            </Row>
                    </xsl:for-each>
                </xsl:for-each>
            </Table>
            <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                <PageSetup>
                    <Header x:Margin="0.3"/>
                    <Footer x:Margin="0.3"/>
                    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                </PageSetup>
                <Print>
                    <ValidPrinterInfo/>
                    <HorizontalResolution>300</HorizontalResolution>
                    <VerticalResolution>300</VerticalResolution>
                </Print>
                <Selected/>
                <Panes>
                    <Pane>
                        <Number>3</Number>
                        <ActiveRow>6</ActiveRow>
                        <ActiveCol>2</ActiveCol>
                    </Pane>
                </Panes>
                <ProtectObjects>False</ProtectObjects>
                <ProtectScenarios>False</ProtectScenarios>
            </WorksheetOptions>
        </Worksheet>
    </Workbook>
</xsl:template>
</xsl:stylesheet>

EDIT: If you want to show only producers that are linked to all channels, rather than all producers for all channels, you will need a concatenated key that references both channel and producer

<xsl:key name="producers_by_channel" match="trailers/ES_PRODUCT" 
                                     use="concat(p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name, '|', 
                                                 trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname)"/>

Try this XSLT. Note I have stripped out the static Styles and WorksheetOptions element to reduce the size. Also, I have rejigged the match attribute on the keys too.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"
            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:output encoding="utf-8" indent="yes"/>

<xsl:key name="channels" match="trailers/ES_PRODUCT" use="p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name"/>
<xsl:key name="year" match="trailers/ES_PRODUCT" use="@year"/>
<xsl:key name="producers_by_channel" match="trailers/ES_PRODUCT" 
                                     use="concat(p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name, '|', 
                                                 trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname)"/>
<xsl:key name="full" match="trailers/ES_PRODUCT" use="concat(p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name, '|', 
                                                             trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname, '|',
                                                             @year)"/>

<xsl:template match="/">
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
              xmlns:html="http://www.w3.org/TR/REC-html40">
        <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

        </DocumentProperties>
        <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
            <AllowPNG/>
        </OfficeDocumentSettings>
        <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
            <WindowHeight>11595</WindowHeight>
            <WindowWidth>19200</WindowWidth>
            <WindowTopX>0</WindowTopX>
            <WindowTopY>0</WindowTopY>
            <ProtectStructure>False</ProtectStructure>
            <ProtectWindows>False</ProtectWindows>
        </ExcelWorkbook>
        <!-- Styles go here -->
        <Worksheet ss:Name="Hoja1">
            <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
                <Column ss:Width="66"/>
                <Column ss:AutoFitWidth="0" ss:Width="69"/>
                <xsl:variable name="ROOT_A" select="trailers"/>
                <xsl:variable name="distinct_channels" select="$ROOT_A/ES_PRODUCT[generate-id() = generate-id(key('channels',p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name)[1])]" />
                <xsl:variable name="distinct_years" select="$ROOT_A/ES_PRODUCT[generate-id() = generate-id(key('year',@year)[1])]" />
                <xsl:for-each select="$distinct_channels">
                    <xsl:variable name="thisChannel" select="p_product_restrictions/ES_PRODUCTRESTRICTION/pr_channel/ESP_CHANNEL/@name"/>
                    <xsl:variable name="distinct_producers" select="key('channels', $thisChannel)[generate-id() = generate-id(key('producers_by_channel',concat($thisChannel, '|', trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname))[1])]" />
                    <Row ss:Height="15.75">
                        <Cell ss:StyleID="s75">
                            <Data ss:Type="String">
                                <xsl:value-of select="$thisChannel"/>
                            </Data>
                        </Cell>
                        <xsl:for-each select="$distinct_producers">
                            <xsl:variable name="thisProducer" select="trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname"/>

                                <Cell ss:StyleID="s75">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisProducer"/>
                                    </Data>
                                </Cell>
                        </xsl:for-each>
                    </Row>

                    <xsl:for-each select="$distinct_years">
                        <xsl:variable name="thisYear" select="@year"/>

                            <Row ss:Height="15.75">
                                <Cell ss:StyleID="s74">
                                    <Data ss:Type="String">
                                        <xsl:value-of select="$thisYear"/>
                                    </Data>
                                </Cell>
                                <xsl:for-each select="$distinct_producers">
                                    <xsl:variable name="thisProducer" select="trailerProject/wontrailerproject/producer/ES_PERSON/@pe_fullname"/>
                                        <Cell ss:StyleID="s74">
                                            <Data ss:Type="Number">
                                                <xsl:value-of select="count(key('full', concat($thisChannel, '|', $thisProducer, '|', $thisYear)))"/>
                                            </Data>
                                        </Cell>
                                </xsl:for-each>
                            </Row>
                    </xsl:for-each>
                </xsl:for-each>
            </Table>
            <!-- WorksheetOptions go here -->
        </Worksheet>
    </Workbook>
</xsl:template>
</xsl:stylesheet>