Well I hinted at this product back in June and now Sage have officially unvelied this in the UK. Of course I was speculating that this product is perhaps a successor to Line 500 but there is nothing to confirm this. In fact I believe Sage 1000 is still being actively developed so it will be interesting to see where X3 sits in the product range. It is not a new product, in fact its been around for some time under the moniker of Adonix X3 and was swept up by Sage on one of their many acquisition spree’s.

Details at the moment are sketchy, most of the product information on the Sage website is very high level but I will aim to get more details as soon as possible.

If you work with VI contract costing chances are you need to run comaprisons of actual costs against initial and current estimates. This query will give you the initial costs and revenue for each contract grouped by expense code and expense group. If you look at the comment section you can change MIN to MAX to get the latest estimate.

SELECT
ced.contract_code, ccg.expense_code, ccg.expense_group, ced.cost, ced.revenue
FROM
scheme.cfercdm AS ccg WITH (NOLOCK)
INNER JOIN (
SELECT
esta.contract_code, esta.expense_code, estb.version, SUM(esta.cost) AS cost,
SUM(esta.revenue) AS revenue
FROM
scheme.coestdtm AS esta WITH (NOLOCK)
INNER JOIN (
SELECT
contract_code, expense_code, MIN(estimate_vers) AS version
FROM
scheme.coestdtm WITH (NOLOCK)
GROUP BY
contract_code, expense_code) AS estb ON
esta.contract_code + esta.expense_code + esta.estimate_vers = estb.contract_code + estb.expense_code + estb.version
GROUP BY
esta.contract_code, esta.expense_code, estb.version) AS ced ON
ccg.expense_code = ced.expense_code

Having spent the best part of the last year on a single Sage 1000 site running multiple projects I have witnessed first hand the frustration of the CRM user experience. In fact so much so that my current client is now considering ripping out CRM and going back to good old Line 500.

Why? Simply because basic customer (and now with version 2.2) supplier maintenance is so much more long winded than it is in Fin and Ops. You can of course turn the maintenance options back on in Fin & Ops menus but now with version 2.2 you get a nice little warning (reading annoying) message that tells you maintenance should be carried out within CRM.

Of course the problem isn’t in the individual products per se, Sage CRM is an awesome product and whilst long in the tooth, Line 500 is a very capable product. The issue is in the poor integration.

And of course this same nightmare exists in the mid-market in the shape of Sage 200 Suite, 2 very good products spoilt by a very poorly designed and executed integration.

The reason for the post is that I have been alerted to something called ‘SData’. SData is (apparently) Sage’s new architecture which will allow any product to talk to any other. This was announced some time ago but the only product I hear it mentioned with is SalesLogix. Be interesting to see where and when (and if) this gets added, or the integration between CRM and financials gets *siginificantly* tightened up.

I work in the dealer channel and closely with Sage VARs in the 500/1000 marketspace so can normally get wind of whispers/rumours of impending announcements. Of course not being a VAR but an independant consultant means I have no restrictions on what I can/cannot do or can/cannot say.

I heard mention the other day of a possible (medium to long term) replacement for Line 500/1000. It is another product in the Sage stable called X3. I have heard of this product and its been around for some time and like many other products came about via way of an aquisition.

What is it like? Cant really say much other than it looks like a more modern product with a more modern architecture. The reality is that Line 500 has a very ancient architecture which is very inflexible. I think it is fast losing ground to things like Microsoft Navision which offers users the ability to add fields to the database, add new fields to screens etc

Is it me or is CRM counter-productive? Not according to the company I have just been helping do a large scale Sage 1000 implementation for.  They want to rip it out and just stick to good old fin and ops. Why? Because the maintenance of customers (and now with version 2.2, suppliers) is just so long winded when compared to the ‘old’ way of doing this. Yes you can turn the maintenance options back on in the menus but now version 2.2 gives you a warning about mainting a customer/supplier through fin and ops. Of course we all know the integration is poor, this implementation had 8 companies and that caused all sorts of problems.

So why even use the CRM part? Well many sites find the dashboard, and the fact you can add your own reports quite useful. If you are very sales focused then the ability to enter and track prospects before they hit the fin and ops is useful. Don’t get me wrong, CRM on its own is, and always has been (even when it was accpac CRM) is a great product. As a bundle it just doesn’t work that well and certainly can be counter-productive. Net result, rumour has it Line 500 sales are still good as many organisations simply don’t need it.

