2002 Activity included in current year totals!! RRS feed

  • Question

  • I know I am late to working with the Beta and I don't know if this is the proper forum at this point, but I must say I am disappointed.

    The biggest issue is the totals are incorrect.  I set up a simple report - a single row - to display our cash category.  Two columns YTD and Prior YTD (run through May) and received totals that did not reconcile with FRx and originally could not balance to anything for some accounts. 

    We have our fiscal years setup with an adjustment period (Period 13 with a December 1 start date).  After running SQL Profiler, I found MR is running a SELECT with something like

    SUM(CASE WHEN ((source.YEAR1= 2000 AND source.PERIODID=13) OR (source.YEAR1=2002 AND source.PERIODID=13) or (source.YEAR1=2004 AND source.PERIODID=13) THEN PERDBLNC ELSE 0 END))

    As a result, MR is picking up 2002 Y/E adjustments in my current YTD balance.

    With some of those earlier years we actually had 14 periods total - 2 adjustment periods.  Period 13 was internal closing entries and period 14 was audit adjustments.  We have found adjustment periods do not work well in Dynamics since there is no way to identify specific transactions with that period, so we have only implemented period 13 in recent years.

    Anyway, my original trial with MR is no reports would generate.  Again tracing the SQL activity, we found having periods 14 in 1999 and 2000 would crash MR.  After eliminating those periods and reconciling those years we were able to get MR up and running.


    Friday, July 2, 2010 5:13 PM