SSRS - RDP with multiple temp table

(0) Share

Report

Posted on by stekbi

Hi people,

I'm building a RDP based SSRS report and I'm facing an issue.

In my DP class, I use 2 table to get my data (classical Header + Lines). The problem is if I set my both table as temp table or both as Regular, my report is not displaying all datas. If I set 1 table as temp and the other as regular, everythink is working fine.

My questions are :

Is it possible to use 2 temp table with a RDP extending SRSReportDataProviderBase ? (prefered solution, easier and faster to build)

Or do I need to extend SrsReportDataProviderPreProcess and manage this with regular table and transId as SalesOrderReport for exemple ?

thank you for you advice,

Stek

AX 2012 R2

I have the same question (0)

All responses (12)

Answers (2)

Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

Yes, using InMemory tables is how it should be done. I'm sure you would find many examples in the standard application.

Therefore your problem must lie somewhere else.

How do you use the data in the form? What if create a simple design with two tables - first print everything from the headers data set and then the lines data set (without trying to relate them)? If you get the data, you'll know that the RDP class is fine and the problem in your actual design. If it doesn't work either, we'll at least know that and we'll have to think more about the cause.

Was this reply helpful? Yes No
stekbi 40 on at

Like (0)

Report

Thank you Martin for your fast answer.

I did a misstake when I expose my case. My 2 tables are not related.

Header table has only one record anyway.
[quote]How do you use the data in the form?[/quote]
I think you wanna say "report" instead of "form" ? Or I misunderstood your explanation.

My design is already as simple as you said.

Note that I've noticed when testing with both table as Regular that some fields in one of the table are not filled.(EDIT : I forgot I commented my code. It work fine with regular table too) But if I use the 1 temp 1 regular combo, all fields are filled fine.

Again thank you for helping and sorry for bad english

Was this reply helpful? Yes No
stekbi 40 on at

Like (0)

Report
I noticed that the temp table which is not filled is random. Sometime it's the HeaderTable, sometimes it's the LinesTable. I really dont understand what's happening.

Here is a job I use to test my RDP. got the same problem when executing the job or the report. So the problem might be in my classes.

static void job11(Args _args) { DLZvendorReminderDP rdp; DLZvendorReminderContract contract; DLZvendorReminderHeaderTmp headerData; DLZvendorReminderLineTmp lineData; contract = new DLZvendorReminderContract(); contract.parmDelayDate(mkDate(14, 2, 2020)); contract.parmVendId('00005'); rdp = new DLZvendorReminderDP(); rdp.parmDataContract(contract); rdp.processReport(); headerData = rdp.getDLZVendorReminderHeaderTmp(); while select headerData { info(strFmt("%1 – %2", headerData.ReminderID, headerData.IntroMessage)); } lineData = rdp.getDLZVendorReminderLineTmp(); while select lineData { info(strFmt("%1 - %2", lineData.PurchId, lineData.LineNumber)); } info("fin"); }

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

Like (0)

Report

Sorry, I meant a report. All right, so now we know that the problem isn't anywhere in the report design, but it's in processReport(). Therefore you should debug processReport().

Was this reply helpful? Yes No
stekbi 40 on at

Like (0)

Report
Here is my DP simplified as much as possible.

[ SRSReportQueryAttribute(queryStr(PurchLineBackOrder)), SRSReportParameterAttribute(classstr(DLZvendorReminderContract)) ] class DLZvendorReminderDP extends SRSReportDataProviderBase { DLZvendorReminderContract contract; DLZvendorReminderLineTmp vendorReminderLineTmp; DLZvendorReminderHeaderTmp vendorReminderHeaderTmp; Query q; VendAccount vendId; DlvDate delayDate, projectedDelayDate; }

[ SRSReportDataSetAttribute(tableStr(DLZvendorReminderHeaderTmp)) ] public DLZvendorReminderHeaderTmp getDLZVendorReminderHeaderTmp() { select vendorReminderHeaderTmp; return vendorReminderHeaderTmp; } [ SRSReportDataSetAttribute(tableStr(DLZvendorReminderLineTmp)) ] public DLZvendorReminderLineTmp getDLZVendorReminderLineTmp() { select vendorReminderLineTmp; return vendorReminderLineTmp; }

