Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

dynamic row formatting based on dataset field values

HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.

Dynamic report

Dear all,
I want to create a dynamic report so that I can change some field quickly.
For example: in future, if Field {Report ID} on report header is replaced by {Running User ID}. How can I do once for all reports.
Many thanksWill this work? The idea is to only maintain data in the table.

1. setup a database table that contains user-id and names etc.
2. create a string variable in the report for display.
3. write a routine to detect for the requires condition and pull data off the table.
4. for each report, plug in the same codes created in step-2.

Hope it works for you.

Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;

SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql

Dynamic Parameters in 8.5

Will 8.5 allow you to make parameters dynamic.
Specifically is it possible to create a parameter that when that table and or field in SQL is changed, it will update my crystal report with those changes? If not in a parameter is there anyway to do this?If you're going to use Crystal Reports alone that will not be possible since parameters are static.

I don't know how to do it but if you use stored procedures maybe you can make it dynamic.

Dynamic Parameters

I need to build a report in which every field of the dataset acts as a
Parameter to filter data in the report, so that the user is not binded
only to a fixed set of filters.
Based on the datatype of the filter; the condition should be specified
and the user will only supply the value for which he is looking for.
For Example:
In the report i have about 10 fields
EmpSSN No varchar(20)
Emp FirstName varchar(20)
Emp LastName varchar(20)
Emp Location varchar(20)
Emp MartialStatus char(1)
Emp DOB datetime
Emp Salary numeric(18,2)
Emp Designation varchar(20)
Emp Department varchar(20)
Emp Status varchar(20)
When i choose the filter to be as DOB the condition needs to be
generated as >,>=,<,<=,!= and user can define a specific data for the
filter.
Pls advise.Hi,
I am about to start building the exact same report as you outlined
below. did you get any references or whether or not this can be achieved
using reporting services
--
Ciaran
Software developer
"brinda.shree@.gmail.com" wrote:
> I need to build a report in which every field of the dataset acts as a
> Parameter to filter data in the report, so that the user is not binded
> only to a fixed set of filters.
> Based on the datatype of the filter; the condition should be specified
> and the user will only supply the value for which he is looking for.
> For Example:
> In the report i have about 10 fields
> EmpSSN No varchar(20)
> Emp FirstName varchar(20)
> Emp LastName varchar(20)
> Emp Location varchar(20)
> Emp MartialStatus char(1)
> Emp DOB datetime
> Emp Salary numeric(18,2)
> Emp Designation varchar(20)
> Emp Department varchar(20)
> Emp Status varchar(20)
> When i choose the filter to be as DOB the condition needs to be
> generated as >,>=,<,<=,!= and user can define a specific data for the
> filter.
> Pls advise.
>

Dynamic Meta tag with SQL field

Hello all, I use Asp.net 1.1 vb script and MS SQL server 2000. I'm wondering if there's a way to put a database field into the title meta tag. For example if I have a detail page for cars which pulls the text info from the SQL data base and I want the field "name" in the title tag

details.aspx?name=Hummer would have the Title meta tag as Cars..."Hummer"

and

details.aspx?name=Corvette would have the Title meta tag as Cars..."Corvette"

so that for each car showing in details.aspx page it wouldn't have to have the same title tag for search engines

I am not understanding how the SQL database comes into play here. Could you explain further?|||

The SQL database comes into play bcause it danamically creates what look like mutiple pages from a single details page. Lets say the details page calls up fruits from the SQL database. and the Name column has, oranges, apples and blueberry. The details page can be

details.aspx?name=orange
or
details.aspx?name=apple
or
details.aspx?name= blueberry

and have info on each but all three will have the same Titile tag because they all run off of the details.aspx page.

So a solution could be to insert the "name" into the title so that the title meta tag and at the top of the web browser would also hchange for each fruit

sql

dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

Dynamic image size on rdlc report

Hi.

How to set the image size (height and width) dynamically? Size of the image will be obtained from a data field. I tried to edit the source but found no luck:

<TableCell> <ReportItems> <Image Name="img3"> <Sizing>Clip</Sizing> <Source>External</Source> <Style> <BorderStyle> <Default>Solid</Default> </BorderStyle> <BorderColor> <Default>Red</Default> </BorderColor> </Style> <Width>0.12in</Width> <Value>file:///c:\barbetter.bmp</Value> </Image> </ReportItems> </TableCell>

I hope someone can help me with this.. Thanks

Raniel L.

I don't think this can be done unless you're generating the report in XML on the fly.

Dynamic fields in SQL

I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)The standard solution for your problem is to model the problem in the database, stroring the user defined attributes in a separate table.

Something like:
create table MY_TABLE (id int, name varchar(32) primary key(id))
create table MY_DYNAMIC_ATTRIBUTES
( id int
, attribute_name varchar(20)
, attribute_type_code char(1)
, attribute_value varchar(255)
primary key (id, attribute_name)
foreign key (id) references MY_TABLE(id)
)

The problem is usually the application reading and writing these tables.
A general report would need crosstabbing.

Specific reports, where you already know which fields are involved is easier, could even be done with a view:

create view MY_DYNAMIC_VIEW as
select
t.id
, t.name
, d1.attribute_value as address
, d2.attribute_value as city
, convert(int, d3.attribute_value) as age
from MY_TABLE t
left join MY_DYNAMIC_ATTRIBUTES d1 on d1.id = t.id and d1.attribute_name = 'address'
left join MY_DYNAMIC_ATTRIBUTES d2 on d2.id = t.id and d2.attribute_name = 'city'
left join MY_DYNAMIC_ATTRIBUTES d3 on d3.id = t.id and d3.attribute_name = 'age'|||Originally posted by plextoR
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok
using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction.
any suggessions?
Thnak :)

Suggestions?

Yeah, don't do it...

Just think what kind of mess you'll end up with...

Ummmm I want a varchar(8000) column...ummm I want another one...and another one...

Booooooooooooooooooooooom

What's business requirement to support, in non tech terms...

dynamic field value

Hello,
In the page header I have a textbox whose value I want to change based on
the parameter. If param=1 then I want the value of the textbox to be Invoice
otherwise I want it to be 'B/S'.
How can I change the value of the textbox?
Thankstry setting the value property of the textbox to something like
=IIF(Parameters!Param_1.Value=1,"Invoice ","B\S")
inova. SOLUTIONS
Peter Jacobs MCSE MCDBA MCT
Güterstrasse 5/7
CH-3072 Ostermundigen
"collie" <collie@.discussions.microsoft.com> wrote in message
news:110178DB-C794-414C-B8FF-6E4448714494@.microsoft.com...
> Hello,
> In the page header I have a textbox whose value I want to change based on
> the parameter. If param=1 then I want the value of the textbox to be
> Invoice
> otherwise I want it to be 'B/S'.
> How can I change the value of the textbox?
> Thanks|||You can use report parameters for doing this. Add a report parameter to your
report. Then refer the report parameter in your jump to url option as
=Parameters!WebServerPath.Value + "?Id=" + Trim( Fields!ID.Value)
Where "WebServerPath" is ur report parameter and "Id" is your row value
which u want to pass it to the new page.
Hope this helps.
"collie" wrote:
> Hello,
> In the page header I have a textbox whose value I want to change based on
> the parameter. If param=1 then I want the value of the textbox to be Invoice
> otherwise I want it to be 'B/S'.
> How can I change the value of the textbox?
> Thanks|||Thanks guys
"Rajan" wrote:
> You can use report parameters for doing this. Add a report parameter to your
> report. Then refer the report parameter in your jump to url option as
> =Parameters!WebServerPath.Value + "?Id=" + Trim( Fields!ID.Value)
> Where "WebServerPath" is ur report parameter and "Id" is your row value
> which u want to pass it to the new page.
> Hope this helps.
>
> "collie" wrote:
> > Hello,
> >
> > In the page header I have a textbox whose value I want to change based on
> > the parameter. If param=1 then I want the value of the textbox to be Invoice
> > otherwise I want it to be 'B/S'.
> > How can I change the value of the textbox?
> >
> > Thanks

