Choice Interactive Filters and the Task SLA Table

[stephenrodgers]

SN Community, I need your minds. As the meme says, it's big brain time.

So I've got a dashboard that I'm building primarily for managing HR cases (the newer sn_hr_core_case scoped app one, not the old hr_core one). It's a pretty basic dashboard, but we'd like to see basic operational metrics, and basic SLA metrics. So far so good. Reports are built against only two tables: sn_hr_core_case (for the HR Cases) and task_sla (for seeing how much of the SLA has elapsed for the HR Cases).

Enter interactive filters. Again, nothing crazy, just basic Choice (status, priority) and Reference (assignment group, assigned to) filters.

The Reference filters are EASY. Set an interactive reference filter for sn_hr_core_case, set another one for task_sla. BOOM. Done. Feel smart. 

The Choice filters are making me lose my mind.

My first thought was that sn_hr_core_case extends task, and task_sla is a database view of task, so I should be able to just make a filter of [task] click the database view and hierarchy boxes, and be done. Nope. That picks up the sn_hr_core_case tables just fine, but task_sla doesn't seem to care. Writing a filter against sn_hr_core_case directly has the same result (but I expected that).

The ONLY way that I've found to get task_sla to listen to a filter is to write the choice list filter against task_sla. But obviously if I do that, then sn_hr_core_case isn't responding. I even tried the hail mary of clicking the hierarchy box on task_sla, but that didn't work.

I really don't want to have to make my users do an extra filter just to make the SLA report work.

The only "workaround" I've come across so far is making a Cascading Filter...NOT so that I can actually have it cascade, but just so that I can define multiple target tables and fields. That's a *little* kludgy because now it has a different UI for no apparent reason, but I can live with that if I have to.

Help? I'm open to fixes (if I'm being dumb) or workarounds (if anyone knows a cleverer way to achieve the same result).

[Adam Stout]

Choice filters only apply to a single table (and it's extensions).  Can you create a filter on sn_hr_core_case and then create a view to join sn_hr_core_case and task_sla?  [Be sure to check "Apply to both database views and tables"].

If that doesn't work, you need a custom interactive filter.

[stephenrodgers]

@Adam Stout

Thanks Adam, I'm trying to work through that. Here's what I tried so far:

1. Created a database view to join sn_hr_core_case and task_sla (called it u_case_sla)
2. Created a choice filter against status.sn_hr_core_case with "Apply to both database views and tables" checked (Didn't work)
3. Created a choice filter against status.u_case_sla with "Apply to both database views and tables" checked (Didn't work)

Just making sure I understood your advice correctly. Did I have it right, or did I go astray?

Thanks!

[stephenrodgers]

@Adam Stout

Ok, found something that DID work. Tell me if I've done something terrible please:

1. Created a database view to join sn_hr_core_case and task_sla (called it u_case_sla)
2. Rewrote the report against u_case_sla (this was the missing step)
3. Created a choice filter against status.u_case_sla with "Apply to both database views and tables" checked (Worked this time)

Thank you!

[Abdul Aamer]

Hi stephenrodgers,

Thanks a lot!!

The solution above really helped me solve my problem on one of the reports.

Best Regards

Abdul Aamer