Query criteria / parameter for a whole project.
-
I have a large collection of 'projects', i.e., RS reports for each of our customers. Each project is effectivley a library of maybe up to 15 reports. They are usually modified slightly according to the customer's requirements.
Most of the reports use a variery of SQL tables / views and to ensure the customer only sees theor data. I am currently changing the query criteria for each report in the project each time I deply a new library / set. This is time consuming and open to the risk that I mis-key the criteria for one or more report. The criteria is always the customer's account number and is common across most of the database tables. E.g: WHERE (LIVE_INVOICESUMMARY_CRREPONLY.IS_SUCODE = 'DH166'...
Is there any easy way to set this criteria at the project level, rather than at individual report level? Can it be done by getting the SQL query to refer to something in the custom code?
I know I can use parameters, but again, these have to be set per individual report.
I'm on SQL2000
-
Can't you put the logic in an SQL Server function which is called by your views or sprocs? It depends on where the customer account number is available of course.
-
My SQL knowledge is rather 'basic'; I don't know where to start with this.
All I want to do (if it's possible) it create some custom code that says what my account code, is so I can reference it in all of the queries. Then when I copy and delpoy the project for another customer, all I have to do is change the custom code once. Is this actually possible, or am I on a wild goose chase?
-
If you don't want to dabble at the SQL Server end, the only thing I've done similar to what you're trying to do is to pick up the user account (a glabal constant: User!UserID). I then use this as a parameter for another parameter based on a dataset which gives me the section that user works in. I'm not clear how your report learns what the client account code is: do they log in with it?
-
I’m probably not explaining myself very well, sorry. I’ll try and elaborate…
I start with a ‘base’ project. It has a shared data source so I can see and use all of my d/b tables and views I have created. In each project I have a library of reports that get deployed for each new customer. Usually some extra reports get added or existing reports modified a bit according to the client’s needs. If this wasn’t the case, I could have one set of reports and use their credential in the query to render the reports with their data. Unfortunately this isn’t the case…
As a result, I’m basically changing the SQL query for every report in the project – I suppose you could say it’s “hard coded” in each report. Once of the simple examples of the SQL query looks like this:
SELECT LIVE_INVOICESUMMARY.*
FROM LIVE_INVOICESUMMARY
WHERE (IS_InvoiceDate >= CONVERT(DATETIME, @fromdate, 103)) AND (IS_InvoiceDate <= CONVERT(DATETIME, @todate, 103)) AND (IS_SUCODE = 'DH166')
It’s the IS_SUCODE = 'DH166' bit I’m changing every time from ‘DH166’ to, say, ‘DZ999’.
I know I can use a parameter in the query; I think I can pass the results of some custom code to a parameter as an expression in the default, non-queried value. Am I right? I guess it is the format and syntax of the custom code (and how to call the result) that I’m lost with.
I think I’m
OKcoping with the SQL aspect, learning as I go along. Is it possible to call the result of the custom code directly into the SQL query? In theory this would be easier as it would eliminate the need to create and use the parameter.Does that make more sense? Thanks for you input Jon. Any help at all is welcome.
-
I think I understand now! You produce a new project for each customer (from the base project) and then tweak the individual reports as they require.
Depending on how you name your projects and which folder you deploy them to, you could just use the global constant
Globals!ReportFolder
in an expression in the custom code.
-
I don't have any particular format for naming the folders. It is usually just the customer's name, so I don't think this will work.
-
Can you implement a standard naming convention then? If so, you could create a table that maps the folder name (or part of) to the customer code required in the query. It'll be easy enough to add new customers in the future, apart from having to create all the reports, of course.
-
Thanks for your help. I think I'm going to give up on this line of enquiry. My brain hurts now, and as far as I can tell there doesn't seem to be an easy way of setting a parameter for the whole project which can be used by all of the reports; at least not without a steep learning curve for me!
-
I'm sure this would work. You need something which is unique to each project. Globals!ReportFolder is just such a thing. As long as each project is associated with a different customer and each customer looks at a different folder on Report Manager, you're there!
But hey, it's Friday afternoon, so sleep on it over the weekend...
-
Well said, Jon - saved me the trouble.
I did a bit of dabbling and it seems easy enough to get one of the Globals into a SQL function. From there, it would be straightforward to look up the customer code in a mapping table, and the function could return this code. The query in the report then uses the function as part of the Where clause.
-
Maybe you're right, I'll give it a go.
Is there anything else that can held or referenced at project / global level?
-
the only other constant is Globals!ReportServerURL. BTW these are set by right-clicking on the project name in solution explorer and choosing properties.
-
Am revisiting this issue.
Can anyone enlighten me as to how to get the Globals into the SQL function?
E.g., if I use a convention where the folder name starts with the account code I would use for the SQL criteria:
DA001 CustomerName
DB999A Another Customer
So I only use the 5, 6 or 7 characters before the first space in the folder name?
Cheers!
-
You could just pass the global variable as a parameter to a stored procedure.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply