parameter properties in report manager - "Has Default"

ok I've designed a report template that uses cascading paramters based
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...

Parameter prompt value

is there a way to get parameter prompt value inside the report?
If i have parameter 1 with a prompt value "Customer ID", how can i get
"Customer ID" string value?Not really documented anywhere that I have seen but this is how to do it.
Parameters!ParamName.Label
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bosstan1@.gmail.com> wrote in message
news:1141676084.764341.275170@.e56g2000cwe.googlegroups.com...
> is there a way to get parameter prompt value inside the report?
> If i have parameter 1 with a prompt value "Customer ID", how can i get
> "Customer ID" string value?
>

parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo reply

parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo replysql

Parameter Problem in Scheduled Report

Hi,
I have a Monthly Report set to be generated in everyday 7am. The default
value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
extract a OCT report, it could be changed the parameter in Report Manager
(i.e. override default of MONTH parameter); however, once it is changed to
10, the scheduler would based on 10 to generate the Montly report afterward.
Is there any method to restore the default value without redeploy the report?
Thanks.
Yan.Why don't you or your users manually change the value of the parameter in
Report Viewer instead?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"YeungTakYan" <YeungTakYan@.discussions.microsoft.com> wrote in message
news:DF08AB98-747C-4C4C-91EE-F8AC7AEDD284@.microsoft.com...
> Hi,
> I have a Monthly Report set to be generated in everyday 7am. The default
> value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
> extract a OCT report, it could be changed the parameter in Report Manager
> (i.e. override default of MONTH parameter); however, once it is changed to
> 10, the scheduler would based on 10 to generate the Montly report
> afterward.
> Is there any method to restore the default value without redeploy the
> report?
> Thanks.
> Yan.|||Once schedule is set, the parameter (textbox, dropdown list, ... ) becomes
disabled. So the parameter could not be changed in the View mode.
"Alain Quesnel" wrote:
> Why don't you or your users manually change the value of the parameter in
> Report Viewer instead?
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "YeungTakYan" <YeungTakYan@.discussions.microsoft.com> wrote in message
> news:DF08AB98-747C-4C4C-91EE-F8AC7AEDD284@.microsoft.com...
> > Hi,
> >
> > I have a Monthly Report set to be generated in everyday 7am. The default
> > value of the MONTH parameter is previous month (i.e. Nov). Yet if need to
> > extract a OCT report, it could be changed the parameter in Report Manager
> > (i.e. override default of MONTH parameter); however, once it is changed to
> > 10, the scheduler would based on 10 to generate the Montly report
> > afterward.
> > Is there any method to restore the default value without redeploy the
> > report?
> >
> > Thanks.
> >
> > Yan.
>

Parameter problem

HI, I have a report that users select a parameter value from a dropdownbox.

I want to be able to limit the number of parameter options depending on who is logged in.

user A can only see values 'ABC', 'DSC', 'BHT''

whereas user B can see values 'MKP','NHJ','BLP'

is this possible? if so how?

TIA

