INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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?

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

(OP)
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?)

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close