Conditionally counting detail field

Hello. I'm trying to conditionally count a field, for example:
iif(Fields!GrantCodeID.Value = 70, 1, 0)
GrantCode is a field and whenever it equals 70, I want to count it so
I can display the count after the detail.
Any help is much appreciated.
Thanks!=Sum(iif(Fields!GrantCodeID.Value = 70, 1, 0))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael" <mike1174@.comcast.net> wrote in message
news:dfd40822.0408181042.401a6798@.posting.google.com...
> Hello. I'm trying to conditionally count a field, for example:
> iif(Fields!GrantCodeID.Value = 70, 1, 0)
> GrantCode is a field and whenever it equals 70, I want to count it so
> I can display the count after the detail.
> Any help is much appreciated.
> Thanks!

Conditionally adding a column to my custom component

Hi,

I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.

Depending on its setting, I want to include (or not include) a column in the output.

Any advice on how to go about doing this (with some sample code) would be much appreciated!

Here's how I'm declaring the property in ProvideComponentProperties()

IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New(); IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; IncludeErrorDesc.Name = "Some Name"; IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName; IncludeErrorDesc.Value = Convert.ToBoolean(false);

Thanks in advance

-Jamie

Implement SetComponentProperty method in your component and if the property is set to true add your column, otherwise find it in the collection and remove it.

I do not have a time to build you a sample, but give it a try and let us know if it does not go well.

BTW, you do not need the following line from your sample:

IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;

Thanks.

|||Hi Bob,
I nevre replied to this. Just wanted to say thanks for this - it worked a treat!
-Jamie|||

You are welcome, Jamie. I am glad it worked out.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a store
d
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional where clause, depending on parameter

I am looking for a way to create a query that, depending on the value of a
parameter, builds the correct where-clause. This should be usable in a stored
procedure.
Example:
parameter @.ShowArchived
select x, y, z from table_zyx WHERE ...
if @.ShowArchived > 0 --> WHERE archive=1
else --> WHERE archive=0 OR archive is null
All help is more than welcome!Hmm perhaps something like this:
WHERE isnull(archive,0) = case when @.ShowArchived > 0 then 1 else 0 end
it isn't optimal but you can change it if it works for you.
MC
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Vicky" <Vicky@.discussions.microsoft.com> wrote in message
news:F249666A-1E70-4C13-B48E-ED50B064771C@.microsoft.com...
>I am looking for a way to create a query that, depending on the value of a
> parameter, builds the correct where-clause. This should be usable in a
> stored
> procedure.
> Example:
> parameter @.ShowArchived
> select x, y, z from table_zyx WHERE ...
> if @.ShowArchived > 0 --> WHERE archive=1
> else --> WHERE archive=0 OR archive is null
> All help is more than welcome!|||you can use dynamic sql.
potentially a "simpler" to understand solution, and sometimes faster to
run is to have different select statements separted by if statements
stuffed into a stored procedure.

Conditional WHERE clause

Hi,

[SQL 2005 Express]

I would like a DropDownList to be populated differently depending on the selected value in a FormView.

If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.

Here is the SQL for case 1:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID).

Here's the SQL for case 2:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID) AND
(TypeID = 3).

Here's my best (failed) attempt to get what I want:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
IF @.CompanyID = 2 THEN
BEGIN
(CompanyID = @.CompanyID)
END
ELSE
BEGIN
(CompanyID = @.CompanyID) AND
(TypeID = 3)
END

I've also tried:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE @.CompanyID
WHEN 2 THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND
(TypeID = 3)
END

and

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE WHEN (@.CompanyID = 2) THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND (TypeID = 3)
END

I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.

Thanks very much.

Regards

Gary

Gary,

I think you are trying to construct a select based on the values selected in some control in your web form. (correct me if i am wrong) while going through your issue, i think the following solution should work -

Generate a dynamic where clause -

declare @.SQLSelect varchar(4000),

@.SQLWhere varchar(2000)

set @.SQLSelect = 'SELECT
AdviserID,
AdviserName
FROM
Advisers '

If @.CompanyID = 2

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID

End

Else

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID + ' and TypeID = 3'
End

set @.SQLSelect = @.SQLSelect + @.SQLWhere

Execute @.SQLSelect

