Expression in a pivot table help

[m4u]

Hello

I have two tables:

employee timesheet with employee, role, date , date year, date month and hours worked on that date

standard hours - date and standard hours for that date

I want to show both figures in a pivot table pivoted by role, employee, year, month - whith each cell showing me the amount of hours the employee worked that month and the number of standard hours for that month (basically the standard hours figure is the same for all employees)

I have Sum(HoursWorked) to show the hours worked, and SUM(StandardHours) to show the standard hours

So far so good

The problem begings on those months where a certain employee had no records at all. For some reason, for those months the standard hours figure is showing zero as well, even though it should show the figure from the standard hours table, which exists

What am I doing wrong, or what should I change to make it work?

[#2]

Can you post an example of your app so I can take a look?

[m4u]

The app is quite big so I dont know how to put it here - but the scenario is quite simple and happens in all charts - for instance, straight table:

Ill try to rephrase

Table 1 - Date, HoursStandard (single record per date)

Table2 - Date, Employee, HoursWorked (multiple records per single date)

I want to display a chart with 4 fields - employee, date, SUM(HoursWorked), SUM(HoursStandard).

The thing is, that whenever I dont have a record on a certain date for a certain employee in Table2, I still want to show that line in my straight table :

Employee A, 12/1/2010, HoursWorked=0, StandardHours=9

For some reason, if HoursWorked=0, the record doesn't show at all..

[#4]

Take a look at the attached, I did nothing special so this was why I was asking for a mock-up, because I can't see what could have gone wrong.

Unless of course you tell me that the attached is not what you are after.

Let me know,

[m4u]

Hi

In the attached file - A.Jones appears only in 5 lines - for the dates he has data. I want him to appear in all 12 lines of the Standard table.. sort of right join, with 0 next to his hours..

[#6 · Accepted solution]

Hi

In that case you will probably need to create a single table from your two tables, probably using a full join so that your single table holds dates and employees. What is the source of your data, if it is SQL then I might be able to help with the query?

Cheers,

[m4u]

Hi

It is SQL Server, and I know how to create a query to bring all data as a single table on the db level, but I was wondering how this can be acheived using the existing data model. This is due to the fact that the model itself holds several other tables associated with those ones, and logically this is the right table structure.. so I am wondering whether there is some way to solve this on a display level..

Thanks for your help

[#8]

Hi

OK, so the way I did it in a straight table was to add the following expression:

=Sum(Total HoursWorked)

Obviously you don't want that expression so I hid the column in the presentation tab.

This seems to work for a straight table, not sure about how to do the same thing with other chart types but worth investigating on your side.

Cheers,

[m4u]

Doesn't work

never mind, I give up and handle it on the data load level

thanks for your help

[#10]

Hi,

Based on the data model, I do not think that you will be able to see the display of records on the dates the employee that not worked. The plain simple fact being that the standard hours table does not have any employee name so there is no association for it to show the standard hours against a particular date where employee is a dimension.

What you could do is to create load distinct of employees from your work table and create another table with standard hours for each employee for each date. My thoughts are that this is the only way it will show the standard hours for an employee for dates where he/she has not worked.

Nimish