Microsoft Dynamics AX (Archived)

How to get the data in to the excel sheet from ssrs report

(0) Share

Report

Posted on by srinivas pamidi

1,737

I want to fetch the data of Actuals vs budget report in to excel sheet.

It is Possible to get the data in to the excel sheet ?

i am already check with the in build feature in SSRS report after opening the report converted to excel it is not fulfill my requirement.

can i write the code in the DP class and fetch the data to the excel sheet in my own format?

*This post is locked for comments

I have the same question (0)

All responses (19)

Answers (1)

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

Like (0)

Report

You don't have to add code to DP class - you can likely use it without any change. What DP classes do is that they return data in (usually temporary) tables, so you can take the data and do anything with it, such as exporting to Excel.

It would be also interesting to know what isn't fulfilling your requirements when saving the report to Excel; maybe there is a solution for it.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi srinivas pamidi,

You can write Code in DP, whatever you want format in excel you can print the data directly to Excel.

whatever your own format you can full fill your requirement.

this is my past Experience i was did this.
If you want farther help like code, please let me know

Thanks,

Was this reply helpful? Yes No
srinivas pamidi 1,737 on at

Like (0)

Report

Hi Suresh,

Thank you for your help.

Please find the below screen shots for further understanding.

After Giving all parameters in SSRS standard report the report is looks like.

I want my own format in excel sheet, if it is possible to get the both excel and ssrs report i want both other wise i want only excel report.

My own excel report is looks like this.

In Ax the navigation for open this report is

Budgeting->Reports->Basic Budgeting-Actual Vs budget

In AOT these are the details of this report

Table-BudgetTmpBalance

Class-BudgetBalancesActualsContract

BudgetBalancesActualsController

BudgetBalancesActualsDP.

Can you Please Send the Code to get the data in to the excel sheet.

Was this reply helpful? Yes No
srinivas pamidi 1,737 on at

Like (0)

Report

Hi Martin,

Main Problem i am facing after exporting the report in to excel i want split in the ledger dimension.

i wan to display the report with seperate ledger dimension and name of the ledger dimension.

Was this reply helpful? Yes No
srinivas pamidi 1,737 on at

Like (0)

Report

Hi Suresh,

Please share the sample code for how to get the data in to excel sheet

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi srinivas pamidi,

I'm sharing the code for Data to Excel, please try this.

QueryRun                    queryRun;
    Query                       query;// = new Query();
    SSrsRdpContractSales        contract;
    InventSiteId                siteID;
    Commaio file;
    QueryBuildRange qbr;
    container line,header;
    HcmWorker hcmWorker;
     SysExcelApplication xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   int                  row = 1;
   str                  fileName;
    //#define.filename("C:\\DispatchDetailsNew.csv") //if we need text file to be genetated: use “.txt” as extension for the file”
    #define.filename("D:\\Callup details\\ALL ERP reports\\DispatchExport.csv")
    #File

    void uploadToExcel()
    {
        xlsApplication           = SysExcelApplication::construct();
       //Open Excel document
       //xlsApplication.visible(true);
       //Create Excel WorkBook and WorkSheet
       xlsWorkBookCollection    = xlsApplication.workbooks();
       xlsWorkBook              = xlsWorkBookCollection.add();
       xlsWorkSheetCollection   = xlsWorkBook.worksheets();
       xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

        fileName ="D:\\Callup details\\ALL ERP reports\\DispatchReportExport_New.xlsx";

        xlsWorkSheet.cells().item(row,1).value("Invent Site Id");
        xlsWorkSheet.cells().item(row,2).value("Invoice Date");

        row++;
        while select salesReqTmp
        {

            xlsWorkSheet.cells().item(row,1).value(salesReqTmp.InventSiteId);
            xlsWorkSheet.cells().item(row,2).value(salesReqTmp.InvoiceDate);


            row++;
        }
        //Check whether the document already exists
        if(WinApi::fileExists(fileName))
            WinApi::deleteFile(fileName);
        //Save Excel document
        xlsWorkbook.saveAs(fileName);
        //Open Excel document
        xlsApplication.visible(true);
        info("Export completed");

    }
uploadToExcel();

}

If you have still any doubts, please free to ping us again.

This is just a suggestion.

Thanks,

Was this reply helpful? Yes No
srinivas pamidi 1,737 on at

Like (0)

Report

Hi Suresh,

It is working fine.

But in this report i am facing one problem to split the ledger dimension

suppose

dimensionFocus = budgetTmpBalanceLocal.DimensionFocus;

in this line the dimension focus is coming like this.

"14240301-00000002-00000021-100020--"

It is posssible to split the ledger dimension like this

14240301 -main account

00000002 - business unit

00000021 - department

100020 - worker

and get the each dimension description

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Dear srinivas pamidi ,

static void DimensionSplitting(Args _args)
{
GeneralJournalAccountEntry generalJournal;
DimensionAttributeLevelValueAllView dimAttrView;
DimensionAttribute dimAttr;
int i;

setPrefix("Ledger dimension Splitting");
while select generalJournal
{
i++;
if (i > 10)
break;

setPrefix(int2str(i) + ". " + DimensionAttributeValueCombination::find(generalJournal.LedgerDimension).DisplayValue);
while select DisplayValue from dimAttrView
where dimAttrView.ValueCombinationRecId == generalJournal.LedgerDimension
join BackingEntityType from dimAttr
where dimAttr.RecId == dimAttrView.DimensionAttribute
{
switch (dimAttr.BackingEntityType)
{
case tableNum(DimAttributeMainAccount):
info(strFmt("Main Account: %1", dimAttrView.DisplayValue));
break;

case tableNum(DimAttributeOMBusinessUnit):
info(strFmt("Business Unit: %1", dimAttrView.DisplayValue));
break;

case tableNum(DimAttributeCustTable):
info(strFmt("Customer: %1", dimAttrView.DisplayValue));
break;

case tableNum(DimAttributeOMDepartment):
info(strFmt("Department: %1", dimAttrView.DisplayValue));
break;

case tableNum(DimAttributeHcmWorker):
info(strFmt("Worker: %1", dimAttrView.DisplayValue));
break;
}
}
}

}

try this, this is just suggestion.

Thanks,

Was this reply helpful? Yes No
srinivas pamidi 1,737 on at

Like (0)

Report

Hi Suresh,

I am try this code in the job it is working in dp class but how to map this code to the current dimension focus.

I am write the below code, but relation is not find

dimensionFocus = budgetTmpBalanceLocal.DimensionFocus;

while select generalJournal

where generalJournal.LedgerAccount == dimensionFocus

{

i++;

if (i > 10)

break;

while select DisplayValue from dimAttrView

where dimAttrView.ValueCombinationRecId == generalJournal.LedgerDimension

join BackingEntityType from dimAttr

where dimAttr.RecId == dimAttrView.DimensionAttribute

{

switch (dimAttr.BackingEntityType)

{

case tableNum(DimAttributeMainAccount):

mainAccount = dimAttrView.DisplayValue;

break;

case tableNum(DimAttributeOMBusinessUnit):

businessUnit = dimAttrView.DisplayValue;

break;

case tableNum(DimAttributeOMDepartment):

businessUnit = dimAttrView.DisplayValue;

break;

case tableNum(DimAttributeHcmWorker):

businessUnit = dimAttrView.DisplayValue;

break;

}

}

}

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi srinivas pamidi,

I never Tried this in Excel. But You try to achieve this excel code which we discussed.

Thanks,

Was this reply helpful? Yes No