Plataan

Key Tips: CTRL-F10 shortcut in NAV 2009

In Dynamics NAV 2009 the shortcuts have changed. They are not the same anymore as they were in previous versions. For an end user that is used to work with the 'old' shortcuts this might be confusing and difficult to learn.

A nice trick or workaround is the CTRL-F10 shortcut. When you use this one in the Role Tailored client then the Action groups get a number assigned. That way you can use te keyboard and the numbers to click on the actions, instead of using the mouse and so do it faster.

Example:

Open the Customer List in the RTC:

Now, press CTRL-F10 and see what happens:

Numbers appear below the Action Groups. Now press for example on 2 using the keyboard:

You will see that each action in the action group gets a number assigned. Now you can use that number as the shortcut to that Action. For example if you now press on 4, the Customer Statistics window will open:

Nice, is'nt ?

Summary:

The CTRL-F10 shortcut will turn on or off key tips in the Action pane. When you press the key tip number, that action gets carried out (first key selects a group and shows key tips within that group).

More about Group containers in NAV 2009

Something interesting about Group containers in Pages in Dynamics NAV 2009 came to my attention, and I thought why not share it?

When designing page objects in NAV 2009, you can use Group containers to group fields together. This way you can somehow control where fields should be shown on a page and you can group them together.

But a Group container also has some very interesting properties like for example: Visible and Enabled. Ok, that's not so special you might be thinking…

è But heres the clue: as from NAV 2009 these properties can be set as the result of an expression.

What does this mean?

You are now able to "play" with the visible and enabled property for a group container via C/AL code. This way you can enable or disable a group of fields in 1 go.

The following is valid for the Page properties:

• Visible
• Enabled
• Editable

For example in the Customer Card Page:

Add a Group container around the fields Address and Address2:

Create a Global Boolean variable BAddressgroupVisible and assign it into the Visible property of the Group:

In the C/AL code, for example in the OnAfterGetRecord trigger add the following code:

Result: the fields are shown:

Now assign FALSE to the Boolean:

Result: the fields are not shown:

Very interesting, isn't it?

As you can see, you instead of using a Boolean variable, you could let it depend on a field from the table. As a consequence the fields will then be shown for some records and not for others.

There's a little trick you need to apply if you use a variable. In the properties of the variable you need to put the property IncludeInDataset to Yes:

If you don't do that then you will get the following error message at runtime:

Remark:

• The IncludeInDataset property defines that the variable will be added to the DataSet of the Page.
• The Visible and Enabled properties can only be set from fields contained in the DataSet of the Page.

Sure Step Spring Release 2009

Last week a new version of Microsoft Dynamics Sure Step was released: Spring Release 2009.

This release made several new features available and simplified the "standard project type" within Sure Step.

The release notes are telling us following:

 What‘s new in the spring 2009 release of Sure Step?
With the spring 2009 release of Sure Step, both your presales and implementation consultants will find valuable new questionnaires, requirements gathering documents, fit gap analysis guidance, product-focused testing scripts, and a variety of new templates across all implementation phases. The updates, which are most valuable to you if you serve customers in the mid-market, include:

• The “Standard” project type, which has been simplified, makes it easier for you to use Sure Step for the implementation of Microsoft Dynamics solutions for mid-market customers.
• A view of “key deliverables” for each project enables you the ability to more quickly and easily adapt Sure Step to your existing Microsoft Dynamics practice.
• A new search tool makes it easier for you to quickly find the right guidance for any implementation situation.
• Role-tailored and product-specific guidance developed by several Microsoft partners provides you even more best practices that can be applied to your projects.
• Improved product upgrade guidance helps you set the stage for successful technical upgrades to current and future Microsoft Dynamics releases.

I installed this new release last Friday, just before a Sure Step training that I delivered in Vilnius, Lithuania. What did I experience and learn from this release so far?

1. Search functionality available. This is what we really needed. Same question in each training Sure Step training: "is search available". So, yes it is and it works pretty fine as well.
2. Mapping between Diagnostic Phase and the Sales framework. Sure Step is also supporting Sales and driving towards successful sales by means of decision accelerators. A recurring question is how that these decision accelerators relate to the sales framework. In this release, we can find the answer illustrated by a simple diagram. Pretty nice.
3. Simplified and rationalized “Standard Project Type”. Before this release the standard project type was nearly identical to the enterprise type of project. Then why have both project types if they are the same? This question is now addressed by simplifying the standard project type. This really makes sense and it is much more tuned with mid-market implementations. It makes the process also much more adoptable for most implementation partners. However in the rush to simplify and rationalize it, I am afraid that a few things in the client were neglected. I can see some inconsistencies and negligence. For instance in the table of content no phases are identified which results in a hard to comprehend list of items (in the diagram of the standard type the phases are identified) and there a few more surprising things. For instance, risk management still appears in the standard project type but no more issue management? I guess these things still need to be tuned. But let’s concentrate on the important thing: the standard project is now really a standard one and no longer the enterprise type and that is ok.
4. New “working Deliverables”. In each Sure Step training we try to divide the document deliverables and tools into working and key deliverables. Working deliverables are those that can help consultants, developers, … to work in a much more efficient manner, to ramp up new hires and to standardize company quality. Key deliverables are customer facing deliverables, the end result of a process that needs validation. In the spring release I can see that more working deliverables became available. As an example, much more questionnaires (I really love these) and they are also much more tuned to the products and to role based principles. Good stuff that we like.
5. Filter possibility. Not really certain about this one yet. Looks like great functionality that allows you to filter the deliverables of your projects. For instance if you want to see the key deliverables of a specific phase or the documents for technical audience or…the filters will help you. I do not seem to manage however to filter on documents of one project only, it looks like I also have other content in the result set. Anyway, could be me, need to investigate more.

So,  great new features and content in this release that I need to explore in greater depth in the upcoming days and weeks. And yes, some critical questions to pose…once I got the answers, I will blog about it.

PS: I forgot some things

• The new spring release is also compliant with IE8. Previous version was not.

• I also tried the updater first but that didn't work for me, I need to do a full new install. No worries you will not loose your existing projects.

• For the NAV people: some fresh new NAV tools and templates were made available in this release

How can I create a Link in an email that opens a specific page in the Role Tailored Client?

In the Classic Client for Dynamics NAV, and in older versions (4.X, 5.x) when creating emails from C/AL code, we had the possibility to use the Form.URL method and include it into the body of an email. Whenever someone then clicked on the Link, the Classical Client would be opened and the corresponding Form was shown.

In Dynamics NAV 2009, in the Role Tailored Client, the Form.URL method is no longer supported. The RTC does not display Forms, it uses Pages and Pages do not have an URL method.

So, how can we then create a Link towards a specific Page?

To solve this, you have to create the URL as follows: dynamicsnav:////runpage?page=<Page No>&bookmark=<URL>.

But how can we get the BookmarkURL for a specific Page?

Well, what you need to do is use a RecordRef variable that points towards the specific Page and use its RecordId property. The problem might then be that your code is in a Codeunit and can sometimes be called from within a Form or from within a Page. How can you make your code compatible with both environments?

To solve these kinds of issues, in C/AL there's a new function available: ISSERVICETIER:

• If you call ISSERVICETIER from the Classic client, then the function returns false.
• If you call ISSERVICETIER from the RoleTailored client, then the function returns true.

This allows you to execute different code that is based on the client that you are running.

Here's an example of how you can write your code, to be compliant with the Classical Client and the Role Tailored Client:

Keep in mind that in the past (before Dynamics NAV 5.0 and 2009) you could use the"Mail" Codeunit to create and send emails. As from Dynamics NAV 5 and 2009 it is also possible to use SMTP (Codeunit "SMTP Mail") to do this.

In the code example above I used a Mail Codeunit variable to send mail in the Classic Client and I used SMTP for the Role Tailored Client. But of course you can also use SMTP from within the Classical Client.

Get trained

 

a CEO was debating a training initiative for his company, someone asked him :

  "What if you train everyone and they all leave?"

 

He responded:

  What if we don't train them and they all stay?

How can I see Notes in the Dynamics NAV 2009 Classic Sql Client?

In the Role Tailored Client you have a new functionality to use called Notes. Note work very similar to Comments in the Classic Client. For example, you open a Customer Card in the RTC and then create a Note for that Customer like this:

But the problem is that these notes created in the Role Tailored Client are not accessible via the Classic Client. So if you are working in Mixed Mode, meaning using the Classic Client and the Role Tailored Client on the same Sql Server database, then notes created via the RTC cannot be displayed in the Classic Client.

How can we make these Notes accessible via the Classic Client?

Well, you are probably thinking, no problem, just create a form around the Notes table and link it to for example the customer card. Well, that's not possible, because there's no Notes table. So the question is where are these Notes stored in the database? After a little research I found that Notes are stored in the Record Link table. This table is used to store Record Links, and now this table has been redesigned to also store Notes:

So, now we know where to find Notes in the database, so now you should be able to also visualize Notes in the Classic Client.

How can you now visualize a Note?

Well, the Note is saved as a BLOB field in the table, so you can for example export it to a Text file and then view it.

Example Form:

And the code behind the View Note button could be this:

 

 

Reporting Services Tip about using the user's regional settings.

When creating Sql Server Reporting Services Reports you can sometimes end-up with data in US format instead of the local regional settings of the user.

For Example:

