Here is the scenario.... I would like to create a report that would show the user's "worked" days.

We created a custom report type called "All Activities", since we needed to bring some information from the User object (CreatedBy) to be used as a filter, which "mimics" the "Task & Events" oob report type.

The report would be based on the CreatedDate field.

E.g. If the user creates 10 activities for the day, then I would like to count as 1 for that specific day. Then we would like to group the results by week. Like, if the user created two activities on Monday, 3 Tuesday, and the rest of the week he/she logged in but didn't created any activity, the result for that week would be 2.

In this report I would have one month of data.

I tried creating Power of 1 fields on the User and Activities (task/event) but I wasn't able to get it to work.

I have tried all possible combinations of grouping using the CreatedBy, CreatedDate + PowerOf1, but I haven't been able to accomplish this.

I have also tried using PREVGROUPVAL and PARENTGROUPVAL, from all different groupings possible, without success.

Any suggestion?


I was able to accomplish this with Reporting Snapshot.

I created a custom object called "Worked Days" with 3 custom fields: Activity Created Date(Date/Time), User(Text(255)) and Worked?(Number(1,0)).

Created a report using my custom Report Type(All Activities) and grouped by: User -> Created Date -> Power of 1.

In my Reporting Snapshot I used the Grouping Level = Grouping 3: Power Of 1, then mapped the fields:

Created Date -> Activity Created Date
Created By: Full Name -> User
Power Of 1 -> Worked(1,0)

Scheduled the snapshot to run daily(every single day) at the end of the day.

Finally, created a report on my custom object (Worked Days) grouped by the User, Activity Created Date (Weekly) and Worked?

Hope that can help someone :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.