Dynamics Community Forum Thread Details

how to add financial dimension values in ssrs report

then the method more or less is as follow:

protected void CWSUpdateFinanceLog()
{
DefaultDimensionView defaultdimensionview;
CustTable custTable;
while select forupdate custAgingReportTmp
{
custTable = custTable::find(custAgingReportTmp.accountNum);
select firstonly displayvalue from defaultdimensionview
where defaultdimensionview.DEFAULTDIMENSION == custTable.DEFAULTDIMENSION
&& defaultdimensionview.REPORTCOLUMNNAME = 'FINLOC';
custAgingReportTmp.finLoc = defaultdimensionview.displayValue;
custAgingReportTmp.update();
}

}

Thanks for your reply @Chandra Setiawan. I have actually done something similar to this but unable to achieve the result. Kindly let me know what I am missing

My requirement: to add customer 'financial location' to customer aging report at header level.

things I have done to achieve this: 1. added a new field called 'CustFinLoc' in both custTmpAccountSum, custAgingReporTmp and custAgingReportTmp

2. In custBalanceList.insertIntoTmpAccountSum() added the below code:

while select firstOnly custTable

where custTable.AccountNum == _toCustVendTable.AccountNum

join DisplayValue from dimAttrView

where dimAttrView.ValueCombinationRecId == custTable.DefaultDimension

join Name from dimAttr

where dimAttr.RecId == dimAttrView.DimensionAttribute

{

dimStorage = DimensionAttributeValueSetStorage::find(custTable.DefaultDimension);

// if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == 'FINANCIALLOCATION')

//{

for (i=1 ; i<= dimStorage.elements() ; i++)

{

if(DimensionAttribute::find(dimStorage.getAttributeByIndex(i)).Name == 'FINANCIALLOCATION')

{

finDim = dimStorage.getDisplayValueByIndex(i);

}

agingCalculatedTmp.CustFinLoc = finDim;

// in the above code my idea was to insert this custFinLoc in agingCalculatedTmp table

but I am aware that unless I call insert() this wont insert, but if I do, it throws an error 'çant insert multiple customer/vendor accounts, record already exists'.

So please help me out here :( Do you think I will have to insert a similar new field to capture finLoc in 'ágingCalculatedTmp' and use this code in CustVendAgingCalculation.process() instead? but this table gets its data from agingProcessingTmp and agingProcessingDetailsTmp tables. so there is no ending to this backtracking, I am confused

3. In this step my idea is to insert the fields from agingCalculatedTmp table to custTmpAccountSum table with the below code: (I just added CustFinLoc to the first select stmt)

insert_recordset custTmpAccountSum (CurrencyCode, BillingClassification, InvoiceId, TransDate, Txt, Voucher, AccountNum, Name, GroupId, CustFinLoc)

select CurrencyCode, BillingClassification, InvoiceId, TransDate, Txt, Voucher, CustFinLoc

from agingCalculatedTmp

group by CurrencyCode, BillingClassification, TransDate, InvoiceId, Voucher, Txt

join AccountNum, Name, GroupId from toCustomer

group by AccountNum, Name, GroupId

notexists join custTmpAccountSum // Avoid inserting a duplicate

where custTmpAccountSum.AccountNum == _toCustVendTable.AccountNum

&& agingCalculatedTmp.CurrencyCode == custTmpAccountSum.CurrencyCode

&& agingCalculatedTmp.BillingClassification == custTmpAccountSum.BillingClassification

&& agingCalculatedTmp.TransDate == custTmpAccountSum.TransDate

&& agingCalculatedTmp.InvoiceId == custTmpAccountSum.InvoiceId

&& agingCalculatedTmp.Voucher == custTmpAccountSum.Voucher

&& agingCalculatedTmp.Txt == custTmpAccountSum.Txt;

4. Next I inserted the newly added column 'çustFinLoc' from custTmpAccountSum table

to custAgingReportTmp table in CustAgingReportDP.insertCustAgingReportTmp() by just adding the field names to the list of already existing selected fields

5. Added a new field in all 4 report designs which takes custAgingReportTmp .custFinLoc as input

When I compile all objects and deploy report I dont get any error but data is blank on the newly added field (screenshot below)

Can someone kindly help me understand where I am going wrong here :(

Quick Links

Leaderboard