passing client GUID from CRM to SSRS report to run and get values

(0) Share

Report

Posted on by PratRav

I have uploaded SSRS report to CRM which I created in SSRS, when I go into CRM and then run it from Report section it runs successfully, only thing is it's looking for GUID in order to show reports with value.

I want to automate this something like whenever I go to each client Profile in CRM and click on run reports it should fetch it's GUID and pass it to that report as parameter and show the report.

Please help.

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (2)

Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report

Hi,

you can use pre-filter for you report.

If you are using FetchXML based report then you need to enable prefiltering using

enableprefiltering = 1

if you are using SQL based report then use CRMAF_ alias to query to get selected / current record id.

here are some links which will be helpful for creating CRM pre filtered report.

technet.microsoft.com/.../dn531099.aspx

makdns.blogspot.com/.../dynamics-crm-passing-crm-records-id-and.html

www.powerobjects.com/.../microsoft-dynamics-crm-pre-filtering-for-crm-reporting

blogs.msdn.microsoft.com/.../microsoft-dynamics-crm-pre-filtering-tips

Was this reply helpful? Yes No
ScottDurow 21 on at

Like (0)

Report

In addition to this - if you have to have a single GUID for your report you can use a pre-filtered fetchxml query that then is used to extract a parameter that you can pass to a sub report.

Was this reply helpful? Yes No
PratRav on at

Like (0)

Report

Hi Mahadeo,

Looks like very informative reply!

But my SSRS reports has around 5-6 different datasets, which has overall 60 fields which I am using it in reports.

My Dynamics CRM and SSRS reports are using same database but they are totally separate from each other.

Right now I am running reports from SSRS and passing hard coded value. I want to change that to no default value and then want to upload that to REPORT section of CRM and then I want to pass GUID from each customer profile, and I have tab on each and every customer profile, when I click on that button , I want to pass that GUID to that report automatically and fetch records from that SSTS reports.

So do you want me to change all datasets , I mean store procedures and wright again with CRMAF_ alias?

Please I am kind of beginner in CRM so asking lot of questions!

Was this reply helpful? Yes No
PratRav on at

Like (0)

Report

Hi Scott do you want me to change something on all stored procedure which I wrote in sql, or something on CRM?

Sorry I have no Idea about fetchXML part, The reason why I developed the report in SSRS is just because the report require lot of joins and few calculation.

Do you want me change datasets with ALIAS name or somthing in CRM. Please consider me as beginner in CRM and explain.

Thanks.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

before the filtered view name or table name add a prefix CRMAF_

This send the GUID of the record on which you are running the report.

Was this reply helpful? Yes No
PratRav on at

Like (0)

Report

If I add CRMAF_ in front of filtered view, then I also have to change my store procedures right?

Do you want me to change in all views that I am using in one report, because I am using 4-5 different views and around 7-8 different store proc. Only thing common among them is AdmissionId, which is GUID for client's record. I have to pass that as report parameter.

Do you want me to change anything on CRM?

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report
You need to use CRMAF_ only to get customer id.

other datasets remains same.

As you mentioned, currently you are using hard coded customer id, and wants that ID to be automatically passed when report is running from that customer record or from grid for selected records.

You can use CRMAF_ like

DECLARE @CustomerId UNIQUEIDENTIFIER -- if you are using to run report only from Contact / customer record, and has only one Id. SELECT @CustomerId = ContactId FROM FilteredContact AS CRMAF_Contact SELECT C.fullname, SO.OrderNumber FROM FilteredContact C JOIN SalesOrder SO ON C.contactid = SO.ContactId WHERE C.contactid = @CustomerId --if you are using to pass multiple contactId(s), i.e. want to run report from grid/ list on selected contacts IF OBJECT_ID('tempdb..#SelectedCustomers') IS NOT NULL DROP TABLE #SelectedCustomers CREATE TABLE #SelectedCustomers (ContactId UNIQUEIDENTIFIER, fullName NVARCHAR(200)) INSERT INTO #SelectedCustomers(ContactId, fullName) SELECT ContactId, fullname FROM FilteredContact AS CRMAF_Contact --WILL PULL ORDERS ONLY FOR SELECTED CUSTOMERS SELECT C.fullname, SO.OrderNumber FROM #SelectedCustomers C JOIN SalesOrder SO ON C.contactid = SO.ContactId WHERE C.contactid = @CustomerId

Was this reply helpful? Yes No
PratRav on at

Like (0)

Report

Hi Scott, Is this for CRM Premise? I have premise that's why.

Was this reply helpful? Yes No
PratRav on at

Like (0)

Report

Hi Mahadeo,

So I did following change, in to my main Dataset which is fetching more that half of the values in report.

My Dataset is using following store proc,

ALTER PROCEDURE [dbo].[RPT_NewAdmissions]

@AdmissionId uniqueIdentifier

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT @AdmissionId=new_admissionId from [Filterednew_admission] AS CRMAF_newadmission

SELECT
a.new_admissionId AS AdmissionId
,a.[new_ClientIdName] As Client
,[new_ClientDateOfBirth] AS DateOfBirth
,a.[new_Age] AS Age

FROM [dbo].[Filterednew_admission] a

JOIN dbo.Contact c on a.new_ClientId = c.ContactId

WHERE
a.new_AdmissionId = @AdmissionId
END

Now there are other dataset using different store procedures to get other values in report and every store procedure is using join on a.new_AdmissionId = @AdmissionId.

I am putting all these fileds from different datasets into table form in SSRS report. so when I run the report I HAVE CURRENTLY ONE PARAMETER WHICH IS AdmissionId which show up as TextBox on top of the report. where I write any ID and it will pull all fields from different dataset for that ID and shows on reports.

I deployed this report in SSRS and uploaded back to CRM with exisitng file option and added to admission area so whenever I go to perticular admission page for perticular person, I do run report and select my report which I uploaded to CRM , it runs no problem, but still shows me that text box and value whih I need to put as Admission ID and click view report.

Can you please guid me what I am doing wrong here? let me know if you need more info on what I am trying to do here.

Was this reply helpful? Yes No
Verified answer

Mahadeo Matre 17,021 on at

Like (0)

Report

try this

mahadeomatre.blogspot.com/.../run-report-from-custom-ribbon-button.html

Was this reply helpful? Yes No