In case you didn’t already know the BDE group have gone into administration. As I am currently working on a project with some BDE professional services people I found about this early on Tuesday when it came to light that Sage have actually revoked BDE’s Sage reseller status. Things have quickly detoriated to the point that BDE are no longer trading. If you are a customer of BDE’s you should have received a letter from Sage informing you of this and giving you alternative. If you need to get in touch with any BDE professional services team member then I am still in contact with some of them and can put you in touch.

Following on from the last post it would be easy to say, the forecast report doesnt work, we know that so lets just bear that in mind. Or you could have what I have, a customer demanding a report which reflects accurate figures. So if you want to get a report with the right figures then you can use this SQL:

SELECT
asset_no, description, depn_code AS life, transfer_date, transfer_period,
capital_value, depn_code - pers_deprcd_so_far AS periods_left, asset_wdv AS nbv,
pers_deprcd_so_far,
CASE WHEN pers_deprcd_so_far = 0 AND transfer_period <> (LEFT(scheme.GetCurrentSagePeriod(GETDATE()), 3) +
RIGHT(scheme.GetCurrentSagePeriod(GETDATE()), 2)) THEN datediff(m, transfer_date, GETDATE()) ELSE 0 END AS catchup,
CASE WHEN transfer_date < GETDATE() THEN
CAST(CASE WHEN depn_code - pers_deprcd_so_far = 0 THEN 0
ELSE asset_wdv / (depn_code - pers_deprcd_so_far) END AS money) ELSE 0 END AS forecast
FROM
scheme.famastm
ORDER BY
asset_no

I have also wrapped this into a SQL reporting services report, available free if you email me here.

Doesn’t work 100%. OK so you may not encounter this problem, I normally hit these sort of problems as I spend most of my time doing data migration work from other systems so I’m more likely to find anomolies that wouldnt always occurr in normal operation.

The issue here is that you *could* have a situation where the forecast depreciation report gives you one figure but the actual depreciation figure when you run the depreciation calculation is different.

In a nutshell the forecast report doesnt correctly calculate ‘catch-up’ depreciation. So if you have an asset with a transfer period of say 08/2008 and you are in period 12/2008 and the number of periods depreciated so far is zero then the report should actually show four months depreciation, it doesn’t!

Bizarrely the actual calculation works fine! Word from Sage is that the report isn’t meant to work ‘backwards’ but if you have on the one hand a report which is used as the basis of the calculation but then doesn’t agree to that actual calculation it isn’t very good.

So as more and more organisations move to the Line 500 webclient or go fully web based with Sage 1000 you would think that for large scale deployments this technology would work seamlessly wouldn’t you? Truth is as the financial and operations module is one very large and cumbersome Java applet it causes no end of problems at sites that I do consultancy for. Sure it works, but can be very temperamental. Of course the decision to move from the universal client to a web offering in a short space of time was (at the time) only realistically acheivable using java. Now things have changed somewhat but the problems with using the webclient have not. There are frameworks now available that can deliver a rich user interface across the web without the need for Java. Want to see what I mean, please take a look at this early prototype here

Of course its early days and re-writing an ERP isnt something a lone developer let alone a small team could undertake, but this demonstrates what is acheivable and could be used as a means to offer limited functionality, SOP/POP enquiries for instance and be run alongside an existing Sage Line 500/1000 deployment.

I’m not questioning anyone’s judgement here but Business Intelligence (BI) is fast becoming the new CRM (aka the emprerors new clothes). Why? Because its seems many medium and large enterprises are falling over themselves to get some form of BI implemented without really understanding what it will and wont give them. Let me give you an example, the company I am currently consulting for have chosen to buy not one, but two BI solutions but then discovered that both of these solutions, whilst eventually delivering the needs of the business, out of the box neither solution can report on the one key area of data the business needs to report on.

The reason I raise this is because we have delivered virtually all of the management reporting requirements using sql reporting services, no BI cubes in sight! Of course one of the useful things as regards GL reporting is that the periodic balances are stored in the GL master table anyway so the aggregations are done, its then just a case of splitting out by cost centre and creating views to group the account codes by sales, cost of sales, balance sheet etc.