Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

Conditional format within matrix, depending on subtotal?

Hi there,

I'm having trouble inserting a conditional format to a specific column.

e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.

Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.

I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...

I tried to create a simple report from a cube with Month,Store,Turnover, Previous Year turnover and Delta %. Then I placed on the rows the months and the stores and the values in the colums (that should be the way that you did on the report, am I correct?) and I added the subtotal. Then in the background expression I wrote this:

"=iif(sum(Delta.values) > sum(Delta.values,"Dataset1"),"Green","White")"

Doing this I had the monthly delta background in green when it was higher than the total one.

I hope that I was clear enough!

|||So, am I getting this right:

You simply created another dataset in which you calculate the "total delta %". You then refer in the background expression dialog box to the "total delta %" field of the new dataset?
|||

The dataset is the same, I just refer to the whole dataset in the formula.

So, I have only one dataset (dataset1) and in the % delta for the background I use a formula like:

iif((sum(Fields!CYRevenue.value)-sum(Fields!PYRevenue.value))/sum(fields!PYRevenue.value) > (sum(Fields!CYRevenue.value,"Dataset1")-sum(Fields!PYRevenue.value,"Dataset1"))/sum(fields!PYRevenue.value,"Dataset1"),"Green","White")

Hope it helps!

CONDITIONAL FORMAT IN SUBTOTAL

This is a multi-part message in MIME format.
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello!
I'm trying aply conditional format in subtotal fields belongings to a = Matrix. But it seems it doesn't work. I'd like to change the background = color each two rows , I'm using something like that:
=3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")
Anyone Knows why it doesn't work rowNumber in subtotal fields or any new = idea?
--=_NextPart_000_0014_01C535DC.35B57990
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello!

I'm trying aply conditional format in = subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to = change the background color each two rows , I'm using something like = that:

= =3Diif(RowNumber(Nothing) mod 2=3D1,"Black","White")

Anyone Knows why it doesn't work = rowNumber in subtotal fields or any new idea?
--=_NextPart_000_0014_01C535DC.35B57990--I haven't tried this yet, but from using Report Services so far... the only
functions recognized in expressions for fields and such are global VB
Functions. So I think that's why RowNumber doesn't work, but I could be
wrong.
Maybe you could try putting your subtotals in a different matrix with a
query that actually calculates the subtotals... and also with said query add
the row numbers (using identity or whatnot) and use those for your formatting
expression.
"Estefania" wrote:
> Hello!
> I'm trying aply conditional format in subtotal fields belongings to a Matrix. But it seems it doesn't work. I'd like to change the background color each two rows , I'm using something like that:
> =iif(RowNumber(Nothing) mod 2=1,"Black","White")
> Anyone Knows why it doesn't work rowNumber in subtotal fields or any new idea?

Conditional Format

I have a report that I would like to highlight some rows in it. I would have
to change the backgound color and set font to bold. The backgound color and
the flag to bold or not would be return by the stored procedure with the
data. For example:
'Gross Sales', 100, 'LightGrey', 'Bold'
'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
Is there a way to set formatting conditionally like that based on data
returned? Any help is appreciated.
Thanks,
Carmen.Hi Carmen
I assume that you are using a table to provide these rows. If you select
the row and click on the Background Color property, the top option is
'<expression>'. Selecting this takes you to a window that allows you to
access fields from your dataset.
something like '=Fields!BackgroundColor' entered here should do what you are
looking for, provided that your stored procedure returns a value that
definitely matches a color selection.
A similar thing can be done under the Font>FontWeight property for the row.
All the best,
Ed Allison
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:9BFFBF24-99D9-4D05-9C64-C4E0466337BC@.microsoft.com...
>I have a report that I would like to highlight some rows in it. I would
>have
> to change the backgound color and set font to bold. The backgound color
> and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||They will not bind up automatically based on name... So in the Gross Sales,
background color expression you might place. (ie the query colum names
Select col, whatever, backcolor, fontweight from yourtable..)
=Fields!backcolor.Value
If the backcolor value might be empty you can provide a default value ie
=IIF(Fields!backcolor.Value is nothing,
'SomeDefaultColor',Fields!backcolor.Value)
(it would probably be better to provide a default back color in the query,
so it can be changed more easily than going into each expression tho.)
Hope this helps...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Carmen" wrote:
> I have a report that I would like to highlight some rows in it. I would have
> to change the backgound color and set font to bold. The backgound color and
> the flag to bold or not would be return by the stored procedure with the
> data. For example:
> 'Gross Sales', 100, 'LightGrey', 'Bold'
> 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> Is there a way to set formatting conditionally like that based on data
> returned? Any help is appreciated.
> Thanks,
> Carmen.|||Thanks Ed and Wayne for your responses. I decided to manually (in the report)
set backgound and bold since joining two queries on the report seemed a
little too complicated. However, I might use you guys suggestion in the
future.
Thanks again,
Carmen.
"Wayne Snyder" wrote:
> They will not bind up automatically based on name... So in the Gross Sales,
> background color expression you might place. (ie the query colum names
> Select col, whatever, backcolor, fontweight from yourtable..)
> =Fields!backcolor.Value
> If the backcolor value might be empty you can provide a default value ie
> =IIF(Fields!backcolor.Value is nothing,
> 'SomeDefaultColor',Fields!backcolor.Value)
> (it would probably be better to provide a default back color in the query,
> so it can be changed more easily than going into each expression tho.)
> Hope this helps...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Carmen" wrote:
> > I have a report that I would like to highlight some rows in it. I would have
> > to change the backgound color and set font to bold. The backgound color and
> > the flag to bold or not would be return by the stored procedure with the
> > data. For example:
> >
> > 'Gross Sales', 100, 'LightGrey', 'Bold'
> > 'Net Sales', 80, '','' or 'Net Sales', 80, 'Transparent','Normal'
> >
> > Is there a way to set formatting conditionally like that based on data
> > returned? Any help is appreciated.
> >
> > Thanks,
> >
> > Carmen.

Conditional For Insert Trigger

Sort of new to this, hoping for some help here.
I've got a table which you insert Scores and comments for Different
Issues. Each User may only input ONE score, but many comments per
issue.
I'm attempting to run a trigger which looks at the distinct users (in
the table) and then will either allow the comment AND score to be
Inserted if the User hasn't put in a score for that issue yet. OR if
the User already HAS input a Score, set the Score to NULL and allow the
rest of the record to be inserted.
My trigger works, but instead of letting the *New* User to put in the
score, it just nulls it.
Sorry if this is kind of long, but I hope I've explained it
sufficiently. I hope someone can help me out. Thanks in advance
This is my trigger :
CREATE TRIGGER trCheckUserResponses
ON dbo.tblIssues_Responses
FOR INSERT
AS
IF (SELECT UserID from Inserted) not in (select distinct UserID from
tblIssues_responses)
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score= Inserted.Score
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
END
ELSE
BEGIN
Update tblIssues_Responses
Set tblIssues_Responses.Score = NULL
From tblIssues_Responses AS A, Inserted AS B
Where A.Response_ID = B.Response_ID
ENDPlease post DDL and sample data.
ML
http://milambda.blogspot.com/

Conditional Font Weight

I need to conditionally set the font weight of my data when a calculation is equal to or less than 70. I am using the following expression in the font weight property:
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra Bold", "Normal")
I get no errors when I run my report, however it does not apply the correct weith to my display. I am positive the calculation is correct since I use it in other conditions and they work fine.
Any help would be apreciated.Extra Bold maps to 800. You can check this by placing a textbox on the
design surface and setting the Font Weight to Extra Bold. Next open the code
view for the report and check the value serialized for the textbox font
weight.
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100
<=70, "800", "Normal")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CarrieWells" <CarrieWells@.discussions.microsoft.com> wrote in message
news:4AFCE118-56E0-4B52-A7B8-B8D551290D6E@.microsoft.com...
> I need to conditionally set the font weight of my data when a calculation
is equal to or less than 70. I am using the following expression in the font
weight property:
> =iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra
Bold", "Normal")
> =iif(Fields!Quantity.Value > 10, "Normal", "Bold")
> I get no errors when I run my report, however it does not apply the
correct weith to my display. I am positive the calculation is correct since
I use it in other conditions and they work fine.
> Any help would be apreciated.
>

Conditional FK Deletes

How would I use a Foreign Key to prevent deletions on the parent table?

For example, I have an Orders table and an OrderCancels table related by a FK on the iOrdID. When the app requests to delete a record on the Orders table, I need to check the OrderCancels table via the iOrdID FK for corresponding records, and if there are, not delete the order.I am a little confused as the foreign key prevents exactly that - deleting a parent record that has a child. If you application attempts to delete a parent record that has child records enforced using a foreign key constraint then the application will receive an error from sql server which will tell it that the delete was unsuccessful.|||Let me clarify - I know you can check 'Cascade deletes' in the SQL GUI so that if you delete a parent, the child gets deleted as well. This is how all of our current FKs work.

Are you saying that if I want to check for children and cancel the parental deletion if the children are found, all I have to do is uncheck 'Cascade Deletes' on the FK? If so, how do I capture this error and return it to the app.

Sorry if all this seems obvious, but I am a very green DBA...

TIA,

-Justin|||You never mentioned cascading - this is very important.

The answer is yes. SQL Server automatically sends this error to the calling application.

Take a look at the books online "Cascading Referential Integrity Constraints" article.

Conditional Filtering in Reporting Services

I am using the following SQL query in a SSRS 2005 report (see below).
I need dbo.FilteredNew_Assets.new_assettypename = "Computer" ONLY when
the value of dbo.FilteredNew_Assets.new_assignedemployeeid is NOT
null. In other words, only when
dbo.FilteredNew_Assets.new_assignedemployeeid has a value do I need
dbo.FilteredNew_Assets.new_assettypename to be filtered.
Is this a task that needs to be accomplished within my SQL query or
somewhere within the context of the actual report? Either way, how do
I accomplish this?
SELECT dbo.FilteredNew_Employee.new_employeeid,
dbo.FilteredNew_Assets.new_assignedemployeeid,
dbo.FilteredNew_Employee.new_employeetypename,
dbo.FilteredNew_Employee.new_firstname,
dbo.FilteredNew_Employee.new_lastname,
dbo.FilteredNew_Assets.new_assettypename,
dbo.FilteredNew_Assets.new_computertypename,
dbo.FilteredNew_Assets.new_manufacturer,
dbo.FilteredNew_Assets.new_model,
dbo.FilteredNew_Assets.new_modelnumber,
dbo.FilteredNew_Assets.new_assetsid,
dbo.FilteredNew_Assets.new_name
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
dbo.FilteredNew_Assets ON
dbo.FilteredNew_Employee.new_employeeid = dbo.FilteredNew_Assets.new_assignedemployeeid
ORDER BY dbo.FilteredNew_Employee.new_lastnamePut filterednew_assets into a derived table like this and you=B4re able
to link on this derived table.
The union all reunites both sets of data, one set containing specific
conditional filter
SELECT *
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
(
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NOT NULL And
FNA.New_assettypename =3D "Computer"
UNION ALL
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NULL
) DerivedNewAssets ON dbo.FilteredNew_Employee.new_employeeid =3D
DerivedNewAssets.new_assignedemployeeid

Conditional fields based on export type

I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).

I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.

Any help would be appreciated.

Jerry

Hi Jerry

I do not know if it is possible for PDF. I know you can limit what fields are exported for CSV and XML.
To do this open the properties box on the field you do not wish to export and click on the data output tab.
Then change the Output radio box from 'Auto' to 'No'

Cheers
Mark
|||

What I really need is the ability to disable the creation of the hyperlink when the report is exported.

I see now that the pageload method is not even fired at the time the choice to export is made. Therefore I will need to determine what program is being run upon this selection and be able to set the parameters of that program. As I see now this just may not exist.

Conditional expressions for background color

I am trying to use conditional expressions within a report field to change
the background color depending on the value of one of the fields in the
report.
Within the properties for one of the report items, I click the background
color item and then choose the expressions and that brings up an edit
expression window. I key the following code into the expression pane.
=iff(fields!libseq.value > 2, "Red", "Blue")
when I rebuild the solution or try to run the report, I get the following
message.
C:\VS Test Applications\Report Testing\Report4.rdl The background color
expression for the textbox 'textbox10' contains an error: [BC30451] Name
'iff' is not declared.
Does anyone know what needs to be declared and where?
thanks,
hughIt shoud be IIf and not iff
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:uv82bHbgGHA.4144@.TK2MSFTNGP02.phx.gbl...
>I am trying to use conditional expressions within a report field to change
>the background color depending on the value of one of the fields in the
>report.
> Within the properties for one of the report items, I click the background
> color item and then choose the expressions and that brings up an edit
> expression window. I key the following code into the expression pane.
> =iff(fields!libseq.value > 2, "Red", "Blue")
> when I rebuild the solution or try to run the report, I get the following
> message.
> C:\VS Test Applications\Report Testing\Report4.rdl The background color
> expression for the textbox 'textbox10' contains an error: [BC30451] Name
> 'iff' is not declared.
> Does anyone know what needs to be declared and where?
> thanks,
> hugh
>|||Hi Hugh,
Thank you for your post.
As Jasper mentioned, the function you use should be Iif not Iff.
Iif is a Visual Basic Fuction. Here is an article for your reference.
Expression Examples in Reporting Services
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
If you have questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional expressions - isnull(A) OR isnull(B)

I am trying to reproduce an expression in my access front-end database
in an SQL view (using Visual Studio 2005 view definition). The
expression is:
SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password,
dbo_T200PEOPLE.PasswordHint
FROM dbo_T200PEOPLE;
Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
gratefully received!Take a look at CASE expression in the BOL
"neilr" <neilryder@.yahoo.com> wrote in message
news:1148376579.910713.297590@.i40g2000cwc.googlegroups.com...
>I am trying to reproduce an expression in my access front-end database
> in an SQL view (using Visual Studio 2005 view definition). The
> expression is:
> SELECT dbo_T200PEOPLE.PersonNo, dbo_T200PEOPLE.FirstName,
> dbo_T200PEOPLE.LastName, IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured, dbo_T200PEOPLE.Password
,
> dbo_T200PEOPLE.PasswordHint
> FROM dbo_T200PEOPLE;
> Can anyone tell me how to reproduce the "IIf(IsNull([Password]) Or
> IsNull([PasswordHint]),"No","Yes") AS Secured" part? All help
> gratefully received!
>|||OK that did it thanks. For anyone else interested, it now looks like
this:
CASE
WHEN PEP.Password IS NULL OR
PEP.PasswordHint IS NULL OR
PEP.Salutation IS NULL OR
PEP.FirstName IS NULL OR
PEP.JobTitle IS NULL
THEN 'No'
ELSE 'Yes'
END
AS DataComplete

Conditional Expression quirk?

When using the conditional expression in a derived column transformation, I found that the following expression:

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : [F1Name]

is invalid while

[F1Depth]==3 ? [F2Name] + "--" + [F1Name] : "" + [F1Name]

is valid.

In both cases, the output type is set to Unicode String (DT_WSTR) with 4000 characters. The error in the first case is:

Error at Data Flow Task [Derived Column (2784)]: Failed to set property "Expression"on "output column" "FactorName" (2918).

Would this be considered a bug, or is there a reasonable explanation?

Thanks,

Anna.

Some more information: The columns F1Name & F2Name are output columns from a lookup that is based on a SELECT statement and their underlying data type/length are varchar (7900)

It seems like the Expression compiler is unable to map this varchar length to any data type?

|||

Is it possible that you have NULL data?|||Expressions are limited to 4000 characters.|||

Phil,

Thanks for your response. However, adding an empty string before the string column makes the expression valid. Why is that?

Thanks,

Anna.

|||Could it have something to do with the ANSI to Unicode conversion? If you explicitly cast F1 to Unicode, does it work?

|||

Thanks for the suggestion.

Either of these works fine:

(DT_STR, 4000, 1252) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to string [DT_STR]

or

(DT_WSTR, 4000) (F1Depth == 3 ?F2Name + "--" + F1Name : F1Name) and the output set to Unicode string [DT_WSTR]

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I do get a warning about the length in both cases, but that is not really an issue.

Is this related more to the length of the source column and the 4000 character restriction in the expression?

|||

Annapurni wrote:

Therefore, I don't think this is related to ANSI to Unicode conversion. It seems like when the source string is > 4000 characters, I need to either explicitly cast it? Adding an empty string "" to the actual string also seems to force the type.

I think you hit the problem right there - In your original code, it thinks you are trying to return a DT_WSTR, but you are returning a DT_STR. Since any literal string value is interpreted as Unicode by SSIS, appending an empty string is forcing the conversion of the DT_STR to a DT_WSTR.

I think. Not 100% positive, though.

Conditional Expression - i.e., IIF in Access

I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

In Access the SQL is this:

SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);

But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

CASNumber = CASE Type
WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
END,
FROM dbo.tblConstituents INNER JOIN
dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

Any ideas would be greatly appreciated.SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END|||Originally posted by Brett Kaiser
SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END

Well, when I do that, I get
"The Query Designer does not support the CASE SQL construct."

Can you even use CASE in a view?|||Also, I need to assign an alias to that column.|||What are you using?

Aren't you using query analyzer?

If you're using Access you may need to make it a PASS THRU query

SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol|||I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help|||What do you mean in VIEW?

Are you doing this in Enterprise Manager?

I would recommend against that.|||Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql ;)|||What s/he said...

Use QA though for SQL Server development...

You'll have a lot less headaches...

Conditional Expression

I have an expression that looks like
this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
The field returns fine, but I want negative values to be returned as 0 when
the value is <=0.
Any ideas on how to do this? Would I add a IIF to the expression? Any help
is appreciated.
Thanks!
RyanOn May 24, 1:19 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I have an expression that looks like
> this;"=Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!QTYONORD.Value"
> The field returns fine, but I want negative values to be returned as 0 when
> the value is <=0.
> Any ideas on how to do this? Would I add a IIF to the expression? Any help
> is appreciated.
> Thanks!
> Ryan
This should work.
=iif(Fields!Quantity.Value-Fields!QuantityAvailable.Value-Fields!
QTYONORD.Value <= 0, 0, Fields!Quantity.Value-Fields!
QuantityAvailable.Value-Fields!QTYONORD.Value)
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Expression

I have the following being applied as an expression:

=iif(sum(Fields!Percentile.Value) > 10, "Yellow", "Black")

I want my percents to show up yellow if they are above 10.00%, and black if they are under 10%, doesn't appear to be working right?one other question I will have with this is to find out if i can make the entire block yellow instead of just the text?

conditional execution of the next job in DTS

I have a job which exports and emails the data from a table (subject to some conditions) . The data is exported to a test file. I donot want to send the email if there are no rows exported. or the filesieze is 0. Otherwsie I want to send the email with this text file as attachement.

Any ideas?

Thanks
RaguYou can turn steps on or off. See the topic titled "Using ActiveX Scripts in a DTS Workflow" in BOL.

Also the topic "Using ActiveX Scripts in DTS" may be helpful.

Phil|||activex in dts is single threaded in dts and slows all kinds of stuff down.
try using any built in task first if you can.
if you want to try something trick, try the mesage queue task.
or
you can provide if logic for rows returned in an execute sql task and if successfull have the on success run an xpcmdshell from the sql task/
or try a data driven query task ++++ place if logic in the query
if rows returned then transfer to text
if no rows returned then raiserror

Books Online {Data Driven Query Task}
Books Online {Execute SQL Task}
Books Online {Message Queue Task}
Books Online {DTS tasks, overview}|||Phil/Ruprect: Thanks for your replies. I was using logic in sql (Execute SQL Task) to initiate the next step but the table has grown to 15 M rows. I plan to use activex script to just check the size of the output file (Thanks to sqldts.com site for the script) and accordingly initiate the next job.

Thanks again for the replies.

Ragu

Conditional execution of first task

I have a situation where I'd like to conditionally execute the first task in a package based on the contents of a user variable.

If user variable "Var1" is false I want to begin execution with the first task.

If "Var1" is true I want to begin execution at the second task.

My first thought of course was SequenceContainer, but the same issue would exist for the first task in a SequenceContainer.

Is there a way to do this?

Thanks!

To conditionally execute a task you would use a constraint with an expression on it, but you need two tasks (containers) to do this. The ideal answer is to use something that does nothing, but offers a start point for the constraint. A sequence container works rather well, just drop it on and collpase it (the arrow on the right-hand side of the header). You can then link that to the real task, nothing should go inside that sequence container.|||

I have created a sample solution inline with Darren's suggeston to use 'Sequence Container' for placing the precedence condition @. http://mystutter.blogspot.com/2006/03/sql-server-integration-services.html

Please let me know your comments.

Thanks,
Loonysan

|||

Thank you. Your example was perfect.

I found another solution as well. I created a Sequence Container, inside that I created another Sequence Container and my first task. I put an expression constraint between those. Then I linked the outer Sequence Container to the second task.

I appreciate the help.

Conditional execution of DTS package task

I've searched everywhere for this but can't find the answer

I want to run a DTS package that simply executes a SQL statement to get
a count of rows in a table, if the resulting number is greater than
zero I want to execute another step in the same package, else just
quit.

I don't to pass global vars from one package to another

How can I do this?

Any help would be greatHi Scouser,

I've got a suggestion: Create a stored proc that does the count of the
records and then execute the DTS from the procedure, if the condition
is true.
Look at this message if you want to see how to execute the DTS-Package
from a stored procedure:
http://groups.google.de/group/borla...62580101f3 8fe

Michael
www.zankl-it.de

Conditional Execution in the Control Flow via Script Task

Greetings.

I'm trying to conditionally execute a dataflow based on the presence of a data file. If the data file isn't present, I'd like to execute gracefully without error.

Logic is as follows:

If FileExists Then
execute dataflow
Else
exit w/o error
End If

I've got the code ready to go, but I'm not sure how to do this conditional branch logic. Right now, the code calls the Dts.Results.Success / Failure. The problem, however, is Failure is exactly that... which doesn't result in the graceful exit I'm looking for.

Anyone have any ideas?

Thanks in advance.

Here is how I would do that:

Create a script task in your control flow to check if the file exists and write that result into a SSIS variable, let's say FileExists=1 -->exists; FileExtis=0 -->Does not exist. Then create a precedence constraint from the script task to the data flow. Then Edit the precedence constraint to use evaluation operation 'Expression and constraint'; Value 'success' and write the expression like @.[User::FileExists]==1.

This way the dataflow will be executed only if the sript task succed and the value of the variable FileExists is equal to one.

Rafael Salas

|||This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.|||

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

|||

Rafael Salas wrote:

Phil Brammer wrote:

This sounds like a fantastic suggestion for the File System Task. I would suggest you submit it at the Microsoft Connect site.

You mean to have a 'Check if file exists' operation in the file system task?

Rafael Salas

Indeed.|||

Good Idea. I followed your suggestion; for those interested in voting on that suggestion:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=231838

Rafael Salas

|||For those curious.. I documented the steps for future peoples that landed on this thread.

dichotic.wordpress.com

Conditional Execution based on Multi-Valued Parameter Selection

If a user selects all (266) parameter values from a mult-valued parameter list, I would like to display a table based on a version of a stored-proc that does not select records using parameter driven criteria.

The total selected count can be acquired by =Parameters!ParmName.Count syntax. Total available parameter values can be extracted by =CountDistinct(Fields!ColName.Value,"DatasetName").

Setting a filter on a table and inspecting the total selected count can limit the number of values executed for the query.

That is great, but I would also like to run an efficient query if all values were selected. Thought I could set a filter on a table and compare these two values, but I can't use an aggregate.

Any ideas?

Decided to create a one row dataset (query) containing a count of the number of values to select from. Used this value in a hidden parameter "PrimaryListCount" and used the new dataset to populate the default report parameter value. Then I determined if all values were selected using the following table filter:

=CInt(Parameters!PrimaryNames.Count) = =CInt(Parameters!PrimaryListCount.Value)

If you have a better solution, I'd like to know about it. If not, hope this can help someone else.

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Conditional drillthrough report?

Is it possible to execute conditional drillthrough reports i.e. to execute a
different report based on the values contained in the main report?On Apr 10, 2:26 am, georges <geor...@.discussions.microsoft.com> wrote:
> Is it possible to execute conditional drillthrough reports i.e. to execute a
> different report based on the values contained in the main report?
This link might help.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/bba9dfc46640ab3/100ad792fec5beb6?lnk=st&q=reporting+services+conditional+drill&rnum=2#100ad792fec5beb6
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional DistinctCount

I want a distinctCount in a group for a criteria. How can i do it
I am doing something like this
=CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
"grpResCat")
Is this right?Try this:
=CountDistinct(IIF(Trim(CStr(Fields!CATE.Value)) = "N", Fields!ERNR.Value,
Nothing), "grpResCat")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anand Prabhu" <AnandPrabhu@.discussions.microsoft.com> wrote in message
news:594F0D45-8C6E-4988-A41D-DA03E858EC86@.microsoft.com...
> I want a distinctCount in a group for a criteria. How can i do it
> I am doing something like this
> =CountDistinct(IIF(Fields!CATE.Value='N'),Fields!ERNR.Value,nothing),
> "grpResCat")
> Is this right?
>

Conditional Displaying

Hi all,

I am using Crystal reports XI R1 . Here is the problem, I have an integer field (x), i need to display three more fields (a,b,c) based on that integer field (x) when it is maximum.

for example
when max(x) then i need to display the corresponding records of a, b, and c.

Any help would be greatly appreciated.

Thanks in advance
Sudharsan.What is the database?
I think you need to write the query

Select * from table
where x=(select max(x) from table)

and design the report using that query

Conditional Display of report Header/footer data

Is visibility of the header/footer regions able to be turned on off
based on some expression?Yes. Select the header or footer whichever you'd like, and find the
Visiblity/Hidden section, choose "expression" from the drop down, or simply
type your expression in the space provided. This will be evaluated at
runtime and set the visibility to its proper state.
Michael
"Weston Weems" wrote:
> Is visibility of the header/footer regions able to be turned on off
> based on some expression?
>

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

Conditional display

This is a multi-part message in MIME format.
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
How do I get a row to display conditionally? For example below - I = don't want the shirts row to display because it's zero. I'm using = Report Builder against a Cube:
Products: Qty:
shoes 5
shirts 0
gloves 10
--=_NextPart_000_006A_01C7E655.2B232280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
How do I get a row to display = conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:

Products: = Qty:
shoes  = ; 5
shirts = 0
gloves = 10

--=_NextPart_000_006A_01C7E655.2B232280--On Aug 24, 3:46 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> How do I get a row to display conditionally? For example below - I don't want the shirts row to display because it's zero. I'm using Report Builder against a Cube:
> Products: Qty:
> shoes 5
> shirts 0
> gloves 10
I'm not sure if this will work in your scenario, but in a standard
SSRS report you could use an expression like this in the Value
property of the table cell/etc:
=iif(Fields!Qty.Value = 0, Nothing, Fields!Qty.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

conditional detail sections

i have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanksi have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanks

What I am getting from this is :
You want 'detail a' section to be 10 lines in height even if it contains less than 10 records.

If you want this only once on each page then do the following:
Remove your 'details b' section.
Make Page Header 10 lines in height approx.
Go to Page Header's properties in 'Format' and Check 'Underlay following sections'.

If you want this effect more than once on each page then you can group your records and do the same settings as mentioned above to that group header.

Just keep in mind you have to remove 'detail b' section.

Conditional Deletion in Replication

I am developing a transactional replication plan. I have one table
that most of the time I do not want to replicate deletions. There is
an exception to this rule. If the status_id of another table is false,
then the deletion should be replicated.
I have been having difficulty trying to put this condition in place. I
have tried modifying the sp_MSdel_dboTable stored procedure. I have
removed the if @.@.rowcount check and replication errors with the
messages:
-- Error executing a batch of commands. Retrying individual commands.
Eventually, it will fail with the message: if @.@.trancount > 0 rollback
tran
Any ideas on how to get around this issue?
Publisher: SQL 2000
Distributor: On a separate server
Subscriber: SQL 2005
Thanks for any help I can get.
You need to replicate an indexed view as a table on the subscriber. The
indexed view would only "show" rows which match the status_id in the other
table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emreeves" <em_reeves@.lycos.com> wrote in message
news:1143754411.192208.291820@.i40g2000cwc.googlegr oups.com...
>I am developing a transactional replication plan. I have one table
> that most of the time I do not want to replicate deletions. There is
> an exception to this rule. If the status_id of another table is false,
> then the deletion should be replicated.
> I have been having difficulty trying to put this condition in place. I
> have tried modifying the sp_MSdel_dboTable stored procedure. I have
> removed the if @.@.rowcount check and replication errors with the
> messages:
> -- Error executing a batch of commands. Retrying individual commands.
> Eventually, it will fail with the message: if @.@.trancount > 0 rollback
> tran
> Any ideas on how to get around this issue?
>
> Publisher: SQL 2000
> Distributor: On a separate server
> Subscriber: SQL 2005
> Thanks for any help I can get.
>
|||Thanks! This sounds better than the next approach I was going to
try... creating a 1st Insert/Update trans replication, and a 2nd Delete
only with row filtering.
BTW - Thanks very much for your replication book. It has helped loads
this past few weeks.

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
Thanks and Regards,
Prasanth
Hi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth
|||Thanks, I will do with that.
"Uri Dimant" wrote:

> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
>
>

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
--
Thanks and Regards,
PrasanthHi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth|||Thanks, I will do with that.
"Uri Dimant" wrote:
> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> > Hi,
> >
> > I need to delete many records in a table based on some conditions in the
> > where clause. This delete is taking plenty of time. How do I disable the
> > logging of transactions?
> > --
> > Thanks and Regards,
> > Prasanth
>
>

Conditional Data Inserting

Hello,

My project import data from à text file to a database. I'm able to import the whole file but i want to import only if data are younger than the one i have in database.

How can i do ? Where can i see a tutorial about it ?

thanks a lotDo both the source rows and the database rows have a date that can be compared. If so then you could use either a lookup or a mergejoin to get the two dates into the same data flow (by lookup or join on some key that indicates the rows are matched) and then use a conditional split to send the rows you want to update to a OLEDB command transform with the appropriate update statement (or if you do indeed want to insert the data not update then just send it to an OLEDB destination).

Thanks,
Matt|||There's a tutorial here that compares merge join and lookup components. Its not exactly the same as your situation but it may help!!

-Jamie|||Thanks it is very useful !

But i'm still blocked because in some case i need to insert, and in other case i need to update...|||Ash has a great post on upserts here:
http://sqljunkies.com/WebLog/ashvinis/archive/2005/06/15/15829.aspx
Give that a shot.
Thanks,

Conditional CREATE SCHEMA

Hi,
I would like to write TO-SQL batches that call the CREATE SCHEMA...
statement. However, there is a reasonable likelihood that the schema I want
to create already exists. However, the CREATE SCHEMA statement has the
limitation that it be the first command in a batch. I found out the hard
way that this means that I can't run a batch like this...
IF (SCHEMA_ID('MySchema') IS NULL)
CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
How do I conditionally create a schema based on whether it currently exists?
I came up with this, but I'd like to think there is something a little more
elegant.
DECLARE @.CS varchar(255);
SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
IF (SCHEMA_ID('MySchema') IS NULL)
EXEC (@.CS);
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
Although you may not think your solution elegant it is a solution to the
issue which can be useful, for instance if you want to take some action
outside the scope of the current transaction.
John
"Daniel Jameson" wrote:
> Hi,
> I would like to write TO-SQL batches that call the CREATE SCHEMA...
> statement. However, there is a reasonable likelihood that the schema I want
> to create already exists. However, the CREATE SCHEMA statement has the
> limitation that it be the first command in a batch. I found out the hard
> way that this means that I can't run a batch like this...
> IF (SCHEMA_ID('MySchema') IS NULL)
> CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
> How do I conditionally create a schema based on whether it currently exists?
> I came up with this, but I'd like to think there is something a little more
> elegant.
> DECLARE @.CS varchar(255);
> SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
> IF (SCHEMA_ID('MySchema') IS NULL)
> EXEC (@.CS);
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>|||Hi Daniel,
I understand that you would like to find an elegant way to create a schema
if the schema does not exist.
If I have misunderstood, please let me know.
I think that your current workaround is elegant. If you use "Generate
Scripts... " to generate the script of schema objects in SQL Server 2005
Management Studio, you will find the following scripts:
USE [AdventureWorks]
GO
/****** Object: Schema [HumanResources] Script Date: 09/17/2007
13:41:51 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
GO
As you can see, it is very similar to yours. Currently I do not think that
there is a better way due to the design limitation of using CREATE SCHEMA.
Please feel free to let us know if you have any other questions or
concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Daniel,
What is this issue going on?
If there is any issue, please feel free to post back. We are very glad for
further assistance.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Conditional Count(*)

In a group-by statement, how can i get the count(*) of a given column conditioning this column independently of the whole group-by statement?

I have this query:

SELECT field1, SUM(field2) WHERE blah blah FROM table GROUP BY field1

It's all good.

I need to add a column with the count for a specific value on a column:
COUNT(field3) where field3 = 1

How can I insert this second query on the first one?

Thanks a lot !!!

CarlosUse SUM and DECODE:
SELECT field1, SUM(field2), SUM(DECODE(field3,1,1,0))
WHERE blah blah FROM table GROUP BY field1

;)|||I'd use SQL to achieve the same purpose. Something like:SELECT col1, Sum(col2), Sum(CASE WHEN 1 = col3 THEN 1 END)
FROM myTable
WHERE blah
GROUP BY col1Bear in mind that the WHERE clause gets first shot at the data, so you'll only see rows that pass the WHERE criteria. If you need to count all of the rows with col3 = 1 instead of just the rows that meet your criteria, then I'd use a "nested query", something like:SELECT col1, Sum(col2), (SELECT Count(*) FROM myTable AS z WHERE 1 = z.col3)
FROM myTable
WHERE blah
GROUP BY col1-PatP|||Thanks a lot !!!

It works great !!..

Carlos|||I have a similar question...

I have a db with helpdesk ticket info. I want to get the number of created, open, and closed tickets by each site (and the % closed if possible). Here is what I want the output to look like.

Created Open Closed %Closed
Site1 #created site1 #open site1 #closed site1 #closed/#created
Site2 #created site2 #open site2 #closed site2 #closed/#created
Site3 #created site3 #open site3 #closed site3 #closed/#created
Site4 #created site4 #open site4 #closed site4 #closed/#created

The site name is in a field called assigned_to_worker_location, and the status is in a field called status_lookup_value. When I used the code below, it does total by site, it just give me everything for all columns and rows. Here is the code:
==============================================
SELECT workitem.assigned_to_worker_location as Site,
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE (datepart(mm,wi.workitem_created_on)=datepart(mm,g etdate()))) as "Created",
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE wi.workitem_status_lookup_value = 'Open' or
wi.workitem_status_lookup_value = 'Wait-User' or
wi.workitem_status_lookup_value = 'Wait-Vendor' or
wi.workitem_status_lookup_value = 'Wait-Part' or
wi.workitem_status_lookup_value = 'Requested') as "Open",
(SELECT COUNT(*)
FROM dbo.HD_workitem_current_view as wi
WHERE (wi.workitem_status_lookup_value = 'Closed' or
wi.workitem_status_lookup_value = 'Resolved') and
(datepart(mm,wi.workitem_created_on)=datepart(mm,g etdate()))) as "Closed",
STR(ROUND(
(convert(decimal,@.Closed) /
convert(decimal,@.Total) * 100),2),10,2) + ' %' AS 'Percent Closed'

FROM dbo.hd_workitem_current_view as workitem
GROUP BY workitem.assigned_to_worker_location|||select Site
, Created
, Open
, Closed
, round(100.0 * Closed / Total,2) as Percent_Closed
from (
select workitem.assigned_to_worker_location as Site
, sum(case when datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Created
, sum(case when wi.workitem_status_lookup_value
in ('Open','Wait-User','Wait-Vendor'
,'Wait-Part','Requested')
then 1 else 0 end ) as Open
, sum(case when wi.workitem_status_lookup_value
in ('Closed', 'Resolved')
and datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Closed
, count(*) as Total
from dbo.hd_workitem_current_view as workitem
group
by workitem.assigned_to_worker_location
) as d|||Thank you much! That worked except for the % calculation. For example, I have 22 items created and 22 items closed, that should be 100%, but it shows 6%.|||whoops, you are rightselect Site
, Created
, Open
, Closed
, round(100.0 * Closed
/ ( select count(*)
from dbo.hd_workitem_current_view )
,2) as Percent_Closed
from (
select workitem.assigned_to_worker_location as Site
, sum(case when datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Created
, sum(case when wi.workitem_status_lookup_value
in ('Open','Wait-User','Wait-Vendor'
,'Wait-Part','Requested')
then 1 else 0 end ) as Open
, sum(case when wi.workitem_status_lookup_value
in ('Closed', 'Resolved')
and datepart(mm,wi.workitem_created_on)
=datepart(mm,getdate())
then 1 else 0 end ) as Closed
from dbo.hd_workitem_current_view as workitem
group
by workitem.assigned_to_worker_location
) as d|||Thanks again, worked great!

Conditional Count in RDLC Expression

I'd like to put a count in a textbox but only a count where a column
doesnt equal certain values. Here is what I tried:
=Count(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
Obviously this didn't work. Can anyone point me in the right
direction?
Thanks,
JasonOn Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DCOUNT(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||On Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DSUM(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||It did thanks

Conditional Count in Detail Row - Count results across columns

Hi,
I am trying to establish a way to count the number of True/False entries
across a number of columns. That is I have a dataset returning results of
organisations (rows) and whether they have opted for a particular criteria
(columns). I want to know how many of the criteria a particular organisation
has opted for and display that on the end of the detail row for each
organisation.
Org Name Criteria1 Criteria2 Criteria3 Total Selected
ABC Ltd Yes No Yes 2
LMO Ltd No No Yes 1
XYZ Ltd Yes Yes Yes 3
Totals 2 1 3
I have managed the Totals in the footer using a conditional count.
eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
Fields!cf_subindicator111name.Value, Nothing))
I am unsure of how to do the same thing at the end of the detail line (Total
Selected) for a conditional count of the discreet fields in the dataset. I
have 89 Criteria in all to count.
I have a feeling that this needs to be done at the SQL query level creating
a running total for each organisation returned where they have opted Yes for
a Criteria but I don't know where to start with this.
Hope this is clear.
Any ideas?
SimonHi,
You were right :). You should do it in your stored procedure. Maybe you
should use PIVOT operator ans aggregation function, like COUNT. Also, you
should try the RunningValue function in SSRS in worst case scenario.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> Hi,
> I am trying to establish a way to count the number of True/False entries
> across a number of columns. That is I have a dataset returning results of
> organisations (rows) and whether they have opted for a particular criteria
> (columns). I want to know how many of the criteria a particular
> organisation
> has opted for and display that on the end of the detail row for each
> organisation.
> Org Name Criteria1 Criteria2 Criteria3 Total Selected
> ABC Ltd Yes No Yes 2
> LMO Ltd No No Yes 1
> XYZ Ltd Yes Yes Yes 3
> Totals 2 1 3
> I have managed the Totals in the footer using a conditional count.
> eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> Fields!cf_subindicator111name.Value, Nothing))
> I am unsure of how to do the same thing at the end of the detail line
> (Total
> Selected) for a conditional count of the discreet fields in the dataset. I
> have 89 Criteria in all to count.
> I have a feeling that this needs to be done at the SQL query level
> creating
> a running total for each organisation returned where they have opted Yes
> for
> a Criteria but I don't know where to start with this.
> Hope this is clear.
> Any ideas?
> Simon|||Thanks Janos.
The issue here is I am unclear on the specifics of how to do this, where it
sits in the select statement and what the format would be. I am only just
managing to get the select, from and where statements working at the moment
:) New to Reporting Services and SQL and could do with some pointers. I will
look up the Pivot operator and see if that gets me anywhere. Any pointers
would be appreciated.
Simon
"BERKE Janos" wrote:
> Hi,
> You were right :). You should do it in your stored procedure. Maybe you
> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> should try the RunningValue function in SSRS in worst case scenario.
> Regards,
> Janos
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> > Hi,
> >
> > I am trying to establish a way to count the number of True/False entries
> > across a number of columns. That is I have a dataset returning results of
> > organisations (rows) and whether they have opted for a particular criteria
> > (columns). I want to know how many of the criteria a particular
> > organisation
> > has opted for and display that on the end of the detail row for each
> > organisation.
> >
> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >
> > ABC Ltd Yes No Yes 2
> > LMO Ltd No No Yes 1
> > XYZ Ltd Yes Yes Yes 3
> >
> > Totals 2 1 3
> >
> > I have managed the Totals in the footer using a conditional count.
> >
> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> > Fields!cf_subindicator111name.Value, Nothing))
> >
> > I am unsure of how to do the same thing at the end of the detail line
> > (Total
> > Selected) for a conditional count of the discreet fields in the dataset. I
> > have 89 Criteria in all to count.
> >
> > I have a feeling that this needs to be done at the SQL query level
> > creating
> > a running total for each organisation returned where they have opted Yes
> > for
> > a Criteria but I don't know where to start with this.
> >
> > Hope this is clear.
> >
> > Any ideas?
> >
> > Simon
>|||Hi Simon,
Post here a similar table structure to me, and I'll write the code for you
;). I will add some comments to my code as well.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> Thanks Janos.
> The issue here is I am unclear on the specifics of how to do this, where
> it
> sits in the select statement and what the format would be. I am only just
> managing to get the select, from and where statements working at the
> moment
> :) New to Reporting Services and SQL and could do with some pointers. I
> will
> look up the Pivot operator and see if that gets me anywhere. Any pointers
> would be appreciated.
> Simon
> "BERKE Janos" wrote:
>> Hi,
>> You were right :). You should do it in your stored procedure. Maybe you
>> should use PIVOT operator ans aggregation function, like COUNT. Also, you
>> should try the RunningValue function in SSRS in worst case scenario.
>> Regards,
>> Janos
>> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
>> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to establish a way to count the number of True/False
>> > entries
>> > across a number of columns. That is I have a dataset returning results
>> > of
>> > organisations (rows) and whether they have opted for a particular
>> > criteria
>> > (columns). I want to know how many of the criteria a particular
>> > organisation
>> > has opted for and display that on the end of the detail row for each
>> > organisation.
>> >
>> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
>> >
>> > ABC Ltd Yes No Yes 2
>> > LMO Ltd No No Yes 1
>> > XYZ Ltd Yes Yes Yes 3
>> >
>> > Totals 2 1 3
>> >
>> > I have managed the Totals in the footer using a conditional count.
>> >
>> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
>> > Fields!cf_subindicator111name.Value, Nothing))
>> >
>> > I am unsure of how to do the same thing at the end of the detail line
>> > (Total
>> > Selected) for a conditional count of the discreet fields in the
>> > dataset. I
>> > have 89 Criteria in all to count.
>> >
>> > I have a feeling that this needs to be done at the SQL query level
>> > creating
>> > a running total for each organisation returned where they have opted
>> > Yes
>> > for
>> > a Criteria but I don't know where to start with this.
>> >
>> > Hope this is clear.
>> >
>> > Any ideas?
>> >
>> > Simon|||Janos,
Hi, not sure what you mean but my current query is as follows.
SELECT CRMAF_FilteredIncident.customeridname,
CRMAF_FilteredIncident.incidentid,
CRMAF_FilteredCF_Assessment.cf_casenumberid,
CRMAF_FilteredCF_Assessment.cf_applicationsubtypename,
CRMAF_FilteredCF_Assessment.cf_applicationtypename,
CRMAF_FilteredCF_Assessment.cf_subindicator111name,
CRMAF_FilteredCF_Assessment.cf_subindicator112name,
CRMAF_FilteredCF_Assessment.cf_subindicator121name,
CRMAF_FilteredCF_Assessment.cf_subindicator122name,
CRMAF_FilteredCF_Assessment.cf_subindicator123name,
CRMAF_FilteredCF_Assessment.cf_subindicator131name,
CRMAF_FilteredCF_Assessment.cf_subindicator141name,
CRMAF_FilteredCF_Assessment.cf_subindicator211name,
CRMAF_FilteredCF_Assessment.cf_subindicator221name,
CRMAF_FilteredCF_Assessment.cf_subindicator231name,
CRMAF_FilteredCF_Assessment.cf_subindicator232name,
CRMAF_FilteredCF_Assessment.cf_subindicator233name,
CRMAF_FilteredCF_Assessment.cf_subindicator241name,
CRMAF_FilteredCF_Assessment.cf_subindicator242name,
CRMAF_FilteredCF_Assessment.cf_subindicator243name,
CRMAF_FilteredCF_Assessment.cf_subindicator244name,
CRMAF_FilteredCF_Assessment.cf_subindicator245name,
CRMAF_FilteredCF_Assessment.cf_subindicator251name,
CRMAF_FilteredCF_Assessment.cf_subindicator252name,
CRMAF_FilteredCF_Assessment.cf_subindicator311name,
CRMAF_FilteredCF_Assessment.cf_subindicator312name,
CRMAF_FilteredCF_Assessment.cf_subindicator313name,
CRMAF_FilteredCF_Assessment.cf_subindicator321name,
CRMAF_FilteredCF_Assessment.cf_subindicator322name,
CRMAF_FilteredCF_Assessment.cf_subindicator323name,
CRMAF_FilteredCF_Assessment.cf_subindicator331name,
CRMAF_FilteredCF_Assessment.cf_subindicator332name,
CRMAF_FilteredCF_Assessment.cf_subindicator341name,
CRMAF_FilteredCF_Assessment.cf_subindicator342name,
CRMAF_FilteredCF_Assessment.cf_assessmenttypename,
CRMAF_FilteredCF_Assessment.cf_subindicator411name,
CRMAF_FilteredCF_Assessment.cf_subindicator421name,
CRMAF_FilteredCF_Assessment.cf_subindicator422name,
CRMAF_FilteredCF_Assessment.cf_subindicator423name,
CRMAF_FilteredCF_Assessment.cf_subindicator431name,
CRMAF_FilteredCF_Assessment.cf_subindicator441name,
CRMAF_FilteredCF_Assessment.cf_subindicator511name,
CRMAF_FilteredCF_Assessment.cf_subindicator512name,
CRMAF_FilteredCF_Assessment.cf_subindicator513name,
CRMAF_FilteredCF_Assessment.cf_subindicator514name,
CRMAF_FilteredCF_Assessment.cf_subindicator521name,
CRMAF_FilteredCF_Assessment.cf_subindicator522name,
CRMAF_FilteredCF_Assessment.cf_subindicator523name,
CRMAF_FilteredCF_Assessment.cf_subindicator524name,
CRMAF_FilteredCF_Assessment.cf_subindicator525name,
CRMAF_FilteredCF_Assessment.cf_subindicator6110name,
CRMAF_FilteredCF_Assessment.cf_subindicator6111name,
CRMAF_FilteredCF_Assessment.cf_subindicator611name,
CRMAF_FilteredCF_Assessment.cf_subindicator612name,
CRMAF_FilteredCF_Assessment.cf_subindicator613name,
CRMAF_FilteredCF_Assessment.cf_subindicator614name,
CRMAF_FilteredCF_Assessment.cf_subindicator615name,
CRMAF_FilteredCF_Assessment.cf_subindicator616name,
CRMAF_FilteredCF_Assessment.cf_subindicator617name,
CRMAF_FilteredCF_Assessment.cf_subindicator618name,
CRMAF_FilteredCF_Assessment.cf_subindicator619name,
CRMAF_FilteredCF_Assessment.cf_subindicator621name,
CRMAF_FilteredCF_Assessment.cf_subindicator622name,
CRMAF_FilteredCF_Assessment.cf_subindicator623name,
CRMAF_FilteredCF_Assessment.cf_subindicator624name,
CRMAF_FilteredCF_Assessment.cf_subindicator625name,
CRMAF_FilteredCF_Assessment.cf_subindicator626name,
CRMAF_FilteredCF_Assessment.cf_subindicator631name,
CRMAF_FilteredCF_Assessment.cf_subindicator632name,
CRMAF_FilteredCF_Assessment.cf_subindicator633name,
CRMAF_FilteredCF_Assessment.cf_subindicator634name,
CRMAF_FilteredCF_Assessment.cf_subindicator641name,
CRMAF_FilteredCF_Assessment.cf_subindicator642name,
CRMAF_FilteredCF_Assessment.cf_subindicator643name,
CRMAF_FilteredCF_Assessment.cf_subindicator651name,
CRMAF_FilteredCF_Assessment.cf_subindicator652name,
CRMAF_FilteredCF_Assessment.cf_subindicator711name,
CRMAF_FilteredCF_Assessment.cf_subindicator712name,
CRMAF_FilteredCF_Assessment.cf_subindicator713name,
CRMAF_FilteredCF_Assessment.cf_subindicator714name,
CRMAF_FilteredCF_Assessment.cf_subindicator721name,
CRMAF_FilteredCF_Assessment.cf_subindicator731name,
CRMAF_FilteredCF_Assessment.cf_subindicator741name,
CRMAF_FilteredCF_Assessment.cf_subindicator742name,
CRMAF_FilteredCF_Assessment.cf_subindicator743name,
CRMAF_FilteredCF_Assessment.cf_subindicator811name,
CRMAF_FilteredCF_Assessment.cf_subindicator812name,
CRMAF_FilteredCF_Assessment.cf_subindicator821name,
CRMAF_FilteredCF_Assessment.cf_subindicator911name,
CRMAF_FilteredCF_Assessment.cf_subindicator921name,
CRMAF_FilteredCF_Assessment.cf_subindicator931name,
CRMAF_FilteredCF_Assessment.cf_subindicator941name,
CRMAF_FilteredCF_Assessment.cf_subindicator951name,
CRMAF_FilteredCF_Assessment.cf_subindicator961name,
CRMAF_FilteredIncident.cf_casesubtypename
FROM FilteredCF_Assessment AS CRMAF_FilteredCF_Assessment INNER JOIN
FilteredIncident AS CRMAF_FilteredIncident ON
CRMAF_FilteredCF_Assessment.cf_casenumberid =CRMAF_FilteredIncident.incidentid
WHERE (CRMAF_FilteredIncident.cf_casesubtypename = 'Assessment')
What I want to do is a conditional count where the subindicator fields are
equal to yes and be able to total those at the end of each row.
Thanks.
"BERKE Janos" wrote:
> Hi Simon,
> Post here a similar table structure to me, and I'll write the code for you
> ;). I will add some comments to my code as well.
> Regards,
> Janos
>
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> > Thanks Janos.
> >
> > The issue here is I am unclear on the specifics of how to do this, where
> > it
> > sits in the select statement and what the format would be. I am only just
> > managing to get the select, from and where statements working at the
> > moment
> > :) New to Reporting Services and SQL and could do with some pointers. I
> > will
> > look up the Pivot operator and see if that gets me anywhere. Any pointers
> > would be appreciated.
> >
> > Simon
> >
> > "BERKE Janos" wrote:
> >
> >> Hi,
> >>
> >> You were right :). You should do it in your stored procedure. Maybe you
> >> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> >> should try the RunningValue function in SSRS in worst case scenario.
> >>
> >> Regards,
> >>
> >> Janos
> >>
> >> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> >> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to establish a way to count the number of True/False
> >> > entries
> >> > across a number of columns. That is I have a dataset returning results
> >> > of
> >> > organisations (rows) and whether they have opted for a particular
> >> > criteria
> >> > (columns). I want to know how many of the criteria a particular
> >> > organisation
> >> > has opted for and display that on the end of the detail row for each
> >> > organisation.
> >> >
> >> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >> >
> >> > ABC Ltd Yes No Yes 2
> >> > LMO Ltd No No Yes 1
> >> > XYZ Ltd Yes Yes Yes 3
> >> >
> >> > Totals 2 1 3
> >> >
> >> > I have managed the Totals in the footer using a conditional count.
> >> >
> >> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> >> > Fields!cf_subindicator111name.Value, Nothing))
> >> >
> >> > I am unsure of how to do the same thing at the end of the detail line
> >> > (Total
> >> > Selected) for a conditional count of the discreet fields in the
> >> > dataset. I
> >> > have 89 Criteria in all to count.
> >> >
> >> > I have a feeling that this needs to be done at the SQL query level
> >> > creating
> >> > a running total for each organisation returned where they have opted
> >> > Yes
> >> > for
> >> > a Criteria but I don't know where to start with this.
> >> >
> >> > Hope this is clear.
> >> >
> >> > Any ideas?
> >> >
> >> > Simon
> >>
>