Default Report Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Default Report Parameters

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-10-31 : 11:39:29

I need to create a report and set the default value of Manager (MgrID) = the Domain ID of the person that is currently logged in.

I do have the domain ID on the table where the report is being pulled from, just not sure how to pass the current domain id to the report as a parameter.

---
http://www.ssisdude.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:04:11

Try using User!UserID

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-11-05 : 11:38:01

I'm confused on this one, so let me give you more details on what I'm trying to accomplish.

The goal is to create a report for managers that both managers and non-managers (in case someone has to cover for them) can access to.

The report will be used on our login review for the SOX audit process that happens every quarter.

I was asked pass the current user as the default id to the report, so, if you're a manager and you click on the report link, you'll automatically see the logins that you need to approve, they are linked to your name on the reporting table. I got that to work.

Now, the problem that I'm having is with my second condition, in case a manager is out of office another manager or someone on his team need to approve the logins, I can't get my second condition to work. I have 3 drop downs/parameters available on the report, Manager Name, Server Name and Database Name, but forget about Server and Database name for now.

Basically, in case the id of the current user doesn't match with an id the reporting table, we need to then be able to select the parameters from the drop down and this is what I can't get to work.

This is what I have on my main dataset:

--Condition 1, this works.
('MyDomain\'+MgrLogin =@CurrentUser
AND Manager = @CurrentManager)

OR

--Condition 2
(Manager = ISNULL(@Manager, Manager)
AND ServerName = ISNULL(@ServerName, ServerName)
AND DBName = ISNULL(@DBName, DBName)
AND @Manager <> @CurrentManager
AND 'CITRITE\'+MgrLogin <> @CurrentUser)

Any suggestions?

---
http://www.ssisdude.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:48:26

didnt understand what you pass for @Manager & @CurrentManager?

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-11-05 : 12:02:27

@Manager = Value from the drop down
@CurrentManager = Name of the Manager where the name matches with the id that's being passed to the report.

I tried to many things at this point and am getting lost already.

---
http://www.ssisdude.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 12:16:49

will your Manager field contain alternate managers info also? I'm a little doubtful on the below condition

(Manager = ISNULL(@Manager, Manager)

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-11-05 : 13:22:35

We use that so we can use the '*All' option, so we can have the option to view all the managers.

This is my Manager dataset, that I use for the Manager drop down.

select
Manager,
Manager as 'ManagerName',
'MyDomain\'+MgrLogin as MgrLogin
--MgrLogin
from
dbo.sql_users with(nolock)
where
len(Manager) > 0
group by
Manager,MgrLogin
UNION
select
null,
'*All'as 'ManagerName',
'*All'

order by
Manager

---
http://www.ssisdude.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources