Query criteria / parameter for a whole project.

Question

Post reply

Query criteria / parameter for a whole project.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 1

Hi Jon,

No idea how to use stored procedures - as I've said before, I'm a beginner!

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 2

The following shows code that uses a Reporting Services report parameter in a SQL function.

Select dbo.fn_MyFunction(@TestValue) As MyValue

If you wanted to use a stored procedure, then it would be similar:

Exec dbo.MyStoredProc @TestValue

@TestValue is a Reporting Services report parameter.

I'm not sure if this helps you, so please ask if you need further info.

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 3

Hi Michael - not really sure what that does or how to use it - beginner status I'm afraid

I had a vague plan as follows - to create a default, non-queried report parameter using an expression on each report in the project and call it something like 'accnumber'. This could look at the Global called ReportFolder. Assuming I stick to the naming convention which has my customer's account code as the first 5/6/7 characters then a space, I should be able to get the expression to return the account number as a parameter which can be used in the SQL criteria.

So my questions are..

1. Can this work?

2. If it does, what expression to use? I've tested the theory and can get the parameter value to appear on the face of the report using this expression: "=left( Globals!ReportFolder, 5)"; for a test report deployed to folder called "TEST Deployment Library" it returns "/TEST". My problem is that the account can be a variable length string between 5 - 7 charcters.

Thanks for the help guys

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 4

I would create a SQL function that strips the account number out of the Report Folder string. This function should return just the account number. So, if you pass in 'DAB01 My Customer' to the function, you'll get 'DAB01' returned. This can then be used as criteria in a SQL query.

Let's say you have an RS parameter called @ReportFolder. You could use some SQL like this:

Select *

From MyTable

Where MyTable.AccountNumber = fn_GetAccountNumber(@ReportFolder)

This would return all rows from MyTable where the Account Number was the one that corresponded with your @ReportFolder parameter. fn_GetAccountNumber would return just the account number from the Report Folder string as specified above.

Paul.Mitchell. Ten Centuries Points: 1131 More actions · Answer 5

Managed to make it work!

Did what I suggested above and used this expression to create the default, non-queried report parameter:

=Mid( Globals!ReportFolder ,2,InStr( Globals!ReportFolder, " ")-2)

This extracts the customer's account number from the folder name string.

Than set the parameter so it doesn't appear when the report runs. Don't know if it is the most efficient solution, but I can understand it and it seems to work OK.

Thanks for all input, greatly appreciated.

Michael Lysons SSCertifiable Points: 6492 More actions · Answer 6

Nice one, mate

I strongly suggest you look into making a function that strips out the account number, using similar T-SQL code to the code you posted above. Your code will be easy to reuse then and if it ever needs to change - say, the format of the folder name changes - then it's just one change in a function and everything continues to work.