locked
Custome Dynamic CRM Reports RRS feed

  • Question

  • I am new to CRM Reports, but have many years experience developing SSRS Reports.

    In CRM, if I'm in a quote record and I created a custom report. how do I get that quote ID to pass to my Custom report where QuoteID = @QuoteID using SQL Select Statements. Please don't reference the account. I am looking for a quote.

    Many Thanks

    Friday, November 6, 2015 6:45 PM

Answers

  • Hi,

    You have to enable prefiltering to get the selected record id's.

    SELECT quoteid,select other fields here

    FROM

    FilteredQuote AS CRMAF_FilteredQuote 

    • Marked as answer by SunkistDog Monday, November 9, 2015 6:05 PM
    Friday, November 6, 2015 8:56 PM

All replies

  • Hi,

    You have to enable prefiltering to get the selected record id's.

    SELECT quoteid,select other fields here

    FROM

    FilteredQuote AS CRMAF_FilteredQuote 

    • Marked as answer by SunkistDog Monday, November 9, 2015 6:05 PM
    Friday, November 6, 2015 8:56 PM
  • Would something like this work?

    SELECT

      CRMAF_FilteredQuote.quoteid AS quoteid ,


            CRMAF_FilteredQuote

    .name AS Name ,


            CRMAF_FilteredQuote

    .quotenumber AS QuoteNumber ,


            CRMAF_FilteredQuote

    .revisionnumber AS QuoteNumberRevision ,


            CRMAF_FilteredQuote

    .new_programmanagername AS ProgramManager ,


            CRMAF_FilteredQuote

    .totaltax AS totaltax ,


            CRMAF_FilteredQuote

    .totalamount AS totalamount ,


            CRMAF_FilteredQuote

    .new_ftebeginningeffectivedate AS FTEBeginningEffectiveDate ,


            CRMAF_FilteredQuote

    .new_fteendingeffectivedate AS FTEEndingEffectiveDate ,


            CRMAF_FilteredQuote

    .new_description AS BackgroundDescription ,


            CRMAF_FilteredQuote

    .new_studytype AS StudyType ,


            CRMAF_FilteredQuote

    .new_product AS Product ,


            CRMAF_FilteredQuote

    .new_method AS Method ,


            CRMAF_FilteredQuote

    .new_regulatorycompliance AS RegulatoryCompliance ,


            CRMAF_FilteredQuote

    .new_projectordertestingarticles AS TestingArticles ,


            CRMAF_FilteredQuote

    .new_projectordertimeline AS Timeline ,


            CRMAF_FilteredQuote

    .new_projectordersubcontractors AS Subcontractors ,


            CRMAF_FilteredQuote

    .new_projectordersubcontractorname AS Subcontractor ,


            CRMAF_FilteredQuote

    .new_primarycontactidname AS PrimaryContact ,


            CRMAF_FilteredQuote

    .[new_StreetAddress1] ,


            CRMAF_FilteredQuote

    .[new_StreetAddress2] ,


            CRMAF_FilteredQuote

    .[new_StreetAddress3] ,


            CRMAF_FilteredQuote

    .[new_City] ,


            CRMAF_FilteredQuote

    .[new_StateorProvince] ,


            CRMAF_FilteredQuote

    .[new_ZipPostalCode] ,


            CRMAF_FilteredQuote

    .[new_Country] ,


            CRMAF_FilteredQuote

    .LineItemNumber ,


            CRMAF_FilteredQuote

    .Quantity ,


            CRMAF_FilteredQuote

    .ProductDescription ,


            CRMAF_FilteredQuote

    .PricePerUnit ,


            CRMAF_FilteredQuote

    .BaseAmount ,


            CRMAF_FilteredQuote

    .ExtendedAmount ,


            CRMAF_FilteredQuote

    .IsPriceOverridden ,


            CRMAF_FilteredQuote

    .SequenceNumber ,


            CRMAF_FilteredQuote

    .ProductTypeCode ,


            CRMAF_FilteredQuote

    .PropertyConfigurationStatus ,


            CRMAF_FilteredQuote

    .LineItemDescription ,


            CRMAF_FilteredQuote

    .[ST] ,


            CRMAF_FilteredQuote

    .[Country] ,


            CRMAF_FilteredQuote

    .new_projectordertestingarticles ,


            CRMAF_FilteredQuote

    .[revisionnumber] ,


            CRMAF_FilteredQuote

    .totaldiscountamount ,


            CRMAF_FilteredQuote

    .totalamount ,


            CRMAF_FilteredQuote

    .new_projectordertestingarticles ,


            FilteredSystemUser

    .fullname AS BusinessDevelopment ,


            FilteredSystemUser

    .title AS BusinessDevelopmentTitle ,


            FilteredSystemUser

    .internalemailaddress AS BusinessDevelopmentEmail ,


            FilteredSystemUser

    .address1_telephone1 AS BusinessDevelopmentPhone ,


            FilteredAccount

    .name AS AccountName ,


            FilteredAccount

    .new_msainplacename AS MSA ,


            FilteredAccount

    .new_msaeffectivedate AS MSAEffectiveDate ,


            FilteredAccount

    .new_msaproposaltypename AS MSAProposalType ,


            FilteredAccount

    .address1_line1 AS address1_line1 ,


            FilteredAccount

    .address1_line2 AS address1_line2 ,


            FilteredAccount

    .address1_line3 AS address1_line3 ,


            FilteredAccount

    .address1_city AS address1_city ,


            FilteredAccount

    .address1_stateorprovince AS address1_stateorprovince ,


            FilteredAccount

    .address1_postalcode AS address1_postalcode ,


            FilteredContact

    .fullname AS PotentialContact ,


            FilteredContact

    .emailaddress1 AS PotentialContactEmail ,


            FilteredContact

    .telephone1 AS PotentialContactPhone ,


            FilteredContact

    .fax AS PotentialContactFax


    FROM

        FilteredQuote AS CRMAF_FilteredQuote

           

    LEFT JOIN FilteredSystemUser WITH ( NOLOCK ) ON ( FilteredSystemUser.systemuserid = new_salesperson )


           

    LEFT JOIN FilteredAccount WITH ( NOLOCK ) ON ( FilteredAccount.accountid = CRMAF_FilteredQuote.accountid )


           

    LEFT JOIN FilteredContact WITH ( NOLOCK ) ON ( FilteredContact.contactid = CRMAF_FilteredQuote.new_primarycontactid )


    WHERE

       CRMAF_FilteredQuote.statecode = 0;

    Friday, November 6, 2015 9:20 PM
  • It looks good.

    Thanks!

    kalim

    Monday, November 9, 2015 1:09 PM