SQL Query + SSRS Report

Question

SQL Query + SSRS Report

fear_factor187

SSC Rookie

Points: 32
More actions
August 31, 2020 at 1:15 pm

#3784876

I have been tasked with creating a SQL / SSRS Report.
I am very much an amateur in regards to SQL and SSRS.
I am pretty comfortable with basic SQL queries, but this one happens to be more complex then I have been able to figure out.

We have an HR Database that lists employee names + HR info. The employee name column consists of 1 column in the following format "Baker, Robert" (without quotes).
My manager wants a report where he can simply pop in the users name and get their corresponding computer name.
I'm comfortable with the computer name piece. I'm having trouble with the query for the HR Name.
In the SSRS report, I need a "Employee Name" field (parameter) that will use a wild card/like statement to find the relevant employee name from the database.
I can do this with a single instance (one employee). But I can't figure out how to use multiple values in the SSRS report due to having the comma in the employee name field... as well as being able to search via wild card for multiple users.

Help would be very much so appreciated as I don't have anyone to bounce this off of at my work (I'm on my own which can make learning/improving a bit difficult since I'm not around someone more experienced).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

So what's your question? You want to split the last and first names into separate columns in your SQL query? (I would do that... makes querying much easier and faster).

DECLARE @LastFirst VARCHAR(20) = 'Simpson, Homer';

SELECT LastName = LEFT(@LastFirst, CHARINDEX(',',@LastFirst)-1)
, FirstName = RIGHT(@LastFirst, LEN(@LastFirst) - CHARINDEX(',',@LastFirst)-1)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

If you create a text box parameter and allow any text to be entered by the user, then your SQL code could be as simple as:

SELECT ...
  FROM {your table}    t
 WHERE t.LastFirst Like '%' + @EmployeeName + '%';

I would recommend creating a stored procedure that accepts the parameters to filter on - something like:

CREATE PROCEDURE dbo.rptEmployeeSearch
       @EmployeeName varchar(35) --make this the exact same size as the column being searched
    AS 

   SET NOCOUNT ON;

SELECT ...
  FROM dbo.Employee e
 WHERE e.EmployeeName Like '%' + @EmployeeName + '%';
    GO

Depending on how large the employee table is - this may not perform very well because of the leading wildcard. This also allows the user to enter wildcards if needed. The user could enter: Bak%,%Rob and it will find all matches where the last name has 'Bak' and the first name has 'Rob'.

The other option is to build the parameter as a list of all employees and set the parameter as multi-select. If you embed the query in the SSRS report then your query becomes:

SELECT ...
  FROM dbo.Employee
 WHERE EmployeeName IN (@EmployeeName);

If you create a stored procedure (recommended) - then you have to account for the multiple entries:

CREATE PROCEDURE dbo.rptEmployeeSearch
       @EmployeeName varchar(35) --make this the exact same size as the column being searched
    AS 

   SET NOCOUNT ON;

SELECT ...
  FROM dbo.Employee
 WHERE EmployeeName IN (SELECT value FROM string_split(@EmployeeName, ','));

For the parameter - create a new dataset as - name it as Employees:

SELECT EmployeeName FROM dbo.Employee WHERE ...

Then use this dataset as the available values for the parameter. Make sure you filter out non-active employees, duplicates (if they exist), etc...

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 3

Hello

I am looking to solve the same issue in my case the user sometimes wants to search for one number or sometimes pull a report for two numbers

So I tried t_master.phone like '%' + @Object + '%' or t_master.phone like '%' + @Object + '%'

But this do not work, any idea.

Thanks

This reply was modified 4 years, 2 months ago by Ysa.