locked
SSRS Report / CRM 2011 RRS feed

  • Question

  • Hi there. I have an SSRS Report created using Visual Studio 2008 BIDS. It is essentially a contract document that prints the CRM 2011 Contract and Contract Lines as a document. The document consists of several datasets and for each dataset, a tablix. Each dataset is a page in the document (I have page breaks after each tablix). I have a number of filters for each dataset which determines which Contract Lines are going to be printed. The report is deployed to CRM as I want to selectively print individual records. This works absolutely fine when I have just the one Contract record in CRM . However, my problem is when I create another Contract/Contract Lines record in CRM and then select that one record to print the document, it pulls all the details from  the other record. I understand why it is doing this because of the filters but how do I get it to print just the record I want?

    I am not really a developer so any detail of exactly how to fix this would be of great help.

    Thanks


    BM

    Friday, July 19, 2013 11:01 AM

All replies

  • Can u plz post ur  query that report uses.

    Thanks

    Friday, July 19, 2013 2:27 PM
  • Thanks. Here is the query from Dataset1. I use the same query in the rest of the Datasets.

    SELECT        CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson,
                             CRMAF_FilteredContract.billingfrequencycodename, CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname,
                             ContractExtensionBase.New_PAVContractNo, ContractExtensionBase.new_periodbillingamount1, CRMAF_FilteredContract.title,
                             ContractDetailBase.ProductSerialNumber, ContractDetailBase.InitialQuantity, ContractDetailBase.Title AS Expr1,
                             ContractExtensionBase.new_frequencybillingamount, CRMAF_FilteredContract.statecodename, CRMAF_FilteredContract.contractservicelevelcodename,
                             CRMAF_FilteredContract.new_comments, AccountBase.AccountNumber, ContractDetailExtensionBase.new_SubscribedTo, ContractDetailBase.Price,
                             CRMAF_FilteredContract.contractnumber, ContractDetailExtensionBase.new_DetailLine
    FROM            FilteredContract AS CRMAF_FilteredContract INNER JOIN
                             ContractExtensionBase ON CRMAF_FilteredContract.contractid = ContractExtensionBase.ContractId INNER JOIN
                             ContractDetailBase ON ContractExtensionBase.ContractId = ContractDetailBase.ContractId INNER JOIN
                             ContractDetailExtensionBase ON ContractDetailBase.ContractDetailId = ContractDetailExtensionBase.ContractDetailId INNER JOIN


    BM

    Friday, July 19, 2013 2:34 PM
  • Hi,

    Your query seems proper.(though you have used ContractDetailBase table instead of the corresponding filteredview).

    Plz try using filteredview every where and then  run the  report.

    If that also doesnt  work, remove the  report from CRM and reupload it again and then check.

    Thanks

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Friday, July 19, 2013 2:53 PM
  • Hi Kar. Thank you for your reply. I have done as you suggested but still within CRM when I select the Contract record and Run Report, it is still bringing in Contract Line records from other Contract records. If I apply a Dataset Filter to each and every Dataset using the Customer Account Number, the report works. But I don't want to do that as I would have to change all the Dataset filters and re-deploy the report each time. Any further ideas?

    Thanks

    SELECT        CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson,
                             CRMAF_FilteredContract.billingfrequencycodename, CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname,
                             CRMAF_FilteredContractExtensionBase.New_PAVContractNo, CRMAF_FilteredContractExtensionBase.new_periodbillingamount1,
                             CRMAF_FilteredContract.title, CRMAF_FilteredContractDetailBase.ProductSerialNumber, CRMAF_FilteredContractDetailBase.InitialQuantity,
                             CRMAF_FilteredContractDetailBase.Title AS Expr1, CRMAF_FilteredContractExtensionBase.new_frequencybillingamount,
                             CRMAF_FilteredContract.statecodename, CRMAF_FilteredContract.contractservicelevelcodename, CRMAF_FilteredContract.new_comments,
                             CRMAF_FilteredAccountBase.AccountNumber, CRMAF_FilteredContractDetailExtensionBase.new_SubscribedTo, CRMAF_FilteredContractDetailBase.Price,
                             CRMAF_FilteredContract.contractnumber, CRMAF_FilteredContractDetailExtensionBase.new_DetailLine
    FROM            FilteredContract AS CRMAF_FilteredContract INNER JOIN
                             ContractExtensionBase AS CRMAF_FilteredContractExtensionBase ON
                             CRMAF_FilteredContract.contractid = CRMAF_FilteredContractExtensionBase.ContractId INNER JOIN
                             ContractDetailBase AS CRMAF_FilteredContractDetailBase ON
                             CRMAF_FilteredContractExtensionBase.ContractId = CRMAF_FilteredContractDetailBase.ContractId INNER JOIN
                             ContractDetailExtensionBase AS CRMAF_FilteredContractDetailExtensionBase ON
                             CRMAF_FilteredContractDetailBase.ContractDetailId = CRMAF_FilteredContractDetailExtensionBase.ContractDetailId INNER JOIN


    BM


    • Edited by BillyBoy9991 Tuesday, July 23, 2013 2:52 PM Updated
    Tuesday, July 23, 2013 12:43 PM
  • The pre-filtering will only work using Filtered views, and aliases of the form CRMAF_FilteredContractDetail , not with the base tables.

    So, your partial FROM clause should be:

    FROM FilteredContract AS CRMAF_FilteredContract INNER JOIN FilteredContractDetail AS CRMAF_FilteredContractDetail ON                         CRMAF_FilteredContract.ContractId = CRMAF_FilteredContractDetail.ContractId 


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

    Tuesday, July 23, 2013 3:54 PM
    Moderator
  • Just to add to David's comment. Access CRM tables is not supported therefore you should use views. Like:-

    SELECT     CRMAF_FilteredNew_enrolment.new_enrolmentdate, CRMAF_FilteredNew_enrolment.new_teacheridname, CRMAF_FilteredNew_enrolment.new_trn,
                          CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier, CRMAF_FilteredNew_submission.new_name
    FROM         FilteredNew_enrolment AS CRMAF_FilteredNew_enrolment INNER JOIN
                          FilteredNew_submission AS CRMAF_FilteredNew_submission ON
                          CRMAF_FilteredNew_enrolment.new_enrolmentid = CRMAF_FilteredNew_submission.new_enrolementsubid INNER JOIN
                          FilteredNew_verification AS CRMAF_FilteredNew_verification ON
                          CRMAF_FilteredNew_submission.new_submissionid = CRMAF_FilteredNew_verification.new_verificationsubid
    WHERE     (CRMAF_FilteredNew_verification.new_presentationreceivedfromverifier IS NOT NULL)


    Regards Faisal

    Wednesday, July 24, 2013 7:17 AM
  • Sorry, still not working. It is still bringing in Contract Line records from other Contract records when I select a Contract record in CRM to run the report against.

    SELECT        CRMAF_FilteredContract.customeridname, CRMAF_FilteredContract.activeon, CRMAF_FilteredContract.expireson,
                             CRMAF_FilteredContract.billingfrequencycodename, CRMAF_FilteredContract.new_contracttypename, CRMAF_FilteredContract.new_contracttermname,
                             ContractExtensionBase.New_PAVContractNo, ContractExtensionBase.new_periodbillingamount1, CRMAF_FilteredContract.title,
                             ContractDetailBase.ProductSerialNumber, ContractDetailBase.InitialQuantity, ContractDetailBase.Title AS Expr1,
                             ContractExtensionBase.new_frequencybillingamount, CRMAF_FilteredContract.statecodename, CRMAF_FilteredContract.contractservicelevelcodename,
                             CRMAF_FilteredContract.new_comments, AccountBase.AccountNumber, ContractDetailExtensionBase.new_SubscribedTo, ContractDetailBase.Price,
                             CRMAF_FilteredContract.contractnumber, ContractDetailExtensionBase.new_DetailLine
    FROM            FilteredContract AS CRMAF_FilteredContract INNER JOIN
                             ContractExtensionBase ON CRMAF_FilteredContract.contractid = ContractExtensionBase.ContractId INNER JOIN
                             ContractDetailBase ON ContractExtensionBase.ContractId = ContractDetailBase.ContractId INNER JOIN
                             ContractDetailExtensionBase ON ContractDetailBase.ContractDetailId = ContractDetailExtensionBase.ContractDetailId INNER JOIN
                             AccountBase ON CRMAF_FilteredContract.accountid = AccountBase.AccountId


    BM


    • Edited by BillyBoy9991 Wednesday, July 24, 2013 9:45 AM Update
    Wednesday, July 24, 2013 9:05 AM