Dynamic field question

Can someone point me in the right direction to solve the following
(basic) SQL problem below using SQL Server:

Let's say I have a table like this that lists people's likes:

CREATE TABLE likes (
myname VARCHAR (60),
travel BIT,
eatingout BIT,
disco BIT,
swimming BIT);

Let's say I put the following data inside this table:

INSERT INTO likes VALUES ('Darren', 1,0,0,1)
INSERT INTO likes VALUES ('John',1,1,0,1)
INSERT INTO likes VALUES ('Peter',0,0,0,0)
INSERT INTO likes VALUES ('Jill',0,0,0,1)

Then what I want is to create a (view? Or function? I am not sure),
called likes_details' that when I send this query:

SELECT myname, likes FROM likes_details

Returns the following:

Myname likes
DarrenTraveling and swimming
JohnTraveling, eating out and swimming
PeterDone not like anything
JillSwimming only

Please! Can anyone help!!

Thank you in advance.Darren,

The mess-ed up short term workaround to your problem with the existing
schema is:

SELECT myName,
COALESCE( NULLIF (
CASE travel WHEN 1 THEN 'Travelling, '
ELSE SPACE(0) END +
CASE eatingout WHEN 1 THEN 'eating out, '
ELSE SPACE(0) END +
CASE disco WHEN 1 THEN 'disco, '
ELSE SPACE(0) END +
CASE swimming WHEN 1 THEN 'swimming, '
ELSE SPACE(0) END, SPACE(0)),
'Do not like anything')
FROM likes ;

Now, the real solution to your problem is that you need to overhaul your
schema, it has values as column names, under-normalized and thus unusable. A
good way of representing this information would be like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
PersonName VARCHAR(10) NOT NULL,
...);
CREATE TABLE Hobbies (
Hobby_id INT NOT NULL PRIMARY KEY,
HobbyDesc VARCHAR(20) NOT NULL,
...);
CREATE TABLE PersonHobbies(
Person_id INT NOT NULL
REFERENCES Persons(Person_id),
Hobby_id INT NOT NULL
REFERENCES Hobbies(Hobby_id)
PRIMARY KEY (Person_id, Hobby_id)) ;

The primary keys in the Persons table & Hobbies tables are assigned with the
assumption that there could be other relevant attributes associated with
these entities, otherwise using Name & Desc as keys are just fine. The data
for these tables, based on the information you provided could be like:

INSERT Persons SELECT 1, 'Darren' ;
INSERT Persons SELECT 2, 'John' ;
INSERT Persons SELECT 3, 'Peter' ;
INSERT Persons SELECT 4, 'Jill' ;
GO
INSERT Hobbies SELECT 1, 'travel' ;
INSERT Hobbies SELECT 2, 'eatingout' ;
INSERT Hobbies SELECT 3, 'disco' ;
INSERT Hobbies SELECT 4, 'swimming' ;
GO
INSERT PersonHobbies SELECT 1, 1 ;
INSERT PersonHobbies SELECT 1, 4 ;
INSERT PersonHobbies SELECT 2, 1 ;
INSERT PersonHobbies SELECT 2, 2 ;
INSERT PersonHobbies SELECT 2, 4 ;
INSERT PersonHobbies SELECT 4, 4 ;
GO

The above schema represents a m-to-m relationship between Persons and
Hobbies. It allows you to add persons and hobbies to the system without
having to alter the tables and facilitates efficient querying. Now, you can
have a SQL statement like:

