CRM_URL.Value in the SQL Reports

[Jack Bender]

I am creating a new custom SQL Report and in the Jump to URL setting you can
create a formula so that when the user clicks on the text of that record it
takes them right to the CRM record. An example of this is....

=IIF(IsNothing(Parameters!CRM_URL.Value) or Fields!ActiveOpp.Value =
"other",system.dbnull.value,Parameters!CRM_URL.Value &
"?ID={"&Fields!OpportunityID.Value.ToString()&"}&OTC=3")

This will take you to the opportunity record listed in the report. We have
done this before and always hard coded the server url (i.e. http://crm:5555)
in the formula. However, I noticed that the 3.0 canned reports use
"Parameters!CRM_URL.Value" to pass the server URL which is of great benefit,
especially if you have a test and production environment.

When I further investigated the canned reports, I found this CRM_URL
parameter, but I cannot tell how it picks up the value for this parameter.
Does anyone know how that gets passed to the parameter in the SQL report?

Jack Bender

[Jack Bender]

OK,

It took a little investigating and a ton of trial and error, but I figured
it out...

Any custom report that you create in Visual Studio 2005 must use the "CRM"
data source.

You must create it on the Data Tab...New Dataset and under Data Source,
choose New Data Source. Make sure you have the following settings...

Name: CRM
Type: Microsoft SQL Server
Connection String: Data Source=localhost;Initial
Catalog=Adventure_Works_Cycle_MSCRM
"localhost" is the SQL Server
"Adventure_Works_Cycle_MSCRM" is the MSCRM database
Credentials: Use Windows Authentication (Integrated Security)

Once this is done, the parameter value of CRM_URL is autofilled and your
custom reports can be successully imported from within the Reports Area of
Microsoft CRM.

Jack Bender

"Jack Bender" <jbe...@harvestsolutions.net> wrote in message
news:O62Nk7XW...@TK2MSFTNGP02.phx.gbl...

[Tao Yue [MSFT]]

"Jack Bender" <jbe...@harvestsolutions.net> wrote:
> It took a little investigating and a ton of trial and error, but I
figured
> it out...
> Any custom report that you create in Visual Studio 2005 must use the
"CRM"
> data source.

> [snip]

> Once this is done, the parameter value of CRM_URL is autofilled and your
> custom reports can be successully imported from within the Reports Area
of
> Microsoft CRM.

You don't have to call your data source "CRM" -- CRM_URL will get
autofilled as long as it is present. You do need to point at the CRM
database directly rather than using a Shared Data Source, but that is for
other reasons.

Note that CRM_URL is a documented feature, and more information can be
found in the "Dynamic Drill-Through to Microsoft CRM" topic in the CRM SDK
documentation.

--
Tao Yue
Microsoft CRM Development
This posting is provided "AS IS" with no warranties, and confers no rights.