Re the Excel 2007 version of this tutorial, I've moved into the video age, so there's now a video of this tutorial for Excel 2007 on Youtube. Link here:
or just go into Youtube and search on MrDatamart (I've got 4 or 5 up altogether including a couple on the new breed of Excel-based report writers which do all the things you can't do with pivot tables).
Formulas in Excel 2007 Jean, thanks for the kind comment; I hope to be updating this tutorial to Excel 2007 soon.
At the top of your screen there is a bar called Pivot Table Tools. Click on it. Then choose Formulas in the Tools section at the right (next to Pivot Chart). David
It's not that simple Package vendors obviously do publish record layouts for their data tables. They will supply them to their resellers who need them for customisation etc.
However they are usually reluctant to give them to end-users. So if you are working with a client your best bet is to badger the reseller into giving you a copy.
On the other hand, in most cases you are simply seeing what the programmers see, so there's no guarantee they will mean anything! The field names are often incomprehensible and inconsistent. For example, Sage is pretty good but you still find that ACCOUNT_REF means different things in different tables.
Replies: Martyn: A browser isn't sufficient because people don't just want to view their data, they want to play around with it as well - filter it, sort it, add things up etc. and for that you need Excel. Also, if you use Excel it's very easy to customise reports for each individual manager by bringing through only the data that concerns them.
Charlie, We've added links to some of the tutorials at the end of the article. You can also find the tutorials in our Management Reporting Zone under the heading "Import Data into Excel". There's a list of packages and QuickBooks is one of them. In fact, the QuickBooks tutorials show how to create a sales invoice report - product sales, margin earned etc.
Re ODBC, the Import External Data command is associated in most people's minds with ODBC, but Microsoft have been adding all sorts of drivers as well as ODBC. The most important ones are .TXT and .CSV and every package can export text or csv. (I used CSV for QuickBooks)
Finally, to add to Sandra's comment, Import External Data has been around since Excel 97 so it's hardly new. But I think most people so far have used it to download data onto THEIR OWN machine. I don't think many people have tjhought of using it to download data direct onto managers' machines. That idea is new.
Change is the price of progress Obviously, the changes in Excel are a real pain, particularly for those people who are expert in existing versions.
But if you want a product to improve, that means it has to change. Upgrades are a necessary evil and users have to put with the short-term pain in order to attain greater benefits in the long-term.
It's always a temptation for software vendors to compromise new designs in order not to upset their installed base. But in the end that leads to mediocrity. If Microsoft believe that Excel needs radical changes at this stage in its life, then they should back their hunch and go with it.
Iris Accounts Office if you need multi-currency? Intuit seem to be shooting themselves in the foot on this multicurrency issue. Just to add some more pain, Iris have just released a sub-£1000 version of Exchequer and Exchequer has one of the best multi-currency modules in the business. See last week's article.
different product, different offices Just to clarify last year's Payroll cockup issue, I think that Iris payroll support comes from Datchet or somewhere. However, the support for this product comes from the Exchequer offices in Bournemouth.
Bournemouth's track record is pretty good, so I think it's unfair to tar Exchequer with the same brush.
disagree with Denis and Alastair Denis and Alastair are wrong, I think. The data structures required to support BI are completely different from the data structures of existing transaction processing systems. The two cannot be reconciled and trying to run BI straight off the transaction processing system is always going to be a bodge job, I think. (Jim Johnson's last sentence says it all).
The correct approach is to generate a secondary database that is optimised for reporting, and run BI off that..
Please read my interview with Stephen Bow of Topaz who is charge of designing Topaz's own BI system with SQL Server and Analysis Services. Stephen convinced me that a secondary database is the correct approach.
Denis, I'd also make the point that Performance Point, who supply an alternative BI system for Dynamics NAV and whom you've praised on your blog, take the secondary reporting database approach and create a separate data warehouse. Their MD, Nigel Geary, claimed to me that the biggest issue in BI these days was reconciliations - it seems that the practice of providing uncheckable figures is now starting to worry some BI customers.
[sorry, posted this last night and some of it dropped off]
[more apologies: sorry Dennis, that should have been PrecisionPoint, not PerformancePoint]
My answers
Sales Analysis tutorial for Excel 2007
Re the Excel 2007 version of this tutorial, I've moved into the video age, so there's now a video of this tutorial for Excel 2007 on Youtube. Link here:
http://www.youtube.com/watch?v=PrfrVxAixH8
or just go into Youtube and search on MrDatamart (I've got 4 or 5 up altogether including a couple on the new breed of Excel-based report writers which do all the things you can't do with pivot tables).
Formulas in Excel 2007
Jean, thanks for the kind comment; I hope to be updating this tutorial to Excel 2007 soon.
At the top of your screen there is a bar called Pivot Table Tools. Click on it. Then choose Formulas in the Tools section at the right (next to Pivot Chart). David
It's not that simple
Package vendors obviously do publish record layouts for their data tables. They will supply them to their resellers who need them for customisation etc.
However they are usually reluctant to give them to end-users. So if you are working with a client your best bet is to badger the reseller into giving you a copy.
On the other hand, in most cases you are simply seeing what the programmers see, so there's no guarantee they will mean anything! The field names are often incomprehensible and inconsistent. For example, Sage is pretty good but you still find that ACCOUNT_REF means different things in different tables.
It's a minefield out there!
Replies:
Martyn:
A browser isn't sufficient because people don't just want to view their data, they want to play around with it as well - filter it, sort it, add things up etc. and for that you need Excel. Also, if you use Excel it's very easy to customise reports for each individual manager by bringing through only the data that concerns them.
Charlie,
We've added links to some of the tutorials at the end of the article. You can also find the tutorials in our Management Reporting Zone under the heading "Import Data into Excel". There's a list of packages and QuickBooks is one of them. In fact, the QuickBooks tutorials show how to create a sales invoice report - product sales, margin earned etc.
Re ODBC, the Import External Data command is associated in most people's minds with ODBC, but Microsoft have been adding all sorts of drivers as well as ODBC. The most important ones are .TXT and .CSV and every package can export text or csv. (I used CSV for QuickBooks)
Finally, to add to Sandra's comment, Import External Data has been around since Excel 97 so it's hardly new. But I think most people so far have used it to download data onto THEIR OWN machine. I don't think many people have tjhought of using it to download data direct onto managers' machines. That idea is new.
Change is the price of progress
Obviously, the changes in Excel are a real pain, particularly for those people who are expert in existing versions.
But if you want a product to improve, that means it has to change. Upgrades are a necessary evil and users have to put with the short-term pain in order to attain greater benefits in the long-term.
It's always a temptation for software vendors to compromise new designs in order not to upset their installed base. But in the end that leads to mediocrity. If Microsoft believe that Excel needs radical changes at this stage in its life, then they should back their hunch and go with it.
Iris Accounts Office if you need multi-currency?
Intuit seem to be shooting themselves in the foot on this multicurrency issue. Just to add some more pain, Iris have just released a sub-£1000 version of Exchequer and Exchequer has one of the best multi-currency modules in the business. See last week's article.
different product, different offices
Just to clarify last year's Payroll cockup issue, I think that Iris payroll support comes from Datchet or somewhere. However, the support for this product comes from the Exchequer offices in Bournemouth.
Bournemouth's track record is pretty good, so I think it's unfair to tar Exchequer with the same brush.
dot - zero - comma
Well, I use the "dot-zero-comma" slogan because I just worked it years ago out via suck it and see. I think the meaning is:
dot = add decimal places
0 = add a single decimal place, and if it's 0 display 0
( the difference between # and 0 is that # means if it's zero, don't print anything)
comma = round to the nearest thousand. I think two commas = to the nearest million
We need Richard Scoullar. He'll know.
.
finally done
Stuart,
If you're still there, the series continues today 21st August. Apologies.
disagree with Denis and Alastair
Denis and Alastair are wrong, I think. The data structures required to support BI are completely different from the data structures of existing transaction processing systems. The two cannot be reconciled and trying to run BI straight off the transaction processing system is always going to be a bodge job, I think. (Jim Johnson's last sentence says it all).
The correct approach is to generate a secondary database that is optimised for reporting, and run BI off that..
Please read my interview with Stephen Bow of Topaz who is charge of designing Topaz's own BI system with SQL Server and Analysis Services. Stephen convinced me that a secondary database is the correct approach.
Denis, I'd also make the point that Performance Point, who supply an alternative BI system for Dynamics NAV and whom you've praised on your blog, take the secondary reporting database approach and create a separate data warehouse. Their MD, Nigel Geary, claimed to me that the biggest issue in BI these days was reconciliations - it seems that the practice of providing uncheckable figures is now starting to worry some BI customers.
[sorry, posted this last night and some of it dropped off]
[more apologies: sorry Dennis, that should have been PrecisionPoint, not PerformancePoint]