Here we have a report showing the Sales LCY and Profit LCY by Salesperson and Country in a matrix control. The data is coming from the Dynamics-NAV Sql database, but the formatting of the amounts is in US format, even when the report is running on a system that has Belgium regional settings.

How can we correct this?

Well there's a simple solution for this common problem. In the Report Designer in Visual Studio, the report has a LANGUAGE property:

By default this will contain English (United States), but by changing it to an expression you can make it dependant on the users Regional Settings:

Select <Expression>, then the Expression Editor opens. Then type in =User!Language

Run the report again to see the result:

Then Report will recognize user client's culture settings, e.g. Internet Explorer's languages settings.

Integrate the Dynamics NAV 2009 Role Tailored Client in Internet Explorer

Recently I discovered something very interesting on the Dynamics NAV 2009 Role Tailored Client, more exactly on possible ways to create shortcuts and hyperlinks to start the Role Tailored Client.

When you are debugging pages and/or reports, you usually run them from the command prompt like this:

But you can do the exact same thing from within Internet Explorer by typing the command in the URL bar: "dynamicsnav:////runreport?report=50000"

The following table shows some examples of hyperlinks and provides information about how to specify parameters

So in Internet Explorer you could create a menu containing hyperlinks to launch specific pages, reports or items in the navigation pane.

Just go into Internet Explorer and create a hyperlink like this:

And then you can click on it in your favorites and it will launch the Role Tailored client and the page, report or item in the navigation pane you defined in the hyperlink.

So, this gives us the possibility to create a separate menu in Internet Explorer Favorites containing a kind of Navigation Pane for the Role Tailored client.

Nice isn't it?

RIM: Data Migration in NAV 5.1 – Points of Attention

For me, one of the most useful components of the RIM toolkit, is the Data Migration tool. I've been using it quite a lot, also in RIM V1, where it was still a bit rough edged. However, a while ago when I was implementing a company in NAV 5.1 I bounced into a few problems. After doing some research, I was able to resolve my issues and now everything works again smoothly. Because I heard from some of my students that they had the same problems, it seemed worthwhile to write a blog item on this.

Problem 1: Blank date fields

The first problem I had arose when I wanted to import a journal (for example to import open customer or vendor entries). After having set up everything as it should, I started importing some journal lines, and at first sight, everything seemed to be OK. By going in the Migration Overview window to the Migration Records, I saw that all the information was imported flawlessly:

As you can see, the posting dates have been filled in properly as it should. So up to the next step: Apply Migration Data.
Also this step works fine. The Migration records were processed and transferred to the General Journal, in this case a Sales Journal. But there the problem occurred:

As you can see in the screenshot above, the Posting Date is not filled. This is not only the case for the Posting Date, but for all the date fields. It seems that NAV somewhere loses the dates when applying the migration data. This is of course a bad thing, because now I can't post my Sales Journal, and this basically makes the Data Migration tool worthless for importing journal lines.

BUT: this was all happening in a NAV 5.1 version, and I remembered that importing journal lines in NAV 5.0 was working well. So, I started comparing the Data Migration tool from the 2 different version with each other and I discovered some differences in Codeunit 8611 (Migration Management).
I exported CU 8611 form a NAV 5.0 database and I imported it in a NAV 5.1 database. When running the same procedure once again, everything worked perfect, and all the date fields were filled in my Sales Journal:

To be honest, I didn't examine the differences in CU 8611 between the 2 NAV versions detailed enough to know the exact reason of the problem. But if you might have the same problem, then maybe you also should try to import CU 8611 from a NAV 5.0 database into your NAV 5.1 database. Afterwards all your date fields will be filled perfectly!
So far, this "workaround" didn't got me into other problems.

Problem 2: Amount fields

My 2^nd problem wasn't too complicated. When importing amounts, using the Data Migration tool, I saw that NAV did some weird things:

In the Excel you see the amounts 3.000,00 and 9.000,00. When importing in NAV 5.1 and applying the migration data, the result is:

Instinctively I of course started changing my local settings of Windows, but at first this didn't work, because I only changed the number and decimal settings. To get it working I really had to change my location. When changing it to "United States", everything worked well.

 

So if some of you should have one of these problems using, the Data Migration tool, maybe you should try this.

 

Dynamics NAV 2009: Upgrade Toolkit

The Upgrade Toolkit for Dynamics NAV 2009 (W1) has been released. It's available for download on Partner Source.

It is not necessary to perform a data upgrade to move from Microsoft Dynamics NAV 5.0 to Microsoft Dynamics NAV 2009. To take advantage of the Role Tailored client in Microsoft Dynamics NAV 2009, you must carry out an object upgrade from Microsoft Dynamics NAV 5.0 SP1.

This means that a Dynamics NAV database running on version 5.0 with Service Pack 1 can be directly technically upgraded towards Dynamics NAV 2009. Older versions before 5.0 SP1 should first be upgraded towards version 5.0 SP1.

The Upgrade Toolkit consists of a set of tools and procedures that are designed to help you upgrade to Microsoft Dynamics NAV 2009. The tools cover the upgrade for the following products:

• Microsoft Dynamics NAV 3.70
• Microsoft Dynamics NAV 4.00

The tools and procedures that you must use vary depending on the version you are upgrading from.

When you upgrade to a Microsoft Dynamics NAV 2009 database, you can use the database in either the Classic client or the Role Tailored client. However, if you have designed new forms or customized standard forms in the Classic client and you want to use them in the Role Tailored client, then you need to transform the forms. (Using the Form Transformation Tool)

Regarding the Form Transformation Tool, there was an issue with assigning correct control ID's when the Microsoft Dynamics NAV 2009 application is customized. A hot fix has been released here: https://mbs.microsoft.com/partnersource/support/selfsupport/hotfixes/captionidsusingtransformationtoolformicrosoftdynamicsnav2009

 

Book Release: Implementing Microsoft Dynamics NAV 2009

Microsoft Dynamics NAV 2009 has been released in a number of countries already (in Belgium we need to wait a bit longer for the release...). Excellent timing to start preparing and exploring this great new Dynamics NAV product.

Vjeko Babic, a Microsoft consultant and well known blogger (Navigate into success) has written an interesting book about Dynamics NAV 2009: " Implementing Microsoft Dynamics NAV 2009".

The book is co-authored by David Roys, and reviewed by Plataan, and it discusses the freshly released Microsoft Dynamics NAV 2009: the new architecture, the RoleTailored client, the implementation approach, the dos and don’ts, with a lot of examples, tips and tricks and insight into the new version. An excellent gift for Xmas!

Microsoft SQL Server 2008 Feature Pack (free)

Download the Microsoft SQL Server 2008 Feature Pack, a collection of stand-alone install packages that provide additional value for SQL Server 2008.

The Feature Pack is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

• Redistributable components for SQL Server 2008.
• Add-on providers for SQL Server 2008.
• Backward compatibility components for SQL Server 2008.

Amongst these packages you will also find some useful Reporting Services downloads, like for example the new Report Builder application:

• Microsoft SQL Server 2008 Report Builder 2.0

  Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users with a Microsoft Office look and feel. Report Builder 2.0 supports the full capabilities of Report Definition Language (RDL) including flexible data layout, data visualizations, and richly formatted text features of SQL Server 2008 Reporting Services. The download provides a stand-alone installer for Report Builder 2.0.

• Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies

  Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report processing and management capabilities in SharePoint integrated mode. This version also includes data-driven subscriptions. The download provides a Report Viewer Web part, Web application pages, and support for using standard Windows SharePoint Services or Microsoft Office SharePoint Services.

Here's the complete list of packages:

• Microsoft ADOMD.NET
• Microsoft Analysis Management Objects
• Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider
• Microsoft SQL Server 2005 Backward Compatibility Components
• Microsoft SQL Server 2008 Command Line Utilities
• SQL Server Compact 3.5 SP1
• Microsoft Connector 1.0 for SAP BI
• Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007
• Microsoft SQL Server 2008 Datamining Viewer Controls
• Microsoft SQL Server 2005 Driver for PHP
• Microsoft Core XML Services (MSXML) 6.0
• Microsoft SQL Server 2005 JDBC Driver 1.2
• Microsoft SQL Server 2008 Management Objects
• Microsoft OLEDB Provider for DB2
• SQL Server Remote Blob Store
• Microsoft SQL Server 2008 Native Client
• Microsoft SQL Server 2008 Policies
• Microsoft Windows PowerShell Extensions for SQL Server
• Microsoft SQL Server 2008 Replication Management Objects
• Microsoft SQL Server 2008 Report Builder 2.0
• Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
• Microsoft SQL Service Broker External Activator
• Microsoft SQL Server System CLR Types
• Microsoft SQLXML 4.0 SP1
• Microsoft Sync Framework

How to use Enterprise No in NAV 5.0 SP1

That the old-fashioned VAT No. is history in Belgium and replaced by the Enterprise No. is probably well spread by now. But how this Enterprise No. should be mentionned is less familiar to most people.

For companies who already had a VAT No. (xxx.xxx.xxx) the Enterprise No. is composed as follows: 0xxx.xxx.xxx. If your customer or vendor is located in Belgium (and you fill in the country code as BE) NAV will check whether this number is correct by performing the MOD 97 test and it will prohibit you from filling in the VAT No.. For customers or vendors from abroad NAV will only allow you to fill in the VAT No. and not the Enterprise No..

However, there is more to it than that. The Enterprise No. should be proceeded by:

• BTW 0xxx.xxx.xxx or TVA 0xxx.xxx.xxx- for companies who only operate in Belgium
• BTW BE 0xxx.xxx.xxx or TVA BE 0xxx.xxx.xxx – for companies who also operate outside Belgium in the EU

For companies of the type 'vennootschap' the Enterprise No. should also be followed by RPR ('rechtspersonenregister') and the location of the legal court ('arrondissementsrechtbank') to which the company belongs:

• BTW BE 0xxx.xxx.xxx RPR Brussel

Why is it so important to fill this in correctly in NAV 5.0 SP1? If you forget to mention BTW / TVA (depending in which language you are using NAV) that customer will not appear on your yearly VAT listing!

You can always find more information about our wonderfull Belgian Enterprise No. on the following website: www.ondernemingsnummers.be

Max. no. of XML records to send ?

Recently I got this error when trying to use the Export to Excel button on the Customer list of a Dynamics NAV database:

This made me wonder: Is the export to Excel function limited to only 5000 records ?

Well, yes, it is, but the good news is that you can override this limit. When you go to Tools è Options there's a field called: "Max. no. of XML records to send" and its value is set by default to 5000.

So by increasing this you are able to send more than 5000 records towards Excel:

Of course, keep in mind that increasing this to a high value, you potentially can decrease system performance…

Getting the text out of a Dialog (Error/Message) box in Dynamics NAV

Have you ever been in a scenario that you are running Dynamics NAV and an error occurs? Sometimes you may want to have the exact error message in txt format to do some further research, for example in a search engine.

Well, I found out by accident that when you see a dialog box poping up in Dynamics NAV and you select it and do a Copy (CTRL-C), then you can do a Paste (CTRL-V) in a text editor, and voila, the exact error message is now pasted…

For example:

"CTRL-C", "open notepad", "CTRL-V" and this is the result:

Nice, isn't it ?

You do not have permission to delete the MBS MenuSuite object.

Sometimes when you are upgrading a Dynamics NAV database towards Dynamics NAV 5 or Dynamics NAV 5 SP1 with the Upgrade Toolkit you might receive this error when executing Task 5: Delete objects of the Upgrade Toolkit:

The issue occurs because the Upgrade Toolkit tries to delete all MenuSuite objects, but your partner license does not have permissions to delete the MBS MenuSuite object.

Possible solutions:

• You can delete all the Objects except for the Objects you do not have permission for manually.
• Alternatively change the code in Codeunit 104002 – "Delete Objects Excl. Tables" to not delete MenuSuite Objects and handle only these Objects manually. This is not difficult, you just have to remove MenuSuite from the Filter of objects to be deleted:
  
  
  Then after you Import the file containing the Dynamics NAV 5 Objects, make sure that you choose "Replace All" in the Import Worksheet.
• The best solution is that you have your license updated. So to resolve this issue, please send a request to Microsoft Business Solutions Licensing Team to update your license file.

Be careful with Editable SumIndexFields!

When you define a SumIndexField on a table, sometimes people forget to make the field Not Editable. As a consequence, on forms where the SIFT is shown, the user has the possibility to write something into a SIFT field.

What happens in this case and why is this 'dangerous'?

Let's take as an example the Cronus Demo Database, Dynamics NAV 5.1. (Native) In the Customer table (18) there's a field called: "Balance (LCY)". This field displays, in LCY, the customer's current balance. Dynamics NAV automatically calculates and updates the contents of the field using the entries in the Amount (LCY) field in the "Detailed Cust. Ledger Entry table".

When you take the Customer table into design, you can see this calculation in the Calculation Formula of the FlowField:

Now, just for fun, let's make the FlowField Editable:

Ok, now go to the Customer Card and open customer 60000:

Our Balance (LCY) field has now become an editable field. When you click on the Drilldown this is shown:

Now, enter 100 in the Balance (LCY) field:

When we click on the Drilldown, the list is still empty:

But, when you open the "Detailled Cust. Ledg. Entry" table:

You immediately notice that a line was added here, containing only an "Entry No.", "Customer No." and an "Amount (LCY)"!

This is because the FlowField was editable, and for the newly entered total of 100 to be correct, Dynamics NAV went into the underlying table and created an entry on its own…

This is why it is strongly advised to make FlowFields NOT Editable when showing them on Forms.

Confused by WIP? Not anymore!

Two always know more than one. So we put our heads together, trying to figure out how these new WIP methods in Microsoft Dynamics NAV 5.0 actually work. This is what we found out…

Actually it is fairly simple. First of all you have to make the following decision: Do I recognize completed job tasks during the project? And if so, do I recognize the costs or the sales prices?

	WIP –METHOD
No	Completed contract
Yes – cost	Cost Value or Cost of Sales
Yes – price	Sales Value or Percentage of Completion

So the question that was bothering us, what is then the difference between Cost Value and Cost of Sales and between Sales Value or Percentage of Completion. Let's explain with the following example:

STEP 1: CREATE JOB

This is a job divided over several tasks, where each task contains one or more planning lines. The job is sold to the customer for a fixed price (the job planning lines of type schedule are not equal to those of type contract).

STEP 2: REGISTER JOB USAGE & INVOICE THE CUSTOMER

Through the job journal we register the hours performed by Davy on 01/01/08 (job task 1000) and on 02/01/08 (job task 1001). We also register the items used on 02/01/08 (job task 1001), but the cost of these items has increased since the planning to € 800,00 per item. On 31/01/08 we send the customer an invoice, but only for the performed hours of Davy. This results in the following job overview.

STEP 3: CALCULATE WIP

Try out the different WIP Methods on the Job card for the calculation of the WIP Amounts. 

WIP Method	Cost value	Sales value	Cost of sales	Percentage of completion	Completed contract
Total WIP Sales Amount	0,00	2.488,63	0,00	4.167,19	-1.328,00
Total WIP Cost Amount	2.122,27	0,00	1.626,25	0,00	2.144,50
Recog. Sales Amount	1.328,00	3.816,63	1.328,00	5.495,19	0,00
Recog. Costs Amount	22,23	2.144,50	518,25	2.144,50	0,00

Cost Value vs Cost of Sales

Both WIP methods will recognize the revenue of the job at the moment of invoice to the customer. Both WIP methods will do this based on the costs related to the job. So in both cases we will get the same basic formula to calculate the WIP Cost Amount:

Total WIP Cost Amount = cost of consumption – cost of invoice

Notes:

• you can compare the cost of consumption with the batch-job 'Calculate Job WIP Value' in NAV 4.0 and the cost of invoice with the batch-job 'Calculate Job Recognition' in NAV 4.0

• cost of consumption and cost of invoice are no NAV-fields, but used to explain the calculation

However, the way in which these costs of consumption and costs of invoice are calculated are slightly different.

Cost Value

The cost of consumption is initially represented by Usage (Total Cost). However this cost is corrected to represent the part of the agreed sales price versus the planned sales price.

Cost of consumption = Usage (Total Cost) * ( Contract (Total Price) / Schedule (Total Price) )

The cost of the invoice should be interpreted as the planned cost of the job, the costs that you would normally make with all consumptions and is initially represented by Schedule (Total Cost). However this cost is corrected to represent the part of the invoiced sales price versus the sales price of planned consumption.

Cost of invoice = Schedule (Total Cost) * ( Contract (Invoiced Price) / Schedule (Total Price) )

→ Total WIP Cost Amount = [ Usage (Total Cost) * ( Contract (Total Price) / Schedule (Total Price) ) ] - [ Schedule (Total Cost) * ( Contract (Invoiced Price) / Schedule (Total Price) ) ]

Example:

Cost of consumption = (297,00 + 1.847,50) * ( (664 + 7.291,60 + 332,00) / (498,00 + 5.686,60 + 166,00) ) =2.798,595

Cost of invoice = (297,00 + 2.838,24 + 99,00) * ( (664,00 + 664,00) / (498,00 + 5.686,60 + 166,00) ) = 676,3252

Total WIP Cost Amount = 2.798,595 – 676,3252 = 2.122,27

Cost of sales

The cost of consumption is simply represented by Usage (Total Cost). The actual consumption is placed onto the balance accounts, without a correction.

Cost of consumption = Usage (Total Cost)

To recognize the invoiced job costs, we look at the initially planned costs, represented by Schedule (Total Cost). This is the same as for the WIP method Cost Value. However, a different correction of this cost is taken into the result. A percentage is calculated, based on the invoiced sales price versus the sales price of planned invoicing.

Cost of invoice = Schedule (Total Cost) * ( Contract (Invoiced Price) / Contract (Total Price) )

→ Total WIP Cost Amount = Usage (Total Cost) - [ Schedule (Total Cost) * ( Contract (Invoiced Price) / Contract (Total Price) ) ]

Example:

Cost of consumption = 297,00 + 1.847,50 = 2.144,50

Cost of invoice = (297,00 + 2.838,24 + 99,00) * ( (664,00 + 664,00) / (664,00 + 7.291,60 + 332,00) ) = 518,2527

Total WIP Cost Amount = 2.144,50 – 518,2527 = 1.626,25

Conclusion

Use Cost of Sales in a time & material project. Use Cost Value in a fixed price project.

If scheduled prices are equal to contracted and invoiced prices, then the Cost of Sales and the Cost Value will calculate the same Total WIP Cost Amounts.

Sales Value vs Percentage of Completion

Both WIP methods will recognize the revenue of the job at the moment of invoice to the customer. Both WIP methods will do this based on the sales prices related to the job. So in both cases we will get the same basic formula to calculate the WIP Cost Amount:

