How to apply Dimension Range on execute query

(1) Share

Report

Posted on by Faqruddin

1,883

Dears,

I have lookup on Sting Ctrl which returns only specific dimension value. Now If I select Dimension value from lookup I need to filter the grid based on selected dimension value from lookup. Could you please let me know any example on executeQuery() to build logic with specific Dimension Range.

Thanks

Faqru

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (6)

Verified answer

Mea_ 60,284 on at

Like (0)

Report

Hi Faqru Shaik,

You can use DimensionProvider class for that, check this blog post axforce.wordpress.com/.../using-the-class-dimensionprovider-to-filter-a-query-with-multiple-financial-dimensions-criteria for example

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

Thanks ievgen. Lets say SalesTable have FinancialDimension "CostCenter. And I need filter on "CostCenter" value only on my customized form. Any Ideas ?

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

what do you mean "any ideas" I gave you a blog post to follow, what's wrong with it?

Was this reply helpful? Yes No
Verified answer

Hossein.K 6,648 on at

Like (1)

Report
Hi Faqru,

Try this code:

static void addDimensionRange(Args _args) { Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbds; DimensionProvider dimensionProvider = new DimensionProvider(); GeneralJournalAccountEntry accEntry; DimensionAttribute dimAttr; Name dimAttrNameEmpl, dimAttrNameCostCenter; int i; ; select firstOnly dimAttr where dimAttr.BackingEntityType == tableNum(DimAttributeHcmWorker); dimAttrNameEmpl = dimAttr.Name; select firstOnly dimAttr where dimAttr.BackingEntityType == tableNum(DimAttributeOMCostCenter); dimAttrNameCostCenter = dimAttr.Name; qbds = query.addDataSource(tableNum(GeneralJournalAccountEntry)); dimensionProvider.addAttributeRangeToQuery(query, qbds.name(), fieldStr(GeneralJournalAccountEntry, LedgerDimension), DimensionComponent::DimensionAttribute, SysQuery::valueNotEmptyString(), dimAttrNameEmpl, true); dimensionProvider.addAttributeRangeToQuery(query, qbds.name(), fieldStr(GeneralJournalAccountEntry, LedgerDimension), DimensionComponent::DimensionAttribute, SysQuery::valueNotEmptyString(), dimAttrNameCostCenter, true); queryRun = new QueryRun(query); queryRun.prompt(); while(queryRun.next()) { accEntry = queryRun.get(tableNum(GeneralJournalAccountEntry)); info(strFmt("%1 <–> %2", DimensionAttributeValueCombination::find(accEntry.LedgerDimension).DisplayValue, accEntry.AccountingCurrencyAmount)); } }

Was this reply helpful? Yes No
Verified answer

Heinz Schweda 1,367 on at

Like (1)

Report
Hi,

maybe the following function is, what you are searching for?

public void init() { super(); SysQuery::addDimensionAttributeRange(salesTable_ds.query(), salesTable_ds.name(), fieldStr(Salestable, DefaultDimension), DimensionComponent::DimensionAttribute, '1001', 'CostCenter'); }

Kind regards,

Heinz

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

Thanks Heinz. This what I'm exactly looking for. But How I can pass my StringCtlr to above code ? So I can filter the grid.

Was this reply helpful? Yes No
Verified answer

Heinz Schweda 1,367 on at

Like (0)

Report

You could set the property AutoDeclaration of your control to YES and use NameOfYourControl.valueStr()

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report

Thanks Heinz. It works. But once I clear the filter box the grid is showing with same values again.

Was this reply helpful? Yes No
Verified answer

Heinz Schweda 1,367 on at

Like (0)

Report

In that case you can use SysQuery::clearDimensionRangesFromQuery()

Was this reply helpful? Yes No
Faqruddin 1,883 on at

Like (0)

Report
Thanks. Still it same by using SysQuery::clearDimensionRangesFromQuery(). Is SysQuery::clearDimensionRangesFromQuery() applicable to clear single dimension field range if I use QueryBuildRange and SysQuery::findOrCreateRange ? I just pass standard query value. But its not clearing the range.

void executeQuery() { Query q = new Query(); QueryBuildDataSource qbds, qbds = q.addDataSource(tableNum(SalesTable)); sysQuery::clearDimensionRangesFromQuery(q); SysQuery::addDimensionAttributeRange(q , qbds.name(), 'DefaultDimension', DimensionComponent::DimensionAttribute, RootControl.valueStr() , 'root'); super(); }

Was this reply helpful? Yes No