Expand my Community achievements bar.

Do you have questions about the migration to Adobe Business Platform? Come join our upcoming coffee break and ask away!

Report showing 'Inactive Projects'

Avatar

Level 6
I'm trying to create a report that simply shows all projects where no hours have been put against their tasks for a month. I'd like to expand it to show me where no hours have been added for 2 months and 3 months, colour coding would be fine here. I've tried using the filter (projects report) all hours - last update date - less than $$TODAY-1m. This shows nothing. I tried changing it to greater than in case but that didn't show anything either. I've played around for a half hour trying lots of irrelevant filters and I can't get it to show anything. I know there are projects without time against them for at least a week so I tried that to make sure there is some data to show and still nothing. Help please.......
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

5 Replies

Avatar

Level 10
I don't know if this will help as I haven't tested it using the hours data but for our instance, users would know a project is inactive if no updates are done on the project. So one of additions requested by a user was a column for the last update date. Experimenting with code I saw on one of the threads, I was able color code an update today, update within a week and whether there was an update at all in the project and a hover will allow you to see the update without going into the project. Date range changeable. Here's the code. displayname=Last Update image.case.0.comparison.icon=true image.case.0.comparison.leftmethod=lastNote:entryDate image.case.0.comparison.lefttext=lastNote:entryDate image.case.0.comparison.operator=gte image.case.0.comparison.operatortype=date image.case.0.comparison.righttext=$$TODAY image.case.0.comparison.truetext=/static/img/r15/icons/casebuilder/flag_green.gif image.case.1.comparison.icon=true image.case.1.comparison.leftmethod=lastNote:entryDate image.case.1.comparison.lefttext=lastNote:entryDate image.case.1.comparison.operator=between image.case.1.comparison.operatortype=date image.case.1.comparison.righttext=$$TODAY-7d image.case.1.comparison.righttextrange=$$TODAY-1d image.case.1.comparison.truetext=/static/img/r15/icons/casebuilder/flag_yellow.gif image.case.2.comparison.icon=true image.case.2.comparison.leftmethod=lastNote:entryDate image.case.2.comparison.lefttext=lastNote:entryDate image.case.2.comparison.operator=notnull image.case.2.comparison.operatortype=date image.case.2.comparison.righttext= image.case.2.comparison.truetext=/static/img/r15/icons/casebuilder/light_grey.gif image.namevalue=lastNote:noteText linkedname=lastNote namekey=view.relatedcolumn namekeyargkey.0=lastNote namekeyargkey.1=noteText querysort=lastNote:noteText shortview=false textmode=true valuefield=lastNote:entryDate valueformat=HTML

Avatar

Level 10
Very nice, Polly -- thanks for sharing. Laura, if I'm following, I think you might be in a Catch-22: you're looking against hours within a certain timeframe (e.g. this month) and grouping by Project...but if their are no hours, then no rows comes back for that Project, so no group appears in your grid. For those who know SQL, this is a case for an "outer join" (but without getting one). So! To force a row for every Project (are you sitting down?) I suggest you consider adding a fake User called Joe Bloggs (or John Doe, for those reading in North America), and book a small amount of time for Joe against every Project you care about on a particular date (e.g. Jan 1, 2000). With that "seeded" data in hand, you can then add an OR clause to your report looking for any hours on that date, thereby ensuring at least one row for every Project, and in turn, a Project grouping in the report. With that in hand, Polly's handy formatting can then kick in and highlight the fact that some of those Projects are missing hours. Regards, Doug

Avatar

Level 10
Hi: I couldn’t figure it out, either. I have a Hours report, matrix grouped by Entry date, with these filters: [cid:image001.jpg@01D24C6D.218D4BA0] That produces a report that looks like this: [cid:image002.jpg@01D24C6D.218D4BA0] I open the report, and using my Mark III calibrated eyeball, look for projects that have more than two or three blank entries on the right. Those are the projects where no one has charged time in the last two or three weeks. I didn’t figure out how to restrict the report to ONLY those that haven’t had time reported in the last few weeks, so I just scan the whole thing. Crude, but effective ☺ Eric

Avatar

Level 6
Right then, I have a lot to work through here, thanks everyone........

Avatar

Level 6
This works! Thanks Eric, it’s close enough for me ☺ Laura Ray Project Support Analyst Bakkavor Information Systems Bakkavor Group West Marsh Road, Spalding, Lincolnshire, PE11 2BB, UK Direct: +44 (0)1775 763 010 www.Bakkavor.com // Laura.Ray@Bakkavor.com< [cid:image003.png@01D24727.3C928E60]