locked
Passing crm id to SSRS report RRS feed

  • Question

  • I have a simple ssrs report based on an sql query from crm data,

    Now I want this report filtered on the record they are viewing (btw this data is from a custom extension named visit reports)

    I tried the following : (Using CRM 2013 on premise and VS 2013)

    my query for the report is

    SELECT 	bro_visitreportBase.bro_reportby, 
    	bro_visitreportBase.bro_summary, 
    	bro_visitreportBase.bro_report, 
    	bro_visitreportBase.bro_forengicon, 
            bro_visitreportBase.bro_date, 
    	bro_visitreportBase.bro_copyto, 
    	bro_visitreportBase.bro_city, 
    	bro_visitreportBase.bro_subject, 
    	bro_visitreportBase.statecode, 
            bro_visitreportBase.statuscode, 
    	bro_visitreportBase.OwnerIdType, 
    	bro_countryBase.bro_name, 
    	ActivityPointerBase.Subject, 
    	OwnerBase.Name, 
            bro_visitreportBase.bro_visitreportId
    FROM    bro_visitreportBase 
    LEFT OUTER JOIN bro_countryBase ON bro_visitreportBase.bro_countryid = bro_countryBase.bro_countryId 
    LEFT OUTER JOIN OwnerBase ON bro_visitreportBase.OwnerId = OwnerBase.OwnerId 
    LEFT OUTER JOIN ActivityPointerBase 
    LEFT OUTER JOIN AppointmentBase ON ActivityPointerBase.ActivityId = AppointmentBase.ActivityId ON 
                    bro_visitreportBase.bro_visitreportId = AppointmentBase.bro_visitreportid
    WHERE     (bro_visitreportBase.bro_visitreportId = @VisitFilter

    I created a filter dataset to get the id using CRMAF_

    SELECT     bro_visitreportId
    FROM         bro_visitreportBase AS CRMAF_FilteredVisitReport

    I put this value in the parameter @VisitFilter

    What am I missing ?

    Thanks in advance

    RBie


    • Edited by Rbie Thursday, January 26, 2017 9:56 AM
    Wednesday, January 25, 2017 10:44 AM

Answers

  • Have you tried this query:

    SELECT     CRMAF_Filteredbro_visitreport.bro_visitreportId
    FROM         Filteredbro_visitreport AS CRMAF_Filteredbro_visitreport


    Regards, Saad

    • Marked as answer by Rbie Friday, January 27, 2017 10:30 AM
    Friday, January 27, 2017 10:19 AM

All replies

  • You need to pass the id of the current record to the report. You can do this by appending it to the url used to display the report - for example:

    http://serverandorgname/crmreports/viewer/viewer.aspx?action=filter&helpID=report.rdl&id=%7bBFC37ED6-094F-E011-B740-00155D5F7500%7d&p:VisitFilter=B129CBDC-7E4D-E011-B840-00155D5F7500


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, January 26, 2017 9:24 AM
    Moderator
  • Hi David,

    Yeah, that is not really the solution I was looking for.

    I want the report in CRM to show up under Run reports/run on selected records instead of just under run on all records.

    Thursday, January 26, 2017 9:56 AM
  • If you want to have the report show up under Run reports/run on selected records then you don't need to create a parameter in the report - you just need one dataset using CRMAF_Filteredbro_VisitReport as the table alias

    FROM    bro_visitreportBase as CRMAF_Filteredbro_VisitReport

    Then make sure when you add the report in CRM you have the option selected to run the report on the Form


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, January 26, 2017 11:58 AM
    Moderator
  • SELECT CRMAF_Filteredbro_visitreport.bro_reportby, CRMAF_Filteredbro_visitreport.bro_summary,
    CRMAF_Filteredbro_visitreport.bro_report,
    CRMAF_Filteredbro_visitreport.bro_forengicon,
    CRMAF_Filteredbro_visitreport.bro_date,
    CRMAF_Filteredbro_visitreport.bro_copyto,
    CRMAF_Filteredbro_visitreport.bro_city,
    CRMAF_Filteredbro_visitreport.bro_subject,
    CRMAF_Filteredbro_visitreport.statecode,
    CRMAF_Filteredbro_visitreport.statuscode,
    CRMAF_Filteredbro_visitreport.OwnerIdType,
    CRMAF_Filteredbro_visitreport.bro_countryidname,
    ActivityPointerBase
    .Subject,
    CRMAF_Filteredbro_visitreport.OwnerIdName, 
    CRMAF_Filteredbro_visitreport.bro_visitreportId
    FROM  Filteredbro_visitreport AS CRMAF_Filteredbro_visitreport

    LEFT OUTER JOIN AppointmentBase ON CRMAF_Filteredbro_visitreport.bro_visitreportId = AppointmentBase.bro_visitreportid
    LEFT OUTER JOIN ActivityPointerBase ON ActivityPointerBase.ActivityId = AppointmentBase.ActivityId 

    Default CRM Report Use Filtered View.  if you use table you need add privilege in database table.


    MSN:shan_zhengyi_jrsc@hotmail.com


    • Edited by 正义 Thursday, January 26, 2017 2:15 PM
    Thursday, January 26, 2017 2:11 PM
  • Hi David,

    Yeah that is what I thought unfortunatly it doesn't do that. Screenshots below to explain what I did

    Below is the only dataset I currently use for testing

    Added to CRM like

    Result is

    Thursday, January 26, 2017 3:20 PM
  • Your dataset with CRMAF_Filtered should be the first dataset in the report. I think you are using two datasets. Make the dataset with CRMAF_Filtered as first dataset

    Regards, Saad

    • Proposed as answer by Mohd Saad Friday, January 27, 2017 7:51 AM
    • Unproposed as answer by Rbie Friday, January 27, 2017 8:10 AM
    Friday, January 27, 2017 7:50 AM
  • As stated in  the example with the screenshots there is only one dataset in that report.

    • Edited by Rbie Friday, January 27, 2017 8:17 AM
    Friday, January 27, 2017 8:12 AM
  • It's not advisable to use Base Table.Use your query like this:

    SELECT     bro_visitreportId
    FROM         filteredbro_visitreport AS CRMAF_Filteredbro_visitreport


    Regards, Saad


    • Edited by Mohd Saad Friday, January 27, 2017 8:21 AM
    Friday, January 27, 2017 8:20 AM
  • Already tried that, same result unfortunatly
    Friday, January 27, 2017 8:35 AM
  • send me your rdl file

    Regards, Saad

    Friday, January 27, 2017 8:37 AM
  • <?xml version="1.0" encoding="utf-8"?>
    <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>4.93417cm</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.6cm</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox1">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>bro visitreport Id</Value>
                                  <Style />
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox1</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>
                <TablixRow>
                  <Height>0.6cm</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="bro_visitreportId">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=Fields!bro_visitreportId.Value</Value>
                                  <Style />
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>bro_visitreportId</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>
                </TablixMember>
                <TablixMember>
                  <Group Name="Details" />
                </TablixMember>
              </TablixMembers>
            </TablixRowHierarchy>
            <DataSetName>CRM</DataSetName>
            <Height>1.2cm</Height>
            <Width>4.93417cm</Width>
            <Style>
              <Border>
                <Style>None</Style>
              </Border>
            </Style>
          </Tablix>
        </ReportItems>
        <Height>0.47244in</Height>
        <Style />
      </Body>
      <Width>1.94259in</Width>
      <Page>
        <PageHeight>29.7cm</PageHeight>
        <PageWidth>21cm</PageWidth>
        <LeftMargin>2cm</LeftMargin>
        <RightMargin>2cm</RightMargin>
        <TopMargin>2cm</TopMargin>
        <BottomMargin>2cm</BottomMargin>
        <ColumnSpacing>0.13cm</ColumnSpacing>
        <Style />
      </Page>
      <AutoRefresh>0</AutoRefresh>
      <DataSources>
        <DataSource Name="CRM">
          <ConnectionProperties>
            <DataProvider>SQL</DataProvider>
            <ConnectString>Data Source=HARCRMVS04;Initial Catalog=Company_MSCRM</ConnectString>
            <IntegratedSecurity>true</IntegratedSecurity>
          </ConnectionProperties>
          <rd:SecurityType>Integrated</rd:SecurityType>
          <rd:DataSourceID>28816cb4-5b68-4b5e-8fe7-ace8efc111ed</rd:DataSourceID>
        </DataSource>
      </DataSources>
      <DataSets>
        <DataSet Name="CRM">
          <Query>
            <DataSourceName>CRM</DataSourceName>
            <CommandText>SELECT     bro_visitreportid, createdon, createdby, modifiedon, modifiedby, createdonbehalfby, modifiedonbehalfby, ownerid, owneridtype, owningbusinessunit, statecode, 
                          statuscode, versionnumber, importsequencenumber, overriddencreatedon, timezoneruleversionnumber, utcconversiontimezonecode, bro_subject, processid, stageid, 
                          bro_city, bro_copyto, bro_date, bro_forengicon, bro_report, bro_reportby, bro_summary, bro_originatinappointment, bro_countryid
    FROM         Filteredbro_visitreport AS CRM_Filteredbro_visitreport</CommandText>
          </Query>
          <Fields>
            <Field Name="bro_visitreportId">
              <DataField>bro_visitreportid</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="CreatedOn">
              <DataField>createdon</DataField>
              <rd:TypeName>System.DateTime</rd:TypeName>
            </Field>
            <Field Name="CreatedBy">
              <DataField>createdby</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="ModifiedOn">
              <DataField>modifiedon</DataField>
              <rd:TypeName>System.DateTime</rd:TypeName>
            </Field>
            <Field Name="ModifiedBy">
              <DataField>modifiedby</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="CreatedOnBehalfBy">
              <DataField>createdonbehalfby</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="ModifiedOnBehalfBy">
              <DataField>modifiedonbehalfby</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="OwnerId">
              <DataField>ownerid</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="OwnerIdType">
              <DataField>owneridtype</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="OwningBusinessUnit">
              <DataField>owningbusinessunit</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="statecode">
              <DataField>statecode</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="statuscode">
              <DataField>statuscode</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="VersionNumber">
              <DataField>versionnumber</DataField>
              <rd:TypeName>System.Byte[]</rd:TypeName>
            </Field>
            <Field Name="ImportSequenceNumber">
              <DataField>importsequencenumber</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="OverriddenCreatedOn">
              <DataField>overriddencreatedon</DataField>
              <rd:TypeName>System.DateTime</rd:TypeName>
            </Field>
            <Field Name="TimeZoneRuleVersionNumber">
              <DataField>timezoneruleversionnumber</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="UTCConversionTimeZoneCode">
              <DataField>utcconversiontimezonecode</DataField>
              <rd:TypeName>System.Int32</rd:TypeName>
            </Field>
            <Field Name="bro_subject">
              <DataField>bro_subject</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="processid">
              <DataField>processid</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="stageid">
              <DataField>stageid</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="bro_city">
              <DataField>bro_city</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_copyto">
              <DataField>bro_copyto</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_date">
              <DataField>bro_date</DataField>
              <rd:TypeName>System.DateTime</rd:TypeName>
            </Field>
            <Field Name="bro_forengicon">
              <DataField>bro_forengicon</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_report">
              <DataField>bro_report</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_reportby">
              <DataField>bro_reportby</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_summary">
              <DataField>bro_summary</DataField>
              <rd:TypeName>System.String</rd:TypeName>
            </Field>
            <Field Name="bro_originatinappointment">
              <DataField>bro_originatinappointment</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
            <Field Name="bro_countryid">
              <DataField>bro_countryid</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
            </Field>
          </Fields>
        </DataSet>
      </DataSets>
      <rd:ReportUnitType>Cm</rd:ReportUnitType>
      <rd:ReportID>7a92cf60-ddb3-481f-b5e9-1b9f2cd7f447</rd:ReportID>
    </Report>

    Friday, January 27, 2017 8:50 AM
  • Refer highlighted section. It should be CRMAF instead of CRM.


    Regards, Saad

    Friday, January 27, 2017 9:27 AM
  • Sorry, that was just the last thing I tested, I'm pretty much trying everything atm :-).

    The result with CRMAF_ is the same, eq not working


    • Edited by Rbie Friday, January 27, 2017 9:52 AM
    Friday, January 27, 2017 9:46 AM
  • Have you tried this query:

    SELECT     CRMAF_Filteredbro_visitreport.bro_visitreportId
    FROM         Filteredbro_visitreport AS CRMAF_Filteredbro_visitreport


    Regards, Saad

    • Marked as answer by Rbie Friday, January 27, 2017 10:30 AM
    Friday, January 27, 2017 10:19 AM
  • OMG, that worked !!!! Ofc one of the few things I didn't try :-)

    Thank you very much Mohd Saad.

    Friday, January 27, 2017 10:30 AM