Dynamically collapsing textboxes (and adjusting layout)

Just curious if there's a way I can remove a field from my report at run time and shift all the fields underneath it up.

I basically want to end up with the following:

Design time=============================Field 1: Fields!Field1.ValueField 2: Fields!Field2.Value// This one will be blankField 3: Fields!Field3.ValueField 4: Fields!Field4.ValueRun Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"

Problem is, if I set the visibility of the field to false, it still takes up space on the form (as it should). Any suggestions as to how to shift all the fields up without using a table?

Thanks!

I guess I should clairify a bit

This is what is happening now:

Run Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"I want to collapse Field 2 up so that thereis no whitespace between 1 and 3

Not sure if this is even possible...

Dynamically Change the Grouping

Hi!
I am trying to change the Grouping on the report based on which field a user
select. Basically I want to change the way report looks when the user select
a particular field as Group By option. Can any one help me with is.
For Example:
A Report has ClientName, Address, State, City. On the Report, I set Grouping
Property to "ClientName". I want to pass in a parameter that will change
the Grouping Expression to "State" and show the result.
I really need help on this. Can you provide me with an example?
Thank you
Darshan1) Create a new Parameter and call it GroupBy.
2) Since you only have two values to group by, just hard code your
selectable values into this parameter. i.e. ClientName and State
3) Edit your grouping. Instead of using =Fields!ClientName.Value for your
hardcoded group value, refer to the newly created parameter.
To do so, in the group property screen select <Expression...>. Next select
the newly created parameter on the left box, and insert it into the right
box. Or manually type the new parameter in the expression box. i.e.
=Parameters!GroupBy.Value
Now it you have dynamic grouping. Hope that helps - Holler if I was unclear.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Ok, ignore my first instructions. /shakes off the rust...
Here's how you do it.
1) Create a Parameter call it GroupBy
2) Add two new static values
3) Set the first Label = "ClientName", set its Value to 0
4) Set the second Label = "State", set its Value to 1
Now in your group by expression you have to build a conditional expression:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName,Fields!State.Value)
which translates to If GroupBy equals zero, then group by ClientName field,
else group by State field.
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||had a typo in my sample expression, it should be:
=iif(Parameters!GroupBy.Value = 0,Fields!ClientName.Value,Fields!State.Value)
"Darshan" wrote:
> Hi!
> I am trying to change the Grouping on the report based on which field a user
> select. Basically I want to change the way report looks when the user select
> a particular field as Group By option. Can any one help me with is.
> For Example:
> A Report has ClientName, Address, State, City. On the Report, I set Grouping
> Property to "ClientName". I want to pass in a parameter that will change
> the Grouping Expression to "State" and show the result.
> I really need help on this. Can you provide me with an example?
> Thank you
> Darshan
>
>|||Thank you for the example. I have another issue on the same topic:
I have multiple fields then hard coding the value may not be the solution.
Is there any other way to achieve the grouping.
Basically, the user can pick any one field from the list that they can group
by. Can I code a vb function on the report that would allow me to pass the
value.
Example
Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
as String
lsDir = "Fields!" + GroupByStr +".Value"
GroupByClause = lsDir
End Function
Let me know.
Again thank you for all the help in advance.
"RS_CZAR" <ichijoe@.hotmail.com> wrote in message
news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> had a typo in my sample expression, it should be:
> =iif(Parameters!GroupBy.Value => 0,Fields!ClientName.Value,Fields!State.Value)
>
> "Darshan" wrote:
>> Hi!
>> I am trying to change the Grouping on the report based on which field a
>> user
>> select. Basically I want to change the way report looks when the user
>> select
>> a particular field as Group By option. Can any one help me with is.
>> For Example:
>> A Report has ClientName, Address, State, City. On the Report, I set
>> Grouping
>> Property to "ClientName". I want to pass in a parameter that will change
>> the Grouping Expression to "State" and show the result.
>> I really need help on this. Can you provide me with an example?
>> Thank you
>> Darshan
>>|||Try using the Fields() collection passing in the indexer of your fieldname as
is shown here:
http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx
Let me know if you get this to work...I am using SSRS 2005 and cannot get it
to work if I don't specify a grouping...
"Darshan" wrote:
> Thank you for the example. I have another issue on the same topic:
> I have multiple fields then hard coding the value may not be the solution.
> Is there any other way to achieve the grouping.
> Basically, the user can pick any one field from the list that they can group
> by. Can I code a vb function on the report that would allow me to pass the
> value.
> Example
>
> Public Shared Function GroupByClause(ByVal GroupByStr as String) Dim lsDir
> as String
>
> lsDir = "Fields!" + GroupByStr +".Value"
>
> GroupByClause = lsDir
> End Function
> Let me know.
>
> Again thank you for all the help in advance.
> "RS_CZAR" <ichijoe@.hotmail.com> wrote in message
> news:9ACFAE3D-38A6-42DB-AA9A-84217686E49D@.microsoft.com...
> > had a typo in my sample expression, it should be:
> >
> > =iif(Parameters!GroupBy.Value => > 0,Fields!ClientName.Value,Fields!State.Value)
> >
> >
> > "Darshan" wrote:
> >
> >> Hi!
> >>
> >> I am trying to change the Grouping on the report based on which field a
> >> user
> >> select. Basically I want to change the way report looks when the user
> >> select
> >> a particular field as Group By option. Can any one help me with is.
> >>
> >> For Example:
> >>
> >> A Report has ClientName, Address, State, City. On the Report, I set
> >> Grouping
> >> Property to "ClientName". I want to pass in a parameter that will change
> >> the Grouping Expression to "State" and show the result.
> >>
> >> I really need help on this. Can you provide me with an example?
> >>
> >> Thank you
> >> Darshan
> >>
> >>
> >>
>
>

