Problem with report with 2 datasets RRS feed

  • Question

  • Hi,

    i have just created a new - invoice report in reporting services for CRM 4. I'm using two dataset's: 'CRM' -  which has some standard information like name, createddate, owner and so on and another one - 'Products' which retrieve informations about product for this invoice. After adding report to crm i can start it for single record (CRM Dataset Works fine), however DatasET  'Products' retrieves informations about all invoiced products (not only those which are added to parametrized entity). Could any one help me with this error, how can i associate my 'Products' dataset with invoiceid from 'CRM' dataset. Here is my 'Products' dataset:

    SELECT     ISNULL(CRMAF_FilteredInvoiceDetail.productidname, CRMAF_FilteredInvoiceDetail.productdescription) as 'productidname', CRMAF_FilteredInvoiceDetail.productdescription, CRMAF_FilteredInvoiceDetail.invoiceid, Convert(int, CRMAF_FilteredInvoiceDetail.quantity) as 'quanity', 
    (CRMAF_FilteredInvoiceDetail.priceperunit * CRMAF_FilteredInvoiceDetail.quantity - (ISNULL(CRMAF_FilteredInvoiceDetail.volumediscountamount,0) * CRMAF_FilteredInvoiceDetail.quantity +  ISNULL(CRMAF_FilteredInvoiceDetail.manualdiscountamount, 0))) as 'DiscountPrice'
                          CRMAF_FilteredInvoiceDetail.productiddsc, CRMAF_FilteredInvoiceDetail.baseamount, CRMAF_FilteredInvoiceDetail.lineitemnumber, CRMAF_FilteredInvoiceDetail.priceperunit, 
                          CRMAF_FilteredInvoiceDetail.extendedamount, ISNULL(FilteredProduct.productnumber, '') AS productnumber, ISNULL(CRMAF_FilteredInvoiceDetail.uomidname, '-') 
                          AS Unit, CRMAF_FilteredInvoiceDetail.tax AS Tax, CRMAF_FilteredInvoiceDetail.volumediscountamount * CRMAF_FilteredInvoiceDetail.quantity AS Discount, 
                          CRMAF_FilteredInvoiceDetail.manualdiscountamount AS MDiscount
    , ISNULL((CRMAF_FilteredInvoiceDetail.volumediscountamount * CRMAF_FilteredInvoiceDetail.quantity) +  CRMAF_FilteredInvoiceDetail.manualdiscountamount, 0) as 'TotalDiscount',
    ISNULL(CRMAF_FilteredInvoiceDetail.volumediscountamount +  CRMAF_FilteredInvoiceDetail.manualdiscountamount/CRMAF_FilteredInvoiceDetail.quantity,  CRMAF_FilteredInvoiceDetail.volumediscountamount) as 'UnitDiscount'
    , CRMAF_FilteredInvoiceDetail.invoicedetailid, CRMAF_FilteredInvoiceDetail.crm_moneyformatstring
    ,ISNULL(ProductExtensionBase.new_PKWiU, '-') as 'new_PKWiU'
    , CASE ProductextensionBase.New_Vat WHEN 1 THEN '22%' WHEN 2 THEN '7%' WHEN 3 THEN '0%' when 4 THEN 'zw.' ELSE '0%' END AS 'new_Vat'
    FROM         FilteredInvoiceDetail as CRMAF_FilteredInvoiceDetail LEFT OUTER JOIN
                          FilteredProduct ON CRMAF_FilteredInvoiceDetail.productid = FilteredProduct.productid
    		left outer join ProductextensionBase
    	on	CRMAF_FilteredInvoiceDetail.productid=ProductExtensionBase.productid
    left outer join dbo.UoMBase on CRMAF_FilteredInvoiceDetail.UoMId = dbo.UoMBase.UoMId
    left join dbo.UoMScheduleBase
    on dbo.UoMBase.UoMScheduleId = dbo.UoMScheduleBase.UoMScheduleId
    Monday, November 30, 2009 3:07 PM


  • You can't actually create the join btwn the two datasets within the same report to produce a master dataset. - (while you can add parameters and pass those between datasets, it won't actually do what I believe you are trying to do here.)

    I think the easiest method would be to create a separate sub-report for the just the product listing. - add an '@invoiceid' parameter to it.

    The parent report will have the 'invoivce' information and pass the invoiceid to the subreport as a parameter, then use that parameter in your 'product' subreport to filter the results.

    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    • Marked as answer by Jim Glass Jr Monday, December 7, 2009 7:54 PM
    Friday, December 4, 2009 9:21 AM