Export report - Group per Date(months) and display all months even will null values

[thebigbadwolf]

Hello all,
I suspect that a solution must have been shown elsewhere but I only see issues for charts and cross tabs.

I want to create an export report and I have 3 groups, 1.Region, 2.Category and 3.Date and I want to show total hours.
I'm grouping the dates to be shown per Month.

This export will get all data from the table and all dates so I'll be getting multiple years too.

So here I am, some months (and whole years) are not being displayed because obviously there's nothing to show.

Creating another table is not an option I'm afraid.
The good news is that the "date" field contains all dates, every single day from the beginning of data till today, but not all days fall in the all Categories (the previous group), so dates are being "filtered/grouped" out.

I've started with creating formulas like
if month(table."date") = 1 then table."hours" else 0 ...for January
if month(table."date") = 2 then table."hours" else 0 ...for February
etc.

Then grouped the "date" field not by month but by year and then created 12 Group Sections, 3a, 3b, 3c, .. one for every month
and then created some summary fields that summed each month and placed them in the group sections ...
...but I realized that some Categories don't have data for years, so many years don't show up.

I created an SQL expression and I know which is the first date ever.

Is there a way to do it?

Thanks

 

[fisheromacse]

Are you able to join the same table to the report a second time and use the date field as your 'master list' of dates?

 

[thebigbadwolf]

I tried to but it threw an error because of the magnitude of data.

I used left outer join and connected the two date fields. (Right?)

 

[thebigbadwolf]

Maybe I found a way but I'd like to avoid it.
Since it's an report for export (in excel sheet) I could change the grouping and have only one group, the date (showing every month).

Then I could create as many formulas as the combination of the two previous groups: Region and Category.
So if I had 3 different Regions and 4 different Categories the formulas should be

if (table."category" = "a" and table."region" = "europe") then table."hours" else 0
if (table."category" = "b" and table."region" = "europe") then table."hours" else 0
..
if (table."category" = "a" and table."region" = "usa") then table."hours" else 0
..


..and then place their summaries in the 12 group sections.

There's a problem with that solution though. If a new Category is made then I'll have to update this report.