Nav 2009 Group by function in excel

Hi all,

I would like to add group by function which the user can choose which field to group, For example,

I checked Location. Then the first column in Excel should be grouped, but the problem is the employee S1670 is GUL, while the employee S1700 and S1701 should be HQ.

6523.group.png

7610.group2.png

Below is the code that used to merge the cell (group by)

Repeat

IF Location = TRUE THEN BEGIN

Employee.SETRANGE(“Emp Classification 4”);

GroupBy := Employee.COUNT;

ToCol := 4 + GroupBy-1;

ExcelBuf.EnterMergeCell(RowNo,1,“Emp Classification 4”,TRUE,FALSE,’’,TRUE,‘A4’,‘A’ + FORMAT(ToCol),GroupBy-1) ;

END;

UNTIL Employee.NEXT =0

Please advise what is the problem. Thanks in advance.

Regards,

Joan

I’m not quite sure how Location can be True. And what does the ToCol := 4 + GroupBy-1; do? What does the 4 represent?

hi

what is “Emp Classification 4”? is that a value or a field and what is it’s meaning?

Hi Erik,

Location = TRUE is the status of the checkbox. Location is checkbox name.

ToCol := 4 + GroupBy-1; Currently my table only have these 3 record, I am not sure how to group by different location, so I hardcode it, it is the end cell to merge.

Hi Jonathan,

“Emp Classification 4” is field name of Location in Table, sorry for make you all confusing.

Can you all get me?

Thanks in advance.

Regards,

Joan

How you are grouping in Classic report on the basis of selected field for grouping ?

Is that is working in classic report ?

If yes then write the code for exporting to excel on sections of report and check.

Employee.SETRANGE(“Emp Classification 4”);

this filter removes an existing filter for field emp classification 4. is that what you want?

Jonathan,

No. I have no idea on how to do the grouping merge, please suggest any solution.

Thanks.

Regards,

Joan