I need help with Adhoc views please.
Many of my datasources have dates and i want to build reports grouped by either week or month. Note i do not want the Months displayed like Apr-19 I just want April, weeks as numbers 52,1,2,3 etc
I want to be able to see all 12 months on my adhoc views and then provide a value for each year. Sounds simple enough, but when using month names they get sorted alphabetically (i understand why becuase the SQL is now using that string valued data). How can i set a groupby inside the SQL to prevent the charts forcing an undesired groupby to take effect, i have tried creating a domain using a derived table with the group by set but this gets overridden by the chart.
Likewise with weeks, i convert my dates to week numbers. However Adhoc views simple treat the numbers as such and alphabetically sort them, thus making week 52 from 2020 ahead of week 1 2021. Attachment shows simply what i am trying to achieve.
The simple solution here would be to be able to define what the groupby of the SQL query should always be regardless of the fields being used on the adhoc view. For example; display week number but sort the SQL Query by date or display month name but sort the SQL Query by date.
I hope i am not the only person that has encountered this and there is an easy solution that i am over looking.
Can someone please help.
Please note: I know i can solve this using JasperStudio but i need to be able to do this in adhoc because i want to empower less technical users to be able to use the server and avoid coding.