Expression help on dataset parameter value

I am trying to use and expression in the parameters tab on the dataset. I
have a parameter called END_DATE and I want it to be equal to the expression
=DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
The expression comes out fine on the report. I tested it there to see if it
would generate the proper date and it did. Then I moved the expression from
the report and into the Value side of the Parameter on the Parameter tab of
the dataset and
I get an error CLI0111E Numeric value out of range SQLSTATE=22003
on this (I am using DB2)It probably has to be a DB2 function in the dataset...
--
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
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
>I am trying to use and expression in the parameters tab on the dataset. I
> have a parameter called END_DATE and I want it to be equal to the
> expression
> =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> The expression comes out fine on the report. I tested it there to see if
> it
> would generate the proper date and it did. Then I moved the expression
> from
> the report and into the Value side of the Parameter on the Parameter tab
> of
> the dataset and
> I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> on this (I am using DB2)|||Sorry Wayne ... I am not sure what you mean by your reply. The dataset
consists of just a stored procedure. In the dataset tab this is all there is
- -
GRSINST1.SP_RPT_RES_UPTIME
how would I make this a DB2 function in the dataset?
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> 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
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>|||I must have forgotten to say that I am using a db2 stored procedure and
passing it parameters so I dont know where else I can manipulate the parm
since it is used for the query. I thought I had read that you can use
expressions on the parameter tab of the dataset. So why cant I get this
expression to work. It is fairly simple and it works when I put it on the
report (which I did just to verify syntax).
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> 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
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>

Expression for Jump to Report

Hi Experts,
Can anyone tell me how to specify an expression to be mentioned in the "Jump
to Report" in the Navigation tab. I basically dont want the hyperlink for
some values.
= iif( Fields!ItemCount.Value = 0, "", "Detail")
this is the expression which I have currently given. I dont want the
hyperlink when the ItemCount = 0. But now it points to the same page since I
have given empty string. How can I handle this.
Thanks in Advance
GaneshHi,
Instead of empty string just put "Nothing" word. This will solve your problem.
Regards
Amarnath
"Ganesh Ramamurthy" wrote:
> Hi Experts,
> Can anyone tell me how to specify an expression to be mentioned in the "Jump
> to Report" in the Navigation tab. I basically dont want the hyperlink for
> some values.
> = iif( Fields!ItemCount.Value = 0, "", "Detail")
> this is the expression which I have currently given. I dont want the
> hyperlink when the ItemCount = 0. But now it points to the same page since I
> have given empty string. How can I handle this.
> Thanks in Advance
> Ganesh
>
>|||Thanx Amar for the reply,
i tried that = iif( Fields!ItemCount.Value = 0, "Nothing", "Detail")...
I get the following error
The item '/Reports/Nothing' cannot be found. (rsItemNotFound)
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:3FEC77CE-9386-48E4-87CB-171F9EBBC053@.microsoft.com...
> Hi,
> Instead of empty string just put "Nothing" word. This will solve your
> problem.
> Regards
> Amarnath
> "Ganesh Ramamurthy" wrote:
>> Hi Experts,
>> Can anyone tell me how to specify an expression to be mentioned in the
>> "Jump
>> to Report" in the Navigation tab. I basically dont want the hyperlink for
>> some values.
>> = iif( Fields!ItemCount.Value = 0, "", "Detail")
>> this is the expression which I have currently given. I dont want the
>> hyperlink when the ItemCount = 0. But now it points to the same page
>> since I
>> have given empty string. How can I handle this.
>> Thanks in Advance
>> Ganesh
>>|||Amar..
Sorry for the Previous reply...now it is working correct...I had put Nothing
in quotes...
now it is working fine...quite a long time since worked in VB...
Thanks again
Ganesh
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:3FEC77CE-9386-48E4-87CB-171F9EBBC053@.microsoft.com...
> Hi,
> Instead of empty string just put "Nothing" word. This will solve your
> problem.
> Regards
> Amarnath
> "Ganesh Ramamurthy" wrote:
>> Hi Experts,
>> Can anyone tell me how to specify an expression to be mentioned in the
>> "Jump
>> to Report" in the Navigation tab. I basically dont want the hyperlink for
>> some values.
>> = iif( Fields!ItemCount.Value = 0, "", "Detail")
>> this is the expression which I have currently given. I dont want the
>> hyperlink when the ItemCount = 0. But now it points to the same page
>> since I
>> have given empty string. How can I handle this.
>> Thanks in Advance
>> Ganesh
>>

Express SP1 No version #

I downloaded the SQL Server 2005 Express SP1 product today. If I right click SQLEXPR.EXE, select Properties, and select the Version tab, the File Version value is 0.0.0.0. The version listed for the GA product was 9.0.1399.6.

Is this a problem to be concerned about?

Hi,

that shouldn′t bother you, smae for me here. Look at the digital signature date, if it is sometime in April 2006 signed (as mine is) this is the right one for you. Starting the File will also come up with a EULA shwoing the Service pack Level.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SQLEXPR.EXE is a compressed archive, you'll find version numbers on the actual files that are installed.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Express SP1 No version #

I downloaded the SQL Server 2005 Express SP1 product today. If I right click SQLEXPR.EXE, select Properties, and select the Version tab, the File Version value is 0.0.0.0. The version listed for the GA product was 9.0.1399.6.

Is this a problem to be concerned about?

Hi,

that shouldn′t bother you, smae for me here. Look at the digital signature date, if it is sometime in April 2006 signed (as mine is) this is the right one for you. Starting the File will also come up with a EULA shwoing the Service pack Level.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SQLEXPR.EXE is a compressed archive, you'll find version numbers on the actual files that are installed.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Exporting to multiple sheets in Excel has limitations ?

guys, I am exporting a report in excel that generates in different
sheets.
It seems that only 3 tab or sheets can be generated.
If the report exceeds 3 sheets all the rest of the sheets combined in
the last sheet.
Can anyone confirm this? Does it have any work around or I am just
doing something wrong?
thanks in advanceHi, I am doing some testing on sending data to Excel myself. I just tested
this and was able to make 5 sheets when I made page brakes between rectagles
in the report. Have you used the device setting RemoveSpace when rendering
to Excel ?
"jhun_garma@.hotmail.com" wrote:
> guys, I am exporting a report in excel that generates in different
> sheets.
> It seems that only 3 tab or sheets can be generated.
> If the report exceeds 3 sheets all the rest of the sheets combined in
> the last sheet.
> Can anyone confirm this? Does it have any work around or I am just
> doing something wrong?
>
> thanks in advance
>

Exporting to Excel from Preview Tab

I designed a report and from Preview tab, i exported it to Excel format and saved. I sent the Xls file to others, but when they opened it, it is displaying the Metadata info of the file. any idea what is going on?

In my report i have a table, and above it i have a bunch of textboxes which serves as a heading for groups of columns in the table. when i preview it is fine. But Exported to excel, some of these textboxes are wrapping to the next line. any idea what is going on? (I tried aligning all the textboxes)

thanks.

Which version / release of the product are you using? The initial release of RS 2000 did not support older versions of Excel. You can install the second service pack or move to RS 2005.

As for the export, make sure that you are putting the textboxes inside the table header, not as textboxes above the table. This will help you align columns.

Exporting to Excel from Preview Tab

I designed a report and from Preview tab, i exported it to Excel format and
saved. I sent the Xls file to others, but when they opened it, it is
displaying the Metadata info of the file. any idea what is going on?
In my report i have a table, and above it i have a bunch of textboxes which
serves as a heading for groups of columns in the table. when i preview it is
fine. But Exported to excel, some of these textboxes are wrapping to the next
line. any idea what is going on? (I tried aligning all the textboxes)
thanks.Don't know about the second issue but the first issue sounds like you have
not installed either SP1 or SP2. RS initially did not support Excel 2000 or
Excel 97. Starting with SP1 they added support for those version. My guess
is the users you sent the file to are on Excel 2000 or earlier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:8B2D6E73-9824-40CC-90DB-5BAEFEFCEA82@.microsoft.com...
>I designed a report and from Preview tab, i exported it to Excel format and
> saved. I sent the Xls file to others, but when they opened it, it is
> displaying the Metadata info of the file. any idea what is going on?
> In my report i have a table, and above it i have a bunch of textboxes
> which
> serves as a heading for groups of columns in the table. when i preview it
> is
> fine. But Exported to excel, some of these textboxes are wrapping to the
> next
> line. any idea what is going on? (I tried aligning all the textboxes)
> thanks.|||thanks.
"Bruce L-C [MVP]" wrote:
> Don't know about the second issue but the first issue sounds like you have
> not installed either SP1 or SP2. RS initially did not support Excel 2000 or
> Excel 97. Starting with SP1 they added support for those version. My guess
> is the users you sent the file to are on Excel 2000 or earlier.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Giya" <Giya@.discussions.microsoft.com> wrote in message
> news:8B2D6E73-9824-40CC-90DB-5BAEFEFCEA82@.microsoft.com...
> >I designed a report and from Preview tab, i exported it to Excel format and
> > saved. I sent the Xls file to others, but when they opened it, it is
> > displaying the Metadata info of the file. any idea what is going on?
> >
> > In my report i have a table, and above it i have a bunch of textboxes
> > which
> > serves as a heading for groups of columns in the table. when i preview it
> > is
> > fine. But Exported to excel, some of these textboxes are wrapping to the
> > next
> > line. any idea what is going on? (I tried aligning all the textboxes)
> >
> > thanks.
>
>

Exporting to Excel - renaming tabs

I have created an adhoc report in VS which I then export to Excel. The
way the report is set up is that I have a seperate tab(worksheet) for
each company that features on the report. However, I would like the
company name on each tab instead of the generic sheet1, sheet2 etc..
Any ideas welcome!I don't have an answer to your question, but how do you manage to get more
than one tab on the exported Excel file?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mark" <markbtmn@.gmail.com> wrote in message
news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...
>I have created an adhoc report in VS which I then export to Excel. The
> way the report is set up is that I have a seperate tab(worksheet) for
> each company that features on the report. However, I would like the
> company name on each tab instead of the generic sheet1, sheet2 etc..
> Any ideas welcome!|||On Feb 8, 7:28=A0am, "Alain Quesnel" <alainsanss...@.logiquel.com> wrote:
> I don't have an answer to your question, but how do you manage to get more=
> than one tab on the exported Excel file?
> --
> Thank you,
> Alain Quesnel
> alainsanss...@.logiquel.com
> www.logiquel.com
> "Mark" <markb...@.gmail.com> wrote in message
> news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...
>
> >I have created an adhoc report in VS which I then export to Excel. The
> > way the report is set up is that I have a seperate tab(worksheet) for
> > each company that features on the report. However, I would like the
> > company name on each tab instead of the generic sheet1, sheet2 etc..
> > Any ideas welcome!- Hide quoted text -
> - Show quoted text -
Mark,
Out of the box, RS does not have the functionality to rename the
tabs. Folks in my company end up doing some hefty gymnastics
with .NET and/or macros. This link claims to offer a solution but
I've not been able to get it to work. Let us know if you have better
results than me.
http://www.spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS=
-Excel-QnD-XSLT.aspx
Alain,
To get separate tabs by group, check the Page break at end check box
on the edit group dialog.
HTH you both|||On Feb 8, 6:14=A0pm, toolman <t...@.infocision.com> wrote:
> On Feb 8, 7:28=A0am, "Alain Quesnel" <alainsanss...@.logiquel.com> wrote:
>
>
> > I don't have an answer to your question, but how do you manage to get mo=re
> > than one tab on the exported Excel file?
> > --
> > Thank you,
> > Alain Quesnel
> > alainsanss...@.logiquel.com
> >www.logiquel.com
> > "Mark" <markb...@.gmail.com> wrote in message
> >news:2e8aa1cb-4970-43e9-852b-ac9a18551741@.v67g2000hse.googlegroups.com...=
> > >I have created an adhoc report in VS which I then export to Excel. The
> > > way the report is set up is that I have a seperate tab(worksheet) for
> > > each company that features on the report. However, I would like the
> > > company name on each tab instead of the generic sheet1, sheet2 etc..
> > > Any ideas welcome!- Hide quoted text -
> > - Show quoted text -
> Mark,
> Out of the box, RS does not have the functionality to rename the
> tabs. =A0Folks in my company end up doing some hefty gymnastics
> with .NET and/or macros. =A0This link claims to offer a solution but
> I've not been able to get it to work. =A0Let us know if you have better
> results than me.http://www.spacefold.com/lisa/post/Changing-the-Sheet-name=
s-in-SQL-Se...
> Alain,
> To get separate tabs by group, check the Page break at end check box
> on the edit group dialog.
> HTH you both- Hide quoted text -
> - Show quoted text -
Hey toolman,
Apologies in the slow response to your posting. Gave the link a try
but after spending some time on it I still couldn't get it to work.
Seems very long winded just to get some tab names working. We use
another BI reporting tool called Microstrategy which makes it simple
to do this as all you have to do is create a template with named tabs
and link it to your report - simple! Microsoft should take a leaf out
of these guys book! Doesn't help me but maybe i'll find something else
on the net.
Cheers