I have an SSRS report we want to deploy as a tool to look up
companies in our database using either the primary or secondary phone
number. The parameter should able to search both the primary and
secondary phone# columns(Not obligatory, but refereed...If it can only be done with 2 separate parameters that fine)
The problem i'm having is i can't limit or control what data users would be
able to pull. They run their report using the AD credentials so i'm not
sure how to apply security in the report query.
Currently, if i run the report with just a space character, ( I didn't
enable "Allow Blank Values") it returns all companies that have either a
blank primary or secondary phone number...I tried forcing the phone
number parameter to be >= 7 characters, but still didn't work. Tried
adding <> ' ' but that didn't work either.
The report is a very simply select statement;
select cmp_id, cmp_name, cmp_primaryphone,cmp_secondaryphone, cmp_billto, cmp_shipper, cmp_consingee, c.cty_name, c.cty_state from company inner join city c on company.cmp_city=c.cty_code where cmp_active='Y' and cmp_primaryphone=@PhoneNumber or cmp_secondaryphone=@PhoneNumber and len(cmp_primaryphone)>=7 and len(cmp_primaryphone)>=7