Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Multiple parameter thr...
Finance | Project Operations, Human Resources, ...
Answered

Multiple parameter through query range is not working

(0) ShareShare
ReportReport
Posted on by Finopscello 74

Hello all,

I'm trying to apply query range on a report but it's not working.

I have tried following

queryBuildDataSource = query.dataSourceTable(tablenum(InventTrans));
queryBuildRange = SysQuery::findOrCreateRange(query.datasourceTable(tableNum(InventTrans)),fieldNum(InventTrans,DateFinancial));
queryBuildRange.value(SysQuery::range(Fromdate,ToDate));
qbds = query.addDataSource(tableNum(InventLocation));
qbds1 = query.addDataSource(tableNum(InventItemGroupItem));
qbds2 = query.addDataSource(tableNum(CustTable));
qbds3 = query.addDataSource(tableNum(EcoResCategory));

if(WHName)
{
	query.dataSourceTable(tableNum(InventLocation)).addrange(fieldNum(InventLocation,Name)).value(WHName);
}
if(Brand)
{
	query.dataSourceTable(tableNum(InventItemGroupItem)).addrange(fieldNum(InventItemGroupItem,ItemGroupId)).value(Brand);
}
if(CustGroup)
{
	query.dataSourceTable(tableNum(CustTable)).addrange(fieldNum(CustTable,CustGroup)).value(CustGroup);
}
if(EcoResCategoryS)
{
	select ItemID, Product from lclInventTable1
			join ecoResProductCategory1 where ecoResProductCategory1.Product == lclInventTable1.Product
			join ecorescategory1 where ecorescategory1.recid == ecoresproductcategory1.Category
			join  ecorescategoryhierarchy1 where ecorescategoryhierarchy1.RecId == ecoresproductcategory1.CategoryHierarchy
			&& ecorescategoryhierarchy1.Name == 'Sales Category'
			&&  ecorescategory1.Name == EcoResCategoryS;
	query.dataSourceTable(tableNum(EcoResCategory)).addrange(fieldNum(EcoResCategory,Name)).value(ecorescategory1.Name);
}
if(EcoResCategoryP)
{
	select ItemID, Product from lclInventTable1
			join ecoResProductCategory1 where ecoResProductCategory1.Product == lclInventTable1.Product
			join ecorescategory1 where ecorescategory1.recid == ecoresproductcategory1.Category
			join  ecorescategoryhierarchy1 where ecorescategoryhierarchy1.RecId == ecoresproductcategory1.CategoryHierarchy
			&& ecorescategoryhierarchy1.Name == 'Product Category'
		 &&  ecorescategory1.Name == EcoResCategoryP;
	query.dataSourceTable(tableNum(EcoResCategory)).addrange(fieldNum(EcoResCategory,Name)).value(ecorescategory1.Name);
}

