Importing Reporting Services Reports

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  You're welcome, glad it is working. 

  ---

  Best Regards, Donna

  Thursday, July 23, 2009 7:10 PM