locked
CRM Reports with GUID parameter RRS feed

  • Question

  • Hi all,

    We've come across a weird issue in one of our client CRM 2013 on premise environment recently. 

    We have a custom SQL report with a parameter, say the parameter picks up its value (accountid) & label (name) from the following dataset:

    SELECT name, accountid
    FROM FilteredAccount
    WHERE address1_postalcode = '6000'

    We then have another dataset that returns record based on the selected parameter as usual.

    This report works fine when uploaded to our internal development environment, i.e. when we open the report it will load & display the accounts in the parameter drop down. 

    In our client environment, however, the parameter drop down displays blank. Both environment is on UR2. 

    If we upload the same report manually into the report server (in the client environment), the parameter drop down would display the account listing as expected, so the issue only exists in the CRM Report Viewer. 

    After some digging around, we identified that the client's CRM report viewer doesn't like GUID being passed in as the parameter, so the workaround that we did was to update the dataset for the parameter to be:

    SELECT name, Convert(nvarchar(50), accountid) as accountid
    FROM FilteredAccount
    WHERE address1_postalcode = '6000'

    Now the report is working, but there're 50+ reports that we have to update with the workaround above and I'm worried that if we update them now, in the future some update rollup will come out & break the workaround and we'll have to re-update them again.  

    The only different that I can see architecturally is that our dev environment has SQL & SSRS in the same box, where as the client environment has a separate SQL & SSRS box. 

    Has anyone had similar experiences or any idea on what's happening here?

    Thanks in advance.

    Yen

    Wednesday, June 4, 2014 9:13 AM

All replies

  • The best way to test a query is to run directly on the database and see the results therefore your first query is also correct.

    There should not be any difference because of archectecture. The only difference is the name of the database server in the connection string. Please ensure you are running query against correct database


    Regards Faisal

    Wednesday, June 4, 2014 9:27 AM
  • Hi Faisal,

    Thanks for your reply. We've run the query on the database and it's definitely returning the correct result. The parameter also works if uploaded straight to the report server, only not working in CRM.

    We got it to work by converting the Guid to a string in the SQL query, so we're sure it's not an issue with the query or connection string.

    Regards
    Yen

    Thursday, June 5, 2014 1:05 AM