Alternate color on groups.

How would you alternate the color in a grid based on a grouping.

IE. (the 1000 Users should have background color of White and 2000 background would be WhiteSmoke)

ColumnUser ColumnNote

1000 Note1

1000 Note2

2000 Note1

2000 Note2

Try using the following expression for the BackgroundColor property.

=iif(RunningValue(Fields!ColumnUser.Value,CountDistinct,Nothing) Mod 2, "White", "WhiteSmoke")

|||nice...thanks!

Alternate Bar Chart Colours

Hi all,
I am using a simple bar chart based on 1 set of data points. Currently each
bar is the same colour, I would like all the bars to be different colours
and the legend to reflect this. Is this possible? If so, any tips would be
appreciated.
Kind Regards
TazI think this is what you want:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
Steve MunLeeuw
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:%23ipW8uQ0GHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> I am using a simple bar chart based on 1 set of data points. Currently
> each bar is the same colour, I would like all the bars to be different
> colours and the legend to reflect this. Is this possible? If so, any tips
> would be appreciated.
> Kind Regards
> Taz
>|||Exactly what I was looking for.
Thank you very much!
Taz
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:%233RNSzT0GHA.4044@.TK2MSFTNGP04.phx.gbl...
>I think this is what you want:
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> Steve MunLeeuw
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:%23ipW8uQ0GHA.4580@.TK2MSFTNGP05.phx.gbl...
>> Hi all,
>> I am using a simple bar chart based on 1 set of data points. Currently
>> each bar is the same colour, I would like all the bars to be different
>> colours and the legend to reflect this. Is this possible? If so, any tips
>> would be appreciated.
>> Kind Regards
>> Taz
>sql

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.
> Thanks,
> Andre
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that tabl
e
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>
Don't use SELECT * in views. If you want an alias for a table then use a
synonym instead.
sp_refreshview updates the view metadata but if you avoid SELECT * then
you'll never need to use it!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

ALTER TABLE statement does not change view based on table

Hi,
Whenever I add a column to a Table like this
ALTER TABLE xxx {Add Column}
it never adds that column to the view that I have created based on that table
The view T-SQL for the view goes like this
SELECT * FROM xxx
Since I want to return all fields it never includes the newly created
columns that I created using ALTER Table.
When I drop the view and recreate this, it the view works fine.
How can I fix this problem?
Thanks,
Andre
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
You really can't.
Besides, I'm not sure why you're doing what you you're doing.
Firstly, Select * from is bad technique.
Secondly, why are you using this in a view. Might as well simply call the
base table.

> Thanks,
> Andre
>
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Andre
As Greg said, using SELECT * is not good practice, and that is especially
true for views, for just this reason.
That being said, you can take a look at sp_refreshview.
In the future, always let us know what version you are running.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Spongebob76" <andre.beier@.community.nospam> wrote in message
news:BF37273C-661D-4071-A0F8-EB330F444A75@.microsoft.com...
> Hi,
> Whenever I add a column to a Table like this
> ALTER TABLE xxx {Add Column}
> it never adds that column to the view that I have created based on that
> table
> The view T-SQL for the view goes like this
> SELECT * FROM xxx
> Since I want to return all fields it never includes the newly created
> columns that I created using ALTER Table.
> When I drop the view and recreate this, it the view works fine.
> How can I fix this problem?
> Thanks,
> Andre
>
>

'Allow null values' causes report to run automatically

Hi
I have a long running report. The report is based on a SQL Server 2005
stored procedure, with a single Integer parameter. I want to allow the
user to choose NULL for the parameter, but I don't want the report to
run without the user clicking 'View Report'. But I am finding (from
Report Parameters) when I choose 'Allow null values' there is no
default value of 'None'. I don't want to specify a default value,
because this causes the report to execute immediately.
Has anybody come across this?
cheers,
TJWhat you need, and what would be nice, is a default option of None
(not Null) which doesn't allow the report to run until the user
selects a value. Unfortunatley, the only default options you have
are: Non-queried, From query and Null.
The simplest solution is probably to set a Non-queried default value
that is invalid in your report. This will till cause your report to
run but will return an empty result set. If it's a very simple report
you could then set the visibility of your elements to not show, or
possibly show "Please select a value", if this default invalid value
is given. Otherwise you'll get an empty looking report or, worse yet,
an error; either result would probably be confusing to the user.
Matt Penner
On Jan 2, 7:28 pm, TJ <newsgroups...@.gmail.com> wrote:
> Hi
> I have a long running report. The report is based on a SQL Server 2005
> stored procedure, with a single Integer parameter. I want to allow the
> user to choose NULL for the parameter, but I don't want the report to
> run without the user clicking 'View Report'. But I am finding (from
> Report Parameters) when I choose 'Allow null values' there is no
> default value of 'None'. I don't want to specify a default value,
> because this causes the report to execute immediately.
> Has anybody come across this?
> cheers,
> TJ

