January 23, 2009 at 4:44 am
I am trying to produce a report of line managers in SSRS but have two problems. Firstly, I want ALL to appear at the top of a parameter list at runtime rather than amongst manager's whose surname starts with 'A'. One of the datasets has the following:-
SELECT 'ALL' As EjSupervisor
UNION
SELECT DISTINCT EjSupervisor
FROM Ejob
WHERE (EjDateEnd IS NULL) AND (EjSupervisor LIKE '%,%')
ORDER BY EjSupervisor
The other dataset is a stored procedure which returns all the required columns in the query. I have one report parameter to select a line manager. When I run the report even when I pick only one value from the list, all the data is returned. What am I doing wrong
January 23, 2009 at 7:29 am
I am assuming that the first dataset is used to populate the parameter for selecting the manager, and that this parameter is not set to be a multi-value.
That being the case, given that you want the value of 'ALL' to appear first, I wuold change the query to something along these lines
DECLARE @Managers TABLE (RowID INT IDENTITY, Manager VARCHAR(#)) --replace # with the actual size assuming this is a varchar type
INSERT INTO @Managers VALUES ('ALL') --Inserts the ALL value into first position in table variable
INSERT INTO @Managers
SELECT DISTINCT EjSupervisor
FROM Ejob
WHERE (EjDateEnd IS NULL) AND (EjSupervisor LIKE '%,%')
ORDER BY EjSupervisor
SELECT Manager FROM @Managers ORDER BY RowID
As to why your second dataset returns all managers regardless of what you select, I would need to know more about the stored procedure code, as well as if and how you are passing the parameter value into it.
January 23, 2009 at 8:19 am
Thanks, that has worked brilliantly by putting ALL in the correct part of the parameter list (with multi-value box checked).
All the data is being returned from the sp however regardless of which manager I select at runtime. The sp merely consists of a list of cols required from various tables in SQL Manager. I thought that if I bound @Manager to the value field in the report parameter (from query selected) this would be OK but I get a message saying forward dependencies are not allowed - sp is as follows:-
ALTER PROCEDURE [dbo].[spSSWandLM]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
EBase."EbEmpNumber" AS [Emp No],
EBase."EbPSID",
EPerson."EpFirstName" AS [First Name],
EPerson."EpLastName" AS [Surname],
EPerson."EpPrefix" AS Title,
EEmploy."EeSecurityClearance" AS [Security Clearance],
EEmploy."EEClass" [Status],
EJob."EjFlxIDEb",
EJob."EjSupervisorFlxIDEb" AS [LM Emp No],
EJob."EjSupervisor" AS [Line Manager]
FROM
{ oj ((HR_Reporting_Live.dbo.EBase INNER JOIN HR_Reporting_Live.dbo.EEmploy ON
EBase.EbFlxID = EEmploy.EeFlxIDEb)
INNER JOIN HR_Reporting_Live.dbo.EJob ON
EBase.EbFlxID = EJob.EjFlxIDEb)
INNER JOIN HR_Reporting_Live.dbo.EPerson ON
EBase.EbFlxID = EPerson.EpFlxIDEb}
WHERE
EEmploy."EeDateBeg" <= GETDATE() AND
(EEmploy."EeDateEnd" >= GETDATE() OR
EEmploy."EeDateEnd" IS NULL) AND
EJob."EjDateBeg" <= GETDATE() AND
(EJob.EjDateEnd >= GETDATE() OR
EJob.EjDateEnd IS NULL) AND
EPerson."EpDateBeg" <= GETDATE() AND
(EPerson.EpDateEnd >= GETDATE() OR
EPerson.EpDateEnd IS NULL)
AND EEmploy.EEClass = 'LV'
ORDER BY EPerson."EpLastName" ASC
END
January 23, 2009 at 8:59 am
OK, first I need to clarify something.
Is your parameter supposed to be multi-value (that is you can select 1, some, or all of the available values)? If it is, then there really was no need to do the insertion of the ALL value into the list of values, since multi-value parameters have Select All automatically added at the top of the list. If instead, the parameter is supposed to be one where you can only select a single manager or choose the ALL option, then the change we did to your query is fine, but you need to uncheck the multi-value checkbox.
Depending upon what the result of that clarification is, the answer to how you modify the stored procedure will be different.
If the parameter is supposed to be single or all, then you simply need to add an element to your WHERE clause like this
AND (EJob."EjSupervisor" = @Manager OR @Manager = 'ALL')
If the parameter is supposed to truly be a multi-value option, then a bit more work is required, since multi-value parameters are passed as a comma-delimited list. There are a number of different forum postings that cover how to handle it when you pass a multi-value parameter to a stored proc. One of the more common methods is to create a function that will split out the comma-delimited list into a table, and then calling that function in the stored procedure where you need to reference the values. If you do that, then your where clause would add something like
AND (EJob."EjSupervisor" IN (SELECT DataElement FROM fn_List_To_Table(@Manager))
For an example of how to create the function I described see this article
http://www.sqlservercentral.com/scripts/61518/
Hope that all helps.
January 23, 2009 at 9:57 am
Thanks again. I've unchecked the multi-value box in the parameter which is called Manager. When I run amended dataset in the data tab I am prompted to enter All or Manager which is what I want. But if I put @Manager and Value field as Manager in the parameter part of the query I get the following:-
The report parameter ‘Manager’ has a DefaultValue or a ValidValue that depends on the report parameter “Manager”. Forward dependencies are not valid.
If I delete from the parameter tab on query I get:-
Must declare scalar variable @Manager.
dataset to select manager now contains:-
DECLARE @Managers TABLE (RowID INT IDENTITY, Manager VARCHAR(50))
INSERT INTO @Managers VALUES ('ALL') --Inserts the ALL value into first position in table variable
INSERT INTO @Managers
SELECT DISTINCT EjSupervisor
FROM Ejob
WHERE (EjDateEnd IS NULL) AND (EjSupervisor LIKE '%,%')
AND (EJob."EjSupervisor" = @Manager OR @Manager = 'ALL')
ORDER BY EjSupervisor
SELECT Manager FROM @Managers
ORDER BY RowID
BY THE WAY, CAN YOU RECOMMEND A GOOD BOOK FROM PLETHORA OF BOOKS OUT THERE
January 23, 2009 at 10:22 am
It appears that you added the element I said to add to your WHERE clause to the query that pulls the options for your parameter. That is not where it was supposed to go. Instead, you need to add it to the WHERE clause of your stored procedure. Also, make sure that the parameter name when you define it in Reporting Services matches what you are calling it in the stored proc (i.e. Reporting Services parameter name - Manager, Stored Proc parameter @Manager).
Make sure that the query for pulling the options for the parameter does not have any parameters itself, and only uses variables that you declare within the query itself.
January 26, 2009 at 7:41 am
Just returned to this this afternoon but still got a problem with - The report parameter ‘Manager’ has a DefaultValue or a ValidValue that depends on the report parameter “Manager”. Forward dependencies are not valid.
I have filled in the parameter tab for the find mgr dataset with @Manager. The report parameter is called Manager, has a value of Manager and a label of Manager. Default for non-queried is set to ALL. The main dataset is set as a stored procedure and just contains spSSWandLM.
This sp contains:-
ALTER PROCEDURE [dbo].[spSSWandLM]
AS
DECLARE @Manager Varchar(50)
SET @Manager = 'ALL'
BEGIN
SET NOCOUNT ON;
SELECT
EBase."EbEmpNumber" AS [Emp No],
EBase."EbPSID",
EPerson."EpFirstName" AS [First Name],
EPerson."EpLastName" AS [Surname],
EPerson."EpPrefix" AS Title,
EEmploy."EeSecurityClearance" AS [Security Cleared],
EEmploy."EEClass" [Status],
EJob."EjFlxIDEb",
EJob."EjSupervisorFlxIDEb" AS [LM Emp No],
EJob."EjSupervisor" AS [Line Manager]
FROM
{ oj ((HR_Reporting_Live.dbo.EBase INNER JOIN HR_Reporting_Live.dbo.EEmploy ON
EBase.EbFlxID = EEmploy.EeFlxIDEb)
INNER JOIN HR_Reporting_Live.dbo.EJob ON
EBase.EbFlxID = EJob.EjFlxIDEb)
INNER JOIN HR_Reporting_Live.dbo.EPerson ON
EBase.EbFlxID = EPerson.EpFlxIDEb}
WHERE
EEmploy."EeDateBeg" <= GETDATE() AND
(EEmploy."EeDateEnd" >= GETDATE() OR
EEmploy."EeDateEnd" IS NULL) AND
EJob."EjDateBeg" <= GETDATE() AND
(EJob.EjDateEnd >= GETDATE() OR
EJob.EjDateEnd IS NULL) AND
EPerson."EpDateBeg" <= GETDATE() AND
(EPerson.EpDateEnd >= GETDATE() OR
EPerson.EpDateEnd IS NULL)
AND EEmploy.EEClass = 'LV'
AND (@Manager = EJob.EjSupervisor OR @Manager = 'ALL')
ORDER BY EPerson."EpLastName" ASC
END
and the dataset for obtaining the manager contains:-
DECLARE @Managers TABLE (RowID INT IDENTITY, Manager VARCHAR(50))
INSERT INTO @Managers VALUES ('ALL') --Inserts the ALL value into first position in table variable
INSERT INTO @Managers
SELECT DISTINCT EjSupervisor
FROM Ejob
WHERE (EjDateEnd IS NULL) AND (EjSupervisor LIKE '%,%')
ORDER BY EjSupervisor
SELECT Manager FROM @Managers
ORDER BY RowID
January 26, 2009 at 9:12 am
For your dataset that pulls the list of Managers for the parameter, go into the edit screen for it (using the ... button) and check the Parameters tab to make sure that the @Manager parameter is not listed. If it is, then delete it.
For your Stored Proc, you need to remove the SET line below. Having it there causes the stored procedure to basically ignore the value passed in with the parameter.
[quote-0ALTER PROCEDURE [dbo].[spSSWandLM]
AS
DECLARE @Manager Varchar(50)
SET @Manager = 'ALL'
[/quote-0]
January 26, 2009 at 9:19 am
Hi - have done both these things and report runs; however, now no data is being returned.
January 26, 2009 at 9:23 am
Sorry, should have seen this before.
In your Stored Proc, do not DECLARE @Manager, just list it. That is, simply remove the word DECLARE from that line.
Also place that line before the AS.
January 26, 2009 at 9:36 am
Thanks so much for your help - that's all working fine now. I had not realised you can put a parameter into an sp without declaring it and initialising it in some way. The other thing I needed to do was to put the parameter into the dataset for the main query.
January 26, 2009 at 9:39 am
Glad I could be of assistance, and that it is working for you now.
January 27, 2009 at 11:08 am
I use a query like this:
select lastname + ', ' + firstname AS EmployeeName,
EmployeeID
FROM UserProfile
UNION ALL
SELECT '*All Employees', 'ALL'
ORDER BY 1
I stick in an asterisk to make the All appear first. It's a cheat but no one has complained.
Then I have an input parameter in the stored procedure for EmployeeID char(11).
In the procedure I do a thing like this:
DECLARE @EmpSearch varchar(12)
SELECT @EmpSearch = '%'
IF @EmployeeID <> 'ALL'
BEGIN
SET @EmpSearch = @EmpSearch + LTRIM(RTRIM(@EmployeeID))
END
Then, in the main SELECT query in the WHERE clause I have
Where EmployeeID like @EmpSearch.
This scheme has always worked for me and it's simple enough for me to understand:D
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply