RDP SSRS Report (SRSReportDataProviderBase) Data Source join with Temp DB

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (20)

Answers (1)

Martin Dráb 238,782 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

What query you show to users and what data you generate in processReport() are generally two different things, because you can do anything you like in processReport().

First of all, please use the debugger to check whether your code in processReport() works as expected. You didn't share the code, so I can't comment on it, but maybe you try to iterate the query with no data, so it actually doesn't prepare anything for the report.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Thanks for your time to check my post. Please find below example in details.

Note: If i change the table type from TempDB to Regular, it will be working fine.

Query's DataSource

-> CustTable InnerJoin CustAge where CustTable.birthyear = CustAge.birthyear

Example Report selected SysQuery parameters & value

-> Age = 20..30

ProceesReport method Inside report's DP class

[SysEntryPointAttribute]

public void processReport()

{

Query q;

QueryRun qr;

QueryBuildDataSource qbds;

CustTable cTbl;

CustAge AgeTblTmp;

QC_Temp tmpTbl;

Int i;

;

q = new Query(this.parmQuery());

qr = new QueryRun(q);

qbds = q.dataSourceTable(tableNum(CustTable));

ttsBegin;

for (i=1900;i<=2050;i++)

{

ageTblTmp.clear();

select forupdate ageTblTmp;

ageTblTmp.birthyear= i;

ageTblTmp.age = year(systemdateget()) - i;

ageTblTmp.insert();

}

while (qr.next())

{

cTbl2 = qr.get(tableNum(CustTable));

tmpTbl.accountnum = cTbl2.accountnum;

tmpTbl.birthyear = cTbl2.birthyear;

}

update_recordSet tmpTbl setting age = ageTblTmp.age

join ageTblTmp where ageTblTmp.birthyear = tmpTbl.birthyear;

ttsCommit;

}

Expected Result

AccountNum BirthYear Age

John 1996 21

Michael 1995 22

Mike 1994 23

Siva 1993 24

Enrique 1992 25

Leonardo 1991 26

Julio 1990 27

Jose 1989 28

Luciano 1988 29

Marquez 1987 30

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 238,782 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

If the query really contains an inner join to a temporary table (while you don't provide any data to it), you'll find (when you actually debug it) that your code in while (qr.next()) block will never execute.

The code that does execute looks suspicious as well. What's the point of 150 times calling select forupdate ageTblTmp; without any condition? It must be a bug. I think the whole line shouldn't be there.

If I look at your expect result, it seems that all you need is filling a temporary table with three values (AccountNum, BirthYear and (current year - BirthYear)), so there is no need for any strange for loop, any update_recordset or anything like that.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Martin. My main purpose is to allow user to be able to use Advanced Query to filter by "Age".

That is the reason i created a new Temp Table to store a temporary "Age" data for QueryRun to inner join it.

This is just a sample towards my more complexity logic in using tempDB in SSRS Reports.

Would there be a way for TempDB data to be inserted and use for qr.next(), appreciate it.

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

Like (0)

Report
Copy link

Link copied!
Great, it seems that you agree that your code in the while loop doesn't run at all, because you never set any data to the temporary buffer used in the query. What you insert into ageTblTmp variable has no effect, because it's not used in the query.

I'll also assume that you agree that select forupdate ageTblTmp; was a bug, so I'll throw it away, together with other code that isn't used. After some other improvements, your code will now looks like this:

public void processReport() { Query q = new Query(this.parmQuery()); QueryRun qr; CustAge ageTblTmp; QC_Temp tmpTbl; int i; ttsBegin; for (i = 1900; i <= 2050; i++) { ageTblTmp.clear(); ageTblTmp.BirthYear = i; ageTblTmp.Age = year(systemDateGet()) - i; ageTblTmp.insert(); } qr = new QueryRun(q); while (qr.next()) { cTbl2 = qr.get(tableNum(CustTable)); tmpTbl.AccountNum = cTbl2.AccountNum; tmpTbl.BirthYear = cTbl2.BirthYear; } update_recordSet tmpTbl setting Age = ageTblTmp.Age join ageTblTmp where ageTblTmp.BirthYear = tmpTbl.BirthYear; ttsCommit; }

You could pass the temporary buffer to QueryRun by setCursor(), but first tell me what kind of ranges do you need for Age. Maybe we can simply use a range on BirthYear (e.g. BirthYear > current year - parameter). It would be much more efficient that creating records from every year from 1900 to 2050.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

The age ranges criteria for Advanced Query filtering would be quite sensitive on it's birthday
Example:
1. Today: 01-Aug-2017, DateOfBirth: 02-Aug-1997. The Age will be 19
2. Today: 02-Aug-2017, DateOfBirth: 02-Aug-1997. The Age will be 20

The customer have a field of "DateOfBirth" which i use it as a field to calculate the "Age" in Temp Table.
I am thinking filtering by Age would be easier for users to use to generate their business something like monthly promotion list for certain Age range on specific date.

I just tried using the qr.setCursor() function but no data shown yet, is it somehow i misplaced the code, appreciate it.

public void processReport()
{
Query q = new Query(this.parmQuery());
QueryRun qr;
CustAge ageTblTmp;
QC_Temp tmpTbl;
int i;

ttsBegin;

for (i = 1900; i <= 2050; i++)
{
ageTblTmp.clear();
ageTblTmp.BirthYear = i;
ageTblTmp.Age = year(systemDateGet()) - i;
ageTblTmp.insert();
}

qr.setCursor(ageTblTmp);

qr = new QueryRun(q);

while (qr.next())
{
cTbl2 = qr.get(tableNum(CustTable));
tmpTbl.AccountNum = cTbl2.AccountNum;
tmpTbl.BirthYear = cTbl2.BirthYear;
}

update_recordSet tmpTbl
setting Age = ageTblTmp.Age
join ageTblTmp
where ageTblTmp.BirthYear = tmpTbl.BirthYear;

ttsCommit;
}

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

Like (0)

Report
Copy link

Link copied!

Can you please elaborate your requirements about filtering? Your code doesn't support the example, because it doesn't work with date at all. If you're interested just in people having birthday in a given day, you should create a computed column returning birthday and filter it by the date you're interested in (such as systemDateGet() as in your code).

Your code should throw an error on the new line, complaining about you using an uninitialized variable, because it's exactly what you're doing. You're creating the object a line below where you're calling setCursor(). If the error message isn't clear enough, looking at value in debugger would make it obvious. Make sure you learn how to use it.

But I strongly recommend you stop playiong with setCursor() and use the time to explain your requirements, so we can suggest a more sensible solution to you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

My intention would be to allow user to filter by "Age".

I have tried to perform the Debugger inside the ProcessReport method.

1. The ageTmpTbl "TempDB" table have the value inside, when i use the code of qr.setCursor(ageTmpTbl)

-> qr have the correct query as per expected

-> ageTmpTbl shows value of RecId = 1

But upon the report is generated it shows empty records.

I would really like to know the way to use setCursor() for DataSource inside RDP Reports, appreciate if there are some solutions for us to try, appreciate it.

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

Like (0)

Report
Copy link

Link copied!

Yes, I know which value you want to filter, but I still don't know how. Please explain what filters users need to set against Age.

If qr has a value when you call setCursor(), your actual code isn't what you've shown above, because there you clearly doesn't set any value of qr before the call.

That a report shows nothing may mean that you don't provided correct data (therefore you must fix the RDP class and you can ignore the report), or you prove that your RDP class works and you must look for bugs in the report. If you don't isolate the problem, you don't even know which component is failing. Therefore I suggest you ignore the report for now and focus on testing the class.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

The filter need to be set by user in here i would prefer "Age" and value range of 21..30 (Prefer not to use birthday or birthyear).

The printed report will shows records if i set the AgeTmpTbl table type to "Regular".

I highly believe it is caused by my code where somehow i couldn't supply the ageTmpTbl data into the report "DataSource".

And when the queryrun is running and tried to inner join to AgeTmpTbl (inside the data is empty) and the report will shows empty

i believe if only i can set the temp table data into the queryRun it will be working as same as "Regular" table but i couldn't find a way to do so :(

Was this reply helpful? Yes No