I think this will help you to think further, if didn't solve your problem.

Ash

|||

Thanks, Ash.

I suspect that I'm too much of a novice to know what to do with what you've posted.

I've tried executing it as a Query and as a Stored Procedure - with no success. I ended up simplifying it to the same WHERE clause, with no better results:

Here is the simplified query:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000)
SET @.SQLSelect = 'SELECT AdviserID, AdviserName FROM Advisers '
IF @.AdviserCompanyID = 2
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
ELSE
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
SET @.SQLSelect = @.SQLSelect + @.SQLWhere
EXECUTE @.SQLSelect

This generated: "Must Declare the scalar variable @.AdviserCompany"

So, I changed the first line to:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000), @.AdviserCompanyID int

This generated:"Incorrect syntax near '@.SQLWhere'"

No joy after much fiddling. I then tried to create a Stored Procedure - with similar success levels.

What am I not getting?

Thanks very much for your help.

regards

Gary

|||

Gary:

You are not the only one. It took me hours to come to this solution.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT [AdviserID], [AdviserName] FROM [Adviser]

WHERE CompanyID = CASE @.CompanyID WHEN 2 THEN 2 ELSE @.CompanyID END AND

TypeID = CASE @.CompanyID WHEN 2 THEN TypeID else 3 END"

><SelectParameters><asp:ControlParameterControlID="FormView1$companyIDtxtbox"Name="CompanyID"Type="Int32"/></SelectParameters></asp:SqlDataSource>|||

Excellent, Limno - thanks very much! Works a charm...

(Still keen to find out if I was doing something stupid to prevent the Previous idea from working...Anyone?)

Regards

Gary

|||

Hi Guys,

I would like to apply Limnon's solution to the WHERE clause in the following select statement:

SELECT
FIInvestments.InvestmentID,
Accounts.AccountName + ' - ' + CAST(Accounts.AccountNumber AS varchar(20)) + CONVERT
varchar, FIInvestments.InvestmentDate, 3) + ': ' + ' ($' + LEFT (CAST(FIInvestments.Amount AS varchar),
LEN(CAST(FIInvestments.Amount AS varchar)) - 3) + ' for ' + CAST(FIInvestments.Term AS varchar(3)) + '
months)' AS Investment
FROM
FIInvestments INNER JOIN
Accounts ON FIInvestments.AccountID = Accounts.AccountID
WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

(In other words: I want all the Investments with the selected FundID, unless commission has already been paid on them, as evidenced by payment records in the FICommPayments table. This is so I can allocate commissionpayments to them).

However, there is one Fund/FundID (FundID = 141) which pays commission in dribs and drabs, so if that Fund is selected, I wantall the investments within that Fund - not just the ones that haven't had commission paid against them yet.

So, here are my two WHERE statements:

WHERE (FIIinvestments.FundID = @.FundID)

and

WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

And here's my best attempt at a conditional WHERE statement so far:

WHERE CASE @.FundID WHEN 141 THEN (FIInvestments.FundID = @.FundID) ELSE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS (SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes FROM FICommPayments WHERE (InvestmentID = FIInvestments.InvestmentID))) END

The error messages this generates are:

Incorrect syntax near '='.
Incorrect syntax near ')'.

I also tried, with little hope:

WHERE (FIInvestments.FundID = @.FundID) CASE @.FundID WHEN NOT 141 THEN (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))END

This generated:

Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near ')'

Perhaps I should revert to Ash's solution - but I got stuck on that one, too!

Thanks for the help.

Regards

Gary

|||

Hello:

I don't know why your approaches did not work. You may post that question again if you want an answer. Instead, you can split your condition in two separate parts, then use UNION OR UNION ALL (with possible duplicates) to combine the results.

I used a simplified version of your tables to test the following script, it works as to my understanding. But you may need to tweak it for your real case.

SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments WHERE FundID<>141 AND FundID=@.FundID AND FIInvestments.FundID NOT IN (SELECT FICommPayments.FUNDID
FROM FICommPayments INNER JOIN FIInvestments ON FICommPayments.InvestmentID = FIInvestments.InvestmentID)
UNION ALL
SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments
WHERE FundID=141 ANDFundID=@.FundID

|||

Excellent, Limon!

