Customize Report Parameter Properties

Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.No, this is not possible.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> Is it possible to modify the XML code at runtime? I want to control the
> report parameter properties multi-value setting of True/False during
> runtime
> in the XML behind the RDL file. Set it to True if I want the parameters to
> be
> multi-value or False for single-value. This is determined based on the
> value
> selected in parameter one. Parameter one and two are City,State.
> If City is selected in parameter one then I want Parameter two to be a
> single-valued list, if State is chosen in Parameter One then I want the
> list
> in Parameter two to be a multi-valued select list.
> Any ideas or guidance would be appreciated.
>|||Looking at my example, could you recommend an approach to tackle this issue?
"Bruce L-C [MVP]" wrote:
> No, this is not possible.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> > Is it possible to modify the XML code at runtime? I want to control the
> > report parameter properties multi-value setting of True/False during
> > runtime
> > in the XML behind the RDL file. Set it to True if I want the parameters to
> > be
> > multi-value or False for single-value. This is determined based on the
> > value
> > selected in parameter one. Parameter one and two are City,State.
> >
> > If City is selected in parameter one then I want Parameter two to be a
> > single-valued list, if State is chosen in Parameter One then I want the
> > list
> > in Parameter two to be a multi-valued select list.
> >
> > Any ideas or guidance would be appreciated.
> >
>
>|||It is a nice concept but this is not an area you have control over if you
are using the portal (report manager) that ships with Reporting Services. If
you want to create your own web page and then integrate with RS using either
web services or url integration this is certainly possible. RS 2005 ships
with a webform and a winform control for integrating RS reports into your
applications.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> Looking at my example, could you recommend an approach to tackle this
> issue?
> "Bruce L-C [MVP]" wrote:
>> No, this is not possible.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> > Is it possible to modify the XML code at runtime? I want to control the
>> > report parameter properties multi-value setting of True/False during
>> > runtime
>> > in the XML behind the RDL file. Set it to True if I want the parameters
>> > to
>> > be
>> > multi-value or False for single-value. This is determined based on the
>> > value
>> > selected in parameter one. Parameter one and two are City,State.
>> >
>> > If City is selected in parameter one then I want Parameter two to be a
>> > single-valued list, if State is chosen in Parameter One then I want the
>> > list
>> > in Parameter two to be a multi-valued select list.
>> >
>> > Any ideas or guidance would be appreciated.
>> >
>>|||Thank you for your reply. So if I create a separate web page, can I reference
this page as a link from Report Manager going out and launching this custom
page/report? Would I find the winform/webform via Visual Studio?
"Bruce L-C [MVP]" wrote:
> It is a nice concept but this is not an area you have control over if you
> are using the portal (report manager) that ships with Reporting Services. If
> you want to create your own web page and then integrate with RS using either
> web services or url integration this is certainly possible. RS 2005 ships
> with a webform and a winform control for integrating RS reports into your
> applications.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
> > Looking at my example, could you recommend an approach to tackle this
> > issue?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, this is not possible.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
> >> > Is it possible to modify the XML code at runtime? I want to control the
> >> > report parameter properties multi-value setting of True/False during
> >> > runtime
> >> > in the XML behind the RDL file. Set it to True if I want the parameters
> >> > to
> >> > be
> >> > multi-value or False for single-value. This is determined based on the
> >> > value
> >> > selected in parameter one. Parameter one and two are City,State.
> >> >
> >> > If City is selected in parameter one then I want Parameter two to be a
> >> > single-valued list, if State is chosen in Parameter One then I want the
> >> > list
> >> > in Parameter two to be a multi-valued select list.
> >> >
> >> > Any ideas or guidance would be appreciated.
> >> >
> >>
> >>
> >>
>
>|||Well, the only way you could do this is to have a report that just has
links. You would need to have your own website. Usually people either use
Report Manager or provide their own front end via their own application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
news:EFC6D25C-E313-4398-92F6-1C5651FABFB9@.microsoft.com...
> Thank you for your reply. So if I create a separate web page, can I
> reference
> this page as a link from Report Manager going out and launching this
> custom
> page/report? Would I find the winform/webform via Visual Studio?
> "Bruce L-C [MVP]" wrote:
>> It is a nice concept but this is not an area you have control over if you
>> are using the portal (report manager) that ships with Reporting Services.
>> If
>> you want to create your own web page and then integrate with RS using
>> either
>> web services or url integration this is certainly possible. RS 2005 ships
>> with a webform and a winform control for integrating RS reports into your
>> applications.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> news:2E596365-10F4-45F0-A89C-AA99568E53DE@.microsoft.com...
>> > Looking at my example, could you recommend an approach to tackle this
>> > issue?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No, this is not possible.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "arulibaba" <arulibaba@.discussions.microsoft.com> wrote in message
>> >> news:939952BF-0FB7-42A8-AF1C-A9E4005BCAF5@.microsoft.com...
>> >> > Is it possible to modify the XML code at runtime? I want to control
>> >> > the
>> >> > report parameter properties multi-value setting of True/False during
>> >> > runtime
>> >> > in the XML behind the RDL file. Set it to True if I want the
>> >> > parameters
>> >> > to
>> >> > be
>> >> > multi-value or False for single-value. This is determined based on
>> >> > the
>> >> > value
>> >> > selected in parameter one. Parameter one and two are City,State.
>> >> >
>> >> > If City is selected in parameter one then I want Parameter two to be
>> >> > a
>> >> > single-valued list, if State is chosen in Parameter One then I want
>> >> > the
>> >> > list
>> >> > in Parameter two to be a multi-valued select list.
>> >> >
>> >> > Any ideas or guidance would be appreciated.
>> >> >
>> >>
>> >>
>> >>
>>

