Dynamically passing a Parameter Name to Custom Code

Hello,

I'm trying to create a custom code function for Reporting Services. I would like to have it user-friendly and give the user the ability to pass a report parameter name into the function (so it can be a generic function that can be used for many reports).

Is there a way to do this so inside the code I can have access to other properties of the object?

I envision something like:

Function Blah(ParameterName as String) as String

Dim MaxNum as Integer
Dim ParamValue as String

MaxNum = Reports.Parameters!ParameterName.Count - 1
ParamValue = Reports.Parameters!ParameterName.Value

etc...

Is this possible? I can't figure out how to do this. Using dynamic SQL you can do this very easily by concatenating string values together and then executing the string. Is there something similar to this in VB?

Help! Thanks

Below is an example for a custom code function that you can call in a textbox e.g. as

=Code.ShowParametersValues(Parameters!Country)

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

-- Robert

Dynamically Passing a Dataset to a Report in Reporting Services 2005

We are investigating the possibility of moving our reports from a Crystal Reports environment over to Reporting Services 2005. Currently we sometimes create a ADODB.Recordset in VB6 code which is passed to a Crystal Report to be displayed or printed. The Crystal Report structure has previously been created using a .ttx file and the recordset then supplies the data at runtime.

Could someone please tell how this scenario can be achieved using VB.Net 2005 and Reporting Services.

Thank you, in advance.

The equivalent of doing this in SSRS-land is to use the VS 2005 Report Viewer contorl in local mode (the report is processed at the client, not on the SSRS server), provide it with the RDL for the report (called RDLC), and "marry" a dataset to the datasource of the control. You'll find examples of same in books online and out on gotreportviewer.com

dynamically change db servers

hi all,

I am confronted with multiple database servers with database names DB-1, DB-2, etc., table names are all the same.
I'm passing the DBServerName and DBname as variables in my stored procedure.
Is there any better ways to choose different server names other than using EXEC as shown in the sp below?

Any comments are greatly appreciated ;)

CREATE PROCEDURE sp_MyTest
@.SERVERNAME varchar(50),
@.DBNAME varchar(50),
@.CUST_ID varchar(10),
AS
Begin
DECLARE @.strsql VARCHAR(800)

SET @.strsql='Select Cust_ID From ['+@.SERVERNAME+'].['+@.DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@.CUST_ID+'''
EXEC (@.strsql)
Endthats pretty much like i would go with|||Originally posted by Enigma
thats pretty much like i would go with

Hi,
One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

If in same database u can use databasename.username.objectname

irrespective of procedure where ever it is.|||Hi,
One small suggestion if u have different servers , configure for remote server or linked server options u can use any object by giveing the servername.databasename.username.objectname.

If in same database u can use databasename.username.objectname

irrespective of procedure where ever it is.

i believe thats what he is doing when he says

SET @.strsql='Select Cust_ID From ['+@.SERVERNAME+'].['+@.DBNAME+'].dbo.[CUSTOM] WHERE CUST_ID='''+@.CUST_ID+'''

Dynamic sub-reports with parameter passing?

Hello -

I wanted to know if we can have a fully dynamic sub-reports in CR. I will explain my scenario below..please let me know how i should proceed. I have two sub-reports which uses two different stored procedures to get the required data. Both the stored procedures will take three parameters each.

Sub-Report1: AIM.rpt which uses stored procedure "aim_sp"
Sub-Report1: IND.rpt which uses stored procedure "ind_sp"

aim_sp will take "start_dt","end_dt" and "unit" as parameters
ind_sp will take "start_dt","end_dt" and "unit" as parameters as well.

My requirement is to get the required parameters from the main report(main.rpt). These two sub-reports are shown using this blank report file. The only functionality of the main.rpt is to embed the two sub-reports and get the three parameters. And then, pass the parameters from main report to the two sub-reports. And then, the sub-reports will use the two sub-reports to run the stored procedures and get the data dynamically.

What i did??
----

I created the blank main.rpt file and created three parameters. And then i attached the sub reports to the main report. The sub-reports are themselves seperate report files which have stored procedure as database fields. But when i refresh the main report, it is totally looking for nine parameters. Three are from the main report file. Next three are from the stored procedure "aim_sp" and the next are from "ind_sp".

How can we link the main report parameters to all the sub-report parameters in the back ground and let it ask only three parameters?

Please advice me...
THanks,Right-click on subreport whilst its embedded on main report, select Change Subreport Links option. I will use date fields as an example.
Select the ? fields (paramater fields) from the "Available Fields" list, and when you add them to "Fields To Link To", you should be able to link them to a data field in your subreport. That's provided you have a date field in your subreport...you'll need one, otherwise you can't do the selection. If you don't want to show the date field in the subreport, suppress it, it will still be evaluated against.
Then in subreport's Report menu, select Edit selection Formula and select Record... option.
formula would look something like this:
{Database.DateField} >={?dteStart} AND {Database.DateField} <={?dteEnd}

That should work, from what I can recall.

dave