I started off thinking that your suggestion wasn't exactly what I'm after in this case (because I need one list for case 141 and the other for every other case, so the UNION ALL didn't seem like what I was after until it dawned on me what you're doing - very sneaky!).

I've been looking for UNION / UNION ALL for other reasons, so I get a double hit out of this one. Thanks very much - you're making my day on a number of fronts at the moment (including on the other thread)!

If this keeps up much longer, I'll have to put you on a retainer. In fact, I think that the guru's amongst you should work out an easy way of allowing novices like me to secure a commercial agreement/service with you guys in addition to this freebie one. I know that it exposes the community to abuse, but I'm sure there must be a way of doing it... I'll keep thinking and ewxperiencing and come up with something over the next month or two.

Regards

Gary

Conditional Visability in a table

Hi,
I'm new to RS and want to know how to write an expression which will set
visablility of a table group header to false if group1.value = "Account".
Any ideas?
Thanks
Jonthe basics of using an expression to hide a text box is this:
Type an expression that evaluates to a Boolean: True to hide the item and
False to show the item. Click the expression (fx) button to edit the
expression.
Remember, true = hidden, false = show
I don't know if that will work for your table group header as well, but
thats how it works with text boxes.
Karl
"jonwolds" wrote:
> Hi,
> I'm new to RS and want to know how to write an expression which will set
> visablility of a table group header to false if group1.value = "Account".
> Any ideas?
> Thanks
> Jon

conditional update within value

I have a column of data in SQL Server 2000 that I need to replace
values within it with new values. I know how to use CASE statements to
do conditional updates but not how to do this. Here is an example, not
the real example as the values relevant to my company would mean little
to anyone.
If value contains "name", replace it with "fullname"
If value contains "address", replace it with "fulladdress"
and so on...
What I want to do in the field is the following:
Field value now: abc##name##123
Field after change: abc##fullname###123
Field value now: asdlfkjlsdkafjnameasldfjk123
Field after change: asdlfkjlsdkafjfullnameasldfjk123
Field value now: adlsfkjaddresslksdfj34
Field after change: adlsfkjfulladdresslksdfj34
And update all rows in the approriate column with the above logic.
Any ideas?
Thanks.
JRYou don't need a Case statement to do this, you can use
Update #t Set foo = Replace (Replace (foo, 'address', 'fulladdress'),
'name', 'fullname')
Where foo Like '%name%' Or foo Like '%address%'
You could also do it with a Case statement like
Update #t Set foo = Case
When foo Like '%name%' Then Replace (foo, 'name', 'fullname')
When foo Like '%address%' Then Replace (foo, 'address', 'fulladdress')
Else foo
End
Where foo Like '%name%' Or foo Like '%address%'
Please note, however, that depending on your data, those two statements may
do different things. If a row has both "name" and "address" in that column,
the first update statement will change both name and address, but the Case
statement version will update only name to fullname, but won't change
address in that row.
Tom
"JR" <jriker1@.yahoo.com> wrote in message
news:1142706489.831624.92670@.j33g2000cwa.googlegroups.com...
>I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>|||You might want to have a look at STUFF as well, although REPLACE may well do
the trick.
The thing about CASE expressions is that they are 'falling rock' ie for the
first WHEN condition it finds to be true, it will return the THEN bit and
exit the statement. So if your string has multiple bits that need to
replacing, you'll need to run the UPDATE multiple times.
Hope that helps.
Damien
"JR" wrote:

> I have a column of data in SQL Server 2000 that I need to replace
> values within it with new values. I know how to use CASE statements to
> do conditional updates but not how to do this. Here is an example, not
> the real example as the values relevant to my company would mean little
> to anyone.
> If value contains "name", replace it with "fullname"
> If value contains "address", replace it with "fulladdress"
> and so on...
> What I want to do in the field is the following:
> Field value now: abc##name##123
> Field after change: abc##fullname###123
> Field value now: asdlfkjlsdkafjnameasldfjk123
> Field after change: asdlfkjlsdkafjfullnameasldfjk123
> Field value now: adlsfkjaddresslksdfj34
> Field after change: adlsfkjfulladdresslksdfj34
> And update all rows in the approriate column with the above logic.
> Any ideas?
> Thanks.
> JR
>

Conditional totals for matrix report

I have a matrix report that has the value of one (1) in the row field when my
row header has that value.
agent air hotel cruise
-- -- -- --
bob 1 1
jim 1
jane 1 1
What I want is a total at the bottom of the report counting the numbe of
ones in the column. I am very new to SSRS as a whole, so please help!!
Thanks in advance!Carl,
Right click on the data row cell and click on "subtotals". Thats it you
have column totals.
--Venkat
Carl Henthorn wrote:
> I have a matrix report that has the value of one (1) in the row field when my
> row header has that value.
> agent air hotel cruise
> -- -- -- --
> bob 1 1
> jim 1
> jane 1 1
> What I want is a total at the bottom of the report counting the numbe of
> ones in the column. I am very new to SSRS as a whole, so please help!!
> Thanks in advance!|||Thank you for responding. I have tried the right click method, but the cell
that I need the subtotals on does not have "Subtotal" on the menu. Is there
some other way?
Thanks!
"venkat.oar@.gmail.com" wrote:
> Carl,
> Right click on the data row cell and click on "subtotals". Thats it you
> have column totals.
> --Venkat
> Carl Henthorn wrote:
> > I have a matrix report that has the value of one (1) in the row field when my
> > row header has that value.
> >
> > agent air hotel cruise
> > -- -- -- --
> > bob 1 1
> > jim 1
> > jane 1 1
> >
> > What I want is a total at the bottom of the report counting the numbe of
> > ones in the column. I am very new to SSRS as a whole, so please help!!
> > Thanks in advance!
>|||If possible pls send me the rdl file.. i will work on it and send it to
u back..
Carl Henthorn wrote:
> Thank you for responding. I have tried the right click method, but the cell
> that I need the subtotals on does not have "Subtotal" on the menu. Is there
> some other way?
> Thanks!
>
> "venkat.oar@.gmail.com" wrote:
> > Carl,
> >
> > Right click on the data row cell and click on "subtotals". Thats it you
> > have column totals.
> >
> > --Venkat
> > Carl Henthorn wrote:
> > > I have a matrix report that has the value of one (1) in the row field when my
> > > row header has that value.
> > >
> > > agent air hotel cruise
> > > -- -- -- --
> > > bob 1 1
> > > jim 1
> > > jane 1 1
> > >
> > > What I want is a total at the bottom of the report counting the numbe of
> > > ones in the column. I am very new to SSRS as a whole, so please help!!
> > > Thanks in advance!
> >
> >

Conditional Sum Statement

Hey all,
Quick question, I have a field that I need to sum only when another
field is a certan value.
For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
want to sum the sales_amt field when the first character of the
sales_code field is an 'X'. So for the set:
1, 1, 999, X100, $200.00
2, 1, 999, 200, $500.00
3, 1, 999, 898, $1000.00
4, 1, 555, X340, $2000.00
5, 1, 555, X444, $23.00
The resultant sums would be:
for dealer_id 1: 2223.00
for rep_id 999: 200.00
for rep_id 555: 2023.00
I am trying to do this in MRS as opposed to making it an additional
field in my querry.
Thanks!I think this is what you are asking...
And There is probably an easier way to do this but in the expression builder
IIF(SUBSTR(sales_code),1,1) = "x", Sum(sales_amt ),"")
Or something along those lines...
Hope that helps
Kerrie
Jimmy V wrote:
>Hey all,
>Quick question, I have a field that I need to sum only when another
>field is a certan value.
>For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
>sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
>want to sum the sales_amt field when the first character of the
>sales_code field is an 'X'. So for the set:
>1, 1, 999, X100, $200.00
>2, 1, 999, 200, $500.00
>3, 1, 999, 898, $1000.00
>4, 1, 555, X340, $2000.00
>5, 1, 555, X444, $23.00
>The resultant sums would be:
>for dealer_id 1: 2223.00
>for rep_id 999: 200.00
>for rep_id 555: 2023.00
>I am trying to do this in MRS as opposed to making it an additional
>field in my querry.
>Thanks!
--
Message posted via http://www.sqlmonster.com|||Kerrie,
I had to create a calculated field and summed it that way, i did use
your code snippit to generate my calculated field.
Thanks!!!|||Glad I could help, That is the best thing i have heard all day.
Thanks!
Jimmy V wrote:
>Kerrie,
>I had to create a calculated field and summed it that way, i did use
>your code snippit to generate my calculated field.
>Thanks!!!
--
Message posted via http://www.sqlmonster.com|||Hi Jimmy,
Easy way of doing is, if u want a sum by Sales order =x...., AND REP_ID
create a group with the sales order =x JUST "X" and u will get the value for
it
and then subtract this one with rest of value.
regards
JERROB
"Jimmy V" wrote:
> Hey all,
> Quick question, I have a field that I need to sum only when another
> field is a certan value.
> For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
> sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
> want to sum the sales_amt field when the first character of the
> sales_code field is an 'X'. So for the set:
> 1, 1, 999, X100, $200.00
> 2, 1, 999, 200, $500.00
> 3, 1, 999, 898, $1000.00
> 4, 1, 555, X340, $2000.00
> 5, 1, 555, X444, $23.00
> The resultant sums would be:
> for dealer_id 1: 2223.00
> for rep_id 999: 200.00
> for rep_id 555: 2023.00
> I am trying to do this in MRS as opposed to making it an additional
> field in my querry.
> Thanks!
>

