Filtering a Company report on Project Budgets

[roberttrama]

Hi there!

I have been trying to figure out a way to filter a company report by project budgets. Simply put, we have a number of companies that currently receive services as part of a larger partnership. The projects that we work out of have 0 budget. I would like to filter those companies out of a client count report, since they don't require active management and shouldn't be counted in workloads. I am a relatively new Sys Admin and am still struggling with the more advanced reporting asks. 

I started going down the EXISTS rabbit hole, but am having no luck. 

EXISTS:A:$$OBJCODE=PORT
EXISTS:A:ID=FIELD:ownerID
EXISTS:A:project:budget=0
EXISTS:A:project:budget_Mod=notin

Thanks for the help, Community!

Rob

[William--]

Hi Rob. Exists filters can be tricky. I think this is what you are needing: 

EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=PROJ
EXISTS:a:budget=0
EXISTS:a:budget_Mod=ne
EXISTS:a:companyID=FIELD:ID

You could add additional lines to indicate that only Active projects should be considered, or any other project-level criteria. 

Thanks,

-WE

[roberttrama]

Thanks for the help here! Unfortunately, when I put in other filters, this one stops working. I had been filtering on some custom form fields and wanted to add this as an additional layer. 

When using EXISTS, is there special syntax for the other filters or can they just be used normally? The filter below returns no data. I tried a couple different permutations, but no luck. 

DE:Client Status=Active
DE:Client Status_Mod=in
DE:Client Type=Agency
DE:Client Type_Mod=in
EXISTS:a:$$EXISTSMOD=EXISTS
EXISTS:a:$$OBJCODE=PROJ
EXISTS:a:budget=0
EXISTS:a:budget_Mod=gt
EXISTS:a:companyID=FIELD:ID

Thanks again!

[William--]

I presume Client Status and Client Type are fields on your Company custom form?
If so, I suggest starting over on your filter. In the Report filter builder, just build it a normal filter for those two rules and don't worry about the rule for project budgets. 

Once you've used the Report filter builder to create a basic filter (Client Status = Active AND Client Type = Agency), switch the filter over to text mode. This will show you how those rules are built in text mode and you will see the issue is with your filter modifier lines (_Mod=)

It will probably look something like this:

DE:Client Status=Active
DE:Client Status_Mod=in
DE:Client Type=Agency
DE:Client Type=eq

From here, you can append the additional lines for the EXISTS filter and save. 

I will also mention that for a report like this, you might consider just making a project report where budget is not equal to 0, status is active, company:Client Status is Active, and company:Client Type is Agency. Then, group your report by Company name and make the Summary tab your default tab. This will show you not just a list of companies that have active projects with budget, but will also show you a count of how many projects for each company. 

Good luck!