Answered by:
Importing Reporting Services Reports

Question
-
Hi, I'm new at reporting in CRM 4.0 but i know my way around Reporting Services and creating Visual Studio reports...
I was wondering if someone can help me regarding creating reports in Reporting Services / BI Studio (visual studio 2008) and importing them into CRM 4.0.
I have created a simple report to test out the process before I get into some serious report development...
Here are the steps I took...
1. Created a new project called "CRM Reports" in visual studio BI development studio
2. Added a new report using the report wizard
3. Created a NEW data source called MyCRMDataSource
4. Used SQL Server Authentication and used a "ReportUser" login i created on the SQL Server (i think this is where i made my mistake?)
5. Inputted my query
6. Tested the report in the "Preview" and in runs find
7. Opend CRM 4.0
8. Created a new report "from a file"
9. Located the .rdl file in my "CRM Reports" project folder
10. Put in the description, categories, and related record types
11. Save and close
When I ran the report, I got a data set error (which i'm sure we've all seen before)
- An error has occurred during report processing.
- Query execution failed for dataset 'DataSet1'.
- For more information about this error navigate to the report server on the local server machine, or enable remote errors
- Query execution failed for dataset 'DataSet1'.
Wednesday, July 22, 2009 10:30 PM - An error has occurred during report processing.
Answers
-
You need to ensure your datasource in the report is not a shared datasource type. It should be a direct connection to SQL. Once you modify the datasource and uploaded the rdl to CRM again, the report should run as expected.
Best Regards, Donna- Marked as answer by trallantrallan Thursday, July 23, 2009 7:22 PM
Wednesday, July 22, 2009 11:00 PM
All replies
-
You need to ensure your datasource in the report is not a shared datasource type. It should be a direct connection to SQL. Once you modify the datasource and uploaded the rdl to CRM again, the report should run as expected.
Best Regards, Donna- Marked as answer by trallantrallan Thursday, July 23, 2009 7:22 PM
Wednesday, July 22, 2009 11:00 PM -
It is not a shared dataset. It is an embedded dataset that uses login credentials for a SQL Server login that I created. In my "shared data sources" folder, there is nothing.Wednesday, July 22, 2009 11:24 PM
-
Send me your query..Or first try with very simple query like (Select accoutname,accountid from filteredaccoun where accountname = "somevalue")
Let me know your outcome.
Cheers,
HugoThursday, July 23, 2009 2:17 AM -
I have not had to identify login credentials for the datasource when I create a report. I think you have some option in the datasource of your report selected that you shouldn't need. I will send the exact options tomorrow after I look up one of my reports. Whenever I have seen this error, it has always been related to the datasource options in the report.
Best Regards, DonnaThursday, July 23, 2009 2:30 AM -
Here is the information regarding the datasource.
Open your report in Visual Studio
Select the data tab
Select the ... icon to the right of the dataset
Select the ... icon to the right of the datasource
Ensure Type = Microsoft SQL Server
Select the Edit button
Select your CRM SQl server from the Server Name list
Select Use Windows Authentication
Select your CRM database from the Select or enter a database name
Test Connection to ensure it works as expected
Select Ok
Your Connection String should look something like this
Data Source=CRMSQLServerName;Initial Catalog=CRMDatabaseName
Select Ok
Test your report and if it works as expected in VS, try the upload to the CRM server again.
Best Regards, DonnaThursday, July 23, 2009 12:31 PM -
Thanks for you replies everyone.
Donna, I followed your instructions and still no luck. The report works fine so I don't think it's a connection string issue. I deployed the report to report manager and ran it from a different workstation under a different user and the report runs fine.
The only time it doesn't work is when I upload the .rdl file to CRM. In the crm report viewer, it says the query execution failed for DataSet1.
This is getting rather frustrating!Thursday, July 23, 2009 3:57 PM -
Please delete the report from CRM and ensure it is deleted from the report server. Upload the report again through CRM with the changes I suggested above. If that does not work, copy your query here and I'll take a look at it. Also copy the error from the event viewer or turn dev errors on and run the report to see if you obtain a more descriptive error message.
Best Regards, DonnaThursday, July 23, 2009 4:00 PM -
here is the rdl file xml. I changed some of the text so I don't show my company's information.
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
<DataSources>
<DataSource Name="MSCRM_DataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=MYCRMSERVER;Initial Catalog=MYCOMPANY_MSCRM</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:DataSourceID>b357e9f2-96ef-4e93-8433-092e113f1586</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>MSCRM_DataSource</DataSourceName>
<CommandText>SELECT Name
FROM Account
</CommandText>
</Query>
</DataSet>
</DataSets>
<Body>
<ReportItems>
<Tablix Name="table1">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.22in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="textbox2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Name</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>11pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>SteelBlue</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.21in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Name">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Name.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Name</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
<RepeatOnNewPage>true</RepeatOnNewPage>
<KeepTogether>true</KeepTogether>
</TablixMember>
<TablixMember>
<Group Name="table1_Details_Group">
<DataElementName>Detail</DataElementName>
</Group>
<TablixMembers>
<TablixMember />
</TablixMembers>
<DataElementName>Detail_Collection</DataElementName>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Top>0.36in</Top>
<Height>0.43in</Height>
<Width>1in</Width>
<Style />
</Tablix>
<Textbox Name="textbox1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Test Report 1</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>20pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>SteelBlue</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.36in</Height>
<Width>5in</Width>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</ReportItems>
<Height>0.79in</Height>
<Style />
</Body>
<Width>5in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportID>44b30304-4bcb-41b2-ad44-142d2d110163</rd:ReportID>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>Thursday, July 23, 2009 5:01 PM -
What about the deployment of the report?
The report is to be deployed to the report server, correct?
When I upload the file to CRM 4.0 (as new report from file), do I choose the rdl file from my VS project, or should I export the rdl file from report manager?Thursday, July 23, 2009 6:02 PM -
When you upload the file to CRM, that action deploys it to the Report Server. The report should not exist on the Report Server before uploading it through CRM. You should upload the report from Visual Studio.
Delete the report from CRM and ensure it is deleted from the Report Server. Then upload the Report through CRM and not the Report Server.
Best Regards, Donna- Proposed as answer by Donna EdwardsMVP Thursday, July 23, 2009 7:10 PM
Thursday, July 23, 2009 6:28 PM -
I deleted the report from CRM. I ensured it did not exist anywhere on report server.
I went to CRM and uploaded the rdl file.
I ran the report, and same thing Failed for DataSet1
So, I went to the report server and checked to see if the report was deployed VIA CRM. It was (located in the CRM folder under 4.0 - needed to "show details").
So, This is good. Next thing I did is I checked out the data source, it was shared. LAME!
I changed the data source to a custom data source using integrated security and BAM, worked.
What a way to learn the ins and outs of CRM Reporting. But, now that's out of the way.
Thank you so much Donna!Thursday, July 23, 2009 6:49 PM -
You're welcome, glad it is working.
Best Regards, DonnaThursday, July 23, 2009 7:10 PM