Create a Report using Temporary Table

Here I have showed how to create a dynamics AX report using Temporary Table. My temporary table name is "HOLProfitLossSummaryTmp" which contain five fields called ItemCategoryId, bfpStructureID, Name, CostAmount, SecondaryAmount. This report grouped by Item category ID and Structure ID. Finally it has showed the Total value according to the Group.

//Declare a variable to store values
public class ReportRun extends ObjectRun
{
//For User Selection
DialogGroup dialogGroupPeriodCode;
DialogField dialogFromDate;
DialogField dialogToDate;
DialogField dialogStructureID;
DialogField dialogItemCategoryID;
DialogField dialogDepartment;
DialogField dialogSelect;
IssueReturn USelection;
str Selection;
TransDate fromDate;
TransDate toDate;
ItemCategoryId UCategoryId;
str UCatogory;
bfpStructureId UStructureId;
Dimension UDepartment;
str Department1;

//For Data Filling to tempory Table
str StructureID;
ItemCategoryId CategoryID;
real LocalAmount,SecondaryAmount;
real LocalTotal,SecondaryTotal,LocalGrandTotal,SecondaryGrandTotal;
HOLProfitLossSummaryTmp HOLProfitLossSummaryTmp;
}
//Show dialog Fields in Parameter Form to user Selection
public Object dialog(Object _dialog)
{
DialogRunbase dialog = _dialog;
;
dialogGroupPeriodCode = dialog.addGroup("Parameter");
dialogItemCategoryID= dialog.addFieldValue(typeid(ItemCategoryId), UCategoryId,"Item Category ID", "Item Category ID");
dialogStructureID= dialog.addFieldValue(typeid(bfpStructureId), UStructureId,"Structure ID", "Structure ID");
dialogGroupPeriodCode = dialog.addGroup("Date Interval");
dialogFromDate = dialog.addFieldValue(typeid(TransDate),fromDate, "From Date", "From Date");
dialogToDate = dialog.addFieldValue(typeid(TransDate),toDate, "To date","To date");
dialogGroupPeriodCode = dialog.addGroup("Transaction Type");
dialogSelect = dialog.addFieldValue(typeid(IssueReturn), USelection,"Transaction Type", "Transaction Type");
return dialog;
}

//Getting Values from User
public boolean getFromDialog()
{
;
fromDate = dialogFromDate .value();
toDate = dialogToDate .value();
UCatogory = dialogItemCategoryID.value();
UStructureId = dialogStructureID.value();
Selection = dialogSelect.value();
return true;
}

