Filtering a Company report on Project Budgets | Community
Skip to main content
A
Anonymous
August 16, 2022
Question

Filtering a Company report on Project Budgets

  • August 16, 2022
  • 1 reply
  • 711 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

1 reply

William
Community Advisor
WilliamCommunity Advisor
Community Advisor
August 17, 2022

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

If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
A
Anonymous
August 17, 2022

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
Community Advisor
WilliamCommunity Advisor
Community Advisor
August 17, 2022

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!

If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
Terms & ConditionsAccessibility statement

Loading footer...