Reporting services provides the "User!UserID" global parameter in all its reports. This populates with the windows user name if I'm not mistaken. You could pass this value as a parameter to the dataset that you're using to populate the dropdown box.|||If the datasource is driven by a view or a stored procedure or a query you can implement row-level security using a mapping table which locks ( locks up down the appropiate values which should not be seen by some people. If you have a static list, this would be too hard to implement / impossible (Nothing is impossible, impossible just takes longer :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I was hoping to be able to use custom code to populate the parameter dropdownlist/ set the parameter values.

Is this possible? can I reference datasets in my code?

|||

I created a view then set a filter on the dataset to return values based on the User!UserID=UserName field from dataset

then set the parameter to 'From Query' chose the dataset and appropriate field from dataset and everything aok.

|||Welcome to rowlevel security :-)|||

I am trying to do the something similar. In my report i created a stored procedure that stored procedure is passed a parameter CaseNumber. (I am using the Report Veiwer Control in Remote Mode). In Report Designer, I created to datasets the first dataset populates the dropdown list box and it is displayed in the toolbar. The Second dataset takes this parameter (CaseNumber) and brings back the report. Everything works fine in the Report Manager; however, when I run it from frmReport.aspx page the report does not show.

If I run the report without any parameters from the frmReport.aspx page (still using the stored procedure w/o using parameters) the report shows. Can any body help me with this.

Zachary

|||

Fixed the problem - thanks

Zachary

Parameter Problem

Hello:
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

sql

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

Parameter passing in SQL Server 2005 Integration Services (SSIS) 2005 from VB.Net2005

Is it possible to parm in a value to a SSIS

in my SSIS i have a variable;

Name : FileName
Scope : PackageName
Type : String

Value : ""

I have tried adding the following code in my vb.net project ;

pkg.Variables("filename").Value = "C:\temp\testfile.001"
pkg.execute()

but come up with the following error

A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.

can anyone help ?

Make sure the object type for "pkg" is Package, not IDTSPackage90. That interface doesn't expose the variables collection.

Also, you should use the VariableDispenser to lock the variable before writing to it.

|||

hiya this is the code im using

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim ExecEvent As IDTSEvents90

Dim result As DTSExecResult

pkgLocation = "\\Sql1\2007_DTS\FTPSLAVE.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, False, ExecEvent)

pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

i have a warning which i diont understand what it means or its implications

Warning 2 Referenced assembly '..\..\..\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEParseMgd.dll' targets a different processor than the application. testbed

|||

Do you have your application set up for AnyCPU, or is it targeting a specific one (x86 or x64)?

Also, is the code you posted above what you were using previously, or did that correct the first problem?

|||

HI John,

Sorry for the delay in getting back to you .

The application is set to use any CPU and the code is the full function as apposed to a sample snippit.

Im still stumped as to why this wont work ....

|||

I just tested this code - try it out and let me know if it works for you. There are a few changes from your version.

Code Snippet

Dim pkgLocation As String = ""

Dim App As New Application

Dim pkg As New Package

Dim result As DTSExecResult

pkgLocation = "Your Package Path.dtsx"

Try

pkg = App.LoadPackage(pkgLocation, Nothing)

'pkg.Variables("filename").Value = "C:\temp\qqq.xxx"

pkg.Execute()

MsgBox("DTS Executed - result - " & result.ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

MsgBox(" Execution error " & ex.Message)

End Try

|||

John,

thank you very much for the code, that part works great, but, The DTSX (TestProj) is importing a file into two SQL tables, and what i have created a variable as follows;

Name Scope Data Type Value

ImpFileName TestProj String

What i need to do is be able to parm a filename into impfileName fromm the Vb Application at runtime. It was so simple in the old DTS, but i am stumped here

|||

Hi Pete

I haven't done this in vb but here is the code that works in c#

load the package from the server

_ExecutePackage = _DTSServer.LoadFromDtsServer(PackageFolder + PackageName, SSISServer, null);

you have to lock the variable for writing

_ExecutePackage.VariableDispenser.LockForWrite("ImpFileName");

create a new variables object that will be used in the getvariables call

Variables _PackageVars = null;

_ExecutePackage.VariableDispenser.GetVariables(ref _PackageVars);

loop through one or more variable

foreach (Variable v in _PackageVars)

{

v.Value = "what ever you want it to be";

}

release the lock on the variables

if (_PackageVars.Locked)

{

_PackageVars.Unlock();

}

execute the package

PackageResult = _ExecutePackage.Execute();

Hope this helps

|||

John,

Carnt say thank you enough, although im using vb, that c# example showed me exactly where i was going wrong ... Its works !!!!!

Thank you

parameter modification within mdx

Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @.bucket = 6

how would you go about writing @.bucket + 1

so @.bucket now would equal 7

is this possible?

Your options are:

1. Call an SSAS stored procedure from the generated MDX statement, e.g.

SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(MyAssemblyName.MyStoredProcedureName(@.ProductProduct), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. If you want to use an SSRS code-behind function, you need to use an expression-based query, e.g.:

="SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(" & Code.SomeProcedure(Parameters!Product.Value & "), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Parameter missing value after deployment

Hi Friends,

I'm using MSRS 2005. I have created a drillthrough report using parameter. If I try to execute locally it works fine. I am able to call sub report from the master report by passing parameter. But, If I rey to access the report after deploying on to reporting server, I am getting error patameter xxxx is missing a value. What could be wrong? Please help

Thanks & Regards,

Naveen J V

This may be something to do with deployment. I've sometimes found that after deploying my report parameters don't behave as expected. The solution in the past has either to try to re-deploy (sometimes several times) or delete the reports from the report server and deploy again. A warning would be that the second approach the reportID's (GUIDs) wi8ll be re-created so usage stats will get screwed up.

To test whether it's a deployment issue, I suggest deploying to a different target directory to see if the reports work from there.

|||

Hi Adams

Thanks for the quick turnaround. I have tried deploying it into different servers many times. Everywhere, it throws the same error.

Thanks & Regards,

Naveen J V

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".sql

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".

Parameter label property is null when passed to subreports

I have a main report that passes a parameter(pTime = Value: "2004" Label:
"Fiscal 2004") to a sub-report. When I print out Parameters!pTime.Label on
the main report it diplays fine. When I print it out on the subreport it
shows a null value. It seems this parameter label property is not passed
along to the subreport.
Is this bug fixed? If not, does anyone know of a work around. I do not
want to have to look up the label in all my subreports.
FYI: The Parameters!pTime.Value property is passed to the subreport fine.
Thanks,
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________Hi Dan,
Thanks for your posting!
I am trying to reproduce it on my side, I will keep you updated when I find
there is anything valueable to add.
Thank you for your patience and corporation.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Dan,
I am afraid that it is by design. An available values list contains a set
of value/label pairs. When the report is run, the user sees the label. When
the user selects a label, the corresponding value is used as the parameter
value .
However, when parameter value is transferred to subreport, it is not
neceesary to manully choose lable and the parameter value from parent
report is transferred to subreport automatically.
I am not sure why you would like to see label of a parameter in subreport?
Is it possible for you to find a workaround in your project?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||> I am not sure why you would like to see label of a parameter in subreport?
I have to display the prompt descriptions(Labels) on the subreports detail
section. I am querying against an AS cube with MDX to populate parameter
lists.
I the use the Value to MDX query cube data in the subreport.
The values are returned in a format similar to this: "[Gender].[Male]"
with a Label of "Male"
I need to display "Male" on the subreport.
> Is it possible for you to find a workaround in your project?
Yes, I have found a method to extract the text I need from the Value using
expressions.
Thanks,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:%23duw3RdAFHA.764@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> I am afraid that it is by design. An available values list contains a set
> of value/label pairs. When the report is run, the user sees the label.
When
> the user selects a label, the corresponding value is used as the parameter
> value .
> However, when parameter value is transferred to subreport, it is not
> neceesary to manully choose lable and the parameter value from parent
> report is transferred to subreport automatically.
> I am not sure why you would like to see label of a parameter in subreport?
> Is it possible for you to find a workaround in your project?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Parameter label field

How do I refer to the label field of a parameter? I have a parameter named
Group with a lable field of 'HR' and value field of 23. I'm puting a text
box at the top of my report with the parameters in it. I dont want to
display '23' (which is what Parameters!Group.Value returns), I want to
display 'HR'
Thanks,
DougI almost hate to tell you this.
Parameters!Group.Label
The expression builder doesn't show you this but just type it in.
Bruce L-C
"Doug Stiers" <doug@.nospam.com> wrote in message
news:OhP5onOlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> How do I refer to the label field of a parameter? I have a parameter named
> Group with a lable field of 'HR' and value field of 23. I'm puting a text
> box at the top of my report with the parameters in it. I dont want to
> display '23' (which is what Parameters!Group.Value returns), I want to
> display 'HR'
> Thanks,
> Doug
>|||I cant believe I didnt think of that. Thanks.
Doug
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23DCmz7OlEHA.596@.tk2msftngp13.phx.gbl...
> I almost hate to tell you this.
> Parameters!Group.Label
> The expression builder doesn't show you this but just type it in.
> Bruce L-C
> "Doug Stiers" <doug@.nospam.com> wrote in message
> news:OhP5onOlEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > How do I refer to the label field of a parameter? I have a parameter
named
> > Group with a lable field of 'HR' and value field of 23. I'm puting a
text
> > box at the top of my report with the parameters in it. I dont want to
> > display '23' (which is what Parameters!Group.Value returns), I want to
> > display 'HR'
> >
> > Thanks,
> > Doug
> >
> >
>

Parameter key/value list in the actual report?

We are in need of a visualization of all parameters (about 16 of them)
for our reports. (When a cutstomer prints a report, we need to have the
parameters (mostly filterering input) values available)
Is there a way if "looping" the Parameters Collection, creating a String
or populate a table with the parameter/value pairs?
We tried to create a string by looping the Report.Parameters object in
the "Code" section, but it is not a collection so we failed there.
Also tried to access the parameters "by index". Looping until we fails,
but it seems you cant access the paremeters by index :(
The solution we have is to create a table with a field for each
parameter. But this is a non dynamic and non generic solution. A bit of
worl work too for our 25+ reports with about 10-15 parameters each.
Anyone out there have a solution for this problem?
Regards
Jonas MontonenJFYI, parameters can be accessed by name, i.e.
=Parameters("Param1").Value (same as =Parameters!Param1.Value)
Or even =Parameters(Field!A.Value).Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Montonen" <jonas.montonen@.ascade.se> wrote in message
news:OMW23aBwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> We are in need of a visualization of all parameters (about 16 of them) for
> our reports. (When a cutstomer prints a report, we need to have the
> parameters (mostly filterering input) values available)
> Is there a way if "looping" the Parameters Collection, creating a String
> or populate a table with the parameter/value pairs?
> We tried to create a string by looping the Report.Parameters object in the
> "Code" section, but it is not a collection so we failed there.
> Also tried to access the parameters "by index". Looping until we fails,
> but it seems you cant access the paremeters by index :(
> The solution we have is to create a table with a field for each parameter.
> But this is a non dynamic and non generic solution. A bit of worl work too
> for our 25+ reports with about 10-15 parameters each.
> Anyone out there have a solution for this problem?
> Regards
> Jonas Montonen|||Lev Semenets [MSFT] wrote:
> JFYI, parameters can be accessed by name, i.e.
> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
> Or even =Parameters(Field!A.Value).Value
>
Yes, that we know.
What I want is to be able to (programmatically) loop all parameter and
there values, appending them to a tring and display that string in the
report.
So if I add a parameters, I will not have to update the logic for
displaying the parameter values.
Regards Jonas Montonen|||Jonas Montonen wrote:
> Lev Semenets [MSFT] wrote:
>> JFYI, parameters can be accessed by name, i.e.
>> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
>> Or even =Parameters(Field!A.Value).Value
> Yes, that we know.
> What I want is to be able to (programmatically) loop all parameter and
> there values, appending them to a tring and display that string in the
> report.
> So if I add a parameters, I will not have to update the logic for
> displaying the parameter values.
> Regards Jonas Montonen
As a workaround I'm trying another funtion in the Code section.
But I would like to have a identical function in each report... so some
parameter could be missing.
Is there a way of refering/checking if a parameter exists and not get
the error '
Eg:
IF(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
.
.
End if
This line generates this error today :(
"The value expression for the textbox ?textbox2? contains an error: The
expression referenced a non-existing parameter in the report parameters
collection."
FYI:
- Textbox2.Value: "=Code.getParametersHeader()"
- PARAM_THAT_ARE_MISSING is a parmeter that is not present in the actual
report (Dummy to trigger this error)
// Regards Jonas Montonen
Function getParametersHeader()
Dim retString
Dim parCount
Dim newLine
Dim ParamsPerLine
ParamsPerLine = 3
parCount = 0
If(Report.Parameters("Pop").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Pop: " +
Report.Parameters("Pop").Label
end if
if(Report.Parameters("Measure").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Pop: " +
Report.Parameters("Pop").Label
end if
if(Report.Parameters("TimeType").Label <> "") then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "Time Type: " +
Report.Parameters("TimeType").Label
end if
if(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
parCount = parCount +1
newLine = getParamNewLine(parCount, ParamsPerLine)
retString = retString + newLine + "PARAM_THAT_ARE_MISSING: " +
Report.Parameters("PARAM_THAT_ARE_MISSING").Label
end if
Return retString
End Function|||Jonas Montonen wrote:
> We are in need of a visualization of all parameters (about 16 of them)
> for our reports. (When a cutstomer prints a report, we need to have the
> parameters (mostly filterering input) values available)
> Is there a way if "looping" the Parameters Collection, creating a String
> or populate a table with the parameter/value pairs?
> We tried to create a string by looping the Report.Parameters object in
> the "Code" section, but it is not a collection so we failed there.
> Also tried to access the parameters "by index". Looping until we fails,
> but it seems you cant access the paremeters by index :(
> The solution we have is to create a table with a field for each
> parameter. But this is a non dynamic and non generic solution. A bit of
> worl work too for our 25+ reports with about 10-15 parameters each.
> Anyone out there have a solution for this problem?
> Regards
> Jonas Montonen
Not very nice... there must be a better way.
Usage:
TextFieldXX.Value "=Code.getParametersHeader(5, "Pop,AgreementType,
ServiceLevel, CRAZY_PARAM_THAT_DOES_NOT_EXIST")
Function getParametersHeader(paramsPerRow, daWantedParams)
Dim myArray() As String = Split(daWantedParams, ",")
Dim RetVal As String
Dim newLine As String
Dim parCount As Integer
Dim paramsPerLine
ON ERROR RESUME NEXT
For Each curParam As String In myArray ' Iterate through elements.
If(Report.Parameters(curParam.ToString()).Label <> "") then
newLine = getParamNewLine(parCount, paramsPerRow)
RetVal += newLine + curParam.ToString() + ": " +
Report.Parameters(curParam.ToString()).Label
parCount += 1
end if
Next
Return RetVal
End Function
Function getParamNewLine(countValue, valuesPerRow)
if((countValue mod valuesPerRow) = 0) then
return vbcrlf
else
if((countValue mod valuesPerRow) = 1) then
return " "
else
return " | "
end if
end if
End Function|||I'm afraid that is not supported. But I think it is possible to do
indirectly, using custom assemly.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Montonen" <jonas.montonen@.ascade.se> wrote in message
news:%236H$b9LwEHA.1396@.tk2msftngp13.phx.gbl...
> Jonas Montonen wrote:
>> Lev Semenets [MSFT] wrote:
>> JFYI, parameters can be accessed by name, i.e.
>> =Parameters("Param1").Value (same as =Parameters!Param1.Value)
>> Or even =Parameters(Field!A.Value).Value
>> Yes, that we know.
>> What I want is to be able to (programmatically) loop all parameter and
>> there values, appending them to a tring and display that string in the
>> report.
>> So if I add a parameters, I will not have to update the logic for
>> displaying the parameter values.
>> Regards Jonas Montonen
> As a workaround I'm trying another funtion in the Code section.
> But I would like to have a identical function in each report... so some
> parameter could be missing.
> Is there a way of refering/checking if a parameter exists and not get the
> error '
> Eg:
> IF(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
> .
> .
> End if
> This line generates this error today :(
> "The value expression for the textbox ?textbox2? contains an error: The
> expression referenced a non-existing parameter in the report parameters
> collection."
> FYI:
> - Textbox2.Value: "=Code.getParametersHeader()"
> - PARAM_THAT_ARE_MISSING is a parmeter that is not present in the actual
> report (Dummy to trigger this error)
> // Regards Jonas Montonen
> Function getParametersHeader()
> Dim retString
> Dim parCount
> Dim newLine
> Dim ParamsPerLine
> ParamsPerLine = 3
> parCount = 0
> If(Report.Parameters("Pop").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Pop: " +
> Report.Parameters("Pop").Label
> end if
> if(Report.Parameters("Measure").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Pop: " +
> Report.Parameters("Pop").Label
> end if
> if(Report.Parameters("TimeType").Label <> "") then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "Time Type: " +
> Report.Parameters("TimeType").Label
> end if
> if(not Report.Parameters("PARAM_THAT_ARE_MISSING") is nothing) then
> parCount = parCount +1
> newLine = getParamNewLine(parCount, ParamsPerLine)
> retString = retString + newLine + "PARAM_THAT_ARE_MISSING: " +
> Report.Parameters("PARAM_THAT_ARE_MISSING").Label
> end if
> Return retString
> End Functionsql