//Return Exchange Rate according to the Date & Company
AmountMST ExchangeValue(TransDate TranDate)
{
CompanyInfo CompanyInfo;
HOLExchRates HOLExchRates;
;
HOLExchRates=new HOLExchRates();
select CompanyInfo where CompanyInfo.dataAreaId==InventJournalTrans.dataAreaId;
Return HOLExchRates.find2(CompanyInfo.SecondaryCurrencyCode,TranDate);
}
//Send filtered data to the report using Temporary Table
public boolean fetch()
{
boolean ret=false;
QueryRun qr;
InventTable InventTable1;
InventJournalTrans InventJournalTrans1;
Boolean isReportEmpty;
ExchRate Ex;
;

//Initialize Variables
CategoryId=0;
StructureID='';
LocalAmount=0;
SecondaryAmount=0;
LocalTotal=0;
SecondaryTotal=0;
LocalGrandTotal=0;
SecondaryGrandTotal=0;
isReportEmpty=true;

//Filterring Query according to the User Selection
qr = new Queryrun(element.query());
//Structure ID
if (UStructureId!="")
qr.query().dataSourceTable(tablenum(InventTable)).addRange(fieldnum(InventTable,bfpStructureId)).value(UStructureId);
//Item Category
if (UCatogory!='0')
qr.query().dataSourceTable(tablenum(InventTable)).addRange(fieldnum(InventTable,ItemCategoryId)).value(UCatogory);
//Date Range
if (fromDate || toDate)
qr.query().dataSourceTable(tablenum(InventJournalTrans)).addRange(fieldnum(InventJournalTrans,TransDate)).value(queryRange(fromDate, toDate));
//Transaction Type (Selection=0 : "Issue" , Selection=1 : "Return" ,Selection=2 : "All" )
if (Selection=='0')
qr.query().dataSourceTable(tablenum(InventJournalTrans)).addRange(fieldnum(InventJournalTrans,Qty)).value('<0');
else if (Selection=="1")
qr.query().dataSourceTable(tablenum(InventJournalTrans)).addRange(fieldnum(InventJournalTrans,Qty)).value('>0');
element.queryRun(qr);

while (qr.next())
{
InventTable1=qr.get(tablenum(InventTable));
InventJournalTrans1=qr.get(tablenum(InventJournalTrans));
//If Category Id Change print Totals & new Category ID
if ((CategoryId!=InventTable1.ItemCategoryId) && (InventTable1.ItemCategoryId!=0))
{
if (CategoryId!=0)
{
HOLProfitLossSummaryTmp.CostAmount=LocalAmount;
HOLProfitLossSummaryTmp.SecondaryAmount=SecondaryAmount;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
//Send Item Category Total to the report
HOLProfitLossSummaryTmp.TotalLocalAmount=LocalTotal;
HOLProfitLossSummaryTmp.TotalSecondaryAmount=SecondaryTotal;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
LocalGrandTotal+=LocalTotal;
SecondaryGrandTotal+=SecondaryTotal;
LocalAmount=0;
LocalTotal=0;
SecondaryAmount=0;
SecondaryTotal=0;
StructureID='';
}
CategoryId=InventTable1.ItemCategoryId;
HOLProfitLossSummaryTmp.ItemCategoryId=CategoryId;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
}
//If Structure Id Change print Totals & new Structure Details
if ((StructureID!=InventTable1.bfpStructureId) && (InventTable1.ItemCategoryId!=0))
{
if (StructureID!='')
{
HOLProfitLossSummaryTmp.CostAmount=LocalAmount;
HOLProfitLossSummaryTmp.SecondaryAmount=SecondaryAmount;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
LocalAmount=0;
SecondaryAmount=0;
}
StructureID=InventTable1.bfpStructureId;
HOLProfitLossSummaryTmp.bfpStructureId=StructureID;
HOLProfitLossSummaryTmp.Name=this.StructureName();
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
}
//Calculate Primary & Secondary Values
if ( (CategoryId!=0) && (StructureID!=''))
{
LocalAmount+=InventJournalTrans1.CostAmount;
LocalTotal+=InventJournalTrans1.CostAmount;
Ex=this.ExchangeValue(InventJournalTrans1.TransDate);
if (Ex>0)
{
SecondaryAmount+=(InventJournalTrans1.CostAmount/Ex);
SecondaryTotal+=(InventJournalTrans1.CostAmount/Ex);
}
}
}
LocalGrandTotal+=LocalTotal;
SecondaryGrandTotal+=SecondaryTotal;
//Set Report is empty or Not
if (LocalGrandTotal!=0)
isReportEmpty=false;
//Print Grand Values
if (!isReportEmpty)
{
HOLProfitLossSummaryTmp.CostAmount=LocalAmount;
HOLProfitLossSummaryTmp.SecondaryAmount=SecondaryAmount;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();

HOLProfitLossSummaryTmp.TotalLocalAmount=LocalTotal;
HOLProfitLossSummaryTmp.TotalSecondaryAmount=SecondaryTotal;
this.send(HOLProfitLossSummaryTmp);
HOLProfitLossSummaryTmp.clear();
this.send(HOLProfitLossSummaryTmp);
this.send(HOLProfitLossSummaryTmp);
//Print Grand Total
element.execute(1);
}
super();
return true;
}

//Display Structure Name
Display Name StructureName()
{
bfpInventStructureId bfpInventStructureId;
select bfpInventStructureId where bfpInventStructureId.StructureId==HOLProfitLossSummaryTmp.bfpStructureId;
return bfpInventStructureId.Name;
}

//Display DatePage
//BP Deviation documented
display str 70 datePage()
{;
// Date %1 %2 Page %3
return strfmt(element.design().lookupLabel(literalstr("Date ")),element.startDate(),
time2str(element.startTime(),-1,-1),element.page());
}

//Display Company Logo
//BP Deviation documented
display Bitmap CompanyLogo()
{
CompanyInfo companyInfo = CompanyInfo::find();
;
return CompanyImage::find(companyInfo.DataAreaId, companyInfo.TableId,companyInfo.RecId).Image;
}

//Display Start Date
//BP Deviation documented
display PrintDate startDate()
{;
return element.startDate();
}

//Display Page No
//BP Deviation documented
display str 20 pageNum()
{;
//Page %1
return strfmt(element.design().lookupLabel(literalstr("Page ")), element.page());
}

//Display Company Name
//BP Deviation documented
display Name CompanyName()
{;
return CompanyInfo::name();
}

//Display Report Name
//BP Deviation documented
display Name reportName()
{;
return element.design().lookupCaption();
}

//Display Start Time
//BP Deviation documented
display timeOfDay startTime()
{;
return element.startTime();
}

//Display Grand Total from Secondary Currency
//BP Deviation documented
Display real GrandSecondary()
{
Return SecondaryGrandTotal;
}

//Display Grand Total from Primary Currency
//BP Deviation documented
Display real GrandLocal()
{
return LocalGrandTotal;
}