I won't know where in the string "5679" will be, but...

If it is in that textbox (in the details row), I have to display a
drill down row underneith that record.
Any help is appreciated.
TrintWhat do you want to display there ? I detailed row exists for that row where
the '5679' exist you add a new group ti the table, for this etailed group
you can then handle the toggle to tell the report that it only has to toggle
or to be visible if the textbox.Value has a specific value.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"trint" <trinity.smith@.gmail.com> schrieb im Newsbeitrag
news:1113850634.959940.237640@.o13g2000cwo.googlegroups.com...
> If it is in that textbox (in the details row), I have to display a
> drill down row underneith that record.
> Any help is appreciated.
> Trint
>|||Ok,
This is what I don't understand. The hidden rows with downline dealers
only has '5678'. But the visible row that needs a drill down might
have in the dealerlineageid (which is a field not to be displayed)
'5678' and 2754 and 10 other dealer numbers (multiple dealer numbers).
How would I know if 5678 is in there as a value? If it has multiple
dealer numbers separated by commas? Wouldn't the expression for the
"hidden" value need to be a substring query? How would I do that?
Thanks,
Trint

I want to Display records by Page

Hi,
I am in the process to building a report using Reporting Services. The
report are invoices but I want the invoices to display one per page. I am
using a List and the report display invoice 1 , 2, 3 in the same page and I
want to diplay Invoice 1 in Page 1, Invoice 2 in Page 2 and so on... Please
advice!
Thanks,
Adolfo
--
DotNet GuerrillaAdolfo,
Try grouping by the invoice and then setting the group to have a page break
at the end of the group.
"DotNet Guerrilla" wrote:
> Hi,
> I am in the process to building a report using Reporting Services. The
> report are invoices but I want the invoices to display one per page. I am
> using a List and the report display invoice 1 , 2, 3 in the same page and I
> want to diplay Invoice 1 in Page 1, Invoice 2 in Page 2 and so on... Please
> advice!
> Thanks,
> Adolfo
> --
> DotNet Guerrilla|||Thank you David,
--
DotNet Guerrilla
"David Siebert" wrote:
> Adolfo,
> Try grouping by the invoice and then setting the group to have a page break
> at the end of the group.
> "DotNet Guerrilla" wrote:
> > Hi,
> > I am in the process to building a report using Reporting Services. The
> > report are invoices but I want the invoices to display one per page. I am
> > using a List and the report display invoice 1 , 2, 3 in the same page and I
> > want to diplay Invoice 1 in Page 1, Invoice 2 in Page 2 and so on... Please
> > advice!
> >
> > Thanks,
> > Adolfo
> > --
> > DotNet Guerrilla

I want to display only my numeric values in SQL

I have a query that it has a lot of filters to avoid letters or special characters, I was wondering If there is a way to filter the column with one statement to displays only the numbers

here is the sample code

select /*top 200*/

v.arpnumber, o.last, o.First, v.coOwnerFn, v.coOwnerLn, o.address1, o.address2, o.city, o.zip, o.state,o.province,o.country, o.datecreated, o.login, o.pwd

from ownersdb.dbo.AffinityRewardsMembers o ,dbo.contacts v

where system = 'lead'

and o.armembernum = v.arpnumber

and v.contacttype = 'owner'

and v.arpnumber is not null

and v.arpnumber <> ''

and v.arpnumber not between 'a%' and 'z%'

and v.arpnumber not between '%a%' and '%z%'

and v.arpnumber not like '% %'

and v.arpnumber not like '%pk%'

and v.arpnumber not like '%pb%'

and v.arpnumber not like '%z%'

and v.arpnumber not like '%w%'

and o.ownerid is not null

and v.officeid in ('37','32','30','31','20','22','23')

and o.ReportPrinted = 0

and o.login is not null

and o.pwd is not null

/*and city is not null

and city <> ''*/

order by v.ln

the column I'm trying is arpNumber

instead of using all these filter I just want to put

i.e

v.arpnumber is numeric

Any suggestions ?

Thanks in advance

One way you can do that is by writing a user defined function called isNumeric using .net CLR with regular expressions.

Then you can check the arpNumber if it is numeric or not in one word.

|||

"Isnumeric" also has issues. Give a look here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Despite this, a quick and dirty work-around that I sometimes use (that is NOT foolproof!) is to check

isNumeric (@.aStringToBeTested + 'D2') = 1

This eliminates some of the problems -- it eliminates the money "marker" and it eliminates many "float" datatype problems. However, please give the writeup a look. (and like Dale says, give the IsReallyNumeric function a look.)

|||

Try using the isReallyNumeric function found here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

(Thanks Arnie)

Add the function to your database then

replace your pattern tests with

Code Snippet

and dbo.isReallyNumeric(v.arpnumber)=1

|||

I guess I am a bit confused.

Do you want ot ONLY RETRIEVE rows where [v.arpnumber] contains only numbers,
OR,

Do you want to DISPLAY the [v.arpnumber] field, stripping out any non-numeric characters?

If it the first, then DaleJ's suggestion is good and should work for you, otherwise you will need a function similar to the following:

Code Snippet

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(500) )
RETURNS varchar(500)
AS
BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

-- Test
DECLARE @.TestParam varchar(50)
SET @.TestParam = 'ab3452SDF094c&h*¤,1r340.95849%eew#y/'
SELECT dbo.fnNumbersOnly( @.TestParam )

--
3452094134095849

Usage:

SELECT TOP 200
arpnumber = dbo.fnNumbersOnly( v.arpnumber ),
o.last,
etc.,

|||

as a matter of fact i'm trying to retrieve data that will update another store and these values most be numeric, I'll check that func that I read that seems to be a good idea.

sql

I WANT TO DISPLAY ALL THE MONTHS BETWEEN 2 DATES FROM TABLE

Hi
My requirement is to display names all the months between two dates
from the table.
I have lots of START and STOP dates available in my table and I need
to display names of all the months including Start and Stop months.
How can I do that, with function or with some loop.
My database is SQL Server 2000 .
ThanksSee the DATENAME() function and SELECT DISTINCT.
Edmund
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||Edmund,
Here is a way to display the month names between @.start and @.stop
for two variables. You should be able to adapt this to your needs.
It requires a table containing the integers from 0 on up.
declare @.start datetime set @.start = '20050323'
declare @.stop datetime set @.stop = '20070223'
select
datename(month,dateadd(month,datediff(mo
nth,0,@.start)+n,0)) as MonthName
from Numbers1000
where n <= datediff(month,@.start,@.stop)
order by n
Here's how to generate the table of 1000 integers (you probably don't
need more)
create table Numbers1000(
n int primary key
)
declare @.a int set @.a = 0
while @.a < 1000 begin
insert into Numbers1000 values (@.a)
set @.a = @.a + 1
end
Steve Kass
Drew University
Edmund wrote:

>See the DATENAME() function and SELECT DISTINCT.
>Edmund
><akpatelrs@.googlemail.com> wrote in message
>news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
>
>
>|||Try this
declare @.date1 datetime,@.date2 datetime,@.str varchar(1000)
set @.str=''
set @.date1 = dateadd(mm,-4,getdate()) /* set it as yr start date */
set @.date2 = dateadd(mm,10,getdate()) /* set it as yr end date */
while datediff(mm,@.date1,@.date2)>0
begin
-- display month
set @.str= @.str+datename(mm,@.date1)+', '
-- decrease date
set @.date1 = dateadd(mm,1,@.date1)
end
declare @.strlen int
set @.strlen = len(@.str)
if @.strlen>1
select left(@.str,len(@.str)-2) as month_name
else
select @.str
hth
Peter
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>|||akpatelrs@.googlemail.com wrote:
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
Here is data from my table. For instance, for first start n stop date,
i need to display JAN,FEB like wise for second MAR,APR,MAY . This way
for all Start and Stop date from the table.
StartDate
StopDate
2007-01-01 00:00:00.000 2007-02-17
00:00:00.000
2006-03-01 13:38:46.397 2006-05-01
13:38:46.397
2006-05-19 03:18:57.060 2006-05-19
03:18:57.060|||You have a few suggestions already. Let me put my weight behind Steve's use
of an auxiliary table of numbers. There are all kinds of uses for it, it
doesn't take up much space, and it's ideal for situations like that which
you've got into. Populate it once, and then forget it's there. Just make
sure it's large enough - there may be times when you want it to be larger
than 1000. It's easy to top-up of course, but you don't want to find that
you discover it's too small when a client's report comes out short.
Rob
<akpatelrs@.googlemail.com> wrote in message
news:1149518723.100427.10020@.f6g2000cwb.googlegroups.com...
> Hi
> My requirement is to display names all the months between two dates
> from the table.
> I have lots of START and STOP dates available in my table and I need
> to display names of all the months including Start and Stop months.
> How can I do that, with function or with some loop.
> My database is SQL Server 2000 .
> Thanks
>

I want my custom SQL errors to display on the RS Page

Dear Anyone,

We have custom raise error messages embedded in our stored procedures. I've noticed that using Business Intelligence Dev, when these errors are invoked, they actually display on the RS report. But unfortunately, when I tried it on a deployed report, I always get the error below.

Is there a way to display custom SQL errors from my stored proc to the RS page? Where do I enable the remote errors?

Thanks,

Joseph

An error has occurred during report processing.

Cannot create a connection to data source 'AdventureWorks'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors.

Regarding remote errors:

You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table. Alternatively, you can use the SetSystemProperties SOAP call:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_63le.asp

Here is an example for a .rss script that will turn it on:

Public Sub Main()
Dim P As New [Property]()
P.Name = "EnableRemoteErrors"
P.Value = True
Dim Properties(0) As [Property]
Properties(0) = P
Try
rs.SetSystemProperties(Properties)
Console.WriteLine("Remote errors enabled.")
Catch SE As SoapException
Console.WriteLine(SE.Detail.OuterXml)
End Try
End Sub

Then run the .rss script by using the rs.exe command line utility:

rs -i EnableRemoteErrors.rss -s http://Server/ReportServer

Once remote errors are enabled, you could throw a specific exeception with a informative error string in the stored procedure. That message should then be shown as inner message.

-- Robert

I need to display the name of an expanded group

Hi everyone,
I want to display the name of an expanded group within a seperate text
box. The problem is that it doesn't work because my seperate text box
is "not part of the group area" which the name/field belongs to. What
should I do?
How can I access report items of a group outside that group? I though
I could temporarily copy values from a field of a group into a 'global
parameter' (if it's possible, of course) and then make my text box
display the value by referencing that parameter. But since I don't
know how to do that either, I have run out of ideas.On Mar 14, 11:01 am, "mailfo...@.googlemail.com"
<mailfo...@.googlemail.com> wrote:
> Hi everyone,
> I want to display the name of an expanded group within a seperate text
> box. The problem is that it doesn't work because my seperate text box
> is "not part of the group area" which the name/field belongs to. What
> should I do?
> How can I access report items of a group outside that group? I though
> I could temporarily copy values from a field of a group into a 'global
> parameter' (if it's possible, of course) and then make my text box
> display the value by referencing that parameter. But since I don't
> know how to do that either, I have run out of ideas.
If I'm understanding you correctly, you could use something like the
following:
To access a parameter, use an expression like: =Parameters!
ParameterName.Value
To access a dataset outside of it's normal scope, use an expression
like: =Max(Fields!FieldName.Value, "DataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Its not possible to have a report item placed outside the group. It can be
placed only on header, footer or in the grouping. One option is that in your
grouping you can insert as many blank rows and then place the report items,
but that too static headers not the details of any rows. ie you can refer one
value and not multiple.
Amarnath,MCTS
"mailforpr@.googlemail.com" wrote:
> Hi everyone,
> I want to display the name of an expanded group within a seperate text
> box. The problem is that it doesn't work because my seperate text box
> is "not part of the group area" which the name/field belongs to. What
> should I do?
> How can I access report items of a group outside that group? I though
> I could temporarily copy values from a field of a group into a 'global
> parameter' (if it's possible, of course) and then make my text box
> display the value by referencing that parameter. But since I don't
> know how to do that either, I have run out of ideas.
>|||Thank you very much, guys.
Is there any other way to determine whether a group has been expanded
or not? I don't necessarily have to display a value of a field of a
specific group area in a seperate text box, but rather something like
"oh, you have just expanded group xyz, so I let you know that in that
text box you see over there"...?
The code might probably look like this:
textbox.Value:
=IIF(groupXYZ.Expanded,"Group XYZ is expanded","nothing or something
else is expanded")
Is that possible?