Report QML: get Year Quarter in column, how to calculate?

[molnium]

Hi all,

I am in the making of a report for listing revisions and the output should also have a column with year quarters.

So long I got this to work, based on another report found here, many thanks!, but it doesnt show the correct numbers, there must be a mistake somewhere. The basic function used is just

CEILING( MONTH ( date ) / 3 )

So in Query Builder this looks a bit more nested, see the attached image.

The problem is: from month 1 to 5 it calculates Q1, then starting with June it calculates Q2. It seems the ceiling function is not called correctly or something like this...?

When looking at the generated SQl, it looks like

(CONVERT(varchar,DATEPART(YYYY,A0.createStampA2)))+(N' Q')+(CONVERT(varchar,

[MikeLockwood]

May be much easier to execute the report from an Excel macro. Parse, manipulate and present the data in Excel. Can use a histogram for quarters.

[molnium]

Well,

it turns out that MS SQL Server truncates everything after the decimal mark when dividing, so one should use the CAST function, or getquarter.

But since we don't have that in report manager, the workaround in this case was just to add 2, then truncating doesnt get us to another quarter, see the attached image. I just included floor() because maybe that changes sometime, we never know...