Allocate by assets

I need to write a stored procedure in which I allocate based on the assets
of an account.
I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amount
is the amount to be allocated. @.AccountType determines among which accounts
the amount will be allocated. The amount allocated to each account will be
determined by the "assets" of the account.
I need to account for rounding. The amount allocated should always be to 2
decimal places. If there is an unallocated "remainder" it should be
allocated among the accounts at random. It's critically important that the
amount to be allocated matches the amount allocated.
See DDL below:
If I was allocating 10.00 to all Accounts of AccountType 'A' and the assets
of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 would
be allocated 9.00 and account 2 would get 1.00.
Logically, add the assets of all AccountTypes 'A' (100.00) and determine
each accounts percentage of the total. Account 1 has 90% of total and
Account 2 has 10% of total, then allocate based on these percentages.
Sample data and expected results for AccountType 'B'; Amount to be
allocated: 100.00 AccountID, AccountType,Assets,Expected Result
3,'B',33.35,17.33
4,'B',85.01,44.19
5,'B',74.02,38.48
Thanks to anyone who might help.
DROP TABLE Accounts
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL,
[Assets] [decimal](18, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(1,'A',NULL,90)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(2,'A',NULL,10)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(3,'B',NULL,33.35)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(4,'B',NULL,85.01)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(5,'B',NULL,74.02)here's one way, that i've used in the past:
create procedure AllocateValue
@.AccountType char(1),
@.Amount decimal(18,2)
as
set nocount on
-- Allocate the dollar amount based on asset pct
update Accounts
set AccountValue = @.Amount * (Assets/AccountTotal.AccountTypeTotal)
from Accounts
join (
select AccountType, sum(Assets) as AccountTypeTotal
from Accounts
where AccountType = @.AccountType
group by AccountType
) AccountTotal
on AccountTotal.AccountType = Accounts.AccountType
where Accounts.AccountType = @.AccountType
-- Adjust for rounding
-- adjust highest asset, highest account ID [if max asset matches]
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select Max(AccountID)
from Accounts
where AccountType = @.AccountType
and Assets = (select max(Assets)
from Accounts
where AccountType = @.AccountType
)
)
Terri wrote:
> I need to write a stored procedure in which I allocate based on the assets
> of an account.
> I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amou
nt
> is the amount to be allocated. @.AccountType determines among which account
s
> the amount will be allocated. The amount allocated to each account will be
> determined by the "assets" of the account.
> I need to account for rounding. The amount allocated should always be to
2
> decimal places. If there is an unallocated "remainder" it should be
> allocated among the accounts at random. It's critically important that the
> amount to be allocated matches the amount allocated.
> See DDL below:
> If I was allocating 10.00 to all Accounts of AccountType 'A' and the asset
s
> of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 wou
ld
> be allocated 9.00 and account 2 would get 1.00.
>
> Logically, add the assets of all AccountTypes 'A' (100.00) and determine
> each accounts percentage of the total. Account 1 has 90% of total and
> Account 2 has 10% of total, then allocate based on these percentages.
>
> Sample data and expected results for AccountType 'B'; Amount to be
> allocated: 100.00 AccountID, AccountType,Assets,Expected Result
>
> 3,'B',33.35,17.33
> 4,'B',85.01,44.19
> 5,'B',74.02,38.48
>
> Thanks to anyone who might help.
>
> DROP TABLE Accounts
>
> CREATE TABLE [dbo].[Accounts] (
> [AccountID] [int] NOT NULL ,
> [AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccountValue] [decimal](18, 2) NULL,
> [Assets] [decimal](18, 2) NULL,
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (1,'A',NULL,90)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (2,'A',NULL,10)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (3,'B',NULL,33.35)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (4,'B',NULL,85.01)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (5,'B',NULL,74.02)
>
>|||Trey Walpole (treypole@.newsgroups.nospam) writes:
> -- Adjust for rounding
> -- adjust highest asset, highest account ID [if max asset matches]
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select Max(AccountID)
> from Accounts
> where AccountType = @.AccountType
> and Assets = (select max(Assets)
> from Accounts
> where AccountType = @.AccountType
> )
> )
Since Terri said that the rounding should be allocated to an account chosen
at random, here is a variation that does this:
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select TOP 1 AccountID
from Accounts
where AccountType = @.AccountType
ORDER BY newid()
)
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|||ah yes - missed that random bit
Erland Sommarskog wrote:
> Trey Walpole (treypole@.newsgroups.nospam) writes:
>
>
> Since Terri said that the rounding should be allocated to an account chose
n
> at random, here is a variation that does this:
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select TOP 1 AccountID
> from Accounts
> where AccountType = @.AccountType
> ORDER BY newid()
> )
>