Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 2011 and SSRS Reporting - (High Priority) Running a report on a single record RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    How can I run an SSRS report on a single record without generating all the records in the report?

    I already tried clicking individiual records but couldn't run the custom SSRS report from the Run report dropdown because the report wasnt listed there.

    Note that this is high priority... an entire project is dependent on this report working, and i've been trying to solve this for days.

    FYI: Its an On-Prem install... forgot to mention that earlier... my bad.

    Thomas

    Monday, August 1, 2011 3:02 PM

Answers

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    For on-premise you need to put some sort of unique identifier in your SQL where clause to filter against. 

    For online you need to do the same sort of thing.  I would get the data you need in a fetchxml query for online that you want by doing the filtering and then export the fetchxml to use as your query.

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Monday, August 1, 2011 3:46 PM
    Moderator
  • Question
    Sign in to vote
    2
    Sign in to vote

    For a report to show up in that drop-down, it must be set to "Display In" "Forms for related record types". This property is set on the report properties screen: the same screen used to upload a report. The property is listed at the bottom of the window under "Categorization".

    • Proposed as answer by Paul B Bauer Monday, August 1, 2011 5:23 PM
    • Unproposed as answer by Thomas Ward Monday, August 1, 2011 7:03 PM
    Monday, August 1, 2011 5:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Is there a way to pass that clause using the primary field in the system?  I.E. i have a text field that is the primary field in the record.  Can I, using the parameters, specify to SSRS to generate the report where givenParameterName == <the CRM record's primary key>?  If so, what parameter(s) should I use?
    Thomas
    Monday, August 1, 2011 7:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for helping me get it into the dropdown box, Paul, but your answer does not fully answer the question.  As such, I have unmarked it as an answer.
    Thomas
    Monday, August 1, 2011 7:03 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    I believe that's called "prefiltering": your report is run on a subset of records (one in your case). Here is some information on it from MSDN: http://msdn.microsoft.com/en-us/library/gg328288

    If you're using a FetchXML approach to building the SSRS report, your DataSet query should look something like this (snippet from an Opportunity report I did recently):

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunity" enableprefiltering="true" prefilterparametername="OpportunityFilter">
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="opportunityid" />
      </entity>
    </fetch>

    Note the bolded segments: this enables prefiltering on your report. This is the same as doing a specific query, i.e., it's doing the "where opportunityid == CRM's primary key" statement for you. Only the relevant records (the single record you're running your report on) will be returned.

    If you have other queries you need to run (in my case I needed to query OpportunityProducts based on a specific opportunityid), I believe the prefilterparamatername allows you to reference that primary key elsewhere on your page. I didn't use it, instead I have several sub-reports that are generated using the opportunityid parameter from the dataset shown above. So the parameter for my Sub-Report is:

     =First(Fields!opportunityid.Value, "DataSetName")

    Hope this makes sense!

    • Proposed as answer by Jim Glass Jr Tuesday, August 2, 2011 2:39 PM
    • Unproposed as answer by Thomas Ward Tuesday, August 2, 2011 4:12 PM
    Monday, August 1, 2011 7:33 PM
  • Question
    Sign in to vote
    3
    Sign in to vote

    Hi Thomas,

    All you have to do is

    1- write your query with an alias like that CRMAF_FilteredInvoice (for example you are running your query against FilteredInvoice, which is a filtered view for Invoice entity). This will pass the context from CRM to the query so that the query will only be getting the row/rows selected from view

    SELECT     invoiceid,col1,col2,col3 ...
    FROM         FilteredInvoice AS CRMAF_FilteredInvoice

    2- secondly, when you upload this report in CRM

    set the properties

    Display In : Forms for related record types ( so that the report shows up in the invoice form)

                     Lists for related record types (so that the report shows up on the views so that you can select one or more rows to run the report against)

    Related Record Types : Invoices

    3- Now you should be able to run the report against a single invoice record selected from the view (if you select multiple it will still work with lets say 5 or 10 whatever because it is getting the context from the CRM).

    4- you can run the report from the invoice form, when you have opened up any record.

    if you need further explanation, please feel free to write back

    hope it helps,

     

    cheers, S.Khan MCTS

     



    • Proposed as answer by Jim Glass Jr Tuesday, August 2, 2011 2:39 PM
    • Unproposed as answer by Thomas Ward Tuesday, August 2, 2011 3:28 PM
    • Proposed as answer by Sanaullah Khan Tuesday, August 9, 2011 1:16 AM
    Monday, August 1, 2011 11:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Display In : Forms for related record types ( so that the report shows up in the invoice form)

                     Lists for related record types (so that the report shows up on the views so that you can select one or more rows to run the report against)


    Can I omit the second part?  This report is only designed to run on a single record, and not more than one record.  So can I have it only show up on the Forms for related record types rather than on the lists for related record types?

    I shall test this all shortly, as well, to see if this works.

    Thomas

    Tuesday, August 2, 2011 2:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Yes, you can omit the second part.
    Tuesday, August 2, 2011 2:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    NOTE: I tried this and it did not work.  please see the above response to you in regards to the direct SQL query.
    Thomas
    Tuesday, August 2, 2011 2:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I believe that's called "prefiltering": your report is run on a subset of records (one in your case). Here is some information on it from MSDN: http://msdn.microsoft.com/en-us/library/gg328288

    If you're using a FetchXML approach to building the SSRS report, your DataSet query should look something like this (snippet from an Opportunity report I did recently):

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">  <entity name="opportunity" enableprefiltering="true" prefilterparametername="OpportunityFilter">
        <attribute name="name" />
        <attribute name="customerid" />
        <attribute name="opportunityid" />
      </entity>
    </fetch>

    Note the bolded segments: this enables prefiltering on your report. This is the same as doing a specific query, i.e., it's doing the "where opportunityid == CRM's primary key" statement for you. Only the relevant records (the single record you're running your report on) will be returned.

    If you have other queries you need to run (in my case I needed to query OpportunityProducts based on a specific opportunityid), I believe the prefilterparamatername allows you to reference that primary key elsewhere on your page. I didn't use it, instead I have several sub-reports that are generated using the opportunityid parameter from the dataset shown above. So the parameter for my Sub-Report is:

     =First(Fields!opportunityid.Value, "DataSetName")

    Hope this makes sense!


    This makes perfect sense... but we arent using a FetchXML format.  We're using a direct SQL query to the Microsoft SQL Server.  Is there any method to do this?
    Thomas
    Tuesday, August 2, 2011 2:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm not familiar with the direct SQL approach, I think that Sanaulah's response and the link I posted should get you there: http://msdn.microsoft.com/en-us/library/gg328288

    Here are a few more relevant links:

    http://blogs.msdn.com/b/crm/archive/2009/11/13/using-data-pre-filtering-in-microsoft-dynamics-crm-reports.aspx

    http://msdn.microsoft.com/en-us/library/gg328097.aspx

    Sorry I don't have any direct examples.

    Tuesday, August 2, 2011 3:05 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Using these did not work... SSRS would not allow the dataset to be created in the Report Builder.  As such, when its uploaded to the CRM system, SSRS aborts processing, and as I am not the Server Admin, I cannot check the logs to find out why it aborted processing.
    Thomas
    Tuesday, August 2, 2011 3:31 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi Thomas .

    How are you authoring the report ? is it in BIDS ?

    cheers, S.Khan MCTS
    Tuesday, August 2, 2011 10:43 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Thomas .

    How are you authoring the report ? is it in BIDS ?

    cheers, S.Khan MCTS

    No, the report is being authored in SQL Server Report Builder 3.0 for SQL Server 2008.
    Thomas
    Wednesday, August 3, 2011 12:35 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    Hi Thomas,

    So have you included the alias of CRMAF_FilteredEntityName in your SQL query ? If not then go to the view code of your rdl file (report) from the report builder ? if you cannot view the code of your rdl file then you can download your report (rdl) file and then open it up in a text editor or visual studio to view code and add alias to your SQL query and then upload that back again ...

    You will see once you have added this alias to your sql query, the query will get the context from CRM and will filter your dataset in accordance with that.

    feel free to write back,

    cheers, S.Khan MCTS
    • Proposed as answer by Sanaullah Khan Tuesday, August 9, 2011 1:16 AM
    Wednesday, August 3, 2011 10:04 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Thomas,

     Have you tried "Explicit Prefiltering" in your SQL Query? Explicit Prefiltering will work for your requirement. If you select the report at record level it shows only single record related data. If you select at grid level, it shows data related to the selected records.

    http://msdn.microsoft.com/en-us/library/gg328288

    follow this link and try the explicit prefiltering. If you are having any issues let me know.. 

    Guru Prasad

    Thursday, August 4, 2011 6:18 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Did you get a resolution to this issue?

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Wednesday, February 15, 2012 7:16 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try 

    http://dynamicslollipops.blogspot.com/2011/06/creating-new-report-running-on-spesific.html

    Yair Rozenberg

    Friday, March 30, 2012 7:02 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Did you get a resolution to this issue?

    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Forgot I had posted this... this project was moved past me and they went with a different reporting system, which system they went with I do not know.

    Thomas


    • Edited by Thomas Ward Thursday, June 7, 2012 1:11 PM
    • Marked as answer by Thomas Ward Thursday, June 7, 2012 1:11 PM
    Thursday, June 7, 2012 1:10 PM