Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Problem with report with 2 datasets RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    Moderator