Report on "worked" days based on created activities (CreatedBy + CreatedDate)

Question

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?

Answer 1

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 :)