locked
SSRS and FetchXML code issue with PARAMATER.. Newbie screwed up fetchXML code RRS feed

  • Question

  • Hi all,

    I am a rookie at building reports and trying to putt together Account Summary which basically has 8 other reports from CRM.

    I created paramater which allows me to choose froom list of accounts. And then for for example, my dataset is Contacts then pull all the contacts just for that account.

    The PROBLEM is, I get same data no matter what account I select!! What I did is I used ADVANCED FIND in CRM and put Account Name=UBS and then just did FetchXML to get the code. I copied the code in Dataset and then replaced UINAME with @name in the code.. I think this is where I screwed up because no matter which account I select I only get UBS data..  "name" is my field for the account name in CRM..

    I did the same for my other Datasets, for example my appointments per account, my opportunities, etc.. but no matter what account I select I always get UBS account details..


    The code for Contacts is below:


    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="contact">
    <attribute name="fullname"/>
    <attribute name="contactid"/>
    <attribute name="accountrolecode"/>
    <attribute name="sp_function_new"/>
    <attribute name="new_attitude"/>
    <order attribute="fullname" descending="false"/>
    <filter type="and">
    <condition attribute="statecode" operator="eq" value="0"/>
    <condition attribute="parentcustomerid" operator="eq" uiname="@name" uitype="account" value="{94CB6894-FFB8-E311-94D6-6C3BE5A87530}"/>
    </filter>
    </entity>
    </fetch>



    Here is another Dataset for my PRODUCTS.. I basically did the same


    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="sp_products">
    <attribute name="sp_productsid"/>
    <attribute name="sp_name"/>
    <attribute name="sp_producttype"/>
    <attribute name="sp_lifestage"/>
    <attribute name="sp_distributionchannels"/>
    <attribute name="sp_additionalinformation"/>
    <order attribute="sp_name" descending="false"/>
    <filter type="and">
    <condition attribute="statecode" operator="eq" value="0"/>
    <condition attribute="sp_clientid" operator="eq" uiname="@name" uitype="account" value="{94CB6894-FFB8-E311-94D6-6C3BE5A87530}"/>
    </filter>
    </entity>
    </fetch>


    Saturday, April 5, 2014 1:43 AM

Answers

  • yes that's correct

    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Donyc Monday, April 7, 2014 1:35 AM
    Sunday, April 6, 2014 9:44 PM
  • Hi Donyc,

    In your report configuration screen, set the Related Record Types and Display In as per this screen shot.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Donyc Monday, April 7, 2014 2:07 PM
    Monday, April 7, 2014 4:02 AM

All replies

  • Hi,

    Here is a couple of things to check, first make sure your Report Parameter is set as per this screen shot.

    The query for the parameter is

    <fetch distinct="false" mapping="logical" output-format="xml-platform" version="1.0">-<entity name="account"><attribute name="name"/><attribute name="accountid"/><order descending="false" attribute="name"/></entity></fetch>

    second, change your dataset to something like this (note the change in the condition node, its the value that you need to pass your parameter to, not the uiname.)

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
     <entity name="contact">
     <attribute name="fullname"/>
     <attribute name="contactid"/>
     <attribute name="accountrolecode"/>
     <attribute name="sp_function_new"/>
     <attribute name="new_attitude"/>
     <order attribute="fullname" descending="false"/>
     <filter type="and">
     <condition attribute="statecode" operator="eq" value="0"/>
     <condition attribute="parentcustomerid" operator="eq"  value="@name"/>
     </filter>
     </entity>
     </fetch>

    and lastly, check the dataset parameter property have the following setting

    Hope this helps.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Saturday, April 5, 2014 11:06 AM
  • Hi Eric,

    That's awesome and it did the trick. Thanks so much! I started changing all of the datasets now. May I trouble you for 2 more questions again since I am a total rookie...

    1) how exactly can I implement this report when I have final project in SSRS ? I didn't get to these steps yet :) Do I need to export it, how do I get it into CRM basically?

    2) how will the user be able to load the report, will it be from Reports section? And where will they be able to select the account from the dropdown I created under parameter? Can they also run a report directly if the are under Account entity for that account?

    Thanks so much!!

    Dan

    Sunday, April 6, 2014 2:33 AM
  • Hi Donyc,

    To load custom SSRS reports to CRM2013

    1. On your Web Client, In the top navigation - SALES section, locate and Click on REPORTS

    2. Click New in the Reports page

    3. In the Report: New Report dialog window. Select "Existing File" as the report type. Then click Browse and locate your custom report RDL file. Also, in the Administration tab, make sure you select Organisation in the Viewable by setting. Then Click Save and Close.

    4 Your custom Report is now loaded and can be access and executed by users in the REPORTS section. The parameter(s) will appear when user's runs the report.

    If this answered your questions, please mark this as an Answer. Thanks and hope this helps.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Sunday, April 6, 2014 9:33 AM
  • Thanks Eric! I got it to work. Do you know why my report is not showing up if I click on Accounts entity and then click "Run Reports"

    The report is available though under reports sections

    • Edited by Donyc Monday, April 7, 2014 1:03 AM
    Sunday, April 6, 2014 8:22 PM
  • yes that's correct

    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Donyc Monday, April 7, 2014 1:35 AM
    Sunday, April 6, 2014 9:44 PM
  • Thanks Eric, ended up editing my questions. in any case, this all did the trick.. Do you know though my report is not showing up if I click on Accounts entity and then click "Run Reports" form the ribbon, its not on the dropdown

    The report is available though under reports sections

    Monday, April 7, 2014 1:36 AM
  • Hi Donyc,

    In your report configuration screen, set the Related Record Types and Display In as per this screen shot.


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    • Marked as answer by Donyc Monday, April 7, 2014 2:07 PM
    Monday, April 7, 2014 4:02 AM
  • Thanks!
    Monday, April 7, 2014 2:08 PM