Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Suggested answer

adding columns dynamically to the excel template

Posted on by 768

Hello everyone,

I'm trying to develop a report based on the excel template. I use standard class XMLExcelReport_RU. I need to do one unusual thing I didn't before. I have created a temporary table with the info I'll need in my report. It contains records with the date field. Some records will contain the same value for the date field. I need somehow add columns with unique dates from the temporary table.

this is the code where I fill up my temporary table:

protected void createReport()
{
    int i = 1;
    this.initSectionMap();

    this.execute(#HEADER);
    
    while(qr.next())
    {
        smmActivityParentLinkTable = qr.get(tableNum(SMMActivityParentLinkTable));
        smmActivities              = qr.get(tableNum(smmActivities));
        tsTimeSheetLine            = qr.get(tableNum(tsTimeSheetLine));
        tsTimesheetLineWeek        = qr.get(tableNum(tsTimesheetLineWeek));
        
        
        for (i=1; i<=7; i =1)
        {
            if(tsTimesheetLineWeek.Hours[i])
            {
                taskDaysFactTmp_ICL.ActivityType        = smmActivities.ICLPRJDevTaskActivityType;
                taskDaysFactTmp_ICL.ResponsibleWorker   = HcmWorker::worker2Name(smmActivities.ResponsibleWorker);
                taskDaysFactTmp_ICL.ActivityDate = tsTimesheetLineWeek.DayFrom   (i-1);    
                taskDaysFactTmp_ICL.Hours = tsTimesheetLineWeek.Hours[i]; 
            }
        }
        
        taskDaysFactTmp_ICL.insert();
    }
        
    this.execute(#PageHeader);
    this.execute(#Body);
}

#PageHeader section in my template  will contain dynamic columns with unique dates from temporary table.

To do this I need to go through all records from my temporary table and somehow add columns to template:

protected void fillPageHeader()
{
    while select activitydate from taskDaysFactTmp_ICL
        group by taskDaysFactTmp_ICL.ActivityDate
        order by taskDaysFactTmp_ICL.ActivityDate
    {
        
    }
}

How to add columns to template with unique date fields from temporary table?

Thank you.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: adding columns dynamically to the excel template

    Hi,

    I wonder if Excel is the right tool for this kind of report.

    To me it looks as if a power bi based report / cube would be better suited for this purpose.

    Have you checked out the standard cubes for AX2012?

    Best regards,

    Ludwig

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans