locked
Filter a report on "all customers" RRS feed

  • Question

  • I have a report written in SSRS which produces data for a specific customer. I can rewrite that report to produce data for all customers by deleting the condition. I can rewrite the report to produce data for multiple customers by changing the parameter to an "in" instead of "eq" condition. However that only works up to a small number of customers. If I select lots or, as would be more common, all customers then the CRM reports crash with their usual unhelpful messages.

    What I would like to do is add an "All customers" prompt and use that to override the condition. i.e. instead of

      <condition attribute="customerid" operator="in" value="@customer" />

    I want something like

      <filter type="or">
        <condition value="@AllCustomers" operator="eq" value="1" />
        <condition attribute="customerid" operator="in" value="@customer" />
      </filter>

    which of course doesn't work ("AllCustomers" is an integer parameter for the report with values of 0 or 1) . I have tried using a calculated field in the report but that fails as well, presumably this query retrieves data before any calculated fields are produced.

    How can I produce a report filtered on a subset of customers?

    Saturday, September 27, 2014 3:42 PM

All replies

  • By declaring a report parameter , its values obtained from a dataset, you can select whether to allow for multiple selections of the parameters values.

    By specifying yes, the when the report renders the report parameter it will provide a Select All option.

    The TSQL Where condition should use 'In'

    Take a look at http://technet.microsoft.com/en-us/library/dd220464(v=sql.105).aspx

    Sunday, September 28, 2014 12:59 PM
  • By declaring a report parameter , its values obtained from a dataset, you can select whether to allow for multiple selections of the parameters values.

    By specifying yes, the when the report renders the report parameter it will provide a Select All option.

    The TSQL Where condition should use 'In'

    Take a look at http://technet.microsoft.com/en-us/library/dd220464(v=sql.105).aspx

    Unfortunately it doesn't work or more accurately it only works up to a certain number of options. That's exactly the solution I use for other report parameter. For example I have a report parameter "status" which filters on the status of customer support cases and that works because there are at most perhaps a dozen different states. The same approach used for customers works if I select a single customer or multiselect a few customers but if I select all customers which might be several hundred then it seems that FetchXML can not handle it and fails with a generic error.

    The annoyance is that if someone wants "all" customers the condition isn't required so it shouldn't be an issue but to handle both cases in a single report seems to require a multiselect "in" clause and that is not reliable in FetchXML.

    Monday, September 29, 2014 8:43 AM
  • HI JN,

    Please try this.

    Regards,

    Saad

    Monday, September 29, 2014 9:08 AM
  • HI JN,

    Please try this.

    Regards,

    Saad

    Uses SQL which does the job but I appear to be unable to include SQL based queries in reports in CRM solutions.
    Monday, September 29, 2014 11:54 AM
  • Hi J N,

    Are you developing your Report using SQL or FetchXml?

    Saad

    Monday, September 29, 2014 12:11 PM
  • FetchXML

    Monday, September 29, 2014 12:47 PM