emeadaxsupport: Code example: how to export transactions with default dimensions to Excel

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...1/dim2xls.aspx
==============

We recently had a support request where customer wanted to see all fixed asset transactions with financial dimensions in an Excel file. The code below solved the problem. I hope this code example can help people who are thinking of creating a simple xls report or want to see all default dimension values with transactions in one table.

Problem: On the fixed asset transaction form (Fixed assets -> Inquiries -> Fixed asset transactions) you will see the tab 'Financial dimensions' but it is not possible to personalize it so that the dimensions will appear on the Overview tab so that we could export/copy them to excel together with the rest of the data from the Overview tab. So, in short, we need to see fixed asset transactions together with the dimensions either in a report (that we can then export to excel) or on the fixed asset transactions form (Fixed assets -> Inquiries -> Fixed asset transactions) so that we can export this data to excel.

Solution: The following code was proposed that exports all fixed asset transactions to an Excel file together with all dimensions. Because the set of dimensions can be different on different transactions, we're adding dimension columns dynamically in the loop.

public static void main(Args _args)
{
AssetTrans assetTrans;
SysExcelApplication application;
SysExcelWorkBooks workbooks;
SysExcelWorkBook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
DimensionAttributeValueSetItemView dimAttrSet;
DimensionAttribute dimAttr;
str dimAttrStr;
Map dims;
int dimNum;
;
application = sysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');

dims = new Map(Types::String, Types::Integer);

//generate header
row++;
cell = cells.item(row, 1);
cell.value("Voucher");
cell = cells.item(row, 2);
cell.value("Transaction date");
cell = cells.item(row, 3);
cell.value("Fixed asset number");
cell = cells.item(row, 4);
cell.value("Transaction type");
cell = cells.item(row, 5);
cell.value("Amount");
cell = cells.item(row, 6);
cell.value("Fixed asset group");

//generate lines
while select assetTrans
//The following loop will provide the data to be populated in each column
{
row++;
//add fixed asset trans data
cell = cells.item(row,1);
cell.value(assetTrans.Voucher);
cell = cells.item(row,2);
cell.value(assetTrans.TransDate);
cell = cells.item(row,3);
cell.value(assetTrans.AssetId);
cell = cells.item(row,4);
cell.value(enum2str(assetTrans.TransType));
cell = cells.item(row,5);
cell.value(assetTrans.AmountCur);
cell = cells.item(row,6);
cell.value(assetTrans.AssetGroup);

// add dimensions
while select dimAttrSet
where dimAttrSet.DimensionAttributeValueSet == assetTrans.DefaultDimension
join Name from dimAttr
where dimattr.RecId == dimAttrSet.DimensionAttribute
{
if (!dims.exists(dimAttr.Name)) // if dim column does not exists
{
//add dimension column
dims.insert(dimAttr.Name, dimNum + 7);
dimNum++;
cell = cells.item(1, dims.lookup(dimAttr.Name));
cell.value(dimAttr.Name);
}
//add dimension value
cell = cells.item(row, dims.lookup(dimAttr.Name));
cell.value(dimAttrSet.DisplayValue);
}
}
application.visible(true); // opens the excel worksheet
}
Disclaimer. “Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This mail message assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures."



As a result we get the following excel file with the columns on the right showing all dimensions.


This example can be used for any other transaction types where default dimension exists - LedgerJournalTrans, CustTrans or VendTrans. You'd need to change the table and fields names in the code. Theoretically, you can even add button 'Export to Excel' to a form and pass the form's query as an argument to the class.

Have a nice day,
Roman




Источник: http://blogs.msdn.com/b/axsupport/ar...1/dim2xls.aspx