Formatting Issues

Hi all,
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>

Formatting a Select Statement

Hi everyone!
I am looking for a function similar to the Left(), Right(), and
Replace() functions to insert characters into a returned list of my
date field. The date currently looks like this "06202006" the function
I am looking for would return "06/20/2006" something like this if
Insert() was an actual function:
SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
FROM tblLogs;
Please let me know if you have any experience with this.Perhaps you are looking for STUFF:
SELECT STUFF(STUFF([date], 5, 0, '/'), 3, 0, '/')
FROM tblLogs;
However, data formatting is better done in the presentation layer rather
than the database code.
Hope this helps.
Dan Guzman
SQL Server MVP
"EESP" <johnson4@.wwu.edu> wrote in message
news:1150932662.524736.128910@.b68g2000cwa.googlegroups.com...
> Hi everyone!
> I am looking for a function similar to the Left(), Right(), and
> Replace() functions to insert characters into a returned list of my
> date field. The date currently looks like this "06202006" the function
> I am looking for would return "06/20/2006" something like this if
> Insert() was an actual function:
> SELECT DISTINCT insert("/", right(insert("/",left(date, 2)), 2)
> FROM tblLogs;
> Please let me know if you have any experience with this.
>|||Works Great! I would like to format in the presentation layer of the
code, but I'm having troble running that formatting code through a
datgrid returning close to 10K entries. For some reason just works a
lot faster server side. Thanks for the help!

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.
Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.
|||SQL Server returns meta-data with the result set. This meta-data includes information about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to define the length of
the columns presented. Note that when you execute into grid, things are different. The columns are
indeed made smaller to include more information in what you see. So, in other words, what you see if
done by the client application, not SQL server itself.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.
|||Thanks Uri,
Best regards
"Uri Dimant" wrote:

> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
>
>

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||SQL Server returns meta-data with the result set. This meta-data includes information about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to define the length of
the columns presented. Note that when you execute into grid, things are different. The columns are
indeed made smaller to include more information in what you see. So, in other words, what you see if
done by the client application, not SQL server itself.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||Thanks Uri,
Best regards
"Uri Dimant" wrote:
> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> > Hello,
> >
> > Can you tell me how can i format the column with in the output of a query?
> > The results must be returned as text and my output is like this:
> >
> > y x
> > -- --
> > 1 2
> >
> > the way im treating the output is to cast the field as varchar(xxx):
> > select cast(y as varchar(1)), cast(x as varchar(1))
> > from xxxxxx
> >
> > y x
> > - -
> > 1 2
> >
> > It is possible to do it with a different way?
> >
> > Thanks and best regards.
>
>

Format the column with

Hello,
Can you tell me how can i format the column with in the output of a query?
The results must be returned as text and my output is like this:
y x
-- --
1 2
the way im treating the output is to cast the field as varchar(xxx):
select cast(y as varchar(1)), cast(x as varchar(1))
from xxxxxx
y x
- -
1 2
It is possible to do it with a different way?
Thanks and best regards.Hi
SELECT coalesce(x,'')+coalesce(y,'') as blabla
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||SQL Server returns meta-data with the result set. This meta-data includes in
formation about the
datatype for each columns. A tool like Query Analyzer uses this meta-data to
define the length of
the columns presented. Note that when you execute into grid, things are diff
erent. The columns are
indeed made smaller to include more information in what you see. So, in othe
r words, what you see if
done by the client application, not SQL server itself.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
> Hello,
> Can you tell me how can i format the column with in the output of a query?
> The results must be returned as text and my output is like this:
> y x
> -- --
> 1 2
> the way im treating the output is to cast the field as varchar(xxx):
> select cast(y as varchar(1)), cast(x as varchar(1))
> from xxxxxx
> y x
> - -
> 1 2
> It is possible to do it with a different way?
> Thanks and best regards.|||Thanks Uri,
Best regards
"Uri Dimant" wrote:

> Hi
> SELECT coalesce(x,'')+coalesce(y,'') as blabla
>
> "CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
> news:5386EC39-E9F2-4D5E-A965-E9BFF9984D3E@.microsoft.com...
>
>

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