How to make sure background color on details group always start on the same color?

Is it possible to control start color when doing alternating background
colors on a details group in a table?
If alternating between White and Green, I want the first detail row to be
white, and the next green, regardless of row number.
I've tried using both this expression:
=iif(RowNumber(Nothing) Mod 2, "White", "WhiteSmoke")
and this:
= iif(RunningValue( Fields!MyField.Value, CountDistinct, Nothing) mod 2,
"White", "WhiteSmoke")
They both work, but they both seem to count row numbers, regardless of
grouping. If I try using the detail group name instead of nothing, I don't
get any color.
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
"White", "WhiteSmoke")
Is it possible to control this? Any advise appreciated!
Kaisa M. Lindahl LervikFigured it out, silly misunderstanding from my side.
I needed to specify the group one level higher than the details group. So
when I did
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_Group1") mod
2, "White", "WhiteSmoke")
instead of
= iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
"White", "WhiteSmoke")
it worked very well.
Kaisa M. Lindahl Lervik
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%238BagFzUGHA.224@.TK2MSFTNGP10.phx.gbl...
> Is it possible to control start color when doing alternating background
> colors on a details group in a table?
> If alternating between White and Green, I want the first detail row to be
> white, and the next green, regardless of row number.
> I've tried using both this expression:
> =iif(RowNumber(Nothing) Mod 2, "White", "WhiteSmoke")
> and this:
> = iif(RunningValue( Fields!MyField.Value, CountDistinct, Nothing) mod 2,
> "White", "WhiteSmoke")
> They both work, but they both seem to count row numbers, regardless of
> grouping. If I try using the detail group name instead of nothing, I don't
> get any color.
> = iif(RunningValue( Fields!MyField.Value, CountDistinct, "tbl_DG") mod 2,
> "White", "WhiteSmoke")
> Is it possible to control this? Any advise appreciated!
> Kaisa M. Lindahl Lervik
>

How to make Sub total will be first the first record in next page

I have a report with 1 group per page.

I need Sub Total from Group footer in page 1 will be Subtotal in Group Header in Page 2 (or viewing in Group Header in Page 2), Sub Total from Group footer in page 2 will be Subtotal in Group Header in Page 3, and so on...

How to implement it...Plase help me, urgent.

Thanks Before,

AgusHave you tried to use shared variables?One way to solve the problem is to calculate running total in group footer and assign the value to shared variable.Then you could split group header where top section would display shared variable and bottom section would be used to reset it.|||Dear Denan,...

Thanks for your suggestion.
Actually i never used shared variabel. Would you like to help make an example formula code where is it using shared variabel.

Thanks before,

Agus|||Hi Agus
Well,as I said one formula should be placed in group header.Here you display your total and reset it.Formula would be something like this.
shared numbervar Total;//This is your shared variable
numbervar out;//temporary variable

out:=Total;//Assign total to temporary variable
Total:=0.0;//Reset Total
out//Display result

In group footer you calculate running total and assign that value to shared variable which will hold it until CR passes through group header next time

shared numbervar Total;//Shaerd variable must have same name as in header formula
Total:=#YourRunningTotalName

I hope this helps|||Thanks Denan,...

How to make correct join

Hi
I have to tables one called GROUPS and one called ACCOUNT

In table Group I have the follwing fields
Groupid, AccountFrom, AccountTo

In table Account I have
AccountNo, Name etc.

Records in Groups:
P1, 1001, 1002
P1, 1005, 1007
P1, 1010, 1010
P1, 1007, 1012

Now I want to have the corresponding AccountNo from ACCOUNT (from range
AccountFrom..AccountTo), that is the following result:
1001
1002
1005
1006
1007
1008
1009
1010
1011
1012

If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
result will be used in another view.
My problem is that I also get every other record from table ACCOUNT.

Do anyone out there have a solution on my problem ?
BR/JanPost your current SELECT statement.
Usually , you would use an INNER JOIN for your requiremnts , i.e An inner
join returns all rows that result in a match .

Are you currently using a LEFT JOIN?

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<jannoergaard@.hotmail.com> wrote in message
news:1144159750.922225.272910@.v46g2000cwv.googlegr oups.com...
> Hi
> I have to tables one called GROUPS and one called ACCOUNT
> In table Group I have the follwing fields
> Groupid, AccountFrom, AccountTo
> In table Account I have
> AccountNo, Name etc.
> Records in Groups:
> P1, 1001, 1002
> P1, 1005, 1007
> P1, 1010, 1010
> P1, 1007, 1012
> Now I want to have the corresponding AccountNo from ACCOUNT (from range
> AccountFrom..AccountTo), that is the following result:
> 1001
> 1002
> 1005
> 1006
> 1007
> 1008
> 1009
> 1010
> 1011
> 1012
> If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
> result will be used in another view.
> My problem is that I also get every other record from table ACCOUNT.
> Do anyone out there have a solution on my problem ?
> BR/Jan|||Hi there

As I recall the statement it's something like this

Select distinct dbo.Account.AccountNo as AccountNo
>From dbo.Account inner join dbo.Groups
On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
dbo.Groups.AccountTo >= dbo.Account.AccountNo

Hope you have a solution for me. I have tried different ways but don't
seme to get it right.
BR /Jan|||(jannoergaard@.hotmail.com) writes:
> As I recall the statement it's something like this
> Select distinct dbo.Account.AccountNo as AccountNo
>>From dbo.Account inner join dbo.Groups
> On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
> dbo.Groups.AccountTo >= dbo.Account.AccountNo
> Hope you have a solution for me. I have tried different ways but don't
> seme to get it right.

This is certainly better, since it relieves you from the DISTINCT:

SELECT a.AccountNp
FROM dbo.Account a
WHERE EXISTS (SELECT *
FROM dbo.Groups g
WHERE a.AccountNo BETWEEN g.AccountFrom AND g.AccountTo)

But whether that really addresses your issue, I cannot tell, because
your posting was very clear.

If this query does not work out, I suggest that you post the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result from the sample.

This permits anyone who wants to answer to copy and paste into
a query tool and develop a tested query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
Thanks very much. This worked out just as I wanted it to. Next time I
will have in mind what you suggested.
BR/Jan

How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote:

How to lock a particular Row in a sql server

Hi! Group,
this is shiva shanker,
i have a problem , while insert a record in a table , i want to lock
the particular row. so that no one can insert the record during that
period of time.
plz help me,
thanks in adv.
shiva shanker.insert by definition is adding new data into a table. Your insert should not
affect others from inserting into the same table if the table is not
exclusive locked. Take a look at 'tablock or tablockx' in book online.
-oj
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
> Hi! Group,
>
> this is shiva shanker,
> i have a problem , while insert a record in a table , i want to lock
> the particular row. so that no one can insert the record during that
> period of time.
>
> plz help me,
>
>
> thanks in adv.
> shiva shanker.
>|||Yes oj is right,
Shiva your requirement is not clear, is it osmething like when you insert no
other person should insert as you need a sequence of identity number specifi
c
to your insert. In that case go for table lock.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"oj" wrote:

> insert by definition is adding new data into a table. Your insert should n
ot
> affect others from inserting into the same table if the table is not
> exclusive locked. Take a look at 'tablock or tablockx' in book online.
>
> --
> -oj
>
> "shiva" <bany.shanker@.gmail.com> wrote in message
> news:1141793533.750309.243310@.i39g2000cwa.googlegroups.com...
>
>