In classic reports, adding a header to your report is as easy as just copying the Header section from an existing one.
With reports for RTC you need to do a bit of manual work to get your report header. A standard header contains the following information:
Time and date (=Globals!ExecutionTime)
Page Caption and Number (=Globals!PageNumber)
User ID (=User!UserID)
Time and Date, Page Number and User ID are automatically defined as global variables in Visual Studio report designer. All you need to do, is: Add a TextBox, right click on it and select "Expression...", then select from the Globals-Menu:
The other fields (Report Name, Company Name and Page Caption) are labels, and have to be added following the steps described here:
Labels in NAV2009 Report Design
So these are the two ways (Globals and Labels) that you have to add information to your report headers as illustrated here:
Lars Lohndorf-Larsen (Lohndorf )
Microsoft Dynamics UK
Microsoft Customer Service and Support (CSS) EMEA
When adding fields to a report, the caption is typically added automatically or with a bit of drag and drop from the DataSet in VS Report Layout. But if you want to add additional labels, for example Report Name in the header, then there are a few more steps you must follow.
Of course, you can just add a new TextBox in VS Layout, and type in the value you want to show. This is simple but will not give you Multi Language. Using a label from the classic report design gives you Multi language, but is a bit more complicated. This is what you need to do:
As usual, before you can use it in the layout you must add the label to the report sections, and then specify the caption you want to display. In this case, make sure to also specify a name in the DataSetFieldName-property of the label. For this example, let’s call it Report_Label. To get the label value (Caption) into the layout, you must first add it to the Table on the Body-part of the layout. Note: It must be part of the table, and not in a TextBox outside of the table. The name of the cell will default to the DataSetFieldName you specified (Report_Label). And the value must be =Fields!Report_Label.Value. You get this value automatically if you drag it from the Data Set into a detail-section of the table. The caption is only here to be available from the header part of the report, so you can set the Visible-property to No.
Finally you can get the label from the table and add it to a header-section in the report layout: Add a new TextBox, then name it something unique, for example Report_Label1, and set Expression = =ReportItems!Report_Label.Value. Note that "Report_Label" refers to the cell you added above, not directly to the label on the sections.
This picture shows the steps needed to get the label to show in the header:
Lars Lohndorf-Larsen (Lohndorf)
We're just two weeks away from Convergence 2009 in New Orleans from March 9-13. We have created more than 70 sessions and Hands on Labs in the Microsoft Dynamics NAV track. Many of the sessions and all Hands on Labs will be focused upon the newest release, Microsoft Dynamics NAV 2009. For the list of sessions see the session list on Convergence web site.
You can familiarize yourself with the new RoleTailored User Experience, Web Services and Reporting capabilities. You can learn about the different application areas, such as Manufacturing, Distribution, Finance and Service Management. There are also industry related sessions where you can hear how Microsoft Dynamics NAV is used in different industries.
To register for Convergence 2009, or for more information, see the Convergence web site.
One of the changes in Microsoft Dynamics NAV version 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920.
The change of cursor type can also mean a change in behaviour. With Dynamic cursors, SQL Server more often optimises for the ORDER BY - part of a SQL query than is the case with Fast Forward cursors. This is because a result set based on a dynamic cursor has to include new rows. IF SQL Server were to choose an index that fits the WHERE clause then it would have to sort all rows according to the ORDER BY before returning the first row to the client and that by definition is a STATIC result-set.
Take this query as an example:
undefined (("Document No_"=
ORDER BY "Item No_","Posting Date","Entry No_"
With Fast-Forward cursors, in this example SQL Server is likely to try to optimise for the WHERE clause, which is "Document No.". But with the ORDER BY clause specifying a different sorting, SQL Server may then chose a clustered index scan instead.
With Dynamic cursors, SQL Server is more likely to optimise for the ORDER BY clause, which is a valid and existing index. So in this exampe SQL server would chose an index scan on this index. You can see this by running the query from SQL Server Management Studio like this:
--declare @p3 int set @p3=16+4096+8192 -- Fast Forward
--declare @p5 int set @p5=15 – FAST 15
SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"
WHERE (("Document No_"=@P1))
You can enable / disable the 2nd and 3rd line to switch between Fast-Forward or Dynamic cursor, and see the result in the query plan that gets generated.
How does this affect NAV?The change in behaviour can mean that certain queries that ran without problems may be slow, after a customer upgrades the platform to the builds mentioned above. In cases
that we have seen, the problem has been limited to one or very few specific queries, typically on filtered forms, that were slow after upgrading the platform. Use the query from here:
Simple query to check the recent performance history II - now including Query Plan information
to help identifying which queries to trouleshoot. Note that the query shows the Cursor Type in the rightmost column. Then look at whether SQL Server has an index to match the ORDER BY clause.
Also, be extra careful using the SQLIndex-property on keys. By setting this property, the ORDER BY-clause may not match a SQL index anymore, and a Dynamic cursor will have
to scan the table.
Why this change?Internal performance tests show that overall, Dynamic cursors give better performance and fewer blocks. So while the change may cause unforeseen changes in behaviour when a customer upgrades, overall we have seen better performance with Dynamic cursors.
Lars Lohndorf-Larsen (Lohndorf)Escalation Engineer
As of version 5.0, Dynamics NAV introduced the option to export forms to MS Office (Word or Excel). Data presented on the form are exported in an xml, formatted using style sheets then sent to Word/Excel/Other programs
A Style Sheet tool for Dynamics NAV has later been released to help partners create custom style sheets for export to Word in an easy and user friendly way. A possibillity for extending functionality to export to Excel might be considered for future versions.
Meanwhile, customizing style sheets for excel, though requireing basic knowledge in xml and style sheets, can be simplified with some coding handling basic formatting of style sheets.
The FOB attached contains an example of how to create such a tool, allowing some basic customizations to excel style sheets.
Style Tool for Excel (xlt)
The Style Toos for EXceL (xlt) is designed to make it easier to customize style sheets for exporting to Excel. Style sheets have endless possibilities to format your Excel documents. This tool makes some of the most common possibilities simpler, but does not cover all possible changes, so it may not eliminate the need for editing style sheets in external tools, but may help you with some simple customizations.
Note, this tool was created and tested on 5.01 update 1 client, and assumes using default NavisionFormToExcel style sheet (update 1) as a starting point for customizations.
To use it with 5.0 client, you might need to modify the tool.
This is what you can achieve with xlt:Define a (font) style to be used on document, for example Bold, color, underline, itallic, etc.Define a style for certain condition, for example "Net Change" < 0, "Customer No." = 10000, etcAdd totals.
How to use xlt:All functionality in the tool can be accessed from form 73000 "XLT Style Card". Run the form and insert a new record to create a new custom style sheet, and specify Code and Description.
Before you can make changes to a style sheet, you need to import it by clickin on Style -> Import, and choose an existing style sheet for export to Excel (such as NavisionFormToExcel), either from the database or from a file.
On the Destination tab, specify the table and form IDs that the style sheet will be used for.
Example: Run form 73000 and press F3. Fill out Code:ChartOfAccounts, Description: export Chart of AccountsImport the standard NavisionFormToExcel style sheet (by default placed in <client folder>\Stylehseets), by clicking on Style-Import and selecting NavisionFormToExcel style sheet. In New XSLT file location field, specify the location of your customized style sheet (example: c:\temp\NewXsltFIleName.xslt).On Destination tab, specify TableID=15, select 'Style sheet for this form only' and select form 16, Chart of Accounts.
Excel Styles:You are now ready to customize your style sheet. To create new style, click Design -> Excel Styles. This shows you the styles that were imported with the standard style sheet. From here you can modify existing, or create new ones. To create a new style, press F3, and enter a Style ID. Then click on "Edit in Excel (F9)" to define this new style. It will open up Microsoft Excel, and here you can apply any formatting you wish, TO THE TOP LEFT CELL. The style you apply to this cell and it's contents will be imported and applied to all the cells your style applies to. Once you are done applying your style, close and save Excel, and then go back to NAV and confirm to import the style.
As an example, consider the following scenario: we want to modify the existing label (caption) style and also create a new style (red fonts) that we apply to records in Net Change column with negative value.To modify existing label style, click on Design-Excel Styles, press Page Up/Down to browse between records, find Style ID: Label and Press F9 (Edit in excel). In opened excel ark, go to top left cell, in Excel menu select Home-Font and select a different font (for example, Arial Black, size 14). Change also the color to blue. Close the excel and save the file. In NAV client, click on YEs on prompted question (Please apply format in Excel sheet.....). In the Style tab, check that new style is applied (you should see the record with Option=Font, Property=ss:FontName and Value=Arial Black).
You can apply same kind of change directly in the Style tab (instead of opening Excel), provided you are familiar with properties and values as defined in Excel. If modifying styles directly in Style tab, remember to click 'Update (F11)' to apply changes to style sheet.
1) moving away from the style by closing the form or moving to the next record will not save any changes you made manually (and not through excel), and
2) the tool does not validate if changes you do manually are valid.
Now we want to create a new style and apply it to Net Change column of records with net change < 0.Press F3 to create a new style, in Style ID field type RED. Click on F9 (Edit in Excel) and in opened excel sheet click on top left cell. In Excel menu, select Home-Font and select red font color. Close the Style Sheet, answer yes to save changes. In NAV Client answer yes to prompted question (Please apply format in Excel Sheet...). In General tab, You should now see properties defined for StyleID=RED
Conditional formatting:After designing the style(s), then go back to the XLT Style card, and then click Design -> "Conditional Formatting" to apply this style to certain conditions. It is not recommended that you change any of the existing layouts here, but you can create a new one by clicking "Insert (F3)". This opens a mini-wizard where you can specify which columns and values this will apply to.
To follow our example, we will now specify the condition for using the style created in last step, RED style.Close the Excel Styles form and in Xslt style card click on Design-Conditional Formatting. Click on F3 (or click on Insert) to create new condition (it is not recommended to modify any of the existnig, defualt ones). Wizard form opens. We will now create a condition to apply style red to NET Change column with values < 0. Select TableID=15 and Field 32 (Net Change). In Condition field, select'<' anad Value=0. Finally, In Choose style select RED. Click on Finish. You should now see your condition defined in General tab. Condition= (@name='Net Change') and (@value<'0').Click on Update to update the style sheet with new condition.
TotalsYou can specify fields that you want to create totals for, by clicking on Design -> "Total Fields", and select one or more (numerical!) fields. You also need to add one line of C/AL code in codeunit 403 "Application Launch Management". The following line should be added to Codeunit 403, at the top of function LanuchApp:
where XLStyleSheetDataMgt is a new local variable, type Codeunit, subtype 73001 ("XLT Style Sheet Data Mgt")
This will add totals to the data that you export to Excel, and it will add elements to the style sheet to handle these totals. Note: This will not work for RTC because RTC does not make calls to codeunit 403 (see blog here: http://blogs.msdn.com/nav_developer/archive/2008/12/16/dynamics-nav-2009-and-ms-office-integration-send-to-excel-and-word.aspx).
Export style sheet:When you have created one or more style(s) and applied them to certain conditions, then you generate a new style sheet from the "XLT Style Card" by clicking Style -> Export, and then select to export to file or database. If exporting to database, then a new style sheet will be saved in database, using the Description you have specified in the "XLT Style Card".
Open form 18 (Chart of Accounts), in toolbar menu, click on send-to button. Select Microsoft Excel and select 'export Chart of Accounts' style sheet., click on OK.Chart of Accoutns shoud lbe exported to excelm, with labels in Arial Black font, size 14. Net change column where value < 0 should be marked RED and totals should be present for Net Change column.
Other functionality: - From "XLT Style Card", tab "Standard Styles", specify a style to apply to totals. - Click Style -> Reset to re-load the stylesheet, which will erase any changes you have done since you imported it.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
All feedback regarding issues or suggestion to this tool are very welcome!
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
All feedback regarding issues or suggestion to this tool are very welcome!
Jasminka Vukovic, ( jvukovic) and Lars Lohndorf-Larsen (Lohndorf )
Microsoft Dynamics NO
Microsoft Customer Service and Support (CSS) EMEA
The Microsoft Dynamics NAV 2009 Help Toolkit and the Help Source files are now available for download from PartnerSource.
We're making the source files for the core Microsoft Dynamics NAV 2009 Help available to Partners so that you can customize the Help to enhance your add-ins or to match the solutions you sell. You can edit the source files and recompile the Help files, or you can copy from the source files into your own Help or other documentation. The source files for the Group 1 countries (Australia, Canada, Denmark, France, Germany, Italy, Mexico, the Netherlands, New Zealand, Spain, the United Kingdom, and the United States) have been released. We will make available the source files for the other countries after NAV 2009 ships for those countries.
In a post in September, I mentioned that we were updating the NAV Help Guide and the tools in the Help Toolkit we provide to assist you in creating help. This release of the Microsoft Dynamics NAV 2009 Help Toolkit includes a completely re-written Help Guide, with How To topics, walkthroughs, and guidelines for creating Help and transforming Help for the Classic client into Help for the RoleTailored client. We've also replaced the GATE tool with Help Builder, which is a more robust tool for processing source files and compiling Help files. We've also fixed some bugs in the Generate HTML Help batch job that you can use to generate Help topics for tables, fields, forms, pages, and reports.
If you have any questions or comments about these two releases, please send an e-mail to firstname.lastname@example.org.
We continue to be interested in hearing what your needs are, so we can ensure that we are improving the process for customizing and extending Help. You can add your comments below or e-mail us at email@example.com. Here are some questions to get you started:
It's important for us to ensure that we are providing an efficient way for you to customize the documentation. We're looking for volunteers to test our new Help processes, and provide us with feedback on how well these solutions meet your needs. In return, we'll offer extra assistance in using these processes. If you would like to participate, please e-mail firstname.lastname@example.org.
- Paul Chapman
Here are the steps to leverage the use of “Document Map” in a Report.
1. Design Report 113 Customer/Item Sales
2. Select View / Layout
3. Right click on Table top left corner
4. Select Properties
5. Navigate to “Groups” tab
6. Select Table1_Group
7. Select “Edit…”
8. Select “=Fields!Customer_Name.Value” in “Document map label”
9. Select “OK”
10. Navigate to “Navigation” Tab
11. Enter “Customer” or what you want the title to be in the Document List
12. Select “OK”
13. Save and import RDLC changes, and then compile report in Classic client.
14. Run Report 113, it now looks like this:
Notice the expand in the top left corner. Unfortunately this does not display anything in NAV 2009 RTM, this will be fixed in SP1.
16. Click the expand button to see the Document Map
17. Now click the customer “Klubben” and notice that the report in to the right navigates to the area where info about the customer “Klubben” is displayed.
Also check out Waldo's Blog for an example on how to use Document map:http://dynamicsuser.net/blogs/waldo/archive/2010/03/04/nav-2009-reporting-document-map.aspx
Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV
UPDATED April 2nd 2009: Several people have reported to me that the first solution I posted for “How to use the Picture stored in Company Information table:” did not work for them. So I have now updated the section on how to use the picture stored in the Company Information table. The picture is now added in a slightly different way.
There are several ways to do this. Either you use the existing Picture which is stored in table 79 Company Information or you can embed the image into the Report itself, or you can link to the picture externally. In this scenario I will be looking at how to use the picture already in the database and and how to embed the picture into the report itself.
How to embed the Picture:
1. For us to be able to embed the picture into a report we need to have the Company Picture as a file. I have exported the company picture to c:\temp\CompanyPicture.bmp
2. Ok, now let us add this to Report 111. Design Report 111
3. Select "View/Layout" to go into Visual Studio.
4. Select the Report and choose "Report/Embedded Images"
5. Select "New Image..." and select your company picture, in my case c:\temp\CompanyPicture.bmp
6. With that completed open the Toolbox and add a Image control to the Report.
7. With the Image Control added we need to the set correct properties for this control. Set Source=Embedded, Value=companypicutre and MIMEType=image/bmp
9. Save and import RDLC changes, and then compile report in Classic client.
10. Run "dynamicsnav:////runreport?report=111" to open up report 111. And as you can see below company picture is displayed.
We have no looked at how to embed the Company Picture into the report. Now let us have a look on how to use the Company Picture already in the database.
How to use the Picture stored in Company Information table:
1. Deleted the above embedded Company Picture, if you followed the steps above.
2. Design report 111
3. Now we need to have a Variable with is references to the Company Information Table. Lets create a new called CompanyInfo
4. Exit C/AL Globals and ad the following code to the "OnPreReport()" trigger: CompanyInfo.CALCFIELDS(Picture);
5. Now we should add a Picture Box on the Sections with SourceExpr: CompanyInfo.Picture. We need to do this to have the element available for us in Visual Studio.
6. It is now time to open Visual Studio. Select "View/Layout"
7. Notice that we now have an new entry in the DataSet
8. Now lets add this to the report. We need to add the Company Picture first as a TextBox to the body of the report. This will enable us to use it in the Page Header. We add this TextBox in top of the Body.
9. We give the TextBox the following properties:
Value: “=Convert.ToBase64String(Fields!CompanyInfo_Picture.Value)” Name: “CompanyPicture” Visibility Hidden: “True” Color: “Red”
10. Now add the following code to the Report properties.
Shared PictureData as Object
Public Function GetPicture() as Object Return PictureData End Function
Public Function SetPicture(NewData as Object) if NewData>"" PictureData = NewData end if End Function
11. Now let us add the Image control just as we did for the embedded scenario. Open the Toolbox and add a Image control to the Report.
12. With the Image Control added we need to the set correct properties for this control.
Value: “=Convert.FromBase64String(Code.GetPicture())” Source: “Database” MIMEType: “image/bmp”
13. Add textbox to Page Header. Note! This textbox has to be placed above the Picture control just added
Value: “=Code.SetPicture(ReportItems!CompanyPicture.Value)” Visibility Hidden: “True” Color: “Red
14. Save and import RDLC changes, and then compile report in Classic client.
15. Run "dynamicsnav:////runreport?report=111" to open up report 111. And as you can see below company picture is displayed.
In the new RoleTailored client in Microsoft Dynamics NAV 2009 you do not have the possibility to place a field exactly where you want on a page. You can control in which order they should be shown, but where the field will be placed, is up to the RoleTailored client. This is quite different compared to the Classic where we could define Xpos and Ypos for each control.
Using Sub groups in the Page Designer you can control how fields are grouped. This way you can fix fields to the left side of a FastTab and to the right side of the FastTab. Let see an example of this.
Here is how the "General" FastTab on the Customer Card looks out of the box with no customizations:
But adding 2 Sub Groups you can have the General FastTab look this:
How did I do this? Well let's have look at Page 21 in Page Designer where I have added these 2 groups marked with blue:
While the "General" and "Group 1" seem very similar in Type and Subtype, the important part to notice here is INDENTATION. My "Group 1" is indented under the "General" FastTab group. And when indented under a FastTab group this will be displayed as group and not as a FastTab. Then of course I have indented the fields "No., Name, Address, Address 2, Post Code and City" below "Group 1" to have these fields captured in this group. For "Group 2" I done the same.
Actually we go one indention level deeper. Try to add a Sub Group in either "Group 1" and "Group 2" and indent some of the fields into to this new sub Group.
Using Sub Groups gives a bit more control on where fields are placed in the page on the RoleTailored client.