In report processing, can we use a TempDB table for a temporary data holder

(0) Share

Report

Posted on by Community Member

Hi all,

Need some advice on report creation in FO. During report creation using Report Data Provider, can we use another TempTable to hold data first, then in the QueryRun we fetch this TempTable to the TempTable for the report it self.

In detail is like this :

1. I create Table TmpRptA, this is a TempDB table for my report.

2. I also have Table TmpTable which is also a TempDB

3. In method Process Report, I have procedure to fill in this TmpTable first.

4. At queryRun, my query will get this TmpTable like below:

query = new query();

qbds = query.addDataSource(TableNum(TempTable));

queryRun = new QueryRun(query);

while (queryRun.next())

{

TempTable = queryRun.get(tableNum(TempTable));

TmpRptA.clear();

TmpRptA.fieldAA = TempTable.fieldAA;

TmpRptA.insert();

}

This is possible, right ? Because currently there is an error saying "An error occurred during report data sets execution", while as I tried to Debug, in the looping of QueryRun it self, it is not going inside of the loop, just like there is no record can be fetch from that "temporary' TempTable. But the process which populate this TempTable actually inserting some data, so it should have data in it.

Kindly advice

Thanks,

I have the same question (0)

All responses (10)

Answers (2)

nmaenpaa 101,162 Moderator on at

Like (0)

Report

If your error happens before the code goes inside queryRun, you should not even concentrate on your second table at this point, since it's clearly not involved in the error.

Why would you want to use two tables? If you already have the data im the first temp table, what would be the point of copying it to another temp table in the report class?

Could you share your business requirement and why you ended up on this path? This way we can concentrate on solving the actual requirement - which might be a completely different solution than what you are trying to do.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Nikolaos,

No, that error happened after my debug found out it doesn't goes inside queryRun, so when I press continue after it just pass by the QueryRun, the report looks like still processing and the error shows.

The 1st TempTable is actually the place whereby I collecting data from some of my "custom" transaction tables (around 6 custom tables). So I need to gather all necessary data first, doing some additional calculation first with some of its column and then after that my plan is to fetch this data to report temp table.

I think the only reason of doing this, is I want to collect all the tables into one table first before I bring that to report temp table. If I query all my tables like join all the tables, for my case it is quite difficult because these tables is in different structure, will be quite confusing just to fetch all that directly to report temp table. Does it make sense ?

Thanks,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

If it doesn't go inside queryRun.next() it means that there is no data in your temp table buffer. Perhaps you made some mistake there - please compare to standard reports that use temp table as data source.

If you use many temp tables, it's ok but I would handle it earlier than inside the query run. And I think the query should contain same temp table than what your report uses as data source. If you use some other tables while collecting data to the actual report temp table, that's ok. But perhaps it's easiest to just collect all data directly in the actual report temp table.

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report
Hi WWwong,

If you use temp table and query you need to pass cursor to query run object before queryRun.next call

queryRun.setRecord(tempTable); while (queryRun.next()) { ... }

www.axaptapedia.com/Temporary_tables

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ok, I will try to debug again and make sure the temp table buffer first.

Meanwhile, probably it will be best if I put the RDP class it self, just to make sure I'm doing it correctly, provided the data is wrong. So the logic (after I simplify and remove un necessary code, is like below:

[

SRSReportParameterAttribute(classstr(MyContractClass))

]

class MyRDPClass extends SrsReportDataProviderPreProcessTempDB

