BSI Pipeline Report [Irene/Jowell]

[kinosker]

Background: Irene requested for Sales Pipeline Report, which collates based on country, rating and month.

Done:

1. Setup default users at CRM -> Administrator ->Team

Sample for Sales Figures:

[kinosker]

Addendum:

Irene will also require reports in similar format for Order, Cost and Gross Profit too.

[kinosker]

Analysis:

1. Sales, Cost, Order and Gross Profit may come in different currencies type.

2. When aggregating with FetchXML, arithmetic operation is not supported. -> Won't be able to fetch the above mentioned fields and calculate them in USD directly.

3. There are two exchange rates entered in 1 opportunity record. -> msc_exchange rate retrieved from EBM -> exchange rate typed in by sales personnel

4. Multiple entries of Cost, Order and Gross Profit are entered in text. -> Will require to retrieve them by regex

[kinosker]

Addendum:

1. For opportunity, there are two sales type: "Sales" and "Maintenance".
2. When sales type is "Maintenance" -> There will be a period range for the maintenance -> Based on the period range, the cost and order will be divided equally. -> Major impact on the regex, report algorithm
3. Unable to verify if figures collated is accurate -> Will need to generate additional report to validate the data.
4. Include a way for the sales personnel to enter the cost/order milestone

[kinosker]

Done

1. Modified regex to incorporate the sales type "Maintenance" requirements
   • Will create additional milestone using sales type as "Monthly Maint"
2. Modified view to hide "Monthly Maint" sales type.

Follow Up

1. Update Report to filter away sales type "Maintenance" and show "Monthly Maint"
2. Batch run workflow to update record
3. Validate

[kinosker]

Done

1. Update Report to filter away sales type "Maintenance" and show "Monthly Maint"
2. Batch run workflow to update record

Issue Order Pipeline data requires hiding "Monthly Maint" Sales and Cost Pipeline data require hiding "Monthly Maint" 2 Different dataset is needed in 1 single report...

-> Able to use lookup to join the data but when exporting to excel it shows #Value!

[kinosker]

Error when trying to Deploy Report on SSRS to test

1. The permissions granted to user 'APPS\ictcrmadmin' are insufficient for performing this operation. (rsAccessDenied) Get Online Help
2. Generating report from CRM load without data...
3. NTService does not sufficient permission...........

[kinosker]

Possible Resolution:

https://support.microsoft.com/en-sg/help/2590774/microsoft-dynamics-crm-2011-reports-created-by-report-wizard-may-fail

https://community.dynamics.com/crm/f/117/t/105652

[kinosker]

Instead of deploying the report at SSRS, tried deploying the report to CRM directly. -> Another type of error encountered, realized all OOTB reports are not working too -> View #33 for more details

[kinosker]

Fixed

• Issue #33

Pending

• Convert Sales type "Monthly Maint" to "Maintenance"
• Sum of Sales Order
• Possible resolution : Reference another sub-report that calculates based on.................

[kinosker]

Issue:

• Detailed Pipeline View : Unable to sum up the total estimated revenue per month.

Background:

• Dataset fetches Opportunity with Child Milestone records
• Opportunity record row will show multiple times, if there are >1 Child Milestone tagged to it

This causes duplicate opportunity records to get summed up for the month.

Resolution: Added plugin to fetch child milestone records touse the USD rate calculate the estimated revenue per month.

[kinosker]

Suggested Improvement:

• [ ] #68 : In line editable grid for adding milestones
• [ ] Backward Compatibility: Generate lead description from line items added.