Conditional sum in reporting services

I need to find a conditional sum of sales if fields!Example.value = 0
(And same if fields!Example.value = 1)
How do I do that in reporting services?
Thanks
CS=Sum(IIf(Fields!Example.value=0,0,Fields!Example.Value))
"chetanasamal@.gmail.com" wrote:
> I need to find a conditional sum of sales if fields!Example.value = 0
> (And same if fields!Example.value = 1)
> How do I do that in reporting services?
> Thanks
> CS
>

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional Split - Compare DATETIME with constant

Hi,

I have to compare a DATETIME Field with '1/1/1900 12:00:00 AM". Which is default DATE TIME Value in SQL Server.

I did compare like

TRADEAGREEMENTFROMDATE != (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM"))

but (DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) returns "12/31/1800 12:00:00:AM"

Thanks,

Aravind

So the value I got was slightly different.

(DT_DBTIMESTAMP)(DATEPART("mm",(DT_DBTIMESTAMP)"1/1/1900 12:00:00 AM")) = 31/12/1899 00:00:00

Why are you using DATEPART? Do you not just want -

(DT_DBTIMESTAMP)"1/1/1900" = 01/01/1900 00:00:00

That is the same as '1/1/1900 12:00:00 AM' which you asked for above, infact the time format is just my local settings UK rather than US, the values are exactly the same.

Conditional Split - Assign to value

Hello,

When you′re comparing values in the Condition of the Conditional split, can you assign a value to a variable?

If so, how can you accomplish this?

Thank you.

Assign what value?

No, you cannot. Not without using a script component.

Conditional running total question

Hi there,

I have a problem with trying to get a total of a conditional value. My data output looks as follows:

HEAD OFFICE BRANCH (group1)
(Group 2 below)
Ford Mustang 2001 Blue Excellent
Toyota Raider 2005 Red Good
BMW 5.30 i 2006 Blue Excellent
Mazda MX5 2003 Yellow Good

WESTERN CAPE BRANCH
Ford Fiesta 16i 2002 Blue Good
Renault Clio 2.0d 2005 Red Poor
Nissan Hardbody 2001 Pink Good

I have been trying to find a way to get a value which would be a total number of blue cars for the Branches (group1) as well as the total number of blue cars for the whole report but to no avail with my limited experience. I have read up on people creating extra columns that does counts and sums etc but I am still struggling with aggregate inside aggreagate errors.

Please help me
Mike

Hi,

what about doing a plain SUM(IIF(Fields!autom.Value ="Red",1,0)) etc. in the group footer ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Hi Jens,

Thanks for the reply. I should have maybe pointed out that I have used a list to do the grouping and can't seem to find a group footer. Is there another thing that I can try?

Sorry if this is a bit of a newbie question.
Mike

Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional page breaks

I want to set a page break at the change of a group conditionally based on a
parameter value. Is there any way to do this in Reporting Services?Any help here? Because I would *really* like to figure out how to do this.
Once again, I have a grouped table in a report layout. I would like to have
a page break at the end of a grouping happen conditionally based on a boolean
parameter that I pass to the report.