Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
CRM - Sales Pipeline Report - Order by Estimated Close date problem

Unanswered CRM - Sales Pipeline Report - Order by Estimated Close date problem

  • Monday, August 18, 2008 5:04 PM
     
     
    Sign In to Vote
    0

    Please help me - I am trying to get the Sales Pipeline report to order correctly on the Estimated Close date.

    My report formats correctly and I have set to Group by Date from the parent report of the sales pipeline - BUT - when I view the Sales Pipeline Detail report the Estimated Close date is all over the place...

    E.g. August opportunities following by December then November then October then September!

    I have spoken to one of my developers and he says that Group by Date is one thing... you need to also order by estimatedclosedate... but he was no help in providing a solution.

    BTW - UK dates although as all stored as UTC this cannot be the problem... Also we are on CRM 4.0 but I am guessing this was a problem in CRM 3.0 also?

    Thanks in advance,


    Charlie

     

All Replies

  • Tuesday, August 19, 2008 2:12 PM
    Owner
     
     
    Sign In to Vote
    0

    Hi Charlie,

     

    What tool are you using to modify the report? 

     

    If you are using Visual Studio and have access to the Query you can add an Order By 'estimatedclosedate' at the end of your query.  The return will be in the order you desire and should sort in your report view by that order.  The better way to accomplish this is to select the edit Group option in the control (matrix, table, etc) and then select the Sort tab and select the estimatedclosedate from the field picklist.

     

    Best Regards,

     

     
  • Tuesday, August 19, 2008 7:37 PM
     
     
    Sign In to Vote
    0

    Our Pipeline Report by Estimated Close uses the month & year to sort the printout. We did not want the report to break each time the estimatedclosedate changed, but whenever the month changed:

     

    So it sorts by estimatedclosedate, but it prints/breaks by:

     

    =Month(Fields!estimatedclosedate.Value)&"/"&Year(Fields!estimatedclosedate.Value)

     

    Or:

    08/2008

    09/2008

    10/2008

    11/2008

    etc.

     

    Maybe that helps...

     

     
  • Tuesday, August 19, 2008 7:43 PM
    Owner
     
     
    Sign In to Vote
    0
    I am not sure I understand but you can try the Year first and then the Month maybe that will fix the issue.  I assume by 'break' you mean page break?
     
  • Tuesday, August 19, 2008 7:52 PM
     
     
    Sign In to Vote
    0

    Yes, page break (actually a control break to add how many and how much $$$ expected to close each month).

     

    It does NOT use the Year first, then the Month next, but month/year like today would be 08/2008. It does not SORT

    by this, it sorts by estimatedclosedate. It just prints and breaks by month/year. Works OK.

     

     
  • Wednesday, August 20, 2008 10:23 AM
     
     
    Sign In to Vote
    0

    Hi Donna,

     

    Thanks for the reply - the time delay is always a problem when in UK! Especially when the Olympics is on...

     

    I am using Visual Studio 2005 with the SQL Server Business Intelligence Development Studio enhancement.

     

    I have tried to sort using the method you descibe and it does not change the fact that my dates seem to be sorted but in a strange order. I have made sure that I am in the right place in altering the code as I set the order to be descending/ascending and the date order changes but the order is always consistant.

    What i did was: Navigate to the Layout tab in VS | Select the table and with right mouse click select properties | select the Groups tab and select to edit "table3_Group1" | on the new window select the sorting tab and add "=Fields!estimatedclosedate.value" with the direction set to "Aascending"...

    Is this correct? I also tried to remove the existing entry that was already in this list "=Fields)Parameters!CRM_GroupBy.Value).Value" but this made no difference.

     

    By the way I am invoking this report from the parent report of Sales Pipeline where I am selecting the "Group by Date" report option and then once the chart has appeared I am selecting "show all" to invoke the Detail report.

    Interestingly I am getting a few errors outputed at creation of the chart in the sales pipeline: "[rsRuntimeErrorInExpression] The HeadingLabel expression for the chart ‘ChartDate’ contains an error: Conversion from string "07/31/2008" to type 'Date' is not valid." Clearly this error is to do with the fact that my language settings are en-GB and not the US... I.e. For UK 07/31/2008 is an invalid date but for US it is 31st of July 2008. Not sure why this is happening - but could this point to the overall problem why my dates are not ordering?

     

    I.e. My date order in my report for the following months listed so far in the Opportunities section is:

    August 2008

    December 2008

    February 2008

    January 2009 !!!!

    July 2008

    November 2008

    October 2008

    September 2008

     

    I am at a complete loss and please help as this report is almost useless without the dates being in the correct order...

     

    P.s. I have checked the ASP.net user Regional settings and the settings on the server under the Administrator account and all are set correctly to English - United Kingdom and the Local is the same... This maybe a red herring but am at a loss as to what else it could be...

     

    Thanks,

     

    Charlie

     
  • Wednesday, August 20, 2008 10:28 AM
     
     
    Sign In to Vote
    0

    Leonardg,

     

    Thanks for the reply... looking at the report we have sales for 5th August | 15th August | 27th August and you are absolutely correct what to have them grouped by DD is not great for me either.

    Thus your solution is perfect...

     

    However being a novice at all this and I am a complete loss as to where to put the code. I am using VS 2005 so have the editing capability but just the intelligence.

     

    Please help as this sounds ideal - All I have to do extra is to get these dates / months appearing in the correct order and I am flying... I posted back to Donna on this as you will see in this thread...

     

    Thanks,

     

    Charlie

     
  • Wednesday, February 25, 2009 12:29 AM
     
     
    Sign In to Vote
    0
    Hi

    I believe I have figured out what is causing the problem, but do not yet have a solution.

    I too have been frustrated with the poor / broken reporting making it exceedingly painful to get basic sales forecasting data out of MS CRM 4.0.

    The Sales Pipeline Report (Show All) appears to be sorting the Estimated Close Date in ...wait for it...

    **** ALPHABETICAL ORDER!!! ****

    I hope that this sheds some light on the matter and some clever developer can fix the report.

    For our business we choose the 1st of each month for estimated close date so that they are all group together.

    For some reason some of my close dates may be 1st Feb 09 12:00am and some are 12:00pm same date and therefore group differently. Any idea how I can get all appropriate records to have the same close date and not 12:00am or 12:00pm?

    If possible I would really like to add Account Name to this report as otherwise it's largely meaningless.

    If viewing the graph grouped by date each bar of the bar chart is labelled Jan 09. If I try to click one to drill down then I get this error:
    • An error has occurred during report processing.
      • Cannot read the next data row for the data set DSpipeDetails.
        • For more information about this error navigate to the report server on the local server machine, or enable remote errors
    If I try and print the report I get an error: "unable to load client print control"

    For a flagship product frankly this is utter rubbish and makes the product unusable for it's intended purpose.

    Jon