SmartMarker: How to group on date column by Year?

Im using smart marker approach to create a excel file. In addition to other columns, I have column name "ClosingDate" which has the dates in mm/dd/yyyy format. I want to group the rows based on the year of this column.

How do i do that? How do i use Year funtion in the group

&=Report.ClosingDate(Group:Repeat)

this would not work becuase it will group the rows by date and i want to group them by year part.

Hi,


Well, I am afraid, Smart Markers’ Grouping Data operation is applied on the given values in the cells. You have to use your own query for your needs, for example you may use YEAR(Date) (see the topic: http://msdn.microsoft.com/en-us/library/ms186313.aspx) function or DatePart function in your SQL Statement/Query for your requirements,

You may also use YEAR() function in your GROUP BY clause in your SQL query.

Thank you.

So how about making the column hidden?

For example my stored procedure can return two columns “ClosingDate”, “Year”
“Year” is nothing but DATEPART(YYYY,ClosingDate) so in excel i can group by year but after processing can we hide that column

Hi,

You can hide a column using Aspose.Cells.

Please refer to these documents:

Aspose.Cells for .NET
Hiding Unhiding Rows and Columns

Aspose.Cells for Java
Hiding Unhiding Rows and Columns