Total WIP Sales Amount = expected sales price – price of invoice

Notes:

• you can compare the expected sales price with the batch-job 'Calculate Job WIP Value' in NAV 4.0 and the price of invoice with the batch-job 'Calculate Job Recognition' in NAV 4.0
• expected sales price and price of invoice are no NAV-fields, but used to explain the calculation

In both cases, the price of invoice is easy to retrieve, since it is represented by Contract (Invoiced Price). No corrections are necessary here.

Price of invoice = Contract (Invoiced Price)

However, the way in which the price of consumption is calculated is slightly different.

Sales Value

The expected sales price is initially represented by the Contract (Total Price). However, a correction is made based on the expected price of actual consumption versus the prices of planned consumption.

Expected sales price = Contract (Total Price) * ( (Usage (Total Price) / Schedule (Total Price) )

→ Total WIP Sales Amount = [ Contract (Total Price) * ( ( Usage (Total Price) / Schedule (Total Price) ) ] – Contract (Invoiced Price)

Example:

Expected sales price = (664,00 + 7.291,60 + 332,00) * ( (498,00 + 2.426,60) / (498,00 + 5.686,60 + 166,00) ) = 3.816,634

Price of invoice = 664,00 + 664,00 = 1.328,00

Total WIP Sales Amount = 3.816,634 – 1.328,00 = 2.488,63

Percentage of completion

The expected sales price is initially represented by the Contract (Total Price). This is the same as for the WIP method Sales Value. However a different correction of this price is taken into the result. The correction is based on the cost of actual consumption versus the cost of planned consumption.

Expected sales price = Contract (Total Price) * ( (Usage (Total Cost) / Schedule (Total Cost) )

→ Total WIP Sales Amount = [ Contract (Total Price) * ( Usage(Total Cost) / Schedule (Total Cost) ) ] – Contract (Invoiced Price)

Example:

Expected sales price = (664,00 + 7.291,60 + 332,00) * ( (297,00 + 1.847,50) / (297,00 + 2.838,24 + 99,00) ) = 5.495,188

Price of invoice = 664,00 + 664,00 = 1.328,00

Total WIP Sales Amount = 5.495,188 – 1.328,00 = 4.167,19

Conclusion

Sales Value and Percentage of Completion calculate the same Total WIP Sales Amounts, unless the price or cost of the usage should change since the planning.

If you want to take into account changes in sales prices, then use Sales Value.

If you want to take into account changes in costs, then use Percentage of Completion.

Completed Contract

This last method will not recognize the revenue of the job untill the job is completed. Both realized costs and realized sales prises are taken into balance as the job progresses.

→ Total WIP Sales Amount = Contract (Invoice Price)

→ Total WIP Cost Amount = Usage (Total Cost) 

Example:

Total WIP Sales Amount = 664,00 + 664,00 = 1.328,00

Total WIP Cost Amount = 297,00 + 1.847,50 = 2.144,50

STEP 4: HOW THE WIP-TOTAL FIELD INFLUENCES YOUR WIP AMOUNTS !

Now let's take a look how the field 'WIP-Total' on the job task lines. So set the field on 'Total' for each job task line and recalculate the WIP amounts for each WIP method.

WIP Method	Cost value	Sales value	Cost of sales	Percentage of completion	Completed contract
Total WIP Sales Amount	0,00	2.447,49	0,00	4.082,33	-1.328,00
Total WIP Cost Amount	2.037,53	0,00	1.589,04	0,00	2.144,50
Recog. Sales Amount	1.328,00	3.775,49	1.328,00	5.410,33	0,00
Recog. Costs Amount	106,97	2.144,50	555,46	2.144,50	0,00

You will notice that the calculated amounts are slightly different, while the WIP methods and the used formulas are not different than in the previous step. However, the WIP methods are now applied per job task line instead of for the entire project.

Total WIP Cost Amount = ∑ Total WIP Cost Amount / job task line

Total WIP Sales Amount = ∑ Total WIP Sales Amount / job task line

Example - Cost Value

Job Task No. 1000 Total WIP Cost Amount = ( 297,00*(664,00/498,00) ) – ( 297,00*(664,00/498,00) ) = 0,00

Job Task No. 1001 Total WIP Cost Amount = ( 1.847,50*(7.291,60/5.686,60) ) – ( 2.838,24*(664,00/5.686,60) ) = 2.037,53

Job Task No. 1002 Total WIP Cost Amount = ( 0,00*(332,00/166,00) ) – ( 99,00*(0,00/166,00) ) = 0,00

Total WIP Cost Amount = 0,00 + 2.037,53 + 0,00 = 2.037,53

Conclusion

You can combine or separate job task lines for WIP calculation by setting the WIP-Total to Total.

You can exclude job task lines for WIP calculation by setting the WIP-Total to Closed.

Plataan changes Open Kalender Financials NAV 5.0!

Great news! Plataan has made some small changes in its open kalendar trainings concerning financial topics. We have combined some of the financials trainings, so that you can save time and at the same moment are completely prepared to take the Microsoft Dynamics NAV 5.0 Financials Certification Exam.

We have combined these 3 courses in the new training called 'Financials Track', taking you only 5 days, which you would normally spent 6 days to complete.

• Microsoft Dynamics NAV 5.0 Application Setup
• Microsoft Dynamics NAV 5.0 Finance
• Microsoft Dynamics NAV 5.0 Business Intelligence for Information Workers

Although not necessary for the financials exam, you can also follow these onsite trainings to increase your financial knowledge to an expert level:

• Microsoft Dynamics NAV Resources & Jobs
• Microsoft Dynamics NAV Fixed Assets

Check out our trainingcalendar for the exact locations and dates and enroll today!  

Noisy Van

I wanted to bring this new & exiting blog to your attention: http://noisyvan.wordpress.com

It contains a number of very interesting items about for example How to:

• Avoid "not a valid time unit" Errors with Multilanguage Calcdates
• Show Shipment Information on Sales Invoices
• Make Exporting Excel Formulas Fully Multilanguage Aware
• Storing Password in SSIS Packages
• Avoid Too Many Invoice Copies
• …

The CaptionClass property

Not so long ago we were investigating a strange problem with a report. Every time the report was run, the label of one of the fields on a section was not printing what it was supposed to print according to its Caption/CaptionML property, or according to the Caption/CaptionML property of the related field in the table it was linked too. Somehow, something was dynamically changing the label???

After a while we suddenly noticed that the property CaptionClass was filled in, in the properties of the field in the table, and this was the cause of this 'strange behavior.

So, how does this CaptionClass property works?

If the CaptionClass property of a field or a control is defined, the function trigger CaptionClassTranslate (ID 15) in Codeunit 1 (ApplicationManagement) is called by the system every time the field or control is shown. The purpose of this function is to replace the caption, as defined in the design of the field or control, with another string.

2 parameters are passed to this function:

• LANGUAGE

  The LANGUAGE parameter is automatically mentioned by the system as is the Windows Language ID of the active language in Navision.
  If the active language in Navision is English (United States), LANGUAGE will hold the value 1033.

• CAPTIONEXPR

  The CAPTIONEXPR parameter holds the content of the CaptionClass property of the field or control.

In a way, the function trigger CaptionClassTranslate (ID 15) is a system trigger:

This standard code analyzes and unravels the CaptionExpr parameter. This parameter has the following syntax:

• CAPTIONEXPR := <CAPTIONAREA>,<CAPTIONREF>

Depending upon the value of the CAPTIONAREA, different procedures are called. Either:

• DimCaptionClassTranslate(Language, CaptionRef)

  or

• VATCaptionClassTranslate(Language, CaptionRef)

This is the way the standard functionality in Dynamics NAV deals with the CaptionClass property. Every field or control with a defined CaptionClass has a string in this property with the syntax described earlier.

The following diagram illustrates this sequence:

So, knowing this and with a little bit of imagination, you could extend the code in the CaptionClassTranslate trigger to be able to dynamically translate/change labels in Dynamics NAV.

The importance of accounting periods in fixed asset depreciation

Recently I discovered that you better create your accounting periods before you start depreciating your fixed assets. It could seriously affect the calculation of the depreciation amount, but how?

 

First of all, if you do not create an accounting period, then Microsoft Dynamics NAV 5.0 will try to appoint your depreciation to the last existing accounting period, resulting in a depreciation over more than 360/365 days. This could be a problem if your depreciation book does not allow depreciation over more than 360/365 days. If you have already SP1 installed, you will be notified of this issue when you run the report Calculate depreciation by the following warning:

 

 

Even if your depreciation book allows depreciation over more than 360/365 days, a second problem occurs when you are using the depreciation method of declining balance with a switch to straight line. Microsoft Dynamics NAV 5.0 SP1 will remain to depreciate according to the declining balance method.

 

Let's take a look at the following example, with a fixed asset that is setup as follows:

 

 

The expected depreciation:

Straight line : 1.000 * 0,2 = 200

Declining balance:            

Y1:    1.000 * 0,4 = 400

Y2:      600 * 0,4 = 240

Y3:     360 * 0,4 = 144 < 200 The switch is made from declining balance to straight line.

Y4:     160

 

If you haven't created the correct accounting periods, the following depreciation will calculated:

Y1: 400

Y2: 240

Y3: 144

Y4: 86,4

…

 

 

So make sure you have those accounting periods setup, before calculating your fixed asset depreciation!

 

 

 

Sql Server 2008 IntelliSense

A couple of days ago, after hours, when it was raining here in Barcelona (yes that can happen also here), I downloaded, installed and started playing around in the the evaluation edition of Sql Server 2008 (KATMAI).

One of the first things I wanted to double-check was the IntelliSense. Yes, as from Sql Server 2008 IntelliSense has finally arrived!

In the CTP versions of Sql 2008 there was some confusion about backwards compatibility of this IntelliSense. In some CTP's it also worked on Sql2005 databases and in others it didn't. I'm a bit sad to announce that in the final product, it will only work with Sql 2008 databases. So if you use SSMS 2008 on a Sql 2005 or Sql2000 database, there's no IntelliSense.

Anyway, I think it is a really cool and useful feature, especially when you have to write queries in SSMS (Sql Server Management Studio) on Dynamics NAV databases:

(Yes, if you look closely at the image above, I have a Dynamics NAV 5.1 CRONUS DB running on Sql Server 2008 and it seems to work fine, although not officially supported yet…)

You can just start typing your query, and if you want to see the IntelliSense dropdown, just type CTRL + SPACE. Then you can use the arrows or PgUp & PgDn buttons to select the table you are interested in.

This is of course only one simple example of what IntelliSense can do for you, if you want to know more, just have a look in the BOL (Books Online).

For example there's the Complete Word option. If what you have typed has more than one possible match, invoke Complete Word to display a list of possible matches, which you can then use to find the term and insert it into your code.

Another one is the Quick Info option; it displays the complete declaration for any identifier in your code. When you move the mouse pointer over an identifier, its declaration is displayed in a yellow pop-up window:

Another possibility is Delimiter Matching and Highlighting. Automatic matching of syntax pairs gives you immediate feedback on whether syntax elements that must be coded in pairs are correctly paired.

If only we had something like this in the Dynamics NAV C/AL editor…

Choosing the Recovery Model for a Database.

A recovery model is a database configuration option that controls how transactions are logged, whether the transaction log is backed up, and what restore options are available for the database. The recovery model you choose for your database has both data recovery implications and performance implications, based on the logging the recovery model performs or doesn't perform.

SQL Server provides three recovery models: Full, Simple, and Bulk-Logged.

The Full recovery model is the default and the recommended recovery model, logging all operations and letting you recover to the point of failure. The Simple recovery model minimally logs most operations and doesn't let you back up or restore the transaction log.

But, how do you decide upon which recovery model to choose for a Dynamics NAV database on Sql Server?

Well, you should consider both the recovery goals and requirements for the database and whether you can manage log backups.

Answering the following questions can help you determine the availability requirements and your sensitivity to data loss.

Recovery Requirements:

• How important is it to never lose a change?
• How easy would it be to re-create lost data?
• Do you have two or more databases that must be logically consistent?

Staffing Considerations:

• Does your organization use system or database administrators? è If it does not, who will be responsible for performing backup and recovery operations, and how will the individuals be trained?

Data Usage Patterns:

For each database, consider the following questions:

• How frequently does the data in the database change?
• Are some tables modified significantly more frequently than other tables?
• Are there critical production periods? If there are, what are the usage patterns during these periods?
• Does the database experience peak periods for insert and other update operations?
  You might want to schedule data backups to occur during off-peak hours. When the I/O system is under heavy use, typically, only log backups should be used.
• Is the database subject to risky updates or application errors that may not be detected immediately?
  If the database is, consider using the full recovery model. è This lets you use log backups to recover the database to a specific point in time.

When to Use the Simple Recovery Model?

Use the simple recovery model if the following are all true:

• Point of failure recovery is unnecessary è If the database is lost or damaged, you are willing to lose all the updates between a failure and the previous backup.
• You are willing to risk losing some data in the log.
• You do not want to back up and restore the transaction log, preferring to rely exclusively on full and differential backups.

When to Use the Full Recovery Model?

Use the full recovery model and, optionally, also the bulk-logged recovery model if any one of the following is true:

• You must be able to recover all the data.
• If the database contains multiple filegroups, and you want piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
• You must be able to recover to the point of failure.
• You want to be able to restore individual pages.
• You are willing to incur the administrative costs of transaction log backups.

When to Use the Bulk-Logged Recovery Model?

The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. It is recommended that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.

• When is the Dynamics NAV database subject to periodic bulk operations on the database?

Generally, the bulk-logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations. A transaction log backup captures the log and, also, the results of any minimally logged operations that have completed since the last backup. This can make the log backups very large. Therefore, the bulk-logged recovery model is intended only for use during bulk operations that allow for minimal logging.

It is recommended that you use the full recovery model the rest of the time.

Management Summary

The simple recovery model is generally appropriate for a test or development database. For a production database, the best choice is typically the full recovery model. The simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.

Very important!

Make sure that you thoroughly test the backup and RESTORE process !

It is not a very pleasant scenario to discover after a database crash that your backups are corrupt or nowhere to be found or no one knows how to restore the backup(s)… So please test this scenario a couple of times until you feel confident about it.

Some people even say this: You do not have a backup/restore solution until you have successfully tested the complete process.

‘Big’ Backups in Dynamics NAV

When using the Dynamics NAV client to create a backup of a database, you might get into the situation that the backup (.fbk) file that you are creating exceeds 2Gb of data.

Well, in fact this will never happen, because as soon as the .fbk file reaches 2Gb, Dynamics NAV will create an extra .fbk file and continue in that one, until it also reaches 2Gb and then it will create a third file, and so on…

Sometimes this behavior might give an issue…

The name of the extra backup files will contain a number. If you named the backup file backup1.fbk, then the second backup file will be named: backup2.fbk.

The problem occurs when you did not put a number in the name of the backup file AND the path of the folder in which you are storing the backup contains a number. Dynamics NAV will take the name and path of the backup file as a string, and will start searching from right to left for a number to increment. If one is not found then 1 will be added as the number.

So you might get into the situation that you are creating a backup named fin.fbk in a folder called: c:\temp1\fin.fbk. As soon as this backup file becomes greater then 2Gb, Dynamics NAV will try to create an extra backup file like this: c:\temp2\fin.fbk and if the folder c:\temp2 does not exist, an error will occur.

So think about this when you are creating a backup (.fbk) from a big Dynamics NAV database.

An interesting blog about Business Intelligence.

Recently, when I was looking up some information regarding Sql Server Reporting Services, I came upon this Blog: http://www.biblogs.com

I found it to be a very interesting blog and thought it's so interesting to share it with the Plataan knowledge blog addicts.

The goal of the blog is to provide that central place people can come to easily find information in the business intelligence and data warehousing space.

Hug a developer...

Someone forwarded me this today, and for all the developers out there, I thought I had to post it...

How to find out which fields were added to a Dynamics NAV database ?

Sometimes you need to create a list of all of the fields that were added to a database. A very easy and quick way to do this is by using the Field table.

Example:

Create a new form using the form wizard and put Field as the table.

Select all the fields from the Field table and preview the form.

Now you can set a filter on the FieldNo: 50000..99999.

And voila, these are the fields that were added to the database. Of course you will need to expand the filter for fields added in the add-on range.

Reminders in NAV 5.0 SP1: This smells like a bug!

I know how to use reminders in NAV quite well and this from a trainer, a consultant and a user point of view.

But there was always one field that drew my attention: "Use Header Level". There isn't much of information on this field in the official courseware, so I did some testing myself to see what this field exactly does. You can describe this field as follows:

If you check this field, NAV will use the Header Level for all reminder lines. The Header Level is defined as the highest level of the different reminder lines of that reminder.

For example, we have a reminder with 2 invoices on it. For one invoice, it is the first time that it appears on a reminder (Level 1), for the second invoice, it is the second time that it is reminded (Level 2). If you check in this case the field (Use Header Level), NAV will use Level 2 for both invoices. Otherwise it would use Level 1 for the first invoice and Level 2 for the second.

What does this means: basically this means that NAV will use this field to see whether interest should be calculated for the reminder lines. Suppose that in the above example, the field "Calculate Interest" is checked in the setup of Level 2, then NAV will also calculate interest for the invoice that only appears for the first time on the reminder.

I already made the above example on a NAV 4.0 SP3 version and it always worked flawlessly. Last week however, I also tried to make this example on a NAV 5.0 SP1 (BE version) and there it didn't work. First I thought I made a mistake somewhere, so I tried over and over again for several times, but it kept giving me the wrong results. Then I started to play around a little bit with the settings and I discovered what went wrong:

When you go to the batch job "Create Reminders" and on the Options tab page, you check the field "Use Header Level", NAV will not apply the settings of the highest level of the different reminder lines, but it will apply the settings of one level higher.

By making an example it will become clear:

Reminder Term: DOMESTIC
Levels: 3

First invoice which has already been reminded 1 time:

Second invoice that will be reminded for the first time in the second reminder of the first invoice. We will use the "Use Header Level" field:

In this example, the result should be that NAV will not calculate interest (in the setup, Level 2 has no "Calculate Intrest").
But if you create the reminder as shown above, the result is:

As you can see, NAV calculates interest for both invoices and because Level 3 is the only level where interest calculation is activated, we can conclude that level 3 is used instead of Level 2.

If you make the exact same example in 4.0, you will see that Level 2 is used (as it should be). I haven't tried it yet on a W1 version, but it doesn't seem to be something which has got to do with localization. I know that the field "Use Header Level" isn't used that much, but you never know…

Microsoft Releases SQL Server 2008

Microsoft Corp. announced the release to manufacturing of Microsoft SQL Server 2008, the new version of the company's acclaimed data management and business intelligence platform.

SQL Server 2008 (code-named "Katmai") aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime.

SQL Server 2008 will also include support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.

Other new data types include specialized date and time types and a spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the FILESTREAM data type has been added, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a binary BLOB. Backing up and restoring the database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data, and included T-SQL constructs to directly deal with them, without using recursive queries.

Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges". Approximately 70 methods will be available when SQL Server "Katmai" ships to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.

SQL Server includes better compression features, which also helps in improving scalability. It also includes Resource Governor that allows reserving resources for certain users or workflows. It will also include capabilities for transparent encryption of data as well as compression of backups. SQL Server Katmai will support the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be build around the Entity Data Model. SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization Inc., which was acquired by Microsoft.

On the management side, SQL Server 2008 will include the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively. The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries. However, with the current CTP, it is limited to SELECT queries; it will be expanded to other T-SQL constructs over later releases. SQL Server 2008 will also make the databases available via Windows PowerShell providers and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell.

SQL Server 2008 is now available to MSDN and TechNet subscribers and will be available for evaluation download on Aug. 7, 2008. SQL Server 2008 Express and SQL Server Compact editions are available for free download today at http://www.microsoft.com/sqlserver.

As previously announced, pricing for SQL Server will not increase with SQL Server 2008.

More information is available at http://www.microsoft.com/presspass/presskits/sqlserver.

Sure Step V2

On 04/07/2008 the second version of the Sure Step Methodology was released by Microsoft and presented during WPC 08 (Microsoft World Partner Conference) in Houston. What is Sure Step ? We have adressed this already in prior posts (see category Project Management), in essence it is an implementation methodology based on the project management principles of PMI/PMBOK.

On the partnersource Sure Step landing page, you will find this:

"an implementation methodology including an enhanced set of best practices and extendable templates that support collaboration with other partners, provide you more project predictability, and improve your customers’ satisfaction with their Microsoft Dynamics ERP or CRM solutions."

Effective project management is a necessity in every software implementation project. Project management is much more than creating documents. Microsoft is really convinced about the importance of project management and did a great investment in Sure Step in general and the second version specifically.

Partnesource: New in this release

During the year since Sure Step was first released, many Microsoft Dynamics partners have used it to improve their efficiency and new customer relationships. To date, more than 1,300 Microsoft Dynamics service and consulting professionals have been trained on Sure Step. Now, Microsoft is adding even more value to Sure Step in this latest release through many improvements to help you drive increased efficiency, productivity, and profitability.

• New templates and sample documents—364 in total, with sample customer deliverables defined, can be used as a reference guide to help you ramp new consultants and build project documents for customers
• Enhanced project types—5 in total, including streamlined activities and documentation for:
• Rapid deployments with fewer than 20 implementation steps to get your customers up and running quickly on their Microsoft Dynamics solutions
• Multi-site implementation rollouts for large, multi–geography deployments, allowing you to fully implement Microsoft Dynamics at one site and then quickly deploy at all additional sites
• New Sure Step Client helps make your projects easier to manage by providing you a new project workspace where you can customize and maintain multiple projects for multiple customers, with convenient views of all project deliverables and documentation

The new Sure Step Client is a fabulous improvement. You can now create your own project workspace and decide yourself which sure step documents and templates to use. You can also very easily add your own templates. Customization is no issue. For each document and template you can ask sure step guidance very easily. If someone on the project team wants to know about the purpose and details in this template, they are refered instantly to the corresponding content page of sure  step.

As from the early moments in the project lifecycle there is a systematic and very consistent build up untill the end of the project lifecycle.This guided by templates and tools. Early specs and gap fit situation will steer and drive analysis and design in a great way. It takes some time to understand and to see the complete picture but once you are there...you never want to go back from where you came. Sure Step V2 is that good that it becomes addictive:-)

Sure Step is the fastest starting point for any partner who wants to deliver quality implementations and improve his customer satisfaction rates. This is the moment to step onboard!

Plataan had the great opportunity and pleasure to develop the new official Microsoft courseware for Sure Step. This for AX, NAV and CRM tracks. We managed our own project with sure step guidance and templates and managed to deliver in time, on budget and with great quality.

We also delivered the TTT tracks (train-the-trainer) for Microsoft in Redmond. Many North American trainers and Microsoft staff attended the sessions. Everybody was pleased with both the courseware material and with Sure Step V2.

We will now deliver the Sure Step TTT EMEA session in Amsterdam. If you have any questions related to Sure Step, feel free to address them to me. I will try to help you out as much as I can, to get you on the Sure Step.

Also, check out our special training offer on Sure Step and Project Management:

http://www.plataan.be/en/navision/training_calendar/Project_Management.htm

    Vincent Bellefroid

  vincent.bellefroid@plataan.be

   Skype : vincent Bellefroid

Import .fbk undocumented feature

Recently I was giving training about the upgrade toolkit and the developer's toolkit and I noticed that not many people know about this undocumented feature of Dynamics NAV.

Sometimes, especially when doing upgrades in Dynamics NAV, you need to import a lot of objects into a database. Normally you then first export the objects into a .fob or .txt file and then you import them into the new database.

Did you know that there's another way to do this?

If you have an .fbk file of a database (backup file), then you can also use this .fbk file to import into another database.

If you import a .fbk file (File – Import, while the object designer is opened), then Dynamics NAV will look for all of the objects that are in the .fbk file, and will try to import those objects. Next Dynamics NAV will open the Import Worksheet, showing the objects in the .fbk file. Here you can decide what needs to happen with the objects during import (skip, replace, merge…) and when you click on OK the objects are imported, just as if you import a .fob file.

Nice, isn't it!

Requisition Worksheet

Recently I found out a little trick about the Requisition Worksheet in Dynamics NAV.

For some reason the worksheet was not calculating for Items that were not setup with a SKU (Stock Keeping Unit). When we made SKU's for the items, then the calculation worked properly, but without a SKU it did not calculate anything. This seemed very strange and after a little research I found a workaround.

The workaround is to use the location code in the manufacturing setup. Even if not running manufacturing, this part of the manufacturing setup is available.

In the workaround, you have to decide a "replenishment location". This replenishment location is setup using the "Component at Location" field at the Manufacturing Setup. (Manufacturing | Setup | Manufacturing Setup | Planning Tab | Component at Location field)

Once a location is selected in MFG setup, when calculate plan is selected from the requisition worksheet the system will create a Purchase Order for items that are not setup with a SKU.

We were working on a Dynamics NAV 5.0 SP1 German version, but I think this workaround may also work in other and earlier versions of Dynamics NAV.

Dynamics NAV 5 SP1 BE released

This is to inform you that Service Pack 1 for Dynamics NAV 5.0 has finally been released for Belgium. (And other GRP2 countries). You can download it on PartnerSource.

Why is this so interesting?

Well besides numerous bug fixes and new functionality there has been put a lot of effort in performance optimalisation towards Sql Server.

First of all, the famous SIFT-tables have been replaced by indexed views:

Microsoft Dynamics NAV now uses "Indexed Views" to maintain SIFT totals. Indexed views are a standard SQL Server feature. An indexed view is similar to a normal SQL Server view except that the contents have been materialized (computed and stored) to speed up the retrieval of data.

Microsoft Dynamics NAV creates one indexed view for each SIFT key that is enabled. When you create a SIFT key for a table, you must set the MaintainSIFTIndex property for that key to Yes to enable the SIFT key and create the indexed view. Once SQL Server has created the indexed view, it maintains the contents of the view whenever any changes are made to the base table. If you set the MaintainSIFTIndex property for that key to No, SQL Server drops the indexed view and stops maintaining the totals.

Second of all, Dynamics NAV will make use of Bulk Inserts:

Microsoft Dynamics NAV automatically buffers inserts in order to send them to SQL Server in one go.

By using bulk inserts, Microsoft Dynamics NAV reduces the number of server calls thereby improving performance. It also improves scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.

So the Dynamics NAV 5 SP1 seems to promise some interesting performance boosts when working on Sql Server 2005.

What’s the difference between using @ and # when opening a dialog window?

This is a question I almost always get when giving the C/SIDE Introduction and/or C/SIDE Solution Development trainings so why not make a little blog item about it?

This is the syntax of the OPEN function for a dialog:

Dialog.OPEN(String [, Variable1], ...)

• This string contains the text you want the system to display in the window.
• Use a back slash (\) to start a new line.
• Use pound signs (#) to insert variable values into the string, place the pound signs where you want the system to substitute the variable value.
• If you use @ characters instead of #, the string can be used as an indicator. In this case, use @ characters only for the string, and let the variable be an integer.
• The limits of the indicator are 0 and 9999 - meaning that the integer you use for updating the indicator should have a value within this range.

Example in a Codeunit:

Declare 2 variables:

Then write this code:

Now run the Codeunit, result:

To summarize:

How can I move my database files in SQL Server?

This is a question I got asked a few times the last few months, so why not write a little blog article about it.

An easy way to move user database files to a different location is by detaching, moving and attaching the user database.

(Of course, while you are doing this, people will not be able to work in the database. Make sure they logout of Dynamics NAV correctly.)

This database move can be done in SQL Server Management Studio (SSMS) so you don't have to type any code:

• Start SQL Server Management Studio
• Expand the server instance, expand Databases
• Right-click the database you want to move, and choose "Properties"
• In the Properties window, choose "Files" and write down the current file paths. Click "Cancel"
• Right-click the database again, and choose "Tasks - Detach..."
• Click "OK" in the next window
• Use Windows Explorer to move the data and log files (.mdf and .ldf) to the new location
• Right-click Databases, and choose "Attach..."
• In the "Attach databases" window, click "Add"
• In the "Locate database files" window, browse to the new location and select the .mdf file. Click "OK"
• In the details pane, verify that the new location is listed for both the .mdf and the .ldf file. Click "OK"
• In SQL Server Management Studio, choose "View - Refresh" and verify that your database is listed again under Databases

Alternatively you can back up the database, within Sql Server, and restore it, specifying a different location for the files on the options table or use the MOVE clause in the RESTORE command in Transact-SQL:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Key Groups and new functions.

Keygroups have been available in Dynamics NAV for some time now, but since the release of Dynamics NAV 5.0 some new functions can be used to enable and/or disable these key groups via CAL code:

• [Ok :=] KEYGROUPENABLE(GroupName)

  Use this function to enable a key group.

• [Ok :=] KEYGROUPDISABLE(GroupName)

  Use this function to disable a key group.

• [Ok :=] KEYGROUPENABLED(GroupName)

  Use this function to check whether or not a specific key group is enabled.

When you have a look in the design of a table and open the list of Keys of that table you will notice that every Key has a KeyGroup property. You can see this property to specify the predefined key groups that a key belongs to. After you have assigned a key to one or more key groups, you can selectively activate or deactivate the key by enabling and disabling the key groups. In the past this was only possible by going into the Database Key Groups window.

To enable or disable a key group for sorting, click File, Database, Information and the Database Information window appears:

Then click Tables, Key Groups and the Database Key Groups window appears. You can then enable or disable a key group by clicking the appropriate button:

Unfortunately, it is very difficult to get an overview of the keys and tables that are affected when you enable or disable a key group. However, you can look at the design of any table and see the keys that have been defined for that table and whether or not the keys belong to a key group.

Adding a large number of keys to database tables decreases performance. However, by making the keys members of predefined key groups you can have the necessary keys defined and only activate them when they are going to be used.

And as from Dynamics NAV 5.0 we now have the possibility to do this in the CAL code.

This can be useful for reports that use certain keys but that are executed very rarely, for example only once a year. By only enabling these keys when they are needed you can avoid the performance overhead of maintaining them when they are not used.

Remarks:

Be careful with this. When you disable a key, it is no longer available and can also not be used in code. Disabling a key is like deleting it; the difference is that it is still in the list of keys, but disabled.

When working on Sql Server, this means that if that key had any SumIndexField(s) attached to it, the related SIFT tables will also be removed if you disable the key (and recreated when you enable the key).

Enabling a key, means that the index table has to be rebuild, and so this can cause performance issues and locking, especially when there are many records in the table.

This is not to be used as an alternative for not maintaining a key on Sql Server. The MaintainSqlIndex and MaintainSIFTIndex/SiftLevelsToMaintain properties are meant for that purpose.

Plataan hits the news

During the last months Plataan was working hard on the development of Official Microsoft Courseware (MOC) for Microsoft Dynamics Sure Step Implementation Methodology Version 2.

Plataan was granted this assignment by Microsoft Corporation, Redmond US. Plataan was also selected to deliver the train-the-trainer sessions for this courseware both in the US as EMEA.

The Belgian regional televison channel TVL made a newsitem on these assignments, broadcasted during evening news. You can watch it here.

Fun with the Dynamics NAV Debugger

Recently I had to debug some custom code I added in Dynamics NAV. Some object was throwing an error because of an overflow converting Text 60 to Text 30. Because in the code were the error occurred, many other objects were called, it was not so easy to see were the error originated from. But then I noticed a nice feature of the Dynamics NAV debugger that pointed me in the correct direction.

Let's make an example. Codeunit 365 is frequently used to format addresses when running reports. Let's say that when running a Sales Invoice something goes wrong when calling the FormatAddr function.

For example, someone has changed the table design of the Sales Invoice table and enlarged the 'Bill To Customer Name' from 50 to 100 characters. The FormatAddr function expects a parameter Name that can be maximum 90 characters in length. When printing an invoice for a customer with a name bigger than 90 characters this will result in an error.

Go to Tools and enable the debugger (Breakpoint on Triggers is not required for this example):

Then, run the report. You will notice that the debugger stops here (when the error is thrown):

The question now is, can we trace this back to the report and see via which objects and code the report called this function?

Yes, we can. In the debugger you have a special window for this:

The little yellow arrow shows you in what object you are debugging. BUT, here comes the great feature, you can double-click on the underlying objects, and then the debugger will jump to the code of that object.

The little Green arrow shows now where we are debugging, and you can directly see which code in what object was responsible for calling the FormatAddr function.

Even more usefull you can see the object number, name and the trigger/function…

And you can even go further back by double-clicking on the object(s) below this one!

How cool is that?

Conduct key user training early on

Microsoft Dynamics Sure Step Methodology plans "key user training" as of the start of the Analysis phase.

It should primarily focus on educating the customer resources in the capabilities of the application so they can participate in business process analysis, gap/fit discussions, application configuration, and customization design. It will help both the implementing organization and the customer to develop a common language and mutual understanding.

During training I had a lot of interesting discussions regarding this "key user training". Some students confirm the great benefit of key user training early in the process others disagree.

I found a real good blogitem about this subject on the blog of Vjeko. The article is called "Standard enemy" and illustrates another great advantage of "key user training" early in the process. You can read it here. Enjoy.

What about the Always Rowlock option?

Since Dynamics NAV 4.0 SP1 a new database option, for Dynamics NAV on Sql Server, became available: Always Rowlock:

By default it is not enabled and by enabling it Dynamics NAV will send 'WITH ROWLOCK' hints towards Sql Server. Without it, we let SQL Server decide what kind of locks to use.

Rowlocks require memory to maintain, so some times, SQL Server will convert many rowlocks into one table lock when it thinks that it can use its memory better in other places. The ROWLOCK hint will prevent this.

If the ROWLOCK is not issued then SQL Server has the freedom to choose at what level it will lock: row, page or table.

• The up side of this is that it is a big reduction in lock maintenance and memory required for many rowlocks, which instead will be replaced with page locks è improved performance.
• The down-side is that page locking is not as fine-grained and therefore a user can be locking 'too much' data that can impact other users è reduced concurrency.

Forcing ROW Locking keeps the lock-granularity small; the probability of getting blocks is smaller. The disadvantage is that by forcing the small granularity, this could cause a high "pressure" on the master database: administering many Row-Locks is more "costly" than administering few e.g. Range-Locks etc...

If you have a high transaction volume, dealing with large result-sets, "Always Rowlock" could cause an overall decrease of performance if the master-db reacts too slowly due to the high number of lock-administrations.

Finally, "Always Rowlock" reduces blocking-conflicts (actually it is just disguising them) but could be at cost of the overall performance (which could be compensated by sufficient hardware resources).

So, if you activate "Always Rowlock", you may get fewer table locks, but SQL Server may need more memory. It's impossible to say in general, how it will affect each individual system. If you think you have quite a lot of memory, then the only way to see the effect is to turn it on, and see if the system works better. I'm afraid that I don't know of any better general rules, when to advice to turn "Always Rowlock" on or off.

Maybe you could share with us your own experiences with the Always Rowlock option?

Workbreakdownstructure in Microsoft Dynamics Sure Step Methodology

A good technique to master and manage your project scope is the use of a Work Breakdown Structure (WBS).

What is a WBS?

Wikipedia definition:

A work breakdown structure or WBS is a fundamental project management tool. A common technique for defining and organizing the total scope of a project, is to use a hierarchical tree structure. The first two levels of the WBS (the root node and Level 2) define a set of planned outcomes that collectively and exclusively represent 100% of the project scope. At each subsequent level, the children of a parent node collectively and exclusively represent 100% of the scope of their parent node. More generally, outside of it's original mil standard and PERT/CPM context, a WBS is a mapping from one level of system specification to another, for example a requirements cross reference matrix mapping functional requirements to high level or low level design documents. A well-designed WBS describes planned outcomes instead of planned actions

In Microsoft Dynamics Sure Step Methodology WBS structures are included. The planning tools (MS Projects) are also based on the available WBS structures. The only thing not there is a nice visualization of the tree structure. On the web I found a nice tool called WBS Chartpro. You can use this tool to create WBS structures but also it can create a WBS automatically based on a MS Project file.

WBS Chart Pro can be used to generate WBS charts directly from existing Microsoft Project plans. WBS Chart Pro uses the Outline created in Microsoft Project to generate a hierarchical view of the data. A WBS toolbar button can be installed into Microsoft Project for easy transfer of data. As you organize and change your project in Microsoft Project, a WBS chart of that data is only a button away.

Get into the Groove

Microsoft Office Groove is a peer-to-peer application targeted at teams with members who are usually off-line or who do not share the same network security clearance.

Groove is desktop software designed for the collaboration and communication of the members of small groups, developed by Ray Ozzie for the Windows platform. Note that this is not a "cloud service". So "Live Mesh" (see previous blog item) is a cloud service, while Groove is desktop software.

The basic idea for Groove is a set of files to be shared plus some aids for group collaboration which is a shared workspace. A Groove user invites other Groove members after creating a workspace.

By responding to an invitation the person becomes an active member of that workspace and is sent a copy of the workspace that is installed on their hard disk drive. All data is encrypted both on disk as well as over the network, with each workspace having a unique set of cryptographic keys.

Members interact and collaborate in the workspace which is a private virtual location. All changes being made are tracked by Groove and all the copies are synchronized via the network in a peer-to-peer manner, almost instantaneously.

All the members are sent the changes made to the workspace by any of the users and the documents are updated automatically. The change is queued and synchronized to other workspace members, if that member is offline at the time the change is made, but the user's copy is updated when the member comes back online. The change is temporarily stored at the server, if Groove Server is used, which is relayed when the user is back online. Multiple copies are kept for the editors, when more than one person edits a document at the same time, who must agree which changes to save.

Microsoft office Groove operates by means of a client and two servers: Groove Server Manager and Groove Relay server.

You can use and deploy Groove in three ways:

• as an unmanaged account (free version)
• as a hosted account (hosted by Microsoft)
• as an onsite account (setup and managed in your own infrastructure)

(Note: Microsoft Gold Certified partners are entitled to a hosted account for free )

A workspace exists out of tools. For each workspace you can decide which tools to use. For each tool you can assign rights. Some examples of tools are:

• agenda
• file sharing
• discussions
• forms (own Groove forms)
• infopath forms
• notes
• Issue tracking
• ... many more

You can also integrate Groove with Sharepoint (file repository). This brings us to an interesting subject. Why would you use and Sharepoint and Groove?

In a way, you could see Groove as an offline client for Sharepoint (certainly the upcoming version). Sharepoint users need an URL and security settings. For Groove this is not necessary. Groove can be setup much faster and easier when you work with external people. Groove is really interesting for field workers.

Can you store project emails in Groove and  sync a calendar?
At first the answer was no but with GrooveIT! you can. GrooveIT! is the way to take full advantage of Groove workspaces by linking them with your daily work tools.

GrooveIT! is a great tool for your Groove 2007 and Outlook environment. It will sync tour email from Outlook to Groove 2007. New for the 2.0 version is:

• Share your RSS feeds
• Transfer your Outlook tasks
• Create GrooveIT! rules on calendars, tasks list, contacts list, RSS feeds
• Discover improved Groove tools including support for Groove links, attachments management, filtering
• Build discussion threads of your emails, avoid duplicates
• Update calendar events, contacts and tasks already published in Groove

Use the Web as your File Sharing Hub with Microsoft Live Mesh

Microsoft recently unveiled a tech preview of its Live Mesh service. Have all your devices—PCs, and soon Macs and mobile phones—working together to give you anywhere access to the information you care about.

A clear example of a "cloud service", Microsoft believes in "cloud computing".  Live Mesh synchronizes and shares data across multiple machines — currently that means Windows PCs, but look for Windows Mobile as well as Mac OS X support to be added later this year. Mesh offers one-click sharing for desktop folders, remote desktop connections, web-based file access and a news feed to keep track of it all. Eventually Microsoft would like to add Xbox, DVR and other devices to the list of syncing/sharing possibilities.

In his first Channel9 interview, Ray Ozzie, Microsoft's Chief Software Architect talks about Live Mesh, a new technology and platform that enables synchronization and storage "to the cloud." You'll hear about the history of Live Mesh, how it has been influenced by Ray's previous work on products like Groove (more to come on Groove on this blog ...).

Key elements for Live Mesh are:

• All your devices working together
• Access from anywhere
• Simple to share

• Stay informed

Notice that this is quite a change for Microsoft and represents a significant step away from the desktop. Eventually Microsoft hopes, as Ozzie says, that “social mesh notions of linking, sharing, ranking and tagging will become as familiar as File, Edit and View.”

Some video's on Live mesh

How to use “Balance” in Recurring Journals

In the Recurring Journals of Dynamics NAV, there is a Recurring Method "Balance". This is probably the least used method, but according to the standard training material and the help files, this method could be useful to redistribute general expenses to (for example) a cost centre at the end of the month.

An Example: a company purchases from time to time office supplies. Because all the departments are using these office supplies, the purchase is posted to a general dimension. At the end of the month the "general" office supplies could be redistributed by using Recurring Journals.

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month	1000		GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1000	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	600		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	300		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	100		PROD	10%

 

This is the theory, but it's not always obvious to put theory into practice. However, in this case it's not that difficult. But when I first tried this, it didn't work out as I wanted because of the following reason. And this reason is a requirement in order to use the above principle in a successful way:

It is important only to use the general dimension during the posting of the office supplies during the month.

For example: the accountancy department purchases office supplies that will be used only in their department. Normally this purchase could be posted directly to the dimension ADM. But if you do this on the same G/L account as you would post the general office supplies, at the end of the month while redistributing using the Recurring Journal, also the ADM office supplies will be redistributed using the allocation key.

The way most users want it

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month for general purpose	1000		GENERAL
Purchase office supplies during month for Accountancy Department	200		ADM
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1000	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	600		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	300		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	100		PROD	10%

 

The way NAV does it

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month for general purpose	1000		GENERAL
Purchase office supplies during month for Accountancy Department	200		ADM
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1200	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	720		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	360		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	120		PROD	10%

 

You notice the difference?

An alternative is using 2 G/L accounts for office supplies: one for the assignable expenses and one for the non-assignable expenses. It would be the last one that you then can redistribute using a recurring journal.

Plataan GOLD Certified Partner for Learning Solutions

It is with great pleasure that we can announce that Plataan has achieved the GOLD CERTIFICATION status.

Gold Certified Partners represent the highest level of competence and expertise with Microsoft technologies, and have the closest working relationship with Microsoft.

We explicitly want to thank our team (the MCT 's : Microsoft Certified Trainer) for their commitment, knowledge and hard work over the last three years. We also want to thank our training customers (about 150 customers after three years, both domestic as international customers). We want to thank Microsoft and all our vendors and contacts.

Use Live search as your calculator

Did you know that you can use Live Search as your calculator?

In the search window enter a formula like 10*5, then press ENTER. The result is:

Unleash the power of Excel Data mining

Did you know that you can download and install a Microsoft Office Excel Data mining Add In? Once installed you get an extra ribbon in Excel called Analyze:

In this ribbon you will find buttons you can use to analyze your data in Excel. You can use the Data Mining algorithms to detect Key Influencers, Categories, Forecasts, Exceptions … So by exporting your data towards Excel you are able to use these Data Mining capabilities to learn from your data and get to know your business even better.

Example:

Starting from Microsoft Dynamics NAV 5.0 you have the possibility to export data towards Microsoft Excel, with 1 mouse click. Open for example a Customer List form and click on the Export to Excel button:

Result:

You then need to format this data as a table:

As soon as you have done this, the Analyze ribbon becomes available:

Then you can start Data Mining. Let's for example do a Category Detection for our Customers:

A popup window appears asking for which columns to use to detect categories. For this example I will use the columns proposed by the wizard itself. After clicking on the Run button he starts analyzing:

At the end you get an extra page in the Excel workbook containing a report that shows the results of the Data Mining Analysis:

In this example, 5 categories of customers were detected, and by filtering the table below you can see what types of customers were detected by Excel, by analyzing your data. And in the original excel table an extra column was added showing the category:

This is how simple it is to use Data Mining technology in Microsoft Excel.

Requirements:

1. You need to have Microsoft Office Excel 2007.
2. And you need somewhere on your network (or desktop) a running instance of Sql Server Analysis Services. The data mining Add In uses this for his calculations.

Of course the Data Mining algorithms are based upon Mathematical and Statistical formulas, meaning that the more data you have, the more precise the results will be.

If you are convinced about the advantages of Data Mining and you would like to go deeper, you can also use Microsoft Sql Server Analysis Services. With Analysis Services you can also do Data Mining, but there you have many more possibilities.

Would you like to know more about the Data Mining possibilities of Excel or were to download this free add in, then go to this website:

http://www.microsoft.com/sql/technologies/dm/addins.mspx

Reporting Services 2008: Introducing Tablix

In the past we only had these containers: List, Table, Matrix, Chart in Reporting Services. In Sql Server 2008 there's a new container, a Tablix: a Table and a Matrix in one.

On the left is a table with fixed columns and dynamic rows. On the right is a matrix with dynamic columns and rows. But what do you do if you want to mix dynamic and static columns like this? With tablix, it's easy.

Example:

Tablix gives you all the features of both a table and a matrix. You can mix and match behavior of fixed or dynamic as needed. In addition, you can group data more flexibly. Groups can be nested and recursive just as in SSRS 2005, but you can also place groups adjacent to each other.

To summarize:

Matrix is dead, long live Tablix!

The Sql Server 2008 Report Designer

In Sql Server 2008 Reporting Services, the Report Designer had changed a lot. Here's a screenshot of the new Report Designer:

You can drag global expressions from Built-in Fields onto the design surface directly. Use the Insert ribbon to add report items to the design surface.

Editing parameters, data sources, and datasets is made very easy in the Data pane. Just right-click the object and click Edit.

You can still drag and drop fields from the Data pane onto a data region. Each textbox also includes smart tags that you can use to select a field quickly.

Expression placeholders are intended to show you as much information as possible. Parameters indicated by @ symbol and built-in fields indicated by & symbol. Dataset fields use no symbol. A complex expression is shown as <<Expr>>.

As you can see the Report Designer now has a ribbon at the top and zoom capability (right bottom), just like in Microsoft Office. And like I explained in the previous Blog post, you can minimize this ribbon by double-clicking on it.