Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), ' ') AS strValue
Thanks in advance,
Steven
I'm guessing that T.Value is a floating point datatype?
If so, then the problem is deeper then a display issue. Floating point datatypes only store so many signifigant digits of precision - the other digits are lost. They are turned into 0's.
|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
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
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), '&nbsp;') AS strValue
> --
> Thanks in advance,
> Steven
>
sql

Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), ' ') AS strValue
--
Thanks in advance,
StevenI'm guessing that T.Value is a floating point datatype?
If so, then the problem is deeper then a display issue. Floating point datatypes only store so many signifigant digits of precision - the other digits are lost. They are turned into 0's.|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
--
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
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), ' ') AS strValue
> --
> Thanks in advance,
> Steven
>

Numbers show as exponential

Hello,
I am using the code below to sum values for the web. Instead of getting
11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
from being show in exponential?
IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
ELSE NULL END))), ' ') AS strValue
Thanks in advance,
StevenI'm guessing that T.Value is a floating point datatype'
If so, then the problem is deeper then a display issue. Floating point data
types only store so many signifigant digits of precision - the other digits
are lost. They are turned into 0's.|||One thing you might try is to convert the number to a decimal before
converting it to a varchar...
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
"Steven K" <skaper@.troop.com> wrote in message
news:OIk2gv2PEHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am using the code below to sum values for the web. Instead of getting
> 11911712.0, I am getting 1.19117e+007. Is there a way to prevent numbers
> from being show in exponential?
>
> IsNull(Convert(varchar(30),(SUM(CASE WHEN Treg.Region = 'AP' Then T.Value
> ELSE NULL END))), ' ') AS strValue
> --
> Thanks in advance,
> Steven
>

Number of rows in the Excel sheet exceeded the limit of 65536 rows

Hi,
I got this message from the export to xls in reporting services.
Is there a way to prevent this? Can I set some property on the report to use
a new sheet on a group for example?
Thanks
BartOn Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> Hi,
> I got this message from the export to xls in reporting services.
> Is there a way to prevent this? Can I set some property on the report to use
> a new sheet on a group for example?
> Thanks
> Bart
There are a couple of ways to handle this. One way would be to select
the properties on the report control you are using (i.e., table, list,
etc) and select 'Insert a page break after this table' (ie). Another
way to handle this is to select 'Page break at end' as part of the
control's grouping. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hi Enrique,
Thanks for your help.
It seems that a pagebreak is working on groups.
Bart
"EMartinez" wrote:
> On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > Hi,
> >
> > I got this message from the export to xls in reporting services.
> > Is there a way to prevent this? Can I set some property on the report to use
> > a new sheet on a group for example?
> >
> > Thanks
> > Bart
> There are a couple of ways to handle this. One way would be to select
> the properties on the report control you are using (i.e., table, list,
> etc) and select 'Insert a page break after this table' (ie). Another
> way to handle this is to select 'Page break at end' as part of the
> control's grouping. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 15, 9:18 am, Bart <bart...@.community.nospam> wrote:
> Hi Enrique,
> Thanks for your help.
> It seems that a pagebreak is working on groups.
> Bart
> "EMartinez" wrote:
> > On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > > Hi,
> > > I got this message from the export to xls in reporting services.
> > > Is there a way to prevent this? Can I set some property on the report to use
> > > a new sheet on a group for example?
> > > Thanks
> > > Bart
> > There are a couple of ways to handle this. One way would be to select
> > the properties on the report control you are using (i.e., table, list,
> > etc) and select 'Insert a page break after this table' (ie). Another
> > way to handle this is to select 'Page break at end' as part of the
> > control's grouping. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad it worked out.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I'm sorry, but forgot 'not' in my previous message.
A pagebreak doesn't work on groups.
Thanks
Bart
"EMartinez" wrote:
> On Mar 15, 9:18 am, Bart <bart...@.community.nospam> wrote:
> > Hi Enrique,
> >
> > Thanks for your help.
> > It seems that a pagebreak is working on groups.
> >
> > Bart
> >
> > "EMartinez" wrote:
> > > On Mar 15, 5:37 am, Bart <bart...@.community.nospam> wrote:
> > > > Hi,
> >
> > > > I got this message from the export to xls in reporting services.
> > > > Is there a way to prevent this? Can I set some property on the report to use
> > > > a new sheet on a group for example?
> >
> > > > Thanks
> > > > Bart
> >
> > > There are a couple of ways to handle this. One way would be to select
> > > the properties on the report control you are using (i.e., table, list,
> > > etc) and select 'Insert a page break after this table' (ie). Another
> > > way to handle this is to select 'Page break at end' as part of the
> > > control's grouping. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
>
> You're welcome. Glad it worked out.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||Hello Bart,
Here is the limitation of the Excel render in reporting services:
Excel Rendering Limitations
http://msdn2.microsoft.com/en-us/library/ms156418.aspx
Excel 2007 did extend the limitation and you may follow this article:
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx
So my suggestion is that you could try to install the latest services pack
and render to excel 2007.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Nulls in concatenated string

How do I prevent the following null 'Answer'?
This SQL will return a null string for 'Answer' whenever the count is null either for 'subquery-1' or for 'subquery-2', even though the other is not null. I need a string in either case. It would be better to have 'Answer' be "f1=, f2=25" than to have nothing. It doesn't seem right that both COUNT's have to be non-null to get anything other than null for the concatenated 'Answer'. There ought to be a way for COUNT to return 0 in some cases where it now returns null. I'd expect/prefer an 'Answer' of "f1=0, f2=25" or maybe even "f1=<null>, f2=25".
I expect I'd have the same problem with nulls even if I wasn't using subqueries.
SELECT 'f1='+CAST(COUNT(subquery-1) AS VARCHAR)+', f2='+CAST(COUNT(subquery-2) AS VARCHAR) AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldXTheISNULL function should help you out:

SELECT 'f1='+ISNULL(CAST(COUNT(subquery-1) AS VARCHAR),'')+', f2='+ISNULL(CAST(COUNT(subquery-2) AS VARCHAR),'') AS Answer
FROM table1
WHERE condition=5
GROUP BY fieldX