August 4, 2015 at 5:49 am
Hi
I have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers
as I will be using this variable in ssrs.
What am I doing wrong?
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
)
INSERT INTO #TEMP_TABLE (userid, username, unitid, unitname)
SELECT USERS.systemuserid, USERS.fullname, USERS.businessunitid, USERS.businessunitidname
FROM dbo.FilteredSystemUser AS USERS
WHERE USERS.businessunitid = @BusinessUnitID
AND USERS.fullname <> 'SYSTEM'
AND USERS.fullname <> 'INTEGRATION'
set @TheUser = (select username from #TEMP_TABLE)
August 4, 2015 at 6:00 am
i assume Temp_Table contains many rows thats why you can't assign all of them to one variable.
What is the output you expecting? I can;t see declaration of @TheUser is that a table variable or a VARCHAR etc...?
August 4, 2015 at 6:16 am
Hi
I have declared @TheUser as follows: DECLARE @TheUser VARCHAR(200);
Maybe I better back-up and outline what I am trying to do.
Originally I created 2 procedures:
1) atmWonOpportunities_Users
2) atmWonOpportunities_Data (startdate, enddate, username parameters)
In (1) I created a filtered list of all user names associated with a certain business unit and saved it to a temp table.
In (2) I create a filtered query listing all won opportunities during a period of time (date start and date end paramters)
I created a rpt in Report Builder and used these 2 procedures.
When I run the report and select a user from the dropdown list, everything works just fine.
When I click the "All" option to select all users in the dropdown list, I don't get any results.
So someone told me that I needed to assign all users to a variable in the 1st procedure.
So this is why I thought I would try this tactic.
August 4, 2015 at 6:24 am
if your query will return only one row, the code will work, but more then that it will fail because you can't assign many rows into a varchar variable using SET. you can try doing it by combining all results into one string:
select @TheUser = ISNULL(@TheUser, '') + username +'; ' from #TEMP_TABLE
which will give you an output like "User1; User2; User3;"
August 4, 2015 at 6:28 am
If you're trying to create a pick list for an SSRS parameter, you're going about it all wrong.
Each SSRS parameter (that you want to have a pick list) just needs it's own proc or query (aka it's own DataSet). The SSRS parameters are then able to execute the specified query to build the pick list.
When the end user selects a value from the pick list, SSRS passes that value to the main report proc.
August 4, 2015 at 6:33 am
Hi
That makes me feel better.
However in srs using the old way I had things,
I'm getting appropriate results if I select 1 User at a time,
When I select "All" or more than 1, I get no results which is wrong
----------------------------------------------------------------
procedure to display data
DECLARE @CurrentUserName varchar(200);
SET @CurrentUserName =
(
SELECT fullname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @CurrentUserID uniqueidentifier;
SET @CurrentUserID =
(
SELECT systemuserid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitID uniqueidentifier;
SET @BusinessUnitID =
(
SELECT businessunitid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
SELECT DISTINCT
OPP.customeridnameAS 'Customer Name',
OPP.contactidnameAS 'Contact Name',
OPP.actualclosedateAS 'Actual Close Date',
OPP.actualvalueAS 'Actual Value',
OPP.estimatedvalueAS 'Estimated Value',
OPP.createdbynameAS 'Created By Name',
OPP.createdbyAS 'Created By ID',
OPP.statuscodeAS 'Status Code',
OPP.statuscodenameAS 'Status Code Name',
OPP.owningbusinessunitAS 'Owning Bus Unit',
OPP.owneridnameAS 'Owner Name',
OPP.descriptionAS 'Description',
TBU.businessunitidnameAS 'TBU Business Unit Name',
TBU.businessunitidAS 'TBUBusiness Unit ID'
FROM dbo.FilteredOpportunity AS OPP
INNER JOIN dbo.FilteredUserSettings AS TBU ON @BusinessUnitID = TBU.businessunitid
WHERE @BusinessUnitID= TBU.businessunitid
AND OPP.actualclosedateBETWEEN @DateStart AND @DateEnd
AND @TheUser= OPP.owneridname
AND OPP.statuscode= 3
---------------------------------------------------------------
CREATE PROCEDURE atmWonOpportunities_Users
AS
DECLARE @CurrentUserID uniqueidentifier;
SET @CurrentUserID =
(
SELECT systemuserid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @CurrentUserName varchar(200);
SET @CurrentUserName =
(
SELECT fullname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitID uniqueidentifier;
SET @BusinessUnitID =
(
SELECT businessunitid
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
DECLARE @BusinessUnitIDName varchar(200);
SET @BusinessUnitIDName =
(
SELECT businessunitidname
FROM dbo.FilteredSystemUser
WHERE systemuserid = dbo.fn_FindUserGuid()
)
IF OBJECT_ID('#TEMP_TABLE', 'U') IS NOT NULL
DROP TABLE #TEMP_TABLE;
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
)
INSERT INTO #TEMP_TABLE (userid, username, unitid, unitname)
SELECT USERS.systemuserid, USERS.fullname, USERS.businessunitid, USERS.businessunitidname
FROM dbo.FilteredSystemUser AS USERS
WHERE USERS.businessunitid = @BusinessUnitID
AND USERS.fullname <> 'SYSTEM'
AND USERS.fullname <> 'INTEGRATION'
SELECT * FROM #TEMP_TABLE
DROP TABLE #TEMP_TABLE
August 4, 2015 at 7:31 am
There are a few way of doing this, depending on what you want to do.
If your SSRS parameter is single single valued parameter (the user can select only a single value) it's pretty easy.
If it's a multi-valued parameter it's still doable but it's a little more work.
Rather than trying to describe the process step by set, it's probably easier to simply knock out a quick example and attach it... Hold a sec and I have something for you shortly...
-------------------------
Ok.. The attached file was designed using the AdventureWorks2008 database but is generic enough to work in pretty much any version of AdventureWorks you may have. Just create a new data source called AW2008.
Also... note... Rather than creating separate procs I simply used embedded queries... But... Please note that they were written with the syntax you'll want to use in your procs.
That is to say, you'll need to split the comma separated values that are coming out of the SSRS parameter.
Also note that I'm using a splitter function to split the parameter. I'm using a renamed version of Jeff Moden's "DelimitedSplit8K" function, which can be found http://www.sqlservercentral.com/articles/Tally+Table/72993/...
Let me know if you have questions.
HTH,
Jason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply