Hi Community,
I am creating a report using (SRSReportDataProviderBase) in AX 2012 R2 with a Queries where it have a data source of CustTable and inner join a TempDB (where this temp table data will be inserted at ProcessReport method in DP class later on based on some complexity logic), the reason i need to inner join the TempDB is too allow user to be able to use the Advanced Query for filtering purposes.
Problems:
1. If i am using TempDB as the data source, upon generating the report, somehow all the data in Temp table is gone.
Note: if i change the Temp table to "Regular", the report will be working fine.
Could anyone help to share the method or way to make this TempDB works. Appreciate it.
*This post is locked for comments
Hi Martin,
I have tried doing the similar way and it works perfectly, i have also implemented into my reports.
All went perfectly :D.
Thanks for this wonderful guidance, appreciate it so much. Cheers Martin.
Let me give you a proof that it works (and therefore the problem must be somewhere on your side). The class also works as a clean standalone example without any clutter, so it's easy paste to AX, to understand and debug, which is a technique you should learn as soon as possible. You see you get stuck and achieve nothing if you're trying to do everything at once.
class QueryWithTempDbDemo { public static void main(Args _args) { new QueryWithTempDbDemo().run(); } private void run() { QueryRun qr = new QueryRun(this.createQuery()); SysUserRoleEffectiveLicenseTypeTmp t; qr.setCursor(this.createTmpData()); if (qr.next()) { t = qr.get(t.TableId); info(t.RoleAotName); } } private Query createQuery() { Query q = new Query(); QueryBuildDataSource userInfoDs = q.addDataSource(tableNum(UserInfo)); QueryBuildDataSource tempDs = userInfoDs.addDataSource(tableNum(SysUserRoleEffectiveLicenseTypeTmp)); tempDs.addLink(fieldNum(UserInfo, Id), fieldNum(SysUserRoleEffectiveLicenseTypeTmp, UserName)); return q; } private SysUserRoleEffectiveLicenseTypeTmp createTmpData() { SysUserRoleEffectiveLicenseTypeTmp tmp; tmp.UserName = 'admin'; tmp.RoleAotName = 'It all works'; tmp.insert(); return tmp; } }
Now when you have a proof that it works and you have working code that you can play with, you should be able to us it. But again, don't try to do everything at once. Start adding other pieces one by one, so you'll know where you made a mistake when it stops working.
Hi Martin,
I am really appreciate on your effort in trying on solving this, i've tried using the qr.setCursor() for the Temp Table. But i am not sure why it doesn't work at my side and tried to debug couldn't find any clues :(
There are many ways how to round "distance of dateofbirth to today's date" to whole years. All right, I won't bother helping you to design a better solution, if you resist so much.
I already told you how to pass a temporary buffer to a QueryRun instance.
Hi Martin,
My requirement is just a simple requirement as what i have define at below "Definition of Age calculation".
For this part of "Update Age to tmpTbl from ageTblTmp" i am doing it in order to pass the tmpTbl data to RDP report. So when in SSRS report i can have the option to display the "Age" value in a column.
Definition of Age calculation
Age is calculated from the "distance of dateofbirth to today's date."
Example:
-> DateOfBirth 1980-Aug-04, today is 2017-Aug-03, the age will be 37
I understand your concern on how i would like to implement it, but could you please let me know how to join the datasource with tempDB in RDP reports or it cannot be done?
My questions for all of this explanation is only for
1. How to join the datasource tables inner join with tempDB in RDP reports datasource
Are you able to read your code? I can't - let me add some indentation:
public void processReport() { Query q = new Query(this.parmQuery()); QueryRun qr; CustTable cTbl,custTbl; CustAge ageTblTmp; QC_Temp tmpTbl; ttsBegin; while select custTbl group by custTbl.dateofbirth { ageTblTmp.clear(); ageTblTmp.dateofbirth = custTbl.dateofbirth; ageTblTmp.birthyear = custTbl.birthyear; ageTblTmp.birthmonth = custTbl.birthmonth; ageTblTmp.birthday = custTbl.birthday; ageTblTmp.insert(); } while select forupdate ageTblTmp where custTbl.birthmonth < mthofyr(systemdateget()) || ( custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday <= dayofmth(systemdateget())) { ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear; //Example DOB 1980-08-03, Age = 37 ageTblTmp.update(); } while select forupdate ageTblTmp where custTbl.birthmonth > mthofyr(systemdateget()) || ( custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday > dayofmth(systemdateget())) { ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear - 1; //Example DOB 1980-08-04, Age = 36 ageTblTmp.update(); } qr.setCursor(ageTblTmp); qr = new QueryRun(q); while (qr.next()) //Query will shows the result for AccountNum with Age between 21 to 30 { cTbl = qr.get(tableNum(CustTable)); tmpTbl.AccountNum = cTbl.AccountNum; tmpTbl.DateOfBirth = cTbl.DateOfBirth; } update_recordSet tmpTbl //Update Age to tmpTbl from ageTblTmp setting Age = ageTblTmp.Age join ageTblTmp where ageTblTmp.DateOfBirth = tmpTbl.DateOfBirth; ttsCommit; }
This will help everybody to read your code, which will increase your chance that somebody will do it. It's in your best interested to format your code by yourself; there won't be always somebody to do it.
Unfortunately your comments didn't tell me much new. For example, saying that setting Age = ageTblTmp.Age means Update Age to tmpTbl from ageTblTmp is not needed, because it's completely obvious. What's not clear is why you're doing it at all.
But neither of it answers my question. Please tell in your own words your definition of Age (i.e. how it should be calculated). I can read your new code, which (unlike your previous code) works with the date, but I can't be sure that your implementation really matches your business requirement. That's why I want to see the requirement, not your attempt to implement it.
Hi Martin,
Please find below of my further detail explanation and intention on why i use this Age calculation (the update_records / while select updates)
-> Require to create a report to shows list of customers and have the available filtering option by Age (Example value of Age in Advanced Query: 21..30)
Available Physical Table
1. CustTable, Fields: AccountNum, DateOfBirth, BirthYear, BirthMonth, Birthday
Plan
1. Create a tempTb with name of AgeTmpTbl, Fields: Age, DateOfBirth, BirthYear, BirthMonth, BirthDay
2. Create a Query with DataSource: CusTable (Regular) INNER JOIN AgeTmpTbl (TempDB)
3. When user print out the report filter by Age, example 21..30. Report will shows the result of accountnum code which the dateofbirth are from 21 to 30
public void processReport() { Query q = new Query(this.parmQuery()); QueryRun qr; CustTable cTbl,custTbl; CustAge ageTblTmp; QC_Temp tmpTbl; int i; ttsBegin; while select custTbl group by custTbl.dateofbirth { ageTblTmp.clear(); ageTblTmp.dateofbirth = custTbl.dateofbirth; ageTblTmp.birthyear = custTbl.birthyear; ageTblTmp.birthmonth = custTbl.birthmonth; ageTblTmp.birthday = custTbl.birthday; ageTblTmp.insert(); } while select forupdate ageTblTmp where custTbl.birthmonth < mthofyr(systemdateget()) || (custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday <= dayofmth(systemdateget())) { ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear; //Example DOB 1980-08-03, Age = 37 ageTblTmp.update(); } while select forupdate ageTblTmp where custTbl.birthmonth > mthofyr(systemdateget()) || (custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday > dayofmth(systemdateget())) { ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear-1; //Example DOB 1980-08-04, Age = 36 ageTblTmp.update(); } qr.setCursor(ageTblTmp); qr = new QueryRun(q); while (qr.next()) //Query will shows the result for AccountNum with Age between 21 to 30 { cTbl = qr.get(tableNum(CustTable)); tmpTbl.AccountNum = cTbl.AccountNum; tmpTbl.DateOfBirth = cTbl.DateOfBirth; } update_recordSet tmpTbl //Update Age to tmpTbl from ageTblTmp setting Age = ageTblTmp.Age join ageTblTmp where ageTblTmp.DateOfBirth = tmpTbl.DateOfBirth; ttsCommit; }
You again forgot to call setCursor().
Note that neither the update_records nor any of the while selects makes sense to me, so if you keep refusing to explain your intention, I can't help you.
Also, please use </> button in the rich formatting view to paste code.
Hi Martin,
Actually i have started to move the logic into a simple "Job" for debugging and during the debugging inside the QueryRun it shows correct query statement, the only problem i see is that the "DataSource" couldn't get the new inserted data into the TempDB table.
I have revised the code below for a better code understanding on my calculation for "Age", cheers.
public void processReport()
{
Query q = new Query(this.parmQuery());
QueryRun qr;
CustTable cTbl,custTbl;
CustAge ageTblTmp;
QC_Temp tmpTbl;
int i;
ttsBegin;
while select custTbl group by custTbl.dateofbirth
{
ageTblTmp.clear();
ageTblTmp.dateofbirth = custTbl.dateofbirth;
ageTblTmp.birthyear = custTbl.birthyear;
ageTblTmp.birthmonth = custTbl.birthmonth;
ageTblTmp.birthday = custTbl.birthday;
ageTblTmp.insert();
}
while select forupdate ageTblTmp where custTbl.birthmonth < mthofyr(systemdateget()) || (custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday <= dayofmth(systemdateget()))
{
ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear;
ageTblTmp.update();
}
while select forupdate ageTblTmp where custTbl.birthmonth > mthofyr(systemdateget()) || (custTbl.birthmonth == mthofyr(systemdateget()) && custTbl.birthday > dayofmth(systemdateget()))
{
ageTblTmp.Age = year(systemDateGet()) - ageTblTmp.birthyear-1;
ageTblTmp.update();
}
qr = new QueryRun(q);
while (qr.next())
{
cTbl = qr.get(tableNum(CustTable));
tmpTbl.AccountNum = cTbl.AccountNum;
tmpTbl.DateOfBirth = cTbl.DateOfBirth;
}
update_recordSet tmpTbl
setting Age = ageTblTmp.Age
join ageTblTmp
where ageTblTmp.DateOfBirth = tmpTbl.DateOfBirth;
ttsCommit;
}
Yes, it seems that you have a problem in your code, so let's take the report from the question, as it doesn't seem to be related to your problem. You should either test the RDP class directly, or even extract the code to a simple job and test your query there. You make your life more complicated if you're testing your code through a report.
Before I suggest a solution, can you confirm how you want to calculate Age? The example of your requirement (which takes birthday into account) and your code (which respects only year) don't match, so it seems you owe us an explanation.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156