locked
Importing Reporting Services Reports RRS feed

  • 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
    I know i'm missing a step or just doing a step wrong, any help would be great :)


    Wednesday, July 22, 2009 10:30 PM

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
    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
    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,

    Hugo
    Thursday, 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, Donna
    Thursday, 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, Donna
    Thursday, 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, Donna
    Thursday, 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
    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, Donna
    Thursday, July 23, 2009 7:10 PM