Advertisements

Archive

Archive for the ‘SSRS’ Category

Host a SSRS report which gets data from external Data Source – CRM on-premise

January 7, 2016 3 comments

We got a requirement to host a SSRS report in CRM, which gets data from external data source (i.e., Data Base from another SQL server).

CRM normally uses Shared Data Source when you upload any SSRS report which pulls data from CRM.

In our case we had to

  • Create a new Data Source
  • Override the CRM Shared Data Source with a new Data Source

Below are the steps.

Develop and Deploy Report in CRM

  • Design the report with Data Source connected to external Database.
  • Preview and make sure you are getting data properly.
  • Save the .rdl
  • Open CRM application
  • Create a new report and upload the .rdl
Upload SSRS Report To CRM

Upload SSRS Report To CRM

Configure the Report’s DB Connection

Now we got report in CRM and we need to point the report to Custom DataBase by following steps below

  • Connect to the SSRS server
  • Open the browser and type (http://servername/Reports) (You can also get URL from “Reporting Services Configuration Manager à Report Manager URL” tab)
Reporting Service Config Manager

Reporting Service Config Manager

  • In the Home page, click on ‘New Data Source’
Create New Data Source - 1

Create New Data Source – 1

  • Provide the Connection String and Credentials and Save
Create New Data Source - 2

Create New Data Source – 2

  • Go back to the report server home page.
  • Open the folder by name (YourCRMORG_MSCRM)
  • Go to ‘CustomeReports’ folder and select your report (Refer ‘Description’ column for Report Name)
  • Choose Manage from context menu
Create New Data Source - 3

Create New Data Source – 3

  • Choose ‘Data Sources’ tab and select newly created ‘Data Source’
Select the Data Source

Select the Data Source

  • Click on ‘Apply’
  • Close and re-run the report to get the changes.

🙂

Advertisements

Executing Informix DB Stored Procedure in SSRS

November 14, 2015 Leave a comment

Recently we got a requirement to pull the data from IBM Informix DB and prepare report using SSRS.

For who does not know what is Informix, refer here

Below are Stored Procedure details

Name: sp_getcustomersbycity

Description: Accepts date range and City codes as parameters and returns Customer result set.

Query Execution Syntax in SSRS:

{

call sp_getcustomersbycity(‘2015-09-30 20:00:00′,’2015-10-31 19:59:59′,’0′,’HYD,BLR’,null)

}

🙂

Categories: SSRS Tags: , ,

ReportProcessingException: Cannot create a connection to data source ‘CRM’

March 16, 2013 1 comment

Recently, when we deployed our CRM managed solution with reports on our testing environment, we were getting “rsProcessingAborted” error while accessing the  reports.

When we check the event viewer for more error details, we found below “Caller has insufficient privilege to run report” exception

Data source ‘CRM’: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source ‘CRM’. —> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Caller has insufficient privilege to run report as user

Reason

  • In our scenario, SQL Server report service was running under a separate “Service Account”
SSRS Report Service Account

SSRS Report Service Account

  • The service account under which report service was running does not have required privileges to establish connection and access filtered views

Below fix worked for us

Fix

  • Since the Report Service’s “service account” was unable to access CRM filtered views,
    • We added the “service account” as a member of the AD’s “PrivReportingGroup {Org_guid}” and “PrivUserGroup {Org_guid}” groups
    • Provided “service account” SQL Server login with permissions to ReportServer and ReportServerTempDB (db_owner, RSExecRole)

SSRS Extension Installation does not display SSRS Instances – CRM 2011

December 27, 2012 2 comments

The other day to configure CRM environment, we were installing “Microsoft Dynamics CRM 2011 Reporting Extension” and to our surprise

“SSRS Instance” dropdown was blank and we could not proceed further

Reporting Extensions Setup

Reporting Extensions Setup

Reason

  • We had 2 machines one with CRM application server and another with both “SQL server” & “SSRS Reporting services”
  • The reason in our case was, we were trying to install “Microsoft Dynamics CRM 2011 Reporting Extension” on the CRM application server machine

Fix

  • You must run CRM Reporting Extensions Setup on a computer that has Microsoft SQL Server 2008 Reporting Services or Microsoft SQL Server 2008 R2 Reporting Services installed

Here is the MSDN’s  CRM 2011 Reporting Extensions Installation Guide

🙂

Getting logged in user’s GUID in CRM reports

We can get the logged in user GUID in the custom reports deployed in CRM using “fn_FindUserGuid()”  function

Below is the sample query for the same

DECLARE @currUserId UNIQUEIDENTIFIER

SELECT @currUserId=dbo.fn_FindUserGuid()

PRINT @currUserId

🙂

Fix – Unable to add custom Report to the Solution in CRM 2011

June 30, 2012 1 comment

I have a custom SSRS report “ABC Report” in my CRM application.

ABC Report

ABC Report

And, I wanted to include this report in my solution “My Reports” and export to another CRM organization

But to my surprise, I did not find “ABC Report” when I choose “Solution -> Reports  ->  Add existing”

Add existing reports - solution

Add existing reports – solution

Fix :-

  • Go to “Work Place -> Reports”
  • Choose “ABC Report” and click “Edit”
  • In the report window go to “Administration” tab
  • Set Report “Viewable By” option to “Organization” and Save
Set “Viewable By” option to “Organization”
Set “Viewable By” option to “Organization”
  • Open the solution and click “Reports à Add existing reports”
"ABC Report" in the list

“ABC Report” in the list

🙂