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
Is there a problem with your query, or are you strictly wondering how to limit who can run the query?
I'm not trying to limit who can run it, but which values they can use in the parameter @PhoneNumber. They shouldn't be able to run it with a blank value, or with just a space character; It should only accept atleast 7 digits.
I'm looking for a way to restrict the values that can be used in the parameters; The easiest method i can think of is only allowing the report to run if the parameter entered is >=7 characters, which is why i tried LEN(phonenumber)>=7 in the WHERE clause.
1 problem i can point out in the Company table i'm using is that the phone numbers aren't all formatted the same. Some have brackets, dashes, spaces etc...I might end up correct those as well, but the query should work with the records that fall into this case.
In the original query you posted, it looks like you've listed the following two time:
Should it be this instead?:
and (len(cmp_primaryphone)>=7 or len(cmp_secondaryphone)>=7)
Yes, sorry; I read through it and i don't see any other typos, but essentially, most of my WHERE clause has to be modified except for the
cmp_active='Y" --Implies it's active...
I realized my mistake and then i facepalmed pretty hard...XD Your suggestion worked!
Thanks again for all your help!
I went ahead and added this at the bottom of my query so if they enter a value less than 7 characters it will return the error message;
RAISERROR('#####You have not entered a valid phone number. Remove any spaces, brackets or dashes and try again.######',10,0)
Re-tested my report and it's working much better now!
You're welcome! Often times I catch myself having the same issues -- I write the query the way I'd speak it, rather than logistically. It happens to the best of us. Glad I was able to help!