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.  
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) + '%')

Open in new window

gemini715Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErnariashCommented:

What dbo.checkright does? Could you recreate the same functionality on RS? Is dbo.checkright an scalar funtion? if yes then you could have forward dependencies:
Solution: You could create another data set  dtUSERS: with: SELECT dbo.checkright(@RSuser)  AS USER
@RSuser is now a RS parameter set the default value to "=User!UserID"
Make sure the @RSuser is moved up from you @user parameter to allow forward dependencies:
Set @user default value from Query select as DataSet dtUSERS and Value Field as USER.
Then you could set the variables as Internal if need it.
 Please for performance consider change the like to (@user LIKE RTRIM(t.sales_rep) + '%')
 

SELECT dbo.checkright(@RSuser)  AS USER
 
=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
                        (@user LIKE RTRIM(t.sales_rep) + '%')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gemini715Author Commented:
Based on the userID dbo.checkright will return the user (or users in the case of managers).  Thus showing only that user(s) sales information.

Yes, it is a scalar function, so I will try your suggestion and let you know how it works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.