locked
crm2013 charts possible to do with sql instead of fetchxml? RRS feed

  • Question

  • Hi all,

    i Need to do some sophisticated Charts and I am concerned that FetchXml is limited to the comparison with SQL.
    ...more, I think it will not work with fetchXML. But then what should I do? Use ssrs with SQL & show the report in a Dashboard within an iframe?

    I dont think this is the best Option, because of:

    • the missing drilldown
    • Report is only inserted, but not really part of crm
    • optic is bad (because of the Report heads (Export, print, etc.)

    I need to show the result

    1. grouped by week, starting with actual week, plus the 3 weeks bevore to compare the sums
    2. grouped by month, ... with acutal month, plus the 3 months bevore ...
    3. grouped by year, .. with the acutal year, plus the last year

    my acutal SQL Looks like that. the groupings are missing, and some more limitations linke satecode = active etc.

    SELECT        
    DATEPART(WEEK, CRMAF_Filteredbd_va.createdon) AS week, 
    DATEPART(MONTH, CRMAF_Filteredbd_va.createdon) AS month, 
    DATEPART(YEAR, CRMAF_Filteredbd_va.createdon) AS year, 
    CRMAF_Filteredbd_va.bd_number, 
    CRMAF_Filteredbd_va.bd_vn_kid,
    CCRMAF_Filteredbd_va.bd_vn_kidname,
    RMAF_Filteredbd_va.bd_vn_fid,
    CRMAF_Filteredbd_va.bd_vn_fidname,
    CRMAF_Filteredbd_va.bd_pgid,
    CRMAF_Filteredbd_va.bd_pgidname,
    CRMAF_Filteredbd_va.bd_agnr_avmid,
    CRMAF_Filteredbd_va.agnr_avmidname,
    CRMAF_Filteredbd_va.bd_vs,
    CCRMAF_Filteredbd_va.bd_vsname,
    RMAF_Filteredbd_va.bd_vg,
    CCRMAF_Filteredbd_va.bd_vgname,
    RMAF_Filteredbd_va.bd_sum,
    CRMAF_Filteredbd_va.statecode,
    CRMAF_Filteredbd_va.statecodename,
    Filteredbd_agnr.bd_vm_kidname,
    CRMAF_Filteredbd_va.bd_vaid,
    CRMAF_Filteredbd_va.createdon
    FROM
    Filteredbd_va AS CRMAF_Filteredbd_va LEFT OUTER JOIN
    Filteredbd_agnr ON CRMAF_Filteredbd_va.bd_agnr_avmid = Filteredbd_agnr.bd_agnrid
    WHERE
    (CRMAF_Filteredbd_va.bd_vg IN ('100000001', '100000002', '100000003')) AND 
    (DATEPART(year, CRMAF_Filteredbd_va.createdon) <= DATEPART(year, { fn NOW() })) AND 
    (DATEPART(year, CRMAF_Filteredbd_va.createdon) >= DATEPART(year, { fn NOW() }) - 1)
    ORDER BY year DESC, month DESC, week DESC

    and this is the easiest of the 5 requirements.

    Questions:

    1. Is it possible to do this in an CRM Chart?
    2. Are CRM Charts possible to do with SQL, or only fetchxml?

    i hope someone could give me his opinion on that.

    THX, Greets PeB

    Thursday, November 13, 2014 9:05 AM

Answers

  • SQL can't be used for CRM Charts, only FetchXML

    My blog: www.crmanswers.net - Rockstar 365 Profile

    • Proposed as answer by Guido PreiteMVP Thursday, November 13, 2014 9:08 AM
    • Marked as answer by Pe.B Thursday, November 13, 2014 11:23 AM
    Thursday, November 13, 2014 9:08 AM
  • If you have knowledge from javascript development, you can do this with javascript using CRM's soap webserver, in combination with a charttool like Flot charts (http://www.flotcharts.org/) in a html webresource. That webresource can be included in a dashboard, for example.
    • Marked as answer by Pe.B Thursday, November 13, 2014 11:23 AM
    Thursday, November 13, 2014 10:52 AM

All replies

  • SQL can't be used for CRM Charts, only FetchXML

    My blog: www.crmanswers.net - Rockstar 365 Profile

    • Proposed as answer by Guido PreiteMVP Thursday, November 13, 2014 9:08 AM
    • Marked as answer by Pe.B Thursday, November 13, 2014 11:23 AM
    Thursday, November 13, 2014 9:08 AM
  • If you have knowledge from javascript development, you can do this with javascript using CRM's soap webserver, in combination with a charttool like Flot charts (http://www.flotcharts.org/) in a html webresource. That webresource can be included in a dashboard, for example.
    • Marked as answer by Pe.B Thursday, November 13, 2014 11:23 AM
    Thursday, November 13, 2014 10:52 AM
  • woow js sounds good ..

    i think .. with that .. there are no limitations like with fetchXML ...
    i'll give that a big try and hope .. having found the solution for me ...

    But drilltroughs like with fetchxml arent possible with js, or?
    This is like wiht the Report, or?

    thx greets peb

    Thursday, November 13, 2014 11:27 AM