gemini715
asked on
How to use a user defined function in reporting services
I am trying to use a function within a Report in MSRS.
Background
This purpose of this function is to use the reporting services global variable "user!userID" to get sales information pertaining to the user.
The attached code works perfectly in SQL Server 2005. Returning 1000+ results in less than a second. However when I move the exact same query into the dataset of MSRS, it times out.
My question is: Can I use the user!userID global variable in the dataset (i.e. dbo.checkright(user!userID ) OR if I can call my user defined function dbo.checkright(user!userID ) in an expression to filter the data region thus only displaying information pertaining to that specific user.
Background
This purpose of this function is to use the reporting services global variable "user!userID" to get sales information pertaining to the user.
The attached code works perfectly in SQL Server 2005. Returning 1000+ results in less than a second. However when I move the exact same query into the dataset of MSRS, it times out.
My question is: Can I use the user!userID global variable in the dataset (i.e. dbo.checkright(user!userID
SELECT a.company, a.customer_nk, a.balance_hc, LTRIM(a.business) AS business, a.cc, a.balance_cc, t.sales_rep, com_companies.name AS company_name,
a.now, a.l_10, a.l_30, a.l_60, a.l_90, a.l_365, a.g_365, a.sales_365
FROM fin_ar_aging AS a INNER JOIN
com_salesrep_mapping AS t ON a.sales_rep = t.sales_rep_code AND a.company = t.company INNER JOIN
com_companies ON a.company = com_companies.company
WHERE (a.cc = @currency) AND
((SELECT dbo.checkright(@user) AS rep) LIKE '%' + RTRIM(t.sales_rep) + '%')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, it is a scalar function, so I will try your suggestion and let you know how it works.