Chris Harshman
I'd agree with Sean here, the first thing is to replace that splitter function you're using, and preferably only parse the strings once. If you need to get rid of the table scans, then add indexes on EOS_Reports(OrgPathID), and EOS_Reports(SharedOrgPathID). Also consider if you need to do the deduplication caused by UNION, or if you can use UNION ALL instead.
Thanks Chris, I will see possibility of splitter function (being common for all SPs). As suggested, I've created these two indexes, and have put UNION ALL. It seems performance is improved little bit. I can see reports displaying records when selected "few" parameters instead of "ALL" from all drop downs. It's still causing issue for "Select All" in drop-downs of SSRS Report. Query itself is slow, so no issue with the SSRS Rendering part.
I will update if I get any improvements. Thanks a lot.!