Answered by:
CRM 2011 and SSRS Reporting - (High Priority) Running a report on a single record

Question
-
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
- Edited by Thomas Ward Monday, August 1, 2011 6:58 PM
Monday, August 1, 2011 3:02 PM
Answers
-
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
All replies
-
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 PMModerator -
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 -
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?
ThomasMonday, August 1, 2011 7:03 PM -
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.
ThomasMonday, August 1, 2011 7:03 PM -
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 -
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_FilteredInvoice2- 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 -
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 -
Yes, you can omit the second part.Tuesday, August 2, 2011 2:07 PM
-
NOTE: I tried this and it did not work. please see the above response to you in regards to the direct SQL query.
ThomasTuesday, August 2, 2011 2:14 PM -
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?
ThomasTuesday, August 2, 2011 2:26 PM -
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://msdn.microsoft.com/en-us/library/gg328097.aspx
Sorry I don't have any direct examples.
Tuesday, August 2, 2011 3:05 PM -
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.
ThomasTuesday, August 2, 2011 3:31 PM -
Hi Thomas .
How are you authoring the report ? is it in BIDS ?
cheers, S.Khan MCTSTuesday, August 2, 2011 10:43 PM -
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.
ThomasWednesday, August 3, 2011 12:35 PM -
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 -
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 -
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 PMModerator -
Friday, March 30, 2012 7:02 AM
-
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