Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel 2007 will open the door on ERP data, says Microsoft. By John Stokdyk

by
15th Nov 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

With the new version of Microsoft Office 2007 poised for delivery to corporate customers, ExcelZone approached the Microsoft Dynamics UK team to find out what the new version will deliver for users who what to link spreadsheets to their financial and operational software.

In Microsoft's eyes, Office tools such as Excel and Outlook are part of the all-round user experience and act as the entry points to the data held in financial and operational systems. To use the Microsoft terminology, Office and Excel 2007 are part of the "technology stack". This stack reaches from the underlying network and communications architecture and the SQL Server database through to Dynamics business applications and business intelligence tools. Performance charts and other reports derived from this source data can be delivered and viewed over the web via Microsoft's SharePoint intranet toolkit, which publishes information produced or tweaked with Excel and other Microsoft reporting tools.

In recent weeks, however, ExcelZone contributors John Francis and Simon Hurst have questioned whether improvements in Excel 2007 will justify the cost and inconvenience of upgrading - particularly for reasonably experienced accountants who are used to creating their own macros and VBA routines in Excel.

Paul White, UK business group leader for Microsoft Dynamics led the company’s response: "It might be true that power users won't get more out of Excel 2007, but the average user might get more out of it because it exposes a lot more to them. Office 2007 makes it possible for ordinary users to do what experts used to do."

New features: Conditional format styles and Excel Services
As an example of Excel's new features, data cut-and-pasted from the current version of Dynamics NAV into Excel 2007 can be displayed with different conditional formatting styles picked from the Style section of the new Excel menu bar. When a style option is highlighted in the menu palette - for example one that uses graduated tones to suggest the relative size of the figures, or red/yellow/green traffic light icons to reflect the status of the reported values - the result is reflected on your worksheet data.

Some critics dismiss the ready-made conditional formatting options as window-dressing, but the possibilities become more exciting when you factor in Office 2007 SharePoint Server. This part of Office 2007 is described by Microsoft as "a free intranet in a box". Excel Services is one of the ingredients within Office SharePoint Server 2007, explained Microsoft Dynamics partner technology manager Jeremy Palmer.

Having produced a conditionally formatted sales report - a suitable performance indicator for a sales team - Palmer selected the Publish to Excel Services option within Excel 2007 and chose the elements he wanted to display within the target SharePoint page.

"SharePoint can incorporate documents and data from the ERP system and other sources, as well as the reports from Dynamics NAV," Palmer explained. "You can look at report output via SharePoint and take the data into Excel, while maintaining all the formatting. Then you can slice and dice the data in Excel, without having to touch Navision. It broadens the reach of the ERP solution."

For James Wright, who works with Microsoft Dynamics reseller Advantage Business Systems, one of the benefits of Excel Services is the way it allows you to publish an Excel "snapshot", but keep it under the control of the data access rights maintained in the Microsoft Windows Active Directory.

"You can publish an Excel worksheet to Excel Services and set it to only show users data that is applicable to their department. They will only see their data, not the whole workbook," Wright explained.

Tony Crowhurst, BI product manager for Microsoft Dynamics, explained the benefits of using Excel Services to link spreadsheets to ERP applications. "In the past the big problem was that you would have 30 different versions of the same spreadsheet. With Excel Services you can now post a spreadsheet to a SharePoint portal and people can input data to the sheet from there. It could be great way of doing a budget collaboration, for instance.

Test driving the new pivot table layout tools
With AccountingWEB's pivot table expert David Carter in attendance, Palmer then pasted some Dynamics NAV sales invovice data into Excel 2007 to test the new spreadsheet's pivot table functions. [Users will be able to export any screen view of the data to Excel in the next release, Palmer added].

The new edition requires some adjustments. It took a few moments to find the Cell format command to check whether the dates in our data came over as recognisable dates rather than text.

There is also no longer a Data menu option in Excel. The Pivot Table command is now part of the Insert menu. Choosing the Insert Pivot Table option called up the Pivot Table Field List dialogue box showing the data held in each worksheet column, with four boxes below headed Report Filter, Columns, Rows and Values. The Date item was dragged from the top tree into the Column box, and orders were dragged into the Row layout box. For those who do not like this new layout technique, it is possible to revert to the old Layout Wizard approach.

Once again, there was some confusion while we attempted to find the Group and Ungroup option, but once we clicked on a date within the top row, the Group menu palette appeared in the new style ribbon menu, with Group Selection as one of the option. The result was a recognisable and usable pivot table, which could be turned into a pivot chart with one mouse-click.

"It's the first time I've seen the ribbon," said Carter. "They've changed the menu options around quite a bit, but I think the idea of collecting the icons for a particular task into one place is a good one.

"I'm still more comfortable with the old Layout box, but I think we have to accept that we'll have to go back to school to relearn a few things before we can appreciate what the new version of Excel can do."

According to Microsoft, dragging data items to the new style layout boxes make it easier to use pivot tables, particularly if you need to construct more complex pivot tables showing a larger number of data items and report filters. Expanding the spreadsheet's capacity from 256 columns and 65,000 rows to more than 1 million will also cater for users who want to manipulate large datasets in Excel.

One of the messages constantly invoked by Microsoft executives and Dynamics resellers is that Excel is the front-end client for deriving and delivering a lot of the business intelligence that resides within transactional systems. And they have a habit of subtly reminding you that the best way to take advantage of these features is to upgrade to the latest version of all the relevant products: Office 2007, SQL Server 2005 and the various Dyamics family upgrades.

For example, the the next Dynamics releases (GP version 10, due early next year and NAV version 5.0, due later in the year) will include the Report Builder, a drag-and-drop report generator that includes the new XML-based Excel 2007 file format .XLSX as one of the output options (along with PDFs, raw XML, HTML pages or CSV files). The Report Builder will let you create an Excel report and apply the formatting you want. Then you can either refresh the data whenever you view it, or schedule automatic updates within Report Builder.

However this new Report Builder in the Dynamics applications is basically an adaptation of the Reporting Services module that already ships with SQL Server 2005 - so if you have the latest version of the database, and you have the time and patience, you could probably do a lot of the report configuration yourself - which is something open to anyone who uses a SQL Server-based accounting or ERP application from developers other than Microsoft.

That's just the start. Next year Microsoft is planning to release a high end planning and consolidation toolkit called Biz# (Biz sharp) and beyond that, it is slowly working to streamline and standardise a wide collection of BI tools within its Dynamics/FRx and ProClarity stables into a new product, Performance Point Server, which is scheduled to emerge over the next year. Keep up.

Even Microsoft's UK BI product manager Tony Crowhurst admitted that he got confused by the variety of tools on offer.

Faced with so many choices, Advantage's James Wright suggested that a little thought was needed to work out your reporting requirements (for example real-time analytical processing, business scorecards, monthly management packs or ad hoc reports). Once you know what you want, the standard tools within a particular application would probably get you 80% of the way. Then you could turn to Excel, SQL Server Reporting Services (Dynamics Report Builder), or more sophisticated tools such as the Microsoft Balanced Scorecard Manager to get you all the way to your reporting goal.

"You can already use a lot of these technologies with SQL Server and Dynamics suites - and SharePoint 2003 works with all these elements. You don't have to buy all the new kit to do it."

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.