Dynamics User Group

This is a complete new world to me. I've studied both the Style Sheet Toool for NAV user's guide and the whitepaper create before they had the style sheet tool. But nothing really explains me enough. Do any of you know of other documents about this topic? Training material etc?

The problems I'm having is two fold. Is it possible to "import" the standard style sheets into the tool and modify them from here? And can I import the standard styles into word and modify them, or must I create a new style sheet?

The second problem is the user guide only describes how to create new mail merge style sheets. It doesn't talk about Excel. Anyone has something about this?

Hi Erik,

My knowledge is that there is no additional documentation about this, like you mentioned.

1.There is one additional whitepaper in partner source which explain how to extend an additional field in an existing style sheet.

2. Yes, the style sheet tool could only create Mail merge documents and not Excel style sheets at this time. So for creating Excel style sheets you have to copy an existing one and change the xslt file directly.

What I can tell you more is, that the codeunit which is used for export first export the metadata. So it exports the indicated form on the screen as XML. This is a function, you know, which is public available in NAV 2009, to export objects metadata as XML. It is also included (hidden) for forms in NAV 5.0.! (You can see (and use  :)) it if you take a look at the codeunit.)

The export to excel/word was a great idea half implemented. It seems like the developer(s) planned for the tool to be able to to great things - like have a multiple stylesheets for each function (think purchase order with variants or purchase order without, delivery note with prices or without, etc) - but then they were given a tighter deadline or something else to work on and so just shipped what they could get working. As to documenting it - that seems to have been very low down on the list as there essentially isn't any.

Here's the theory:

• You export the information you want in xml format to a file using the codeunit functions
• You take xslt file (take a look at wikipedia if you don't know what xslt is - you're in for fun)
• You run a transform on the two files - this generates another file as output

The resulting output file you generate should have the same format as when you do file/Save As/Office Xml format. This file can be double clicked and will open in excel or word as appropiate as if it was a binary file.

The complex part of this is learning xslt and the office format which is to say everything :-)

You can use the codeunit to generate an xml file in the format that navision will generate. Using this xml file as a base to work from you can manually write an xslt by hand in notepad. Once you have tested the xslt by using one of the command line xslt transform tools and checked the output in office you can import this xslt file into navision

This is the theory. Its horrible to do though - which is why MS wrote the little tool to do all the work with word.

Hi Rene,

Thank you for your answer.

Ad 1) I think this is the whitepaper I was looking at. It's not refering to the tool right?

Ad 2) That's just what I thought! - can you direct me to a good place to find information about the XSLT file format? What I actually need to do right now is "simply" to change the format of the Excel style sheet export. Currently whenever I export ex. an entry list, then all numbers are formatted like text, where the users really need them to be formatted like numbers... That should be an easy task!

It's actually an amazing function this Style Sheet function!

As an additional question, have anyone tried to use the stylesheet function to create a preformatted email? Is it possible?

 Format for a number 8pt swiss, right aligned, wrapping enabled

        <Style ss:ID="TableACurrencyItemAlt">
            <Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
            <Font ss:FontName="tahoma" x:Family="Swiss" ss:Size="8" ss:Color="#000000"/>
            <Interior ss:Color="#CCCCFF" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="_-* #,##0.00\ &#8364;_-;\-* #,##0.00\ &#8364;_-;_-* &quot;-&quot;??\ &#8364;_-;_-@_-"/>
        </Style>

and to use it:

<Cell ss:StyleID="TableACurrencyItemAlt">

<Data ss:Type="Number"><xsl:value-of select="Price"/>
</Data><NamedCell ss:Name="Print_Area"/></Cell>

 As to sources, there are literally thousands if you search on google. I can't say anythink sticks in my mind as a good site.

 Ian

Well that really didn't make me much wiser!

So I'll like to ask in another way:

Has anyone experienced the same problem when using the standard style sheets from NAV 5.0 SP1.

Whenever I export in example an entry list, then all numbers are formatted like text, where the users really need them to be formatted like numbers... This does that it's not possible for the user to do calculations in Excel, without first manually converting all these numbers to numbers!

imurphy:

The export to excel/word was a great idea half implemented. It seems like the developer(s) planned for the tool to be able to to great things - like have a multiple stylesheets for each function (think purchase order with variants or purchase order without, delivery note with prices or without, etc) - but then they were given a tighter deadline or something else to work on and so just shipped what they could get working.

Yes this about captures my feeling also. Interesting to me was the hidden option field that says "Form,Report" indicating to me that they also planned to be able to export reports direct to Word and Excel.

David Singleton:

Yes this about captures my feeling also. Interesting to me was the hidden option field that says "Form,Report" indicating to me that they also planned to be able to export reports direct to Word and Excel.

Well that would really had been a great featured!

I have actually found a blog entry explaining how to fix the problem with the style sheets exporting numbers as text:

http://blogs.msdn.com/nav_developer/archive/2008/07/02/dynamics-nav-5-0-and-style-sheet-issues-decimals-exported-as-text.aspx

The only problem is that I cannot get it to work!

You probably have a problem where xslt is not matching what you want. The instructions say to insert this little fragment :

<xsl:when test="string(number(translate(translate(@value,$nbsp,''),',.','11')))!='NaN'">  
               <xsl:attribute name="ss:StyleID">TextBoxNumber</xsl:attribute>
</xsl:when> 

This says 'when you find a number, the fragment of xml we are currently generating should have an attribute ss:StyleID="TextBoxNumber" added to it'

Above this we have '<Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet">', which will be changed to

<Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="TextBoxNumber">

if the fragment doesn't recognise a number it will generate

<Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" ss:StyleID="TextBox">

this will then be followed by '<data>some data</data></cell>'

To see whats going wrong, you need to look at the output before excel modifies it. To do this, open a copy of windows explorer. In the address bar type '%temp%' and sort by date. The last file produced should be something like 'ORDER-12345-client name - sales order.xml'. Open it in notepad and look for the number which is coming out incorrectly. This won't be easy as it comes out as one huge long line.

Check the StyleID being applied and if its not TextBoxNumber they you have a problem with the pasted fragment. If it is then you have a problem with the definition of the style and its defaulting to plain text.

Sorry about the xslt I posted earlier - should have added some more explanation. What I posted was how to format a cell using xslt, not how to investigate this problem.

Ian

Erik P. Ernst:

Well that would really had been a great featured!

Well I think the option is still there, so maybe it is still planned ? 

David Singleton:

Erik P. Ernst:

Well that would really had been a great featured!

Well I think the option is still there, so maybe it is still planned ? 

How does it look in NAV 2009?

Erik P. Ernst:

How does it look in NAV 2009?

Sorry NDA, I can't comment on that

David Singleton:

Erik P. Ernst:

How does it look in NAV 2009?

 

Sorry NDA, I can't comment on that

Well you should be able to comment on that now!

Well its not there, and I would imagine now that its another C/PLEX.

Now that they are moving to SSRS what logic is there to do this in CLassic Client.