Multiple parameter through query range is not working

(0) Share

Report

Posted on by D365Explorer

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

I have the same question (0)

All responses (11)

Answers (1)

Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

Please give us a more detailed description of your problem. "It's not working" may mean anything.

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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

Regards,

Kamal

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
D365Explorer 95 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
D365Explorer 95 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
D365Explorer 95 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report
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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Multiple parameter through query range is not working

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Multiple parameter through query range is not working

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates