How to make Update relation?

I have two tables: TableX & TableY

there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).

How to?

Lewe:

The are a couple of things that you need to know first:

How does TableX relate to TableY? TableX SHOULD have a key to TableY so that corresponding X and Y members can be joined|||

I'm not sure that you are really talking about needing a 'relation'. It sounds as though you only want a method that will change TableY values when a similar value in TableX is changed.

That 'could' be done with a TRIGGER. But first, it will be necessary for you to identify which row in TableY is to be changed as a result of a particular row in TableX getting changed. In other words, "How do the two tables 'relate' to each other?"

How to make Update relation?

I have two tables: TableX & TableY

there is two similar fields (Size) with the same datatype, I want to make a relation in a way if I change the value in (TableX.Size) the same value will be applied to (TableY.Size).

How to?

Lewe:

The are a couple of things that you need to know first:

How does TableX relate to TableY? TableX SHOULD have a key to TableY so that corresponding X and Y members can be joined|||

I'm not sure that you are really talking about needing a 'relation'. It sounds as though you only want a method that will change TableY values when a similar value in TableX is changed.

That 'could' be done with a TRIGGER. But first, it will be necessary for you to identify which row in TableY is to be changed as a result of a particular row in TableX getting changed. In other words, "How do the two tables 'relate' to each other?"

How to make the report show fields in multiple columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

There is an option in Crystal Reports -- "Format with multiple columns". I couldn't find the similar configuration in SQL reporting. There is a "Columns" setting in Body Properties, but it doesn't work as making the report in multiple columns. Please help!!

How to make or add a table with a field with default value

I am doing a shopping basket type demo.

I have difficulties going from table with fields like (Name,ProductCode) to table(Name, ProductCode, NumberOfItems).

One way could be adding just Name and ProductCode fields and let NumberOfItems come automatically. It could have some common value like 1.

How should I do this with VB.

Do you mean that if you insert a record but don't supply values for all the fields that you want the database to default the value? This is easy to do in SQL Server, here's an example of setting a default value of 1

create table junk ( SomeIntColumn smallint NOT NULL DEFAULT 1)

You can also create your own named defaults, then bind them, eg:

create default blank as ' '
go
create default zero as 0
go
create default today as getdate()
go

create table junk(SomeDateColumn datetime not null)

go

exec sp_bindefault today, 'junk.SomeDatecolumnt

go

This binds a default value named "today' to SoemDateColumn in table junk

How to make Fields dynamic in a table column?

I am developing a single dynamic report in report designer and I have
a small problem. My report has several parameters and I use MDX to
query from a Cube(Analysis Services). Everything works fine until I
want a field in the report to change dynamically when a user changes a
parameter.
Example:
I have one parameter, dimension, which is a drop down list. If the
user chooses 'region' as the dimension, I want the Field!Region.Value
to be my field in the table column. If he chooses 'store' I want
Field!Store.Value to show instead of Region or any other dimension in
the column. Is there any way to make a report dynamic like this in
report designer?
At this moment I have to define fields with the same adress as the
database location: 'Store' has field expression like this -
[Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
the dimension field when the user chooses 'Store' from the drop down
list, I`ll get all the results displayed in the table. This is what I
want to make more dynamic.
My simplyfied MDX query:
SELECT {[Measure].[Units Ordered]} on Columns,
{[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
from Orders
Does anyone have a solution to this problem?You can use the collection-based syntax for referencing field names in the
report layout. E.g.
=Fields(Parameters!DimensionSelection.Value).Value
Based on the actual parameter value, the field name will be determined at
report processing time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Oddvar Brennhovd" <oddvar.brennhovd@.isolutions.no> wrote in message
news:83475cf2.0502230717.48b871f6@.posting.google.com...
>I am developing a single dynamic report in report designer and I have
> a small problem. My report has several parameters and I use MDX to
> query from a Cube(Analysis Services). Everything works fine until I
> want a field in the report to change dynamically when a user changes a
> parameter.
> Example:
> I have one parameter, dimension, which is a drop down list. If the
> user chooses 'region' as the dimension, I want the Field!Region.Value
> to be my field in the table column. If he chooses 'store' I want
> Field!Store.Value to show instead of Region or any other dimension in
> the column. Is there any way to make a report dynamic like this in
> report designer?
> At this moment I have to define fields with the same adress as the
> database location: 'Store' has field expression like this -
> [Customer].[Store].[MEMBER_CAPTION] and if I use this expression in
> the dimension field when the user chooses 'Store' from the drop down
> list, I`ll get all the results displayed in the table. This is what I
> want to make more dynamic.
> My simplyfied MDX query:
> SELECT {[Measure].[Units Ordered]} on Columns,
> {[Customer].[" + Parameters!Dimension.Value + "].Members} on Rows
> from Orders
> Does anyone have a solution to this problem?|||Thank you. Now the problem is solved!

How to make aspnet DB use local time zone?

I created aspnet DB on SQL Server 2K using aspnet_regsql utility. Everything works fine except the DateTime fields in all tables are using a wrong time zone. How do I set it to use my local time zone?

Any help will be greatly appreciated.

All stored procedures in aspnetdb use UTC-based datetime fields. That's pretty normal practice for any database that might need to be accessed from multiple time zones.

How to make a SELECT with a field name inside a variable?

I have a table (for example PERSONS) with several fields (NIF NAME AGE).
I have a cursors than read the differents fiels tat have this table inside a trigger fron another table that contain all the fields of the tables.
This cursor save the name of the fiels inside the variable @.FIELD.
I need to read the value of the several records of the table like:

@.FIELD='NIF' <--(CURSOR)

SELECT @.FIELS FEOM INSERTED --> select nif from inserted(persons)

This sentence doesn't works. How can I do?

Thanks, Otto Martinez.Hi,
you maye use a variable to generate the whole statement then use the exec command
Declare @.sql NVarchar(200)
set @.sql= "Select " + @.Fiels +" from inserted"
exec @.sql

How to loop thru' Fields in Report

I'm using Reporting Server 2005. I have published my report as Web service. Now I'm accessing the Web Service (Report) from my C# code. Here I would like to loop all the fields available in the report. How to do this?

Thanks in Advance.

Regards,

vnisor.

Have you considered using one of the data renderers (such as XML)? When you render it will only return the data in the report and not any formatting. It should make it much easier to extract the data you are interested in programmatically.

How to link details tables to list fields

Hi,
I am trying to create a report with a list for each row, which also contains
tables of related data (1 to many relationship).
There are various drill-down techniques described in the documentation, but
I have been unable to determine how to pass a field from the primary record
as a parameter to the queries for the sub data.
Eg. Have tables Employees and Sales. Sales is related to employees by
employeeID field in the Sales table. I want to show a single employee details
in list, then multiplae sals records in a table embedded in the list.
I need to fgigure out how to pass the current employee.ID field to the table
query as a paremeter.
Any help on this would be appreciated (even if I have to get this working
using sub reports)
Thanks,
...Derek
--
DerekYou have two options for doing this, either use nested data regions or use
subreports:
First here's how to use nested data regions. Write one dataset query to
return employee and sales information. This will likely be a join sql query.
Place a list data region on your screen in Layout view (list or table will do
- these data regions can contain other data regions). >Inside< the list data
region place a table data region. Both list and table must be bound to the
same dataset query you created. Put employee fields in the list data region
outside the table. Put sales fields inside the table. That's it.
A second option is to use subreports. First create a report with sales
information. The report should take employeeid as a parameter. save the
report. Now create the main report with a data region that displays employee
information. This report lists all employees and should have in its dataset
query an employeeid field. There is no parameter used. Inside the data region
with employee information, place a subreport control. Go to the properties of
the subreport. On the General tab of the Properties dialog there is a textbox
where you can specify your subreport. This is the report you first created
and saved. In the same dialog box there is a tab where you specify
parameters. select your employeeid parameter and specify the employeeid field
from the main report dataset. That's it.
Both solutions will give you what you want.
HTH
Charles Kangai, MCT, MCDBA
"DerekJMiller1" wrote:
> Hi,
> I am trying to create a report with a list for each row, which also contains
> tables of related data (1 to many relationship).
> There are various drill-down techniques described in the documentation, but
> I have been unable to determine how to pass a field from the primary record
> as a parameter to the queries for the sub data.
> Eg. Have tables Employees and Sales. Sales is related to employees by
> employeeID field in the Sales table. I want to show a single employee details
> in list, then multiplae sals records in a table embedded in the list.
> I need to fgigure out how to pass the current employee.ID field to the table
> query as a paremeter.
> Any help on this would be appreciated (even if I have to get this working
> using sub reports)
> Thanks,
> ...Derek
> --
> Derek|||Hi,
For me first option is working well. Thanks...
I want to display header on each page.
How to put header titles on each page. If I put table header on top in the
list, it is repeating for all records of the list.
If I put header table outside list, it is not visible on next page.
I hope, my question is clear.
Please help
"Charles Kangai" wrote:
> You have two options for doing this, either use nested data regions or use
> subreports:
> First here's how to use nested data regions. Write one dataset query to
> return employee and sales information. This will likely be a join sql query.
> Place a list data region on your screen in Layout view (list or table will do
> - these data regions can contain other data regions). >Inside< the list data
> region place a table data region. Both list and table must be bound to the
> same dataset query you created. Put employee fields in the list data region
> outside the table. Put sales fields inside the table. That's it.
> A second option is to use subreports. First create a report with sales
> information. The report should take employeeid as a parameter. save the
> report. Now create the main report with a data region that displays employee
> information. This report lists all employees and should have in its dataset
> query an employeeid field. There is no parameter used. Inside the data region
> with employee information, place a subreport control. Go to the properties of
> the subreport. On the General tab of the Properties dialog there is a textbox
> where you can specify your subreport. This is the report you first created
> and saved. In the same dialog box there is a tab where you specify
> parameters. select your employeeid parameter and specify the employeeid field
> from the main report dataset. That's it.
> Both solutions will give you what you want.
> HTH
> Charles Kangai, MCT, MCDBA
>
> "DerekJMiller1" wrote:
> > Hi,
> >
> > I am trying to create a report with a list for each row, which also contains
> > tables of related data (1 to many relationship).
> >
> > There are various drill-down techniques described in the documentation, but
> > I have been unable to determine how to pass a field from the primary record
> > as a parameter to the queries for the sub data.
> >
> > Eg. Have tables Employees and Sales. Sales is related to employees by
> > employeeID field in the Sales table. I want to show a single employee details
> > in list, then multiplae sals records in a table embedded in the list.
> >
> > I need to fgigure out how to pass the current employee.ID field to the table
> > query as a paremeter.
> >
> > Any help on this would be appreciated (even if I have to get this working
> > using sub reports)
> >
> > Thanks,
> > ...Derek
> > --
> > Derek

How to know which fields are identity?

Hi. I need to know if a table has identity fields. Is there any view to get
that information?
Regards,
Diego F.SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
column_name,'IsIdentity')=1 ;
David Portas
SQL Server MVP
--|||Thanks a lot!
Regards,
Diego F.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
> SELECT table_schema, table_name, column_name
> FROM information_schema.columns
> WHERE COLUMNPROPERTY(OBJECT_ID(
> QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
> column_name,'IsIdentity')=1 ;
> --
> David Portas
> SQL Server MVP
> --
>|||I found a problem with that. It doesn't work if I try to use a table from
other database. Is there another possibility?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot!
> --
> Regards,
> Diego F.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
> mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
>|||SELECT
U.name AS table_schema,
T.name AS table_name,
C.name AS column_name
FROM database_name.dbo.syscolumns AS C
JOIN database_name.dbo.sysobjects AS T
ON C.id = T.id
JOIN database_name.dbo.sysusers AS U
ON T.uid = U.uid
WHERE C.status & 0x80 = 0x80 ;
David Portas
SQL Server MVP
--|||Type 'USE AnotherDatabaseName' before that SELECT command.
Or 'AnotherDatabaseName.information_schema.columns' instead of 'information_
schema.columns'.
Diego F. wrote:
> I found a problem with that. It doesn't work if I try to use a table from
> other database. Is there another possibility?
> --
> Regards,
> Diego F.
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>
>|||> 'AnotherDatabaseName.informati=ADon_schema.columns' instead of 'informati=
on_schema.columns'.
That gets you the column names but unfortunately the metadata functions
I used are scoped to the current DB so the WHERE clause won't find the
correct columns.
--=20
David Portas=20
SQL Server MVP=20
--|||David Portas wrote:
>
> That gets you the column names but unfortunately the metadata functions
> I used are scoped to the current DB so the WHERE clause won't find the
> correct columns.
Ah, you are right indeed.|||Thank you all. I'll try to deal with that.
Regards,
Diego F.
"Sericinus hunter" <serhunt@.flash.net> escribi en el mensaje
news:VarIe.953$646.265@.newssvr22.news.prodigy.net...
> David Portas wrote:
> Ah, you are right indeed.