Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Management Reporter - Unable to Drill Down

Jump to Best Answer
  • 1.  Management Reporter - Unable to Drill Down

    Posted 8 days ago
    Recently I had to rebuild the data mart for our Management Reporter because the budget data was missing for one period. Now when we drill down in a report we cannot get to the transaction level data. When we drill to the transaction detail we get the generic MR error "The operation could not be completed due to a failure on the server".

    Below is the error in the event viewer.
    System.Data.SqlClient.SqlException (0x80131904): The correlation name 'dv3' is specified multiple times in a FROM clause. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass7.<ExecuteReaderWithRetry>b__6() at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Microsoft.Dynamics.Performance.Core.SqlCommandWrapper.RunCommand[TResult](SqlCommand command, Func`1 execute) at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.DataMart.Data.DataMartReader.Open() at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.DataMart.Data.DataMartTransactionDetailReader.Open() at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.DataMart.Data.DataMartTotallingTransactionReader.EnsureReader() at Microsoft.Dynamics.Performance.DataProvider.GeneralLedger.DataMart.Data.DataMartTotallingTransactionReader.Read() at Microsoft.Dynamics.Performance.Reporting.NativeReport.TransactionLineBuilder.PopulateTransactionLines() at Microsoft.Dynamics.Performance.Reporting.NativeReport.TransactionLineBuilder.BuildReportTransactionLines(GLTransactionResult result) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.LoadTransactionLines(Guid id, Int32 noteReportId, Int32 unit, Int32 row, Int32 account, Boolean loadNotes, ReportDataContext context, Line data, Structure reportStructure) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.LoadTransactionLines(Guid id, Int32 noteReportId, Int32 unit, Int32 row, Int32 account, Boolean loadNotes, ReportDataContext context, Line data) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.LoadLinesInternal(Guid id, Int32 unit, Nullable`1 row, Nullable`1 account, Boolean loadNotes, ReportDataContext context, Line data) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.<>c__DisplayClass7a.<LoadLines>b__79(ReportDataContext context) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.Execute[T](Boolean writable, Func`2 execute) at Microsoft.Dynamics.Performance.Reporting.NativeReport.ReportDataContext.LoadLines(Guid id, Int32 unit, Nullable`1 row, Nullable`1 account, Boolean loadNotes) at Microsoft.Dynamics.Performance.Reporting.NativeReport.NativeReportService.<>c__DisplayClass22.<LoadLines>b__21() at Microsoft.Dynamics.Performance.Reporting.Security.Service.SecureServiceBase.SecureExecuteInternal[TResult](Func`1 hasPermission, Func`1 executor) at Microsoft.Dynamics.Performance.Reporting.Common.Service.ServiceBase.BaseExecute[TResult](Func`1 executor) ClientConnectionId:098b5bd9-16fb-4767-bd9a-f72fad1a6e0a Component:

    ------------------------------
    George Sheppard
    IT Manager
    Newfoundland and Labrador Liquor Corporation
    St. John's
    ------------------------------


  • 2.  RE: Management Reporter - Unable to Drill Down

    SILVER CONTRIBUTOR
    Posted 7 days ago
    Check the Integration log in the MR Configuration Console.  You should see entries providing additional detail there.

    ------------------------------
    Charles Ray
    Senior Systems Specialist
    CAL Business Solutions, Inc.
    Harwinton CT
    ------------------------------



  • 3.  RE: Management Reporter - Unable to Drill Down
    Best Answer

    SILVER CONTRIBUTOR
    Posted 7 days ago
    HI George,

    When all else fails....we love when IT tells to stop and restart right?!  You could try to stop and restart the MR services which are located on the same box as the configuration console.  Its not gonna hurt anything and is sometime the quick fix.  (one try)

    Next - Is it all reports or just for this one report definition that you can't get to the transaction detail?  If just the one, you could try swapping out the different building block components to see if you can get the report to work with different rows and column definitions. If you find one, you could try recreating that one definition and slowly add the pieces of that definition back in to see if you can identify the book code, or format code total, etc that might be causing the problem.  (I know  you said the budget was the cause of the rebuild in the first place.) Can you run down to the transaction level without the budget piece?  Maybe you still have an issue with the budget.

    If all of them, it is definitely something with the data mart and one of the 6-8 integrations could be failing.

    Curious, do you get any kind of errors if you try to create a new row, or column? sometimes that will give you a start in where to look if you can't create new components.

    Just some ideas for where to start troubleshooting.

    ------------------------------
    Annette Brown
    Sr. GP Implementation Consulant
    Columbus
    Troy MI
    ------------------------------



  • 4.  RE: Management Reporter - Unable to Drill Down

    TOP CONTRIBUTOR
    Posted 7 days ago
    Edited by Jo deRuiter 7 days ago
    Hi @George Sheppard

    There are a couple of things to consider.

    1:  There are buggy issues with certain versions of Management Reporter and certain versions of GP.  Your best bet is to make sure you are using the latest MR version.

    2:  This is what your issue sounds like to me - you re-built the DataMart and THEN it would not drill down - this was a known issue, here is a Blogthat details it.   If you are using Management Reporter CU 15, you have to rebuild the DM from PowerShell and this Blog will help you do that.​

    Resetting the Management Reporter data mart
    Dynamics Financial Reporting remove preview
    Resetting the Management Reporter data mart
    There have been a few questions around troubleshooting the data mart and how to rebuild or reset the data mart when required. In this post, We'd like to clarify when resetting the data mart is required, and hopefully clear up some misunderstandings around the process. We've been hearing, pretty consistently, that customers are rebuilding the...
    View this on Dynamics Financial Reporting >


    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)

    ------------------------------



  • 5.  RE: Management Reporter - Unable to Drill Down

    Posted 7 days ago
      |   view attached
    George,
    I don't know what to tell you to do to fix your MR problem. I can, however, eliminate most of your pain going forward with a tool called PrismView. PrismView combines the best features of Excel and Smartlist, and then gives you significantly greater functionality, ease of use, and visibility into your business. Please don't hesitate to let me know if you'd like to see some additional information.  Thanks.

    All the best,
    Craig
    Why #Microsoftcalledus



    ------------------------------
    Craig L. Sherwood
    Solution Architect
    Centerprism
    (314)205-8484
    csherwood@centerprism.com
    ------------------------------

    Attachment(s)

    pdf
    PrismView_V2.pdf   187K 1 version


  • 6.  RE: Management Reporter - Unable to Drill Down

    Posted 7 days ago
    I want to thank everyone for their suggestions! A big thank you to Annette Brown whose suggestion to stop and restart the MR services fixed my problem. It's so great when the simplest of solutions fix your problems. 

    ------------------------------
    George Sheppard
    IT Manager
    Newfoundland and Labrador Liquor Corporation
    St. John's
    ------------------------------



  • 7.  RE: Management Reporter - Unable to Drill Down

    SILVER CONTRIBUTOR
    Posted 6 days ago
    :)  Wahoo!!!  Glad I could help!! Sometimes its just the simple things eh?!  Thank goodness.


    ------------------------------
    Annette Brown
    Sr. GP Implementation Consulant
    Columbus
    Troy MI
    ------------------------------