?
Solved

How to use a user defined function in reporting services

Posted on 2008-11-11
2
Medium Priority
?
1,117 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:gemini715
2 Comments
 
LVL 9

Accepted Solution

by:
Ernariash earned 375 total points
ID: 22935851

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
 

Author Comment

by:gemini715
ID: 22939820
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question