Help needed on XSLT

baruprasad · October 18th, 2008, 10:15 PM

Hi,
I am new to XSLT programming. I have a VB application using XML & XSLT to generate email formats. I am struck with an issue:

<DatabaseData>

<RecordSet>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Nic" ResourceHrs="28" FunctionName="Other" Hours="28" SortOrder="991" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Project Management" Hours="16" SortOrder="1" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Support" Hours="1.5" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Time Off" Hours="0.5" SortOrder="992" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Dean" ResourceHrs="20" FunctionName="Support" Hours="20" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ryan C Saul" ResourceHrs="4.5" FunctionName="Support" Hours="4.5" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Project Management" Hours="8" SortOrder="1" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Support" Hours="2" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ramakrishna Prasad" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Time Off" Hours="1" SortOrder="992" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Nic" ResourceHrs="28" FunctionName="Other" Hours="28" SortOrder="991" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Project Management" Hours="16" SortOrder="1" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Support" Hours="1.5" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ravi" ResourceHrs="18" FunctionName="Time Off" Hours="0.5" SortOrder="992" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Dean" ResourceHrs="20" FunctionName="Support" Hours="20" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1326" ArtName="PSPM" ResourceName="Ryan C Saul" ResourceHrs="4.5" FunctionName="Support" Hours="4.5" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Project Management" Hours="8" SortOrder="1" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Support" Hours="2" SortOrder="990" PrjHrs="81.5"/>

<Record MgrName="Ram" Email="[email protected]" ID="1332" ArtName="Title" ResourceName="Marg" ResourceHrs="11" FunctionName="Time Off" Hours="1" SortOrder="992" PrjHrs="81.5"/>

</RecordSet>

</DatabaseData>

and need to generate Email to each manager in the following format:

Manager : ramakrishna Prasad

****************************

ID:Name Resource Function Hrs

Total Hours 59.00

1326:PSPM
            Nic 28

                    Other 28

            Ravi 18

                    Project Management 16

                    Support 1.5

                    Time Off 0.5

1332:Title

            Marg 11

                    Project Management 8

                    Support 2

                    Time Off 1

Manager : ram

*************

Total Hours 59.00

1326:PSPM
            Nic 28

                    Other 28

            Ravi 18

                    Project Management 16

                    Support 1.5

                    Time Off 0.5

1332:Title

            Marg 11

                    Project Management 8

                    Support 2

                    Time Off 1

I tried in numberways to generate in the above format and not successful. The issue is, as the same project & resource data exists for two mangers, when I am trying to display "Functions" for second manager, its getting filtered.

Any help in this regard is appreciated.

Thanks
Ramakrishna

Baru

Martin Honnen · October 19th, 2008, 11:38 AM

Do you want to create HTML or plain text output with your XSLT stylesheet? If you want to produce HTML then please post the HTML markup, if you want to create plain text then I am afraid what you inserted in your first post is not clear, other than that you want to group by the MgrName attribute of the Record element. But it is not clear what data you want to extract for each MgrName as your sample result is not well formatted.
To group data with XSLT 1.0 look into Muenchian grouping as described here:
http://www.jenitennison.com/xslt/grouping/index.xml

--
Martin Honnen
Microsoft MVP - XML

mhkay · October 19th, 2008, 05:00 PM

This is a standard grouping problem. Look up "grouping" in your favourite Wrox XSLT textbook for the solution.

Grouping is very easy in XSLT 2.0 using the new xsl:for-each-group instruction. It's rather harder in XSLT 1.0 but can be achieved using the technique called "Muenchian grouping" (google for it) which looks very complex at first sight but makes sense when you get the hang of it.

Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer's Reference

baruprasad · October 20th, 2008, 08:47 PM

Following is the code I used... Inorder to use XSL 2.0 do I need to install any parsers?

Code:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:key name="MgrProj" match="/DatabaseData/RecordSet/Record[not(@ProjectId= preceding::Record[1]/@ProjectId)]" use="@PPMName"/>
<xsl:template match="/DatabaseData/RecordSet">
<Dataset>
    <xsl:for-each select="Record[not((@PPMName = preceding::Record/@PPMName))]">
    <Employee EmailAddr="{normalize-space(@PPMEmail)}">    
        <xsl:attribute name="PMHours">
            <xsl:variable name="PPMName" select="@PPMName"/>
            <table border="1">
                 <tr>
                    <th align="left" WIDTH="500">

                    Project#:Name : Status

                    </th>
                    <th align="left" WIDTH="300">
                    Resource
                    </th>
                    <th align="left" WIDTH="200">
                    Function
                    </th>
                    <th align="left" WIDTH="100">
                    Total Hours
                    </th>                            
                 </tr>            
                <tr>
                    <TD width="300" colspan="3" >

                        <b>Total Hours</b>
                    </TD>
                    <TD width="100" align="right">

                        <b>
                        <xsl:value-of select='sum(//Record[normalize-space(@PPMName) = normalize-space($PPMName)]/@Hours)'/>
                        </b>
                    </TD>
                </tr>
            <xsl:for-each select="key('MgrProj', @PPMName)">
            <xsl:variable name="ProjectId" select="@ProjectId"/>            
                <tr>                        
                    <td width = "500" colspan="4">

                    <xsl:value-of select="@ProjectId"/> : <xsl:value-of select="@ProjectName"/> :<xsl:value-of select="@ProjectStatus"/> 

                    </td>
                </tr>
                <xsl:variable name="ResourceNodes" select="//Record[(normalize-space(@PPMName) = normalize-space($PPMName) and @ProjectId = $ProjectId)]"/>                
                <xsl:for-each select="$ResourceNodes[not(normalize-space(@ResourceName)=preceding::Record/@ResourceName) or (position() = last)]">                
                <xsl:if test="@ResourceHrs > 0">
                    <xsl:variable name="ResourceName" select="@ResourceName"/>
                    <tr>                        
                        <td width = "500">

                        &amp;nbsp;

                        </td>
                        <td width = "300">

                        <xsl:value-of select="@ResourceName"/>

                        </td>
                        <td width = "200">

                        &amp;nbsp;

                        </td>
                        <td width = "100" align="right">

                        <B><xsl:value-of select="@ResourceHrs"/></B>

                        </td>
                    </tr>
                    <xsl:for-each select="//Record[(normalize-space(@PPMName) = normalize-space($PPMName) and normalize-space(@ProjectId) = normalize-space($ProjectId)) and (normalize-space(@ResourceName) = normalize-space($ResourceName))]">                    
                        <tr>                        
                            <td width = "500">

                            &amp;nbsp;

                            </td>
                            <td width = "300">

                            &amp;nbsp;

                            </td>                            
                            <td width = "200">

                            <xsl:value-of select="@FunctionName"/>

                            </td>
                            <td width = "100" align="right">

                            <xsl:value-of select="@Hours"/>

                            </td>                            
                        </tr>                                                
                    </xsl:for-each>    
                </xsl:if>                    
                </xsl:for-each>            
            </xsl:for-each>        
            </table>            
        </xsl:attribute>        
    </Employee>                    
    </xsl:for-each>
</Dataset>
</xsl:template>
</xsl:stylesheet>

Baru

baruprasad · October 21st, 2008, 12:52 AM

Do I need to install "Saxon 7" complier on my machine in order to use XSL 2.0 & for-each-group element?

Quote:

quote:Originally posted by mhkay
This is a standard grouping problem. Look up "grouping" in your favourite Wrox XSLT textbook for the solution.

Grouping is very easy in XSLT 2.0 using the new xsl:for-each-group instruction. It's rather harder in XSLT 1.0 but can be achieved using the technique called "Muenchian grouping" (google for it) which looks very complex at first sight but makes sense when you get the hang of it.

Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer's Reference

Baru

samjudson · October 21st, 2008, 02:58 AM

Saxon 9 is the latest version, and will work with .Net applications.

/- Sam Judson : Wrox Technical Editor -/

mhkay · October 21st, 2008, 03:17 AM

I would certainly recommend Saxon for XSLT 2.0 processing (it's my product, and the market leader!); the current version is 9.1

There are two other XSLT 2.0 processors available, AltovaXML from Altova, and Gestalt from Colin Adams.

Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer's Reference

mhkay · October 21st, 2008, 03:54 AM

>Following is the code I used...

I've lost track of the thread - I'm not sure why you posted this code. But here are some comments on it.

(a) it's incredibly monolithic. Try to break it up into template rules. Don't use nested for-each loops like this: use xsl:apply-templates at each level, with xsl:template rules to define how each element gets processed.

(b) This is clearly wrong:

<xsl:attribute name="PMHours">
            <xsl:variable name="PPMName" select="@PPMName"/>
            <table border="1">
                 <tr>

It's trying to create a table element within a PMHours attribute. You can't have elements inside attributes. There are various things that might happen when you do this, depending on which processor you are using.

(c) <xsl:for-each select="Record[not((@PPMName = preceding::Record/@PPMName))]">

Better to use XSLT 2.0 grouping, or XSLT 1.0 Muenchian Grouping using keys; but if you must do it this way, you should be using the preceding-sibling axis rather than preceding.

Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer's Reference

baruprasad · October 21st, 2008, 09:03 AM

I am using VB6.0 to generate Emails to each Manager in the format required. I will follow your guidelines and see how I can solve this issue.

Quote:

quote:Originally posted by mhkay
>Following is the code I used...

I've lost track of the thread - I'm not sure why you posted this code. But here are some comments on it.

(a) it's incredibly monolithic. Try to break it up into template rules. Don't use nested for-each loops like this: use xsl:apply-templates at each level, with xsl:template rules to define how each element gets processed.

(b) This is clearly wrong:

<xsl:attribute name="PMHours">
            <xsl:variable name="PPMName" select="@PPMName"/>
            <table border="1">
                 <tr>

It's trying to create a table element within a PMHours attribute. You can't have elements inside attributes. There are various things that might happen when you do this, depending on which processor you are using.

(c) <xsl:for-each select="Record[not((@PPMName = preceding::Record/@PPMName))]">

Better to use XSLT 2.0 grouping, or XSLT 1.0 Muenchian Grouping using keys; but if you must do it this way, you should be using the preceding-sibling axis rather than preceding.

Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer's Reference

Baru