Hi,
When I add the table into the report, the default column number is 3. Let's say I need 10 columns on the report, besides right click on the last column and click on "add column right" to add the other 7 columns, is there any easy way?
Thanks.
Sorry, there is no other way of doing this. You have to add additional columns through the popup menu.
-- Robert
sqlThere is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.
I assigned different format in the format field of key property window, but it had no effect. What I did wrong?
Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.
HTH
Thomas Ivarsson
|||Thank you,
I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.
I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.
Do you know what is the format field in key properties for?
Hi,
Can I control a column to displayed to a specifal user or specifal role?
If not the specifal user or specifal role, the column will can not be displayed.
Thank you.
Jeffers
You can set a conditional column visibility using User!UserID. For more involved scenarios, you may need to whip out some code to find the role/group the user belongs to given the user logon name.
in my SQL 2000
the column importDate contain Date as a vachar , type is 20060324225008 ( 2006 -year , 03-month, 24-day)
I want to compare this column with today's date, how to transform it?
how to return value 20060324 not 20060324225008?
thank you
Grab the column as a string
string rawNumber = "20060324";
DateTime dtTime = Convert.ToDateTime(rawNumber);
Does it work?
|||how can I get the value of column = 20060324, is was20060324225008 not 20060324
the problem is how to get 20060324 only
thank you
|||Select LEFT(yourColumn,8) as newValue FROM yourTable
After this, you can use one of datetime functions to compare this date part with today's date part.
I have one database
and I create new database by copy everything from first db
I then alter some column on new database
and now I would like to compare two database
How can I do it ?
There is the manul way which I am sure you know of because like most of use we don't keep track of the changes that we make until time comes to roll the thing out.
There is a great good called RedGate SQL Compare (http://red-gate.com/products/SQL_Compare/index.htm) the tools are sort of expensive but well worth the money. Also if you are interested Microsoft is developing a product call Visual Studio for Database Developers that can be found on the MSDN site. Which esentially does the same thing and can be found on MSDN subscription download too.
|||I would write a script looking at syscolumns and sysobjects from both DB's and look where they do not match, this should get you changes to the DB.|||That is basically what Red Hat does, but it takes it a step furthor and generate the change script for you. I personally think that is worth 250.00|||FYI, here is a simple script I'm using:
/*--Compare the differences in table schemas between 2 databases
--*/
/*--sample to call this stored procedure
exec p_comparestructure 'pubs','northwind'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO
create proc p_comparestructure
@.dbname1 varchar(250), --the name of the database to be comapred
@.dbname2 varchar(250) --the name of the database to be comapred
as
create table #tb1(TableName1 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
create table #tb2(TableName2 nvarchar(250),ColumnName nvarchar(250),Ordinal int,Iden bit,PrimaryKey bit,Type nvarchar(250),
Bytes int,Length int,BitsAfterDecimalPoint int,AllowNulls bit,DefaultValue nvarchar(500),ColumnDescription nvarchar(500))
--Get the schema of database1
exec('insert into #tb1 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname1+'..syscolumns a
left join'+@.dbname1+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname1+'..syscomments e on a.cdefault=e.id
left join'+@.dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--Get the schema of database2
exec('insert into #tb2 SELECT
TableName=d.name,ColumnName=a.name,Ordinal=a.colid,
Iden=case when a.status=0x80 then 1 else 0 end,
PrimaryKey=case when exists(SELECT 1 FROM'+@.dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM'+@.dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM'+@.dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
Type=b.name,Bytes=a.length,Length=a.prec,BitsAfterDecimalPoint=a.scale, AllowNulls=a.isnullable,
DefaultValue=isnull(e.text,''''''),ColumnDescription=isnull(g.[value],'''''')
FROM'+@.dbname2+'..syscolumns a
left join'+@.dbname2+'..systypes b on a.xtype=b.xusertype
inner join'+@.dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join'+@.dbname2+'..syscomments e on a.cdefault=e.id
left join'+@.dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where TableName2=a.TableName1)
select Result=case when a.TableName1 is null and b.Ordinal=1 then 'Table：'+b.TableName2+' absent in'+@.dbname1
when b.TableName2 is null and a.Ordinal=1 then 'Table :'+a.TableName1+' absent in'+@.dbname2
when a.ColumnName is null and exists(select 1 from #tb1 where TableName1=b.TableName2) then @.dbname1+' ['+b.TableName2+'] doesn''t have column：'+b.ColumnName
when b.ColumnName is null and exists(select 1 from #tb2 where TableName2=a.TableName1) then @.dbname2+' ['+a.TableName1+'] doesn''t have column：'+a.ColumnName
when a.Iden<>b.Iden then 'Different identities'
when a.PrimaryKey<>b.PrimaryKey then 'Different Primary Key constraints'
when a.Type<>b.Type then 'Different column data types'
when a.Bytes<>b.Bytes then 'Bytes'
when a.Length<>b.Length then 'Different Lengths'
when a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint then 'Different in BitsAfterDecimalPoint'
when a.AllowNulls<>b.AllowNulls then 'Different in AllowNulls options'
when a.DefaultValue<>b.DefaultValue then 'Different default values'
when a.ColumnDescription<>b.ColumnDescription then 'Different Column Descriptions'
else '' end,
*
from #tb1 a
full join #tb2 b on a.TableName1=b.TableName2 and a.ColumnName=b.ColumnName
where a.TableName1 is null or a.ColumnName is null or b.TableName2 is null or b.ColumnName is null
or a.Iden<>b.Iden or a.PrimaryKey<>b.PrimaryKey or a.Type<>b.Type
or a.Bytes<>b.Bytes or a.Length<>b.Length or a.BitsAfterDecimalPoint<>b.BitsAfterDecimalPoint
or a.AllowNulls<>b.AllowNulls or a.DefaultValue<>b.DefaultValue or a.ColumnDescription<>b.ColumnDescription
order by isnull(a.TableName1,b.TableName2),isnull(a.Ordinal,b.Ordinal)--isnull(a.ColumnName,b.ColumnName)
go
I am going to compare thie value 2007-9-11 (this value was retrived from the column(TxnDate) in my DataBase, type is DateTime)
I write code
select * from ZT_ModifyLog where Year(TXnDate) = Year(GetDate()) AND ( ( Month(TXnDate) < Month(GetDate()) ) and Month(TXnDate) >= Month(GetDate())-1)
it will like
select * from ZT_ModifyLog where Year(2007-9-11 ) = Year(GetDate()) AND ( ( Month(2007-9-11 ) < Month(GetDate()) ) and Month(2007-9-11 ) >= Month(GetDate())-1)
→ select * from ZT_ModifyLog where TxnDate(2007) = 2007 AND 10 < GetDate(10) and 9 >= 9 so return TXnDate between 2007/9/1~ 2007/9/30
but what if Month(GetDate())-13)?? when the -1 biger than 12... I guess the code will cause error ... but can't think out how to avoid and change my code
pleae help... thank you very much
Please do not open multiple threads for same question. Refer:http://forums.asp.net/t/1176161.aspx.
I try to use 'Union' which combine two column in two table . thxYou can use the concatention operator (+):
SELECT
column1 + column2 AS myColumn
FROM
myTable
Terri|||If the columns are numeric then "+" will sum the column values.
If you do not desire this, you should use str() function
Check the below statement,
select ltrim(str(column1)) + ltrim(str(column2)) from mytable
When I select the column and mark visible to 'false' in the properties, it of course asks me for a TextBox. My problem is that I am unable to find a scenario where the textbox is "in the proper group". Is this something that's supported, and if so, I'd appreciate some pointers to lead me in the right direction.
Thanks.bump.. anyone have any clues?|||
I was able to get this to work fine in RS 2005. Where is the textbox that you are setting as the ToggleItem for the table column? I selected the textbox in the table header cell immediately to the left of the column that I wanted to toggle. What is the exact error you are seeing?
-Chris
|||Chris, thanks for your reply. My problem is I was selecting the whole column and trying to toggle the visibility on that. You can't do that; you've got to set the visibility on each portion individually (column header, group, detail). Thanks again.|||Hi Aquineas..
I needed to do the same and managed to get it to work...
Create a text box outside of the table in the mainreport - with suitable text... Show / Hide Detail
and name the text box = ToggleColumns
Now highlight the column in the table and select the ToggleItem under Visibility and type ToggleColumns.
It works for me - even though the ToogleColumns isn't available in the list.
Cheers
Michael
|||I am having the same problem as Aquineas, I took Michael's suggestion but it doesn't seem to work for me. Having done what Micheal has suggested, I hides the column period and there is no way of getting it back on run time. I think I follow the instruction carefully. I am still looking for more suggestion.When I select the column and mark visible to 'false' in the properties, it of course asks me for a TextBox. My problem is that I am unable to find a scenario where the textbox is "in the proper group". Is this something that's supported, and if so, I'd appreciate some pointers to lead me in the right direction.
Thanks.bump.. anyone have any clues?|||
I was able to get this to work fine in RS 2005. Where is the textbox that you are setting as the ToggleItem for the table column? I selected the textbox in the table header cell immediately to the left of the column that I wanted to toggle. What is the exact error you are seeing?
-Chris
|||Chris, thanks for your reply. My problem is I was selecting the whole column and trying to toggle the visibility on that. You can't do that; you've got to set the visibility on each portion individually (column header, group, detail). Thanks again.|||Hi Aquineas..
I needed to do the same and managed to get it to work...
Create a text box outside of the table in the mainreport - with suitable text... Show / Hide Detail
and name the text box = ToggleColumns
Now highlight the column in the table and select the ToggleItem under Visibility and type ToggleColumns.
It works for me - even though the ToogleColumns isn't available in the list.
Cheers
Michael
|||I am having the same problem as Aquineas, I took Michael's suggestion but it doesn't seem to work for me. Having done what Micheal has suggested, I hides the column period and there is no way of getting it back on run time. I think I follow the instruction carefully. I am still looking for more suggestion.
