I have generated a format file with bcp (SQL Server 2000) which generates a
file which would work if there was a column to column relationship between
the csv file and the table. My csv, however is in a different order.
Basically the table has an identity field but the csv file doesn't. They are
pretty similar other than that. I need the identity column in the table to
be ignored but datafield 1 to map to column 2 and so. All's I get is an
invalid column number error. Can anyone point me in the right direction?
Here is the format file, which is failing. There are also some columns at
the end which have been missed off as there are no corresponding datafields
for them.
Here is mine
8.0
13
1 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 3
Forename Latin1_General_CI_AS
3 SQLCHAR 0 12 "," 4 Age
""
5 SQLCHAR 0 12 "," 6
InscriptionID ""
6 SQLCHAR 0 12 "," 7
KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "," 9
ReligionID ""
8 SQLCHAR 0 12 "," 10
CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear
""
11 SQLCHAR 0 12 "," 12
DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay
""
13 SQLCHAR 0 3 "," 14 IsMonth
""
14 SQLCHAR 0 3 "/r/n" 15
IsYear ""
Here is the original
8.0
20
1 SQLCHAR 0 12 "\t" 1 NameID
""
2 SQLCHAR 0 510 "\t" 2
Surname Latin1_General_CI_AS
3 SQLCHAR 0 510 "\t" 3
Forename Latin1_General_CI_AS
4 SQLCHAR 0 12 "\t" 4 Age
""
5 SQLCHAR 0 12 "\t" 5 DOD
""
6 SQLCHAR 0 12 "\t" 6
InscriptionID ""
7 SQLCHAR 0 12 "\t" 7
KeyNameGroupID ""
8 SQLCHAR 0 12 "\t" 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "\t" 9
ReligionID ""
10 SQLCHAR 0 12 "\t" 10
CemeteryID ""
11 SQLCHAR 0 12 "\t" 11
DODYear ""
12 SQLCHAR 0 12 "\t" 12
DODMonth ""
13 SQLCHAR 0 12 "\t" 13 DODDay
""
14 SQLCHAR 0 3 "\t" 14
IsMonth ""
15 SQLCHAR 0 3 "\t" 15 IsYear
""
16 SQLCHAR 0 12 "\t" 16
DOBYear ""
17 SQLCHAR 0 12 "\t" 17
DOBMonth ""
18 SQLCHAR 0 12 "\t" 18 DOBDay
""
19 SQLCHAR 0 12 "\t" 19
KeyforenameID ""
20 SQLCHAR 0 12 "\r\n" 20
KeysurnameID ""
Its really tough to figure this issue out without seeing the table structure
or having a file. Something that has helped me out in this situation is to
get it set up in DTS first. That way maybe you can visually see where
something isnt matched up. Just an idea.
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They are
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafields
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay
> ""
> 13 SQLCHAR 0 3 "," 14 IsMonth
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 NameID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODDay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYear
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBDay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>
|||Hi Chris
Try something like:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 3 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They are
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafields
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay
> ""
> 13 SQLCHAR 0 3 "," 14 IsMonth
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 NameID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODDay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYear
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBDay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>
|||I a slightly staggered sequence where I want 2 to go to 3 etc. Would
something like this work? I ask before I spend ages barking up the wrong
tree!
1 SQLINT 0 0 "" 0 id ""
1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 2 Forename
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> Try something like:
> 8.0
> 14
> 1 SQLINT 0 0 "" 0 id ""
> 2 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "," 3 Forename
> Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "," 4 Age ""
> 5 SQLCHAR 0 12 "," 6 InscriptionID ""
> 6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
> 8 SQLCHAR 0 12 "," 9 ReligionID ""
> 9 SQLCHAR 0 12 "," 10 CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear ""
> 11 SQLCHAR 0 12 "," 12 DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay ""
> 13 SQLCHAR 0 3 "," 14 IsMonth ""
> 14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
> John
> "Chris" wrote:
|||Hi Chris
If your column order is forename before surname then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 2 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
If your column order is forename before surname and the file order is the
same then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Forename
3 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I a slightly staggered sequence where I want 2 to go to 3 etc. Would
> something like this work? I ask before I spend ages barking up the wrong
> tree!
>
> 1 SQLINT 0 0 "" 0 id ""
> 1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 2 Forename
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...
>
>
Sunday, February 26, 2012
Format File Problem - sorry did the first post wrong
I have generated a format file with bcp (SQL Server 2000) which generates a
file which would work if there was a column to column relationship between
the csv file and the table. My csv, however is in a different order.
Basically the table has an identity field but the csv file doesn't. They are
pretty similar other than that. I need the identity column in the table to
be ignored but datafield 1 to map to column 2 and so. All's I get is an
invalid column number error. Can anyone point me in the right direction?
Here is the format file, which is failing. There are also some columns at
the end which have been missed off as there are no corresponding datafields
for them.
Here is mine
8.0
13
1 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 3
Forename Latin1_General_CI_AS
3 SQLCHAR 0 12 "," 4 Age
""
5 SQLCHAR 0 12 "," 6
InscriptionID ""
6 SQLCHAR 0 12 "," 7
KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "," 9
ReligionID ""
8 SQLCHAR 0 12 "," 10
CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear
""
11 SQLCHAR 0 12 "," 12
DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay
""
13 SQLCHAR 0 3 "," 14 IsMonth
""
14 SQLCHAR 0 3 "/r/n" 15
IsYear ""
Here is the original
8.0
20
1 SQLCHAR 0 12 "\t" 1 NameID
""
2 SQLCHAR 0 510 "\t" 2
Surname Latin1_General_CI_AS
3 SQLCHAR 0 510 "\t" 3
Forename Latin1_General_CI_AS
4 SQLCHAR 0 12 "\t" 4 Age
""
5 SQLCHAR 0 12 "\t" 5 DOD
""
6 SQLCHAR 0 12 "\t" 6
InscriptionID ""
7 SQLCHAR 0 12 "\t" 7
KeyNameGroupID ""
8 SQLCHAR 0 12 "\t" 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "\t" 9
ReligionID ""
10 SQLCHAR 0 12 "\t" 10
CemeteryID ""
11 SQLCHAR 0 12 "\t" 11
DODYear ""
12 SQLCHAR 0 12 "\t" 12
DODMonth ""
13 SQLCHAR 0 12 "\t" 13 DODDay
""
14 SQLCHAR 0 3 "\t" 14
IsMonth ""
15 SQLCHAR 0 3 "\t" 15 IsYear
""
16 SQLCHAR 0 12 "\t" 16
DOBYear ""
17 SQLCHAR 0 12 "\t" 17
DOBMonth ""
18 SQLCHAR 0 12 "\t" 18 DOBDay
""
19 SQLCHAR 0 12 "\t" 19
KeyforenameID ""
20 SQLCHAR 0 12 "\r\n" 20
KeysurnameID ""Its really tough to figure this issue out without seeing the table structure
or having a file. Something that has helped me out in this situation is to
get it set up in DTS first. That way maybe you can visually see where
something isnt matched up. Just an idea.
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates
a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They a
re
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||Hi Chris
Try something like:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 3 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates
a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They a
re
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||I a slightly staggered sequence where I want 2 to go to 3 etc. Would
something like this work? I ask before I spend ages barking up the wrong
tree!
1 SQLINT 0 0 "" 0 id ""
1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 2 Forename
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> Try something like:
> 8.0
> 14
> 1 SQLINT 0 0 "" 0 id ""
> 2 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "," 3 Forename
> Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "," 4 Age ""
> 5 SQLCHAR 0 12 "," 6 InscriptionID ""
> 6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
> 8 SQLCHAR 0 12 "," 9 ReligionID ""
> 9 SQLCHAR 0 12 "," 10 CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear ""
> 11 SQLCHAR 0 12 "," 12 DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay ""
> 13 SQLCHAR 0 3 "," 14 IsMonth ""
> 14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
> John
> "Chris" wrote:
>|||Hi Chris
If your column order is forename before surname then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 2 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
If your column order is forename before surname and the file order is the
same then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Forename
3 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I a slightly staggered sequence where I want 2 to go to 3 etc. Would
> something like this work? I ask before I spend ages barking up the wrong
> tree!
>
> 1 SQLINT 0 0 "" 0 id ""
> 1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 2 Forename
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...
>
>
file which would work if there was a column to column relationship between
the csv file and the table. My csv, however is in a different order.
Basically the table has an identity field but the csv file doesn't. They are
pretty similar other than that. I need the identity column in the table to
be ignored but datafield 1 to map to column 2 and so. All's I get is an
invalid column number error. Can anyone point me in the right direction?
Here is the format file, which is failing. There are also some columns at
the end which have been missed off as there are no corresponding datafields
for them.
Here is mine
8.0
13
1 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 3
Forename Latin1_General_CI_AS
3 SQLCHAR 0 12 "," 4 Age
""
5 SQLCHAR 0 12 "," 6
InscriptionID ""
6 SQLCHAR 0 12 "," 7
KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "," 9
ReligionID ""
8 SQLCHAR 0 12 "," 10
CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear
""
11 SQLCHAR 0 12 "," 12
DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay
""
13 SQLCHAR 0 3 "," 14 IsMonth
""
14 SQLCHAR 0 3 "/r/n" 15
IsYear ""
Here is the original
8.0
20
1 SQLCHAR 0 12 "\t" 1 NameID
""
2 SQLCHAR 0 510 "\t" 2
Surname Latin1_General_CI_AS
3 SQLCHAR 0 510 "\t" 3
Forename Latin1_General_CI_AS
4 SQLCHAR 0 12 "\t" 4 Age
""
5 SQLCHAR 0 12 "\t" 5 DOD
""
6 SQLCHAR 0 12 "\t" 6
InscriptionID ""
7 SQLCHAR 0 12 "\t" 7
KeyNameGroupID ""
8 SQLCHAR 0 12 "\t" 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "\t" 9
ReligionID ""
10 SQLCHAR 0 12 "\t" 10
CemeteryID ""
11 SQLCHAR 0 12 "\t" 11
DODYear ""
12 SQLCHAR 0 12 "\t" 12
DODMonth ""
13 SQLCHAR 0 12 "\t" 13 DODDay
""
14 SQLCHAR 0 3 "\t" 14
IsMonth ""
15 SQLCHAR 0 3 "\t" 15 IsYear
""
16 SQLCHAR 0 12 "\t" 16
DOBYear ""
17 SQLCHAR 0 12 "\t" 17
DOBMonth ""
18 SQLCHAR 0 12 "\t" 18 DOBDay
""
19 SQLCHAR 0 12 "\t" 19
KeyforenameID ""
20 SQLCHAR 0 12 "\r\n" 20
KeysurnameID ""Its really tough to figure this issue out without seeing the table structure
or having a file. Something that has helped me out in this situation is to
get it set up in DTS first. That way maybe you can visually see where
something isnt matched up. Just an idea.
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates
a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They a
re
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||Hi Chris
Try something like:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 3 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates
a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They a
re
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||I a slightly staggered sequence where I want 2 to go to 3 etc. Would
something like this work? I ask before I spend ages barking up the wrong
tree!
1 SQLINT 0 0 "" 0 id ""
1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 2 Forename
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...[vbcol=seagreen]
> Hi Chris
> Try something like:
> 8.0
> 14
> 1 SQLINT 0 0 "" 0 id ""
> 2 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "," 3 Forename
> Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "," 4 Age ""
> 5 SQLCHAR 0 12 "," 6 InscriptionID ""
> 6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
> 8 SQLCHAR 0 12 "," 9 ReligionID ""
> 9 SQLCHAR 0 12 "," 10 CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear ""
> 11 SQLCHAR 0 12 "," 12 DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay ""
> 13 SQLCHAR 0 3 "," 14 IsMonth ""
> 14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
> John
> "Chris" wrote:
>|||Hi Chris
If your column order is forename before surname then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 2 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
If your column order is forename before surname and the file order is the
same then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Forename
3 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I a slightly staggered sequence where I want 2 to go to 3 etc. Would
> something like this work? I ask before I spend ages barking up the wrong
> tree!
>
> 1 SQLINT 0 0 "" 0 id ""
> 1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 2 Forename
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...
>
>
Format expressions(converting seconds to minutes)
can any one tell me how to change seconds to minutes by using if condition in rss...
for example i have value of seconds=50, minutes should come like this 50/60=0.833333.... i got the answer ... but the problem is how to roundoff or truncate the minutes to 0.83 or 0.84 from 0.8333333...
Thanks in anticipation
raj
using this expression i have got it ....
=iif( Fields!duration.Value=0,0,format(Fields!duration.Value/60,"0.00"))
Format expression returned a data type that is not valid
Hi,
I'm new on Reporting Services 2005 and would apreciate your help.
I'm trying to format a textbox in a matrix report to present an ABS value.
I've tried several formulas but I'm getting this warning :
"The Format expression used in textbox â'SALDO_PERIODOâ' returned a data type
that is not valid."
The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
Thanks,
--
NSThe format code property has to evaluate to a string. Also, the format code
property uses .NET formatcode string. Please check MSDN for details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nuno Santos" <NunoSantos@.discussions.microsoft.com> wrote in message
news:E6AC8CC1-61A1-4418-9982-691755C7C550@.microsoft.com...
> Hi,
> I'm new on Reporting Services 2005 and would apreciate your help.
> I'm trying to format a textbox in a matrix report to present an ABS value.
> I've tried several formulas but I'm getting this warning :
> "The Format expression used in textbox 'SALDO_PERIODO' returned a data
> type
> that is not valid."
> The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
> I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
> Thanks,
> --
> NS
I'm new on Reporting Services 2005 and would apreciate your help.
I'm trying to format a textbox in a matrix report to present an ABS value.
I've tried several formulas but I'm getting this warning :
"The Format expression used in textbox â'SALDO_PERIODOâ' returned a data type
that is not valid."
The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
Thanks,
--
NSThe format code property has to evaluate to a string. Also, the format code
property uses .NET formatcode string. Please check MSDN for details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nuno Santos" <NunoSantos@.discussions.microsoft.com> wrote in message
news:E6AC8CC1-61A1-4418-9982-691755C7C550@.microsoft.com...
> Hi,
> I'm new on Reporting Services 2005 and would apreciate your help.
> I'm trying to format a textbox in a matrix report to present an ABS value.
> I've tried several formulas but I'm getting this warning :
> "The Format expression used in textbox 'SALDO_PERIODO' returned a data
> type
> that is not valid."
> The textbox value is Sum(Fields!SALDO_PERIODO.Value and the format formula
> I'm using is =Abs(Sum(Fields!SALDO_PERIODO.Value, "SALDOS"))
> Thanks,
> --
> NS
format expression is being ignored in table cell
I am consolidating multiple reports into one and wish to set the formatting
on the cell dependent upon the data.
The following code does not work
= iif(Parameters!RevenueColumns.Value=1,
"#,#"
,"")
&
iif(Parameters!RevenueColumns.Value=2,
"N2"
,"")
&
iif(Parameters!RevenueColumns.Value=3,
"N2"
,"")
When RevenueColumns is 1, the number is being quoted to about 20 decimal
places.
There is nothing in 'format' for the column or row, this is the only place
where format is set. Even if I change it back to just "#,#" it is being
ignored but i know it is accepting changes as i have varied the colour. I
have also tried N0 as the format and also 0.0 but nothing seems to change it,
even changing it back to the original value has not solved the issue. I have
deleted and redeployed the report, cleared out my IE cache and rebooted but
to no avail.
Can anyone help?
thanksthe numeric and the two null strs are being converted to a string, on which
it is not able to perform the formatting
coerce my arce
"adolf garlic" wrote:
> I am consolidating multiple reports into one and wish to set the formatting
> on the cell dependent upon the data.
> The following code does not work
> => iif(Parameters!RevenueColumns.Value=1,
> "#,#"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=2,
> "N2"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=3,
> "N2"
> ,"")
> When RevenueColumns is 1, the number is being quoted to about 20 decimal
> places.
> There is nothing in 'format' for the column or row, this is the only place
> where format is set. Even if I change it back to just "#,#" it is being
> ignored but i know it is accepting changes as i have varied the colour. I
> have also tried N0 as the format and also 0.0 but nothing seems to change it,
> even changing it back to the original value has not solved the issue. I have
> deleted and redeployed the report, cleared out my IE cache and rebooted but
> to no avail.
> Can anyone help?
> thanks
on the cell dependent upon the data.
The following code does not work
= iif(Parameters!RevenueColumns.Value=1,
"#,#"
,"")
&
iif(Parameters!RevenueColumns.Value=2,
"N2"
,"")
&
iif(Parameters!RevenueColumns.Value=3,
"N2"
,"")
When RevenueColumns is 1, the number is being quoted to about 20 decimal
places.
There is nothing in 'format' for the column or row, this is the only place
where format is set. Even if I change it back to just "#,#" it is being
ignored but i know it is accepting changes as i have varied the colour. I
have also tried N0 as the format and also 0.0 but nothing seems to change it,
even changing it back to the original value has not solved the issue. I have
deleted and redeployed the report, cleared out my IE cache and rebooted but
to no avail.
Can anyone help?
thanksthe numeric and the two null strs are being converted to a string, on which
it is not able to perform the formatting
coerce my arce
"adolf garlic" wrote:
> I am consolidating multiple reports into one and wish to set the formatting
> on the cell dependent upon the data.
> The following code does not work
> => iif(Parameters!RevenueColumns.Value=1,
> "#,#"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=2,
> "N2"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=3,
> "N2"
> ,"")
> When RevenueColumns is 1, the number is being quoted to about 20 decimal
> places.
> There is nothing in 'format' for the column or row, this is the only place
> where format is set. Even if I change it back to just "#,#" it is being
> ignored but i know it is accepting changes as i have varied the colour. I
> have also tried N0 as the format and also 0.0 but nothing seems to change it,
> even changing it back to the original value has not solved the issue. I have
> deleted and redeployed the report, cleared out my IE cache and rebooted but
> to no avail.
> Can anyone help?
> thanks
Friday, February 24, 2012
Format Expression
My client's want certain areas of my expression to be bold and also a
different color. Via HTML or CSS this would be the solution:
="<b><font color=blue>Resource(s) Name:</font></b> " &
Fields!ResourceName.Value & vbcrlf &
"<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other Resource
Role:</font></b> " & Fields!OtherResourceRole.Value
However, this does not work with SSRS. Is this something that will be
allowed in SSRS 2008 or is it possible with 2005?
--
<moojjoo/>RS 2008 is going support rich format. I have not played with the CTPs
(general pre-release software) so I am not totally firm in my head on how
this support will show itself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> My client's want certain areas of my expression to be bold and also a
> different color. Via HTML or CSS this would be the solution:
> ="<b><font color=blue>Resource(s) Name:</font></b> " &
> Fields!ResourceName.Value & vbcrlf &
> "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> Resource
> Role:</font></b> " & Fields!OtherResourceRole.Value
> However, this does not work with SSRS. Is this something that will be
> allowed in SSRS 2008 or is it possible with 2005?
> --
> <moojjoo/>|||So basically I am up the river without a paddle on what my client wants at
this time with formating the font?
--
<moojjoo/>
"Bruce L-C [MVP]" wrote:
> RS 2008 is going support rich format. I have not played with the CTPs
> (general pre-release software) so I am not totally firm in my head on how
> this support will show itself.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > My client's want certain areas of my expression to be bold and also a
> > different color. Via HTML or CSS this would be the solution:
> >
> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > Fields!ResourceName.Value & vbcrlf &
> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > Resource
> > Role:</font></b> " & Fields!OtherResourceRole.Value
> >
> > However, this does not work with SSRS. Is this something that will be
> > allowed in SSRS 2008 or is it possible with 2005?
> > --
> > <moojjoo/>
>
>|||You can do things like put multiple text boxes next to each other and format
each of those (which really only works for very simple scenarios).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:17509CDD-B058-44DF-8DD9-004D6B027AF4@.microsoft.com...
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
>> RS 2008 is going support rich format. I have not played with the CTPs
>> (general pre-release software) so I am not totally firm in my head on how
>> this support will show itself.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > My client's want certain areas of my expression to be bold and also a
>> > different color. Via HTML or CSS this would be the solution:
>> >
>> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > Fields!ResourceName.Value & vbcrlf &
>> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
>> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > Resource
>> > Role:</font></b> " & Fields!OtherResourceRole.Value
>> >
>> > However, this does not work with SSRS. Is this something that will be
>> > allowed in SSRS 2008 or is it possible with 2005?
>> > --
>> > <moojjoo/>
>>|||Bruce, I totally can understand that the only problem is I have this in a
table... and they are wanting multiple items in a cell along with that
formatting I am referrring to.
--
<moojjoo/>
"Moojjoo" wrote:
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
> > RS 2008 is going support rich format. I have not played with the CTPs
> > (general pre-release software) so I am not totally firm in my head on how
> > this support will show itself.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > > My client's want certain areas of my expression to be bold and also a
> > > different color. Via HTML or CSS this would be the solution:
> > >
> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > > Fields!ResourceName.Value & vbcrlf &
> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > > Resource
> > > Role:</font></b> " & Fields!OtherResourceRole.Value
> > >
> > > However, this does not work with SSRS. Is this something that will be
> > > allowed in SSRS 2008 or is it possible with 2005?
> > > --
> > > <moojjoo/>
> >
> >
> >|||You are out of luck. This is very common requirement which is why it is
being implemented in RS 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:C4A3785A-A03A-4855-B524-F6E9A65D39E4@.microsoft.com...
> Bruce, I totally can understand that the only problem is I have this in a
> table... and they are wanting multiple items in a cell along with that
> formatting I am referrring to.
> --
> <moojjoo/>
>
> "Moojjoo" wrote:
>> So basically I am up the river without a paddle on what my client wants
>> at
>> this time with formating the font?
>> --
>> <moojjoo/>
>>
>> "Bruce L-C [MVP]" wrote:
>> > RS 2008 is going support rich format. I have not played with the CTPs
>> > (general pre-release software) so I am not totally firm in my head on
>> > how
>> > this support will show itself.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > > My client's want certain areas of my expression to be bold and also a
>> > > different color. Via HTML or CSS this would be the solution:
>> > >
>> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > > Fields!ResourceName.Value & vbcrlf &
>> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf
>> > > &
>> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > > Resource
>> > > Role:</font></b> " & Fields!OtherResourceRole.Value
>> > >
>> > > However, this does not work with SSRS. Is this something that will
>> > > be
>> > > allowed in SSRS 2008 or is it possible with 2005?
>> > > --
>> > > <moojjoo/>
>> >
>> >
>> >
different color. Via HTML or CSS this would be the solution:
="<b><font color=blue>Resource(s) Name:</font></b> " &
Fields!ResourceName.Value & vbcrlf &
"<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other Resource
Role:</font></b> " & Fields!OtherResourceRole.Value
However, this does not work with SSRS. Is this something that will be
allowed in SSRS 2008 or is it possible with 2005?
--
<moojjoo/>RS 2008 is going support rich format. I have not played with the CTPs
(general pre-release software) so I am not totally firm in my head on how
this support will show itself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> My client's want certain areas of my expression to be bold and also a
> different color. Via HTML or CSS this would be the solution:
> ="<b><font color=blue>Resource(s) Name:</font></b> " &
> Fields!ResourceName.Value & vbcrlf &
> "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> Resource
> Role:</font></b> " & Fields!OtherResourceRole.Value
> However, this does not work with SSRS. Is this something that will be
> allowed in SSRS 2008 or is it possible with 2005?
> --
> <moojjoo/>|||So basically I am up the river without a paddle on what my client wants at
this time with formating the font?
--
<moojjoo/>
"Bruce L-C [MVP]" wrote:
> RS 2008 is going support rich format. I have not played with the CTPs
> (general pre-release software) so I am not totally firm in my head on how
> this support will show itself.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > My client's want certain areas of my expression to be bold and also a
> > different color. Via HTML or CSS this would be the solution:
> >
> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > Fields!ResourceName.Value & vbcrlf &
> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > Resource
> > Role:</font></b> " & Fields!OtherResourceRole.Value
> >
> > However, this does not work with SSRS. Is this something that will be
> > allowed in SSRS 2008 or is it possible with 2005?
> > --
> > <moojjoo/>
>
>|||You can do things like put multiple text boxes next to each other and format
each of those (which really only works for very simple scenarios).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:17509CDD-B058-44DF-8DD9-004D6B027AF4@.microsoft.com...
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
>> RS 2008 is going support rich format. I have not played with the CTPs
>> (general pre-release software) so I am not totally firm in my head on how
>> this support will show itself.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > My client's want certain areas of my expression to be bold and also a
>> > different color. Via HTML or CSS this would be the solution:
>> >
>> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > Fields!ResourceName.Value & vbcrlf &
>> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
>> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > Resource
>> > Role:</font></b> " & Fields!OtherResourceRole.Value
>> >
>> > However, this does not work with SSRS. Is this something that will be
>> > allowed in SSRS 2008 or is it possible with 2005?
>> > --
>> > <moojjoo/>
>>|||Bruce, I totally can understand that the only problem is I have this in a
table... and they are wanting multiple items in a cell along with that
formatting I am referrring to.
--
<moojjoo/>
"Moojjoo" wrote:
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
> > RS 2008 is going support rich format. I have not played with the CTPs
> > (general pre-release software) so I am not totally firm in my head on how
> > this support will show itself.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > > My client's want certain areas of my expression to be bold and also a
> > > different color. Via HTML or CSS this would be the solution:
> > >
> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > > Fields!ResourceName.Value & vbcrlf &
> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > > Resource
> > > Role:</font></b> " & Fields!OtherResourceRole.Value
> > >
> > > However, this does not work with SSRS. Is this something that will be
> > > allowed in SSRS 2008 or is it possible with 2005?
> > > --
> > > <moojjoo/>
> >
> >
> >|||You are out of luck. This is very common requirement which is why it is
being implemented in RS 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:C4A3785A-A03A-4855-B524-F6E9A65D39E4@.microsoft.com...
> Bruce, I totally can understand that the only problem is I have this in a
> table... and they are wanting multiple items in a cell along with that
> formatting I am referrring to.
> --
> <moojjoo/>
>
> "Moojjoo" wrote:
>> So basically I am up the river without a paddle on what my client wants
>> at
>> this time with formating the font?
>> --
>> <moojjoo/>
>>
>> "Bruce L-C [MVP]" wrote:
>> > RS 2008 is going support rich format. I have not played with the CTPs
>> > (general pre-release software) so I am not totally firm in my head on
>> > how
>> > this support will show itself.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > > My client's want certain areas of my expression to be bold and also a
>> > > different color. Via HTML or CSS this would be the solution:
>> > >
>> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > > Fields!ResourceName.Value & vbcrlf &
>> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf
>> > > &
>> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > > Resource
>> > > Role:</font></b> " & Fields!OtherResourceRole.Value
>> > >
>> > > However, this does not work with SSRS. Is this something that will
>> > > be
>> > > allowed in SSRS 2008 or is it possible with 2005?
>> > > --
>> > > <moojjoo/>
>> >
>> >
>> >
fORMAT eXCEPTION
Hi
I am continuously getting the error:
I am continuously getting the error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 57: {
Line 58: Connection.Open() ;
Line 59: ThreadResult = ThreadCommand.ExecuteNonQuery() ;
Line 60: }
Line 61:
in the following part of the code :
protected void PostClick(object sender,EventArgs e)
{
SqlConnection Connection ;
SqlCommand ThreadCommand ;
SqlCommand GetThreadCommand ;
SqlCommand MessageCommand ;
SqlDataReader ThreadReader ;
string InsertThread,InsertMessage ;
string GetThread,Message ;
long ThreadID ;
int ThreadResult ;Connection = new SqlConnection("server=HT; Integrated Security= SSPI; Database=Forums");
Feedback.Text = "" ;InsertThread = "Insert Into Threads(ThreadName,TopicId) VALUES(@.ThreadName,@.TopicId)" ;
ThreadCommand = new SqlCommand(InsertThread, Connection) ;ThreadCommand.Parameters.Add(new SqlParameter("@.ThreadName", SqlDbType.VarChar,50)) ;
ThreadCommand.Parameters.Add(new SqlParameter("@.TopicId", System.Data.SqlDbType.Int));ThreadCommand.Parameters["@.ThreadName"].Value = ThreadText.Text ;
ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;try
{
Connection.Open() ;
ThreadResult = ThreadCommand.ExecuteNonQuery() ;
}catch (System.Data.SqlClient.SqlException excp)
{
if (excp.Errors[0].Number == -105121349)
{
Feedback.Text = "<font color=red>*** There is already a thread with the name"+ThreadText.Text+". Please choose a different name and click Post.</font><br><br>" ;
}
else
{
Feedback.Text = "<font color=red>*** "+excp.Errors[0].Message +"<br><br>" ;
}
}
I would be very grateful for any suggestions.I expect the problem is here:
ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;
Perhaps Request.QueryString["TopicId"] is null, or not a value that can be converted into a number? Debug the application and check this value, or alternately, do a Response.Write("TopicID: " + Request.QueryString["TopicId"] );|||Hi
Yes Mr.Douglas you are right. but the problem lies in this statement only in this page. Actually i think its in the following line in the other page :
<a href="http://links.10026.com/?link=threads.aspx?TopicId=<%=Request.QueryString["TopicId"]%>&TopicName= <%=Request.QueryString["TopicName"]%>"
Actually on printing TopicId, i noticed its '1' (with comma) instead of being just 1 due to the courtesy of above line. Can you please tell me how can i remove the comma thats being appended. Thanks a lot|||I presume when you say comma, you really mean the single quotes ' and ' and if so, change to this:
=<%=String.Trim(Request.QueryString["TopicId"],"'".ToCharArray()%
The best bet would be to determine why, on the previous page, it is putting the single quotes in.|||Yes you are right , i can use the trim method but Mr. Douglas why is the following statement attaching a comma.
<%=Request.QueryString["TopicId"]%
in
<a href="http://links.10026.com/?link=threads.aspx?TopicId=
<%=Request.QueryString["TopicId"]%>
&TopicName=<%=Request.QueryString["TopicName"]%>"
Thanks a lot and Mr. Douglas i would like you advise on a designing tool . Which one do u use since yesterday i was trying to do things with Html Table but thats a HeadAche and i can't even arrange the data the way i like.|||I have no idea what the value of Request.QueryString["TopicID"] is, but my guess is that somehow, it has the apostrophes.
I tend to use a mix of tables and Cascading Style sheets, but honestly, my page design skills are less than perfect.
Subscribe to:
Posts (Atom)