Management Reporter Link To Financial Dimension Range Problem

September 23rd, 2013 by

Management Reporter again, folks, after a short delay; I finally completed on a house purchase, in rural Northumberland, two years after I started work at Perfect Image.

I received another call that data on a report was not coming out correctly a short time ago (I’ve had the screenshots stored for a while until I had time to write this post); the numbers didn’t match those in Microsoft Dynamics GP or those on the old FRx report (which did match the numbers in GP.

I started by checking the Row Definition and the Link to Financial Dimension column to see what information was being brought through. The setup with ranges for the excludes struck me as a little odd with the ranges of 1:1, 10:10 and 100:100:

Dimensions

I then compared back to the FRx Row Format and it did make the Management Reporter configuration make more sense:

GL Account Links

I manually amended one of the ranges on Management Reporter and checked if the numbers changed, which they did. I was able to get the numbers to match exactly by manually removing all of the ranges where the low and high of the range were the same.

However, this client is one who made a lot of use of FRx and there were a large number of reports that would then need to be checked and corrected, so I needed to work out a more efficient way than doing it manually.

Fortunately, Management Reporter uses a SQL database which I can interrogate and update where necessary. I had a furtle around in the database and identified the table I needed to update as ControlRowCriteria (the same one I updated when fixing the Fabrikam sample reports row definitions. I produced the following script to identify the entries which would need to be updated:

SELECT
   RowLinkID
   ,Low
   ,High
FROM
   ControlRowCriteria
WHERE
   Low = High

One identified, the next step was to create a script which would remove the High value of the range when the Low and High were the same:

UPDATE
   ControlRowCriteria
SET
   High = ''
WHERE
   Low = High

Once the script was run, the Dimensions were single values except when they really should have been ranges:

Dimensions

I asked the client to check some reports and they confirmed that the output was now correct.

Source: azurecurve

Leave a Reply