Customization at runtime

I have a report which i am using in vb6.

it has following
itemid, itemname,price

but i want to give user choice to change this order at runtime,
according to him like
itemname,itemid,price or it can be any combination.

Ideas will be appreciated.
Thanks in AdvanceHi,
yes you could but not Only using CR but VB + SQL manupulating to suit u r requirement

the SQL which brings u data has to be
select Itemid as Filed1, itemname as Filed2 , price as Filed3

ie u let the user decided the orer he wants (in VB he decides through a ordering using selecting from a Left Listbox to right Listbox)

the according to that u Generate the SQL

select Itemid as Filed1, itemname as Filed2 , price as Filed3
Or
select itemname as Filed1, Itemid as Filed2 , price as Filed3
Or
......(u can simply do this depending on the use selection)

but in crystal Report use a 'Field Definitions Only' in (Database Expert --> create new connection ) then create a .ttx file with 3 field of type String with name Filed1, Filed2, Filed3 then place them in report in order

in VB set the DS value got from SQL to this report the

Note : the Order is Truely dynamic in SQL but user feels like the rpt is, but u have created only one Rpt.

Hope u got it
FaFa|||Thanks it worked.

Do you have any idea about letting user to place fields at specified position.

Thanks in Advancesql

Custom Task w/ runtime user interaction

I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.

Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.

Any thoughts?

Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||

That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.

I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.

i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.

My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.

I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.

|||

For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.

Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.

Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.

Custom Task w/ runtime user interaction

I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.

Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.

Any thoughts?

Is it possible? Yes. Is it recommended? No. SSIS is really designed for batch, non-interactive applications. It would be much better to write a custom app that gathers all the user input in the beginning, and then launches the SSIS package, setting any necessary variables based on the user input at that point.|||

That sounds like a great plan...only I cannot figure out a way to do that which does not require writing an entire program, figuring out a way to store the variable in a file, or somehow PASS it to the SQL SSIS Package, and I have NO IDEA how to do that.

I have the EXACT same need as the person who asked you the question, and I'd be happy with manually entering the date one character at a time. The SSIS Package I am writing pulls special information from a database and places it in a text file with fixed widths. I have everythign on this working perfectly. The problem is that Every three Months - I have to write it again from scratch because the starting date changes.

i just want the simplest way to enter a NEW Starting Date (even if that means storing it somewhere on a file server), and run the Agent using the SSIS Package to make it work.

My Perfect Solution would be a THREE STEP Agent, with Step One getting the variable and passing it to step 2, Step 2 being the query I have now importing the date it got from step one, and step three being the notify portion when it is done.

I can find NO USEDUL INFORMATION on how to do this...or maybe I just don't get it.

|||

For that, I would define a variable in the Package to hold the start date. Then use DTEXEC with the /SET switch, which allows you to set a variable's value from the command line. Agent can run DTEXEC, passing it the /SET value.

Another option would be to store the date in a table on your database, and read it into the package using an Execute SQL task.

Since you are running this from Agent, you really don't want a UI, since no user will be there to respond to it.

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT