I´m trying to solve a problem when implementing new functionality in AX. In this case I am looking at extending (or more correctly remake) the ProjTimeSheetEmpl report. I want the timespan (the days between the from and to dates in the report) to be result columns in the report. Since this timespan is not fixed I must somehow generate the result columns when running the report. I have been looking for functionality that pivots a table in Axapta, but I can´t find any. My idea here is to get the days in the timespan from the WorkCalendarDate table in AX and then pivot the result and make those days part of the result columns for the report.
Any suggestions here? Or are there other (better) ways of doing it?
Hmm, I have investigated my plan b. That is to create this as a report in Reporting Services and write your own SQL fetching the correct data. It is sad to not be able to do this is AX, but at least I can present the information I want to. In SQL Server 2005 you have the pivot functionality by the PIVOT command. If you are running SQL Server 2000 there is a stored procedure you can run to produce this kind of output. Read more on the stored procedure at http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
For now I stick to my plan b. But if somebody out there got any good ideas for my initial problem, please let me know. Thanks in advance!