Dynamics Community Forum Thread Details

Dynamic query based on selected records

Hi there All.
I want to create a report that produce results based on the records that end-user have selected.
This report is on my custom entity that has relation with sales order, sales order as primary entity and my custom entity as related entity.
Previously I have created records based on the parameters, but the end-user entered the parameters in the report.
Anyways, For this , want to pass selected sales order Id as parameter to the report, Should I use Filtered view of every entity?
I have created a parameter named @CRM_SalesOrderId.

with this query :

SELECT        SalesOrderId
FROM            FilteredSalesOrder AS CRMAF_FilteredSalesOrderId;

I have set the Sales Order Id as default value and available value of the parameter.

And this is my main Query :

SELECT     Filteredbmsd_receipt.bmsd_totalamount_base, FilteredSalesOrder.salesorderid, FilteredSalesOrder.new_order_code, FilteredSalesOrder.name AS salesordername, 
                      methodreceiptstring.AttributeValue AS methodreceiptattributevalue, methodreceiptstring.AttributeValue AS methodreceiptattributevalue, 
                      methodreceiptstring.AttributeName AS methodreceiptattributename, methodreceiptstring.Value AS methodreceiptvalue, 
                      receiptstatusstring.AttributeValue AS receiptstatusstringvalue, receiptstatusstring.AttributeValue AS receiptstatusstringattributevalue, 
                      receiptstatusstring.AttributeName AS receiptstatusstringname, receiptstatusstring.Value
FROM         Filteredbmsd_receipt INNER JOIN
                      FilteredStringMap AS methodreceiptstring ON methodreceiptstring.AttributeValue = Filteredbmsd_receipt.bmsd_receiptmethod AND 
                      methodreceiptstring.AttributeValue = 2 AND methodreceiptstring.AttributeName = 'bmsd_ReceiptMethod' AND methodreceiptstring.LangId = 1088 INNER JOIN
                      FilteredStringMap AS receiptstatusstring ON receiptstatusstring.AttributeValue = Filteredbmsd_receipt.bmsd_receiptstatus AND 
                      receiptstatusstring.AttributeName = 'bmsd_receiptstatus' AND receiptstatusstring.LangId = 1088 
                      INNER JOIN FilteredSalesOrder ON ( FilteredSalesOrder.salesorderid=Filteredbmsd_receipt.bmsd_orderid)
                      where Filteredbmsd_receipt.bmsd_orderid in (@CRM_SalesOrderId)
ORDER BY FilteredSalesOrder.new_order_code, methodreceiptvalue

I have created This as my Main Query, and that one as my parameter query.
Did I do right?
Now when enter the main query in the main data set and click Referesh Fields It gives me :
An item with the same key has already been added.

I removed my created Parameter (@CRM_SalesOrderId) to allow the query to created the parameter, But no success.
How can I solve this?And after created that I must select
Forms for related record types;Lists for related record types;Reports area

in display field of the report, right?

Quick Links

Leaderboard > Microsoft Dynamics CRM (Archived)