Dynamically Adding Subreports

I would like to Create a report that will print as one document, but is
basically made up of multiple reports (rdl files), however, I need to be able
to determine which reports (rdl files) should be added and pass parameters to
each rdl file dynamically. Can this be done with SQL Reporting Services?You could do that in VS 2005 using the RS Winforms/Webforms controls in
"local" processing mode and registering a subreport callback which
dynamically provides different RDLs with different sets of parameters.
I don't really have a good suggestion for RS 2000 - you could probably
achieve some of your requirements by dynamically generating RDLs, publishing
and rendering them by writing an application that uses the SOAP-API.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Amie Fedric" <AmieFedric@.discussions.microsoft.com> wrote in message
news:6194746B-7D5B-49CE-8498-50AD8EF3B561@.microsoft.com...
>I would like to Create a report that will print as one document, but is
> basically made up of multiple reports (rdl files), however, I need to be
> able
> to determine which reports (rdl files) should be added and pass parameters
> to
> each rdl file dynamically. Can this be done with SQL Reporting Services?

Dynamic Text Parser?

Hi Guys,

I have a script task that is supposed to read and parse a fixed width source file.

Basically, I want to make the FieldWidths dynamic so that I'll be able to reuse this package with different files. So Instead of hardcoding the field widths directly into my script task, I want it to be stored somewhere that the package can get when executions starts. Is there a way of doing this?

The code looks like this:

Using Reader As New TextFieldParser(mTempFilePAth)

Reader.TextFieldType = FieldType.FixedWidth

Reader.SetFieldWidths(1, 8, 8, 8, 4, 8) <-- I want to change this to handle dynamic widths.

Hi,

The best way to make your package re-usable is to use a "Integration Services Variable", see this link for help on variables http://msdn2.microsoft.com/en-us/library/ms141085.aspx and this one for accessing from a script http://msdn2.microsoft.com/en-us/library/aa337079.aspx.

The main gotcha is you need to be aware of is this bit from the second link "You can make existing variables available for read-only or read/write

access by your custom script by entering comma-delimited lists of

variables in the ReadOnlyVariables and ReadWriteVariables fields on the Script page of the Script Transformation Editor."

Also does SetFieldWidths take a variable number of arguments? If so you may need to define a string SSIS variable with the arguments comma seperated, then parse them into the seperate arguments for SetFieldWidths

Dave

dynamic sql server statements