[ SysEntryPointAttribute(false) ] public void processReport() { this.setDataTest(); } public void setDataTest() { RecordInsertList recInsList; int i; // HEADER vendorReminderHeaderTmp.clear(); vendorReminderHeaderTmp.ReminderID = 'ReminderID'; vendorReminderHeaderTmp.ReminderDate = today(); vendorReminderHeaderTmp.CompanyName = 'CompanyName'; vendorReminderHeaderTmp.CompanyAddress = 'CompanyAddress'; vendorReminderHeaderTmp.CompanyPhone = 'CompanyPhone'; vendorReminderHeaderTmp.CompanyTeleFax = 'CompanyTeleFax'; vendorReminderHeaderTmp.CompanyEmail = 'CompanyEmail'; vendorReminderHeaderTmp.CompanyURL = 'CompanyURL'; vendorReminderHeaderTmp.CompanyCoRegNum = 'CompanyCoRegNum'; vendorReminderHeaderTmp.CompanyEnterpriseNumber = 'CompanyEnterpriseNumber'; vendorReminderHeaderTmp.PrintLogo = NoYes::No; vendorReminderHeaderTmp.CompanyContactName = 'CompanyContactName'; vendorReminderHeaderTmp.VendorContactName = 'VendorContactName'; vendorReminderHeaderTmp.VendName = 'VendName'; vendorReminderHeaderTmp.VendAddress = 'VendAddress'; vendorReminderHeaderTmp.IntroMessage = 'IntroMessage'; vendorReminderHeaderTmp.OutroMessage = 'OutroMessage'; vendorReminderHeaderTmp.insert(); // LINES recInsList = new RecordInsertList(tableNum(DLZvendorReminderLineTmp)); for (i = 1; i <= 20; i ) { vendorReminderLineTmp.clear(); vendorReminderLineTmp.PurchId = strFmt('PurchId %1', i); vendorReminderLineTmp.LineNumber = i; vendorReminderLineTmp.ItemId = strFmt('ItemId %1', i); vendorReminderLineTmp.InventDimStr = 'InventDimStr'; vendorReminderLineTmp.ItemNameAlias = 'ItemNameAlias'; vendorReminderLineTmp.ExternalItemNum = 'ExternalItemNum'; vendorReminderLineTmp.QtyOrdered = i; vendorReminderLineTmp.RemainPurchPhysical = i; vendorReminderLineTmp.PurchUnit = 'PurchUnit'; vendorReminderLineTmp.DeliveryDate = today(); vendorReminderLineTmp.ConfirmedDlv = today(); vendorReminderLineTmp.DLZPurchLineDelayStatus = DLZPurchLineDelayStatus::Delay; recInsList.add(vendorReminderLineTmp); } recInsList.insertDatabase(); }

My both table are tempDB and here is my result:

What i expect: (1 tempDB 1 regular)

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

Like (0)

Report

Hmm, so you're now using TempDB tables? Can you please confirm whether you're using only TempDB tables (not InMemory temporary tables) from the beginning?

Was this reply helpful? Yes No
stekbi 40 on at

Like (0)

Report

I tried with InMemory too but actualy, I confirm using tempDB.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

Both types (InMemory and TempDB) should work, but it's good to know which case we're discussion.

I think the problem is related to the RecordInsertList. You return data from vendorReminderLineTmp buffer, but you've never inserted anything there. You just use the variable to provide input for RecordInsertList.

Maybe it can be done by passing the temporary buffer to the 7th argument of RecordInsertList's constructor, but I would first ask myself whether using RecordInsertList has any benefit in this case.

Was this reply helpful? Yes No
stekbi 40 on at

Like (0)

Report

[quote]You return data from vendorReminderLineTmp buffer, but you've never inserted anything there. You just use the variable to provide input for RecordInsertList.[/quote]
I dont get it. What's wrong with RecordInsertList ? I've always used it this way.

But anyway, the problem persist even if I use simple Insert().

Ok after cleaning all cache, restart AOS and SSRS, it's seems that it's working while inserting record by record.

My new questions are :

Does TempDB / InMemory table support RecordInsertList ?

Is my utilisation of RecordInsertList correct ? If yes, how can I identify in the future that's RecordInsertList causing problem ? (refering to the fact that worked with regular table)
[quote]whether using RecordInsertList has any benefit in this case.[/quote]
Just going for a single trip database insert so it's a benefit, no ?

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

Like (1)

Report

Can you show your current code? How do you test it? The statement that the kernel of AX is broken and insert() method doesn't work at all on temporary tables doesn't sound likely.

The assumption that RecordInsertList always reduces the number of DB calls isn't precise. In certain cases, it still processes records one by one (if insert() method is overriden, InMemory table is used and so on). Therefore checking if there is any benefit still makes sense.

For completeness, RecordInsertList often makes more than just one DB call - it would require holding all records in memory. Instead, it inserts records in batches.

Was this reply helpful? Yes No