SELECT p1.PersonName, h1.HobbyDesc, ...
FROM Persons p1
LEFT OUTER JOIN PersonHobbies ph1
ON p1.Person_id = ph1.Person_id
LEFT OUTER JOIN Hobbies h1
ON h1.Hobby_id = ph1.Hobby_id

Get the resultset to your client application & cross tab the data to the
format with comma, add words like "and" etc for the requirements for
display.

--
Anith

dynamic field positioning

Good day everyone,

I'm can't seem to find a solution for this. Using the scripts( formula editor ), I needed to programmatically edit the position of a field. Does anybody have an idea?

Here's what I did.

I created a new formula field(@.denom), then I placed it at a specific location in the report.

When I meet some conditions, the field's position should change.

Any comments will helpCreate two formulae with same coding
Put them in different position
In the suppress event of them write the query that meets the condition. Suppress one and dont suppress other

Dynamic field list

Hi,
The underlying query in the dataset of my report has a set number of static
fields (which I bind to report elements) but also can return additional
variable number of fields, depending on passed parameters. Is there a way to
access those fields at report runtime?
Thanks.How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10=A0pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset =A0of my report has a set number of st=atic
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way =to
> access those fields at report runtime?
> Thanks.|||That's the thing - I don't know in advance *how many* dynamic columns I am
going to return. Let's say I pass no parameters - the query will return
columns:
A B C
If I pass parameter "1" the query will return columns
A B C D
If I pass parameter "2" the the query will return columns
A B C D E
field list in dataset in report definition can contain only static number of
fields and if I bind report to A B C then D and E become unaccessable even
if query returns them.
The only way I can think of is, since I am launching the report from a .NET
application anyway is download report definition and modify it on the fly by
adding new columns to dataset field list. But I'd like to avoid it if
possible.
<prabhupr@.gmail.com> wrote:
How about creating a SQL View, which has your current Static Column &
Computed Column & then returning that as Query to your Report?
On May 2, 1:10 pm, "Yuriy Galanter" <y...@.galanter.net> wrote:
> Hi,
> The underlying query in the dataset of my report has a set number of
> static
> fields (which I bind to report elements) but also can return additional
> variable number of fields, depending on passed parameters. Is there a way
> to
> access those fields at report runtime?
> Thanks.|||Not tested , Just an idea - Does the use of
=IIF(Fields!Column_1.IsMissing, true, false)in the hidden property of the
coloumn solve your problem ?
P.I.
"Yuriy Galanter" <yuri@.galanter.net> a écrit dans le message de news:
eXm6bCJrIHA.4848@.TK2MSFTNGP05.phx.gbl...
> Hi,
> The underlying query in the dataset of my report has a set number of
> static fields (which I bind to report elements) but also can return
> additional variable number of fields, depending on passed parameters. Is
> there a way to access those fields at report runtime?
> Thanks.
>

dynamic field for backgroundcolor

How can i change the background color of a textbox in RS where the
background color is stored in a dataset?
I have been trying this on the background color properties:
=Fields.LineColor.Value
Values on that dataset include standard colors like:
-DimGray
-Gray
-White
so on
I also tried using Hex or integers but to no avail.
I need this because I have set of rows where color is attached. The
list is dynamic and cannot be hard-coded in the report.Shouldn't that be:
=Fields!LineColor.Value
vice
=Fields.LineColor.Value
John Grant
=""" & Fields!
"rGenius" wrote:
> How can i change the background color of a textbox in RS where the
> background color is stored in a dataset?
> I have been trying this on the background color properties:
> =Fields.LineColor.Value
> Values on that dataset include standard colors like:
> -DimGray
> -Gray
> -White
> so on
> I also tried using Hex or integers but to no avail.
> I need this because I have set of rows where color is attached. The
> list is dynamic and cannot be hard-coded in the report.
>|||typo error
yes it is =Fields!LineColor.Value

Dynamic Field DB Schema Brainstorming

Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||

Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.

|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||

Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.

Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?