{

TempReportDB TempReportDB;

Name selectSite;

TempTable            TempTable;

[SRSReportDataSetAttribute(tablestr(TempReportDB))]

public TempReportDB getTempReportDB()

{

select TempReportDB;

return TempReportDB;

}

[SysEntryPointAttribute]

public void processReport()

{

MyContractClass contract;

Query query;

QueryRun queryRun;

QueryBuildDataSource qbds;

contract = this.parmDataContract() as MyContractClass;

selectSite = contract.parmSelectSite();

if (!selectSite || selectSite == "Asia")

{

              this.processSiteAsia();

}

      if (!selectSite || selectSite == "Europe")

{

             this.processSiteEurope();

}

       if (!selectSite || selectSite == "EMEA")

{

            this.processSiteEMEA();

}

query = new query();

qbds = query.addDataSource(TableNum(TempTable));

queryRun = new QueryRun(query);

while (queryRun.next())

{

TempTable = queryRun.get(tableNum(TempTable));

TempReportDB.clear();

TempReportDB.FieldAA = TempTable.FieldAA;

TempReportDB.FieldBB = TempTable.FieldBB;

TempReportDB.FieldCC = TempTable.FieldCC;

TempReportDB.insert();

}

}

public void processSiteAsia()

{

tblAsia tblAsia;

while select * from tblAsia

{

TempTable.clear();

TempTable.fieldAA = tblAsia.code;

TempTable.fieldBB = tblAsia.name;

TempTable.fieldCC = tblAsia.amountABC;

TempTable.insert();

}

}

public void processSiteEurope()

{

tblEurope tblEurope;

while select * from tblEurope

{

TempTable.clear();

TempTable.fieldAA = tblAsia.EUCode;

TempTable.fieldBB = tblAsia.name;

TempTable.fieldCC = tblAsia.amountEU;

TempTable.insert();

}

}

public void processSiteEMEA()

{

tblEMEA tblEMEA;

while select * from tblEMEA

{

TempTable.clear();

TempTable.fieldAA = tblEMEA.ID;

TempTable.fieldBB = tblEMEA.customer;

TempTable.fieldCC = tblEMEA.balanceamount;

TempTable.insert();

}

}

}

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yeah, I know I missed something, only I don't know what it is.

Thanks Sergei, it goes through QueryRun now.

However the error still persist. So after it "Loading.....", and it looks like the report wants to show suddenly that error "An error occurred during report data sets execution".

But I don't where to debug for that kind of error. I suspected it is related to the report it self. I redeploy it, but the error still. Maybe I should try to delete and create the report again from the scratch ?

Apart from Contract, UIBuilder, and RDP class, is the Controller class and then the Output menu item only, right ?

So the controller is simply like this :

class MyRptController extends SrsReportRunController

{

public static void main(Args _args)

{

MyRptController controller = new MyRptController();

controller.parmArgs(_args);

controller.parmReportName(ssrsReportStr(MyReport, AutoDesign1));

controller.parmShowDialog(true);

controller.startOperation();

}

}

What I still confuse is actually, in the Output menu item, what should be the object used ? is it this Controller, so Object type : Class and Object is MyRptController ? or Object type : SSRSReport and Object is MyReport ?

Initially I put the controller, but it has break error at the line controller.startOperation(); So I changed it, directly to the SSRSReport. Is this the cause of it ? Although I have other report using the same way (using SSRSReport specified in menu item) and it was running fine.

Thanks

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

Your menu item should point to the controller class.

If you get some error, you can share the error message with us and perhaps we can help.

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi WWwong,

Also, double-check that TempReportDB talbe has TempDB type. And try to restore data source on report and re-deploy it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sergei and Nikolaos,

Managed to resolved the error. I delete the report dataset and add again, Re-deploy and now it is fine.

About the menu item, what is actually the different by using Class or SSRSReport ? as I thought it was only with controller we can "directed" the design by coding ? For the error still happened however, so currently I switch back by using SSRSReport.

Here is the error :

Thanks for all the help and guidance.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report
If you use SSRS report as object in the menu item, it will call directly the report on SSRS server. But in this case your temp table has no data.

If you need to run x++ code (for example because you want to populate a temp table) you need to launch the controller class, not the report directly.

Please compare your code to existing reports that use temp table approach. There are plenty of them, for example Free text invoice.

Please check MS documentation. This is for AX2012 but is still perfectly valid:

1) Menu item for report: docs.microsoft.com/.../how-to-create-a-menu-item-for-a-report

If you define a SrsReportRunController class to run the report, set the ObjectType property to Class, and then select the controller class from the drop-down menu for the Object property.

2) Controller class: docs.microsoft.com/.../gg940296(v=ax.60)

The following list describes scenarios that require that you extend the SrsReportRunController class to create a custom controller for the report.

Report requires input data to be pre-processed.

Was this reply helpful? Yes No