Thanks

  • Finopscello Profile Picture
    Finopscello 74 on at
    RE: Multiple parameter through query range is not working

    Hello Martin,

    Thankyou for the explanation.

    As suggested, I'll try to do it by AOT query and then try to implement the logic in code for learning. I asked for the code since I wasn't sure how to work with multiple data sources, ranges, and joins through a query and wanted to have a sense of how to do it through code. In the case of warehouse I need all inventory transactions and include information about InventLocation only if it's available.

    Regards 

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 232,923 Most Valuable Professional on at
    RE: Multiple parameter through query range is not working

    You can't write code before designing the logic.

    Let's say you want to join with InventTrans with InventLocation. How would the query look like? ID of an InventLocation may be used in inventory dimensions of InventTrans table. Therefore you may want to inner-join InventTrans, InventDim and InventLocation.

    Like this:

    select inventTrans
    join inventDim
        where inventDim.InventDimId == inventTrans.InventDimId
        join inventLocation
            where inventLocation.InventLocationId == inventDim.InventLocationId

    But is it really what you want? I don't know, because I don't know your requirements...

    The problem is that not all inventory dimensions must include InventLocationId. If you want just transactions related to a warehouse, the result will be correct, but if you want all inventory transactions (and include information about InventLocation only if available), the query may filter out records that you need. The latter case would require an outer join instead of an inner join.

    I suggest you try to model the query visually in AOT. What you did was putting InventTrans and InventLocation data sources to the root of the data source. They were not related to each in any way. Notice that query data sources in AOT have nested data sources node, where you can add joined data source. Than you can set the type of the join and fields used for join conditons. When you decide to do it in code, you'll almost the same thing, therefore you'll have understanding of how the code should look like.

  • Finopscello Profile Picture
    Finopscello 74 on at
    RE: Multiple parameter through query range is not working

    Hello Martin,

    Thankyou for the help. I'll try to implement it by adding data source to query and then applying the range as you suggested.

    Just for the learning purpose, can you please tell me how I can do this through code. As you said I made multiple mistake, so if it's not too much trouble I want to know the correct way to achieve it through code. 

  • Martin Dráb Profile Picture
    Martin Dráb 232,923 Most Valuable Professional on at
    RE: Multiple parameter through query range is not working

    As you see, the query doesn't have the data sources that you want to filter by, therefore you need to design a query with the extra data sources. You must think which data sources will need to be joined and by which fields, how they'll be filtered (e.g. EcoResCategoryHierarchy filtered by Name) and things like that. You can't implement the query without knowing how it should look like.

    As I mentioned, if you use an AOT query, you don't need to write code for it, but doing it in code is possible too.

    Note that don't understand your explanation regarding why you couldn't set the ranges directly. You do nothing magical with  EcoResCategory.Name - you simply set the range value. Maybe it means again that you don't know how to create the query, instead of not being able to set the range.

  • Finopscello Profile Picture
    Finopscello 74 on at
    RE: Multiple parameter through query range is not working

    Hello Martin,

    You seem to be developing an SSRS. The report probably uses a data provider (RDP) class, where you have a query and you want to apply ranges to this query. Is that correct?
    Yes

    For some reason, you decided not to allow users to set the ranges directly, but you rather created parameters in a contract class and you want to add ranges in code. Is that correct? What is your reasoning for this decision? Wouldn't the other approach be easier?
    I wasn't able to set the ranges directly. As mentioned I need parameter EcoResCategoryS(which is the value from EcoResCategory.Name when for an item the EcoResCategoryhierarchy.Name = "Sales Category", EcoResCategoryP( similar to EcoResCategoryS but EcoResCategoryhierarchy.Name = "Product Category".

    Do you already have a query to be used by the report? If so, please describe it to us. If not, you need to design it first. Your code for adding data sources is wrong, but it's impossible to fix it without knowing how the query should look like. Also, can't you add a the data sources to an AOT query rather than doing it in code?
    Yes the report have a simple query with InventTransOrigin as main data source and inventrans as second data source where InventTransOrigin.RecId == INVENTTRANS.InventTransOrigin.

    Can you please suggest a better approach to this. As mentioned above I haven't worked with query a lot so I'm looking for the guidance 

  • Martin Dráb Profile Picture
    Martin Dráb 232,923 Most Valuable Professional on at
    RE: Multiple parameter through query range is not working

    Let me try to help you describe your situation.

    You seem to be developing an SSRS. The report probably uses a data provider (RDP) class, where you have a query and you want to apply ranges to this query. Is that correct?

    For some reason, you decided not to allow users to set the ranges directly, but you rather created parameters in a contract class and you want to add ranges in code. Is that correct? What is your reasoning for this decision? Wouldn't the other approach be easier?

    Do you already have a query to be used by the report? If so, please describe it to us. If not, you need to design it first. Your code for adding data sources is wrong, but it's impossible to fix it without knowing how the query should look like. Also, can't you add a the data sources to an AOT query rather than doing it in code?

  • Finopscello Profile Picture
    Finopscello 74 on at
    RE: Multiple parameter through query range is not working

    Hello Martin,

    Actually my requirement is to add the parameter for warehouse(InventLocation.Name), custgroup(CustTable.CustGroup), Brand(InventItemGroupItem.ItemGroupId), EcoResCategoryS(which is the value from EcoResCategory.Name when for an item the EcoResCategoryhierarchy.Name = "Sales Category", EcoResCategoryP( similar to EcoResCategoryS but EcoResCategoryhierarchy.Name = "Product Category".

    I have added these parameter through UIBuilder and getting the lookup correctly. Currently I'm able to filter the report data based on Brand parameter but other parameters are not filtering data. I haven't worked with query alot so I'm not sure how to apply the ranges mentioned above. Please let me know if you need any additional specific information

  • Martin Dráb Profile Picture
    Martin Dráb 232,923 Most Valuable Professional on at
    RE: Multiple parameter through query range is not working

    Well, I've just noticed that you're added al data source to the root of the query. They aren't related to each other any way.

    Please start by explaining what you're trying to achieve by your code.

  • Community Member Profile Picture
    Community Member on at
    RE: Multiple parameter through query range is not working

    Please share at what point you are applying these ranges in code.

    Regards,

    Kamal

  • Martin Dráb Profile Picture
    Martin Dráb 232,923 Most Valuable Professional on at
    RE: Multiple parameter through query range is not working

    One obvious bug is that you joined data sources without providing any join conditons.

    Also, the salect staements for lclInventTable1 look strange. You don't really use any fields from the queries - you'll either get the input (ecoResCategoryS/ecoResCategoryP) or an empty string.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,145 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 232,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans