locked
Reports & Data Validation RRS feed

  • Question

  • I am producing some lists that pull data from various records, hence a simple "view" does not work but I had to resort to reports. These lists are then updated by various members of the company and then reimported/updated on a regular basis (which I probably will have to do with some custom code). Considering this, it's important that data in this Excel report has all valid data (option sets, dates, etc). How can I add data validation to reports to assure that cells have proper data?
    Wednesday, September 4, 2013 5:55 PM

Answers

  • My recommendation refers to exporting from views (and advanced finds) not from reports. When you export data from views, you could check a box for re-import. Then the columns only accept specific values (as you said for optionsets). But, when you export the report results AFAIK this feature is not present.


    My Weblog | My Website

    Wednesday, September 4, 2013 8:55 PM
    Moderator

All replies

  • Hi,

    I think the list means "Report" in CRM, and considering this, the list did not update. The data on the entities was updated and it would be reflected in the reports.

    About the validation: Two types of validation could be done. First by JavaScripts, when user opens up a form and fills a field you could check the fields. However, there is some difficulties on this type of validation such as date and time of the server (not the client). The second could be done by plugins, and this is done when the some specified events occur (such as create, update, ...).

    I understand from your question that users want to update data through exporting data, editing in Excel and re-import to CRM. Hence, the first validation could not be done. About the second method, it may partially or completely fails due to some validation errors. For example, if the import could not find a reference to an existing lookup the import of that record fails. But, about all of the validations could be eliminated in the import process.

    As a conclusion, I have 2 recommendations: 1- To avoid editing data in Excel to guarantee the consistency of the data. 2- If it is desired to work with Excel together with CRM, try to refine data and validate them in Excel not in CRM.



    My Weblog | My Website

    Wednesday, September 4, 2013 8:33 PM
    Moderator
  • Maybe my initial question wasn't clear. The second recommendation in your last paragraph is exactly what I want, i.e. validate the data in Excel. My issue is that I don't know how to include this data validation within an Excel file generated by a report. As an example, the lead entity has a Rating field that has the three options "Hot", "Warm", "Cold". How can I restrict that this field in the generated report does not accept any text but simply shows a drop down menu with these three options? I know how to do this in Excel itself but not adding such functionality in a report.
    Wednesday, September 4, 2013 8:46 PM
  • My recommendation refers to exporting from views (and advanced finds) not from reports. When you export data from views, you could check a box for re-import. Then the columns only accept specific values (as you said for optionsets). But, when you export the report results AFAIK this feature is not present.


    My Weblog | My Website

    Wednesday, September 4, 2013 8:55 PM
    Moderator
  • Data exported from a report as Excel is for view only... it is a SSRS feature and is not tied to CRM. If the business process is for the users to update these Excels and to be fed to CRM then you have to do the validations in your custom code (import). As you are aware, this involves additional steps to cleanse teh data but for now there is little else that can be done.

    If my response helps you in finding your answer then please click 'Mark as Answer' and 'Vote as Helpful'

    Wednesday, September 4, 2013 9:08 PM
  • Mamatha, thank for the response. Without data validation we will end up spending too much time on cleaning up the reports. I will have to think of a different solution.
    Thursday, September 5, 2013 4:19 AM