locked
Including an SQL view in a CRM solution RRS feed

  • Question

  • Is it possible to include a view defined in SQL in a CRM solution?

    What I'm trying to do is create some reports which need to be filtered on calculated fields. It appears that FetchXML is not sufficiently capable to do this as filters in FetchXML based reporting are applied before caculated fields are generated. I can of course create a view in SQL at the SQL server but that is not then included in the CRM solution. Is it possible to include that view in some way?

    Monday, September 29, 2014 11:52 AM

All replies

  • Hi J N,

    I don't think it's possible to define a view in SQL. You can either create a Report at that Entity or you have write your view in fetchXML.

    Regards,

    Saad

    Monday, September 29, 2014 12:15 PM
  • Hello,

     You can use FilteredViews to create reports. This report can be included in a solution and deployed.

    Note: FilteredViews are available only OnPremise.

    Regards,

    Jithesh

    Monday, September 29, 2014 12:20 PM
  • Hi J N,

    This is an unsupported solution.

    You can create a Stored Procedure and use it.

    Regards,

    Saad

    Monday, September 29, 2014 12:27 PM
  • Hello,

     You can use FilteredViews to create reports. This report can be included in a solution and deployed.

    Note: FilteredViews are available only OnPremise.

    Regards,

    Jithesh

    Unfortunately that only filters the existing fields, it does not allow calculated fields to be added to the view (or if it does I haven't found that feature)
    Monday, September 29, 2014 12:51 PM
  • Hi J N,

    This is an unsupported solution.

    You can create a Stored Procedure and use it.

    Regards,

    Saad

    Won't they exist on the SQL server and so not be included in a CRM solution when deployed to another organisation?
    Monday, September 29, 2014 12:55 PM
  • Hello,

     Computed fields can be added to SSRS very easily. please see another post where I have said how to do this.

    http://social.microsoft.com/Forums/en-US/c2ef49c9-4f05-4697-895f-d746de29fa27/crm-2011-neglected-leads-report-change-neglected-days?forum=crmdevelopment

    There are a lot of expressions available for you to use inside your report. If you can explain the computed field, one of us could help you :)

    they can be also computed and added into a column (without referencing the data set)

    Regards,

    Jithesh

    Monday, September 29, 2014 1:26 PM
  • Ta for that. I shall try that later. It's the first time I have seen SQL in the dataset query definition and I was under the impression that I could only use FetchXML in that area which is far too restrictive for my needs. For the moment I have found a workaround but it's not very elegant and involves making assumptions about the data I will find.

    The basic problem I had was I was trying to filter on all customers or a subset. In FetchXML it seems there is an upper limit to the in clause so I can not simply select 'all' customers. My first attempted solution was to add a parameter to choose to report on 'all' customers and test that but FetchXML reports can compare fields to parameters but not parameters to constants/ My second solution was to add a calculated field and compare to that but FetchXML reports appear to filter data before producing calculated fields so that failed. The next solution was to add an SQL view but that is not included in a CRM solution so that wouldn't work.

    I shall try rewriting my FetchXML queries as SQl queries and see if that works.

    Monday, September 29, 2014 1:53 PM
  • And please remember to use the FilteredViews instead of direct queries to base tables. If you use base tables, it may work in dev env, but will not work for your end users.

    And SQL query reports are available only onpremise :)

    Cheers,

    Jithesh

    Monday, September 29, 2014 1:57 PM
  • OK, I've tried that and I realise the issue is still there. To support SQL queries as the source I need to connect to the SQL server which means providing a connection string which will of course be different ion each deployed system. So we are back to being unable to include an SQL source in a CRM solution or more accurately we can include an SQL source but it would require custom configuration on each deployed system. The one advantage of FetchXML is that no such configuration is required.
    Monday, September 29, 2014 5:29 PM
  • I beg to differ. There was no fetch XML based reports in CRM 4. Ee used to build reports using sql queries. This function is still there for onpremise.

    same way fetchxml based reports use a shared data source, sql reports can also use a shared data sources.

    regards,

    Jithesh

    Monday, September 29, 2014 7:59 PM
  • As per the last post, you can create a CRM report that is based on SQL, and include it in a solution to deploy to a different environment. However, there are some things you need to ensure:

    • The report uses just one data source, and it is not a shared data source
    • Your dataset(s) reference the CRM filtered views, and no custom SQL objects (such as your own views). The majority of reports that you'd expect to write should be able to meet this criterion
    • This works for OnPremise only

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Proposed as answer by Guido PreiteMVP Tuesday, September 30, 2014 4:25 AM
    • Unproposed as answer by J N Brand Tuesday, September 30, 2014 11:11 AM
    Monday, September 29, 2014 9:34 PM
    Moderator
  • OK, I've tried that and I realise the issue is still there. To support SQL queries as the source I need to connect to the SQL server which means providing a connection string which will of course be different ion each deployed system. So we are back to being unable to include an SQL source in a CRM solution or more accurately we can include an SQL source but it would require custom configuration on each deployed system. The one advantage of FetchXML is that no such configuration is required.

    HI J N,

    I don't think you will need to configure string connection for Reports for different environment. CRM will take care of that.

    As far as SP's are concerned you need to deploy them manually at different environments.

    Regards,

    Saad

    Tuesday, September 30, 2014 5:00 AM
  • OK so what should one set the connection string to in development? If I leave it blank it refuses to run which is not unreasonable because it has no information on how to connect. If I define it then it runs in development but fails in deployment as it "cannot create a connection to data source" presumably because the connection string is wrong?

    Firewalls are open and remote access is enabled which would be the usual cause of that error
    • Edited by J N Brand Tuesday, September 30, 2014 11:47 AM
    Tuesday, September 30, 2014 10:54 AM
  • Hello,

     Please answer the following questions.

    1. Are you connecting to external database or connecting only to your <organisationname>_mscrm database?

    2. Are you planning to use any external stored procedures (they are unsupported.)

    If your data is fully contained in your CRM database, then CRM will use a shared connection already setup when you installed CRM.

    Then

    1. Create a new report using reports wizard, add the required entity and add a field or more (reason - to get you the default parameters, filteredviews etc).

    2. If you are creating a crm 2013 report, it will create a report with fetchxml format.

    3. Now go into the SSRS url that you provided when CRM was installed (general format of the reports url is http://<servername>/Reports_<sql_instance_name>

    4. There will be a new folder which is same as your organisation name.

    5. There will be a datasource under this folder - we will use this shared datasource once the report is deployed to the server (LATER).

    6. There will be a folder called sharedreports (approximate name), navigate into this folder. sometimes the list will not show anything. change the view to details view to see all the reports for your organisation.

    7. find the report that you created in step 1.

    8. click on edit in report builder - this opens the report in reports builder.

    9. change the shared data source to local datasource and specify the connection string (change it to sql server driver instead of fetch driver).

    10. change the dataset,  add appropriate filteredview, join with the @CRM_FilteredContact parameter (if your report is based on contact). This parameter gives you flexibility to pass custom filter from the report front end at run time(similar to advanced find).

    11. before you deploy the reports to your server, you will have to change the data source called CRM back into local data source.

    12. save the rdl file, or saving the changes from reports builder will deploy your changes to server.

    13. The rdl file can be imported from CRM UI to reflect your changes.

    it might sound a bit confusing, but once you do it, it is very easy.

    basically what you are doing is to create a report using the wizard, edit it to change the report as you wish and then import/upload it back into your CRM server.

    Let me know if you have any question. I have written this not in-front of a CRM instance, so please let me know if you need any clarification.

    Regards,

    Jithesh

    Tuesday, September 30, 2014 11:55 AM
  • I'll try that but to answer your questions;

    The reports I am trying to create use only data on the crm server, no external data sources and no stored procedures. A bit of experimentation and digging seems to suggest that CRM is very picky about the datasource names an order. If the SQL data source is called CRM and is the only data source then it will run anywhere and connect to the SQL server. Of course in my case I was starting with a report which had a datasource already defined as a FetchXML datasource. Add another datasource to that and CRM just throws its toys out of the pram. I shall try to see if I can rename and reorder the existing reports and datasources because I really don't want to have to recreate the whole lot using from scratch.

    Tuesday, September 30, 2014 12:46 PM
  • hi,

    please note that all report defenition files are xml file. So you may be able to open the report in notepad/text editor and change data source name.

    regards

    Jithesh

    Tuesday, September 30, 2014 8:43 PM