I am quite new to DAX 2009. I am asked to add a field PaymReference to LedgerTransStatement report. The report is based on a temporary table tmpDimTransExtract. I have been looking into the FETCH method to find out the query which picks the transactions and put in the temporary table so that I can modify the query but I am unable to find out the query or maybe I do not know how the query is getting executed. The execution path for this report is Ledger/Chart of Account Details/Account Statement/Select/Ledger Account
From what I understood by debug and working on this issue is that LedgerTransReportEngine class picks up the transactions based on criteria specified i.e. from date, to date, Account Num etc and I believe all the transactions come from LedgerTrans table. In LedgerTrans table we have the PaymReference field that I have to add to the report. In the criteria selection form before running the report even I can put a Payment Reference and the report is able to pick the specific transaction.
Now the whole problem is to find the place in the code where I can actually add paymReference field in the list of columns being picked up but I am unable to find a query in the code. All I seem to understand is that query criteria is build (from date, to date, Account Num, opening, closing etc.) and LedgerTransReportEngine gets the transactions based on criteria into the TmpDimTransExtract table.
I have already added a paymReference field to the TmpDimTransExtract temp table but I guess I need two things:
Add paymReference field to the query LedgerTransReportEngine is using. How?
Link the newly added paymReference field to get the Payment Reference of Transactions. How?
I am pretty clueless. I would appreciate any pointer if you guys have from your experience.
The insertion into temp table happens in insert* methods in \Classes\LedgerTransReportEngine
One method that got hit in my case is \Classes\LedgerTransReportEngine\insertRegularTransaction
For adding paymReference in this case, add a another parameter (to pass paymReference) to insertRegularTransaction method (better to make it default parameter)
Write an abstract parm method (like parmPaymReference) in \Classes\LedgerBalanceSheetDim
Override this parmMethod in the classes that extent \Classes\LedgerBalanceSheetDim
The overridden parmMethod in \Classes\LedgerBalanceSheetDimCol_Cur should return ledgerTrans.paymReference. (for example see \Classes\LedgerBalanceSheetDimCol_Cur\parmVoucher)
Come back to \Classes\LedgerTransReportEngine
In calculate* methods where the \Classes\LedgerTransReportEngine\insertRegularTransaction is called pass the value to the newly added parameter as _ledgerBalanceSheetDim. parmPaymReference()
Then in the \Classes\LedgerTransReportEngine\insertRegularTransaction method update the temp table field(which is added for paymReference) with the newly added parameter value.
Don’t forget to do a compile forward on \Classes\LedgerBalanceSheetDim
You have explained in detail pretty much what I need to do. I will test it on Monday. Thanks a lot for your time. I am quite convinced that working on these lines I will be able to add the field. I kept on going through debug almost the whole day and got the overall picture and now with steps that you have mentioned hopefully I will get to the end of the tunnel.
Once again thanks for your precious time and your reply.
Just an update. Your detailed steps worked like a charm this morning and made my day. Hats off to you. I sent an email through your personal blog as well. Thanks a million.
The only minute problem left is that despite adding the paymReference prompt field in Auto and Generated Design of the report, it does not appear. I have the paymReference values but the column name is missing. I have tried everything that I can think of.
I appreciate the experienced members of any help that they can provide.
I found answer to my question by experimenting. In fact the prompt control uses the name of its corresponding field from the body of the report as the ModelFieldName and that is how it aligns itself.