In stright t-sql or sql server 2000 stored procdures, I was wondering if you
can setup dynamic sql statments.
Basically I am going to have a sql server 2000 control table where users
can select via a web page, what columns they want to see with their own
unique (personal) sql server 2000. The control table will be setup to show
what columns
users want to display on their own individual sql server 2000 tables.
Thus basically if a user selects that they want to create a table with
columns #1, #9 and #16 from an option control table, I am wondering if there
is a way to do is a way to set this up with t-sql statements?
Thanks!There is, but there is certainly no magic way to do it. Are you talking
about loads of data? If the volume is relatively low, then I would just use
a regular proc and toss the results the user doesn't want to see.
Otherwise I would save in your settings the string value of the columns and
execute something like:
set @.query = 'select ' + @.columnList + ' from table'
And use sp_executeSQL to execute it. I expect you have a where clause too,
so you would add that.
You could then have a table like (you suggest it too):
create table userScreenPref
(
userId sysname,
screenName varchar(20),
columnList varchar(2000)
constraint PKuserScreenPref primary key (userId, columnList)
) --not exactly tested, but you get the point
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Wendy Elizabeth" <WendyElizabeth@.discussions.microsoft.com> wrote in
message news:603A78FD-D327-4263-8991-4340E6A5F9A5@.microsoft.com...
> In stright t-sql or sql server 2000 stored procdures, I was wondering if
> you
> can setup dynamic sql statments.
> Basically I am going to have a sql server 2000 control table where users
> can select via a web page, what columns they want to see with their own
> unique (personal) sql server 2000. The control table will be setup to show
> what columns
> users want to display on their own individual sql server 2000 tables.
> Thus basically if a user selects that they want to create a table with
> columns #1, #9 and #16 from an option control table, I am wondering if
> there
> is a way to do is a way to set this up with t-sql statements?
> Thanks!

Dynamic SQL into a cursor

basically im creating a user defined function that i want to take the parameters that are passed to it to create the sql for a cursor to loop through. As far as i can tell a cursor cannot take dynamic sql, anyone got any bright ideas or an easy way round this?

My code so far is as follows (the variable @.sql is the sql statement i want passed in to the cursor):

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

set @.sql = 'select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

declare archivecur cursor
for
@.sql

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
right i thought id found the answer to append the rows from the dynamic sql into a table variable and loop through that but unfortunately im now getting must declare the variable @.curtable

--##########TESTING VALUES#########--
declare @.inpfieldname as varchar(20)
declare @.inptable as varchar(50)
declare @.inprefno as int
set @.inpfieldname = 'disch_dttm'
set @.inptable = 'provider_spells'
set @.inprefno = 100604947
--#################################--

declare @.inpfield as varchar(50)
declare @.modif as varchar(50)
declare @.funcreturn as varchar(50)
declare @.sql as varchar(1000)

declare @.curtable table (Field1 varchar(50), modifdate varchar(8))

set @.sql = 'insert into @.curtable (field1, modifdate) convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + convert(varchar(20),@.inprefno) + ' order by modif_dttm'

execute(@.sql)

declare archivecur cursor
for
select field1, modifdate
from @.curtable
order by modifdate

open archivecur

fetch next from archivecur into @.inpfield, @.modif

while @.@.fetch_status = 0
begin
if @.inpfield is not null
begin
set @.funcreturn = @.modif
break
end

fetch next from archivecur into @.inpfield, @.modif
end

close archivecur

deallocate archivecur

if @.funcreturn is null begin set @.funcreturn = 'No Match In Archive with discharge' end

print @.funcreturn
|||

Lamffy,

In order to execute dynamic sql, you need EXEC(...) or sp_executesql and no one of them could be used inside a user defined function.

To play with a cursor and dynamic sql , you can define and open the cursor inside the dynamic sql and return it in an output variable.

Example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.customerid nchar(5)

declare @.c cursor

declare @.orderid int

set @.sql = N'

set @.c = cursor local fast_forward

for

select orderid

from dbo.orders

where customerid = @.customerid;

open @.c'

exec sp_executesql @.sql, N'@.customerid nchar(5), @.c cursor output', 'ALFKI', @.c output

if cursor_status('variable', '@.c') = 1

begin

while 1 = 1

begin

fetch next from @.c into @.orderid

if @.@.error != 0 or @.@.fetch_status != 0 break

print @.orderid

end

close @.c

deallocate @.c

end

what are you trying to accomplish, creating a user defined function that involves dynamic sql and cursors?

AMB

|||

Try

set @.sql = 'declare archivecur cursor for
select convert(varchar(50),' + @.inpfieldname + '), left(modif_dttm,8) from dbo.arc_' + @.inptable + ' where prvsp_refno = ' + @.inprefno + ' order by modif_dttm'

exec (@.sql)

open archivecur

|||

Maybe it's me, but this seems like extreme overkill.

What function or looping is required to carry out this task?

I'm thinking a SELECT CASE would accomplish what you are looking for.

Adamus

|||Cheers Mark that worked. You're a lifesaver!! :-)
|||I agree Adamus it does look a bit like overkill but when you see the state of NHS data and the data warehouses it's all stored in you'd understand how much of a nightmare doing anything is!