XSLT Muenchian method complex

Question

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!

Tim C · Accepted Answer · 2016-02-29T09:15:48.740

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>

Hello Tim C, Thanks for your Help. your code is much closer that I need but I need put in each raw only the producers that correspondes to the channel. with your code, the producers are the same in each line. Do you know if is it possible? — Andres Bota, Feb 26 '16 at 22:28
This should be possible, but could you amend your question to show an example of this, as you current XML and expected output has channels that contain all producers. Thanks! — Tim C, Feb 27 '16 at 10:24
Yes, sorry. I added to my original question a new node where the producer is Thomas. If I run your code, with that source, in the result thomas will be on channel 1 (with 0) and in channel 2 (with 1). but I need show only Thomas in channel 2. — Andres Bota, Feb 28 '16 at 16:57

XSLT Muenchian method complex

1 Answers1