Conditionally hiding values in a Matrix

Hi all,
I have a matrix which has a runningvalue in it's detail cell, so it looks
like this:
item, date1, date2, date3...datex
A, 1, 1, 1...1
B, 1, 2, 3...x
C, 1, 2, -1...y
D, 1, -2, -3...z
This works fine and all is well. However, I now wish to filter out all rows
that have no negative values (which in the above example will only leave C
and D). Since the runningvalues themselves are not in the dataset, is there
any way of basing a whole row's visibility or inclusion based on any value
in the detail cell?
If not could you suggest how I could do this?
Thanks!
ShakShak,
I've had this issue, I found that a stored procedure which works it out
before hand is the only feasible solution. Create a temporary table
containing rows for item A, B, C etc, and a flag to say whether they
have negatives. Then in the SP still, do the normal query but join the
temporary table on item so you can include the flag in the output.
Then you can suppress a row based on the the first flag at that level
using =First(Field!Flag.Value) in the groups filters, or more
efficiently you could remove it from within the SP.
Chris
Shak wrote:
> Hi all,
> I have a matrix which has a runningvalue in it's detail cell, so it
> looks like this:
> item, date1, date2, date3...datex
> A, 1, 1, 1...1
> B, 1, 2, 3...x
> C, 1, 2, -1...y
> D, 1, -2, -3...z
> This works fine and all is well. However, I now wish to filter out
> all rows that have no negative values (which in the above example
> will only leave C and D). Since the runningvalues themselves are not
> in the dataset, is there any way of basing a whole row's visibility
> or inclusion based on any value in the detail cell?
> If not could you suggest how I could do this?
> Thanks!
> Shak|||Hi Chris,
While waiting for a reply, that was the solution I worked on. It seems
precalculation is the only way to solve it, which is a shame - some kind of
"two pass" reporting generation might be complicated though.
My solution was slightly different in that I flag all rows with and ID that
may at any time have a runningvalue less than zero; it's then just easy to
filter the dataset on that flag when required.
Thanks for the reply though!
Shak
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:ubjeuVocFHA.2960@.TK2MSFTNGP09.phx.gbl...
> Shak,
> I've had this issue, I found that a stored procedure which works it out
> before hand is the only feasible solution. Create a temporary table
> containing rows for item A, B, C etc, and a flag to say whether they
> have negatives. Then in the SP still, do the normal query but join the
> temporary table on item so you can include the flag in the output.
> Then you can suppress a row based on the the first flag at that level
> using =First(Field!Flag.Value) in the groups filters, or more
> efficiently you could remove it from within the SP.
> Chris
>
> Shak wrote:
> > Hi all,
> >
> > I have a matrix which has a runningvalue in it's detail cell, so it
> > looks like this:
> >
> > item, date1, date2, date3...datex
> > A, 1, 1, 1...1
> > B, 1, 2, 3...x
> > C, 1, 2, -1...y
> > D, 1, -2, -3...z
> >
> > This works fine and all is well. However, I now wish to filter out
> > all rows that have no negative values (which in the above example
> > will only leave C and D). Since the runningvalues themselves are not
> > in the dataset, is there any way of basing a whole row's visibility
> > or inclusion based on any value in the detail cell?
> >
> > If not could you suggest how I could do this?
> >
> > Thanks!
> >
> > Shak
>

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!

Conditional visibility in a table group.

I have a report that presents the detail lines in two groupings. So I
have the detail lines as a subset of group1 which is a subset of group2.
Nothing clever there.
I want now to add a line to the report that is only visible if a
parameter "DisplaySignOff" (which will just show a box which users will
sign) has value = "Y".
If I add a detail group below the existing detail line and enter an
expression to make its visibility conditional on the parameter, the
original detail line becomes invisible too. I tried to change the name
of the added group, but it insists on having the same group name- I can
change it (type some other name), but the change is ignored.
If a add a third group that is a subset of group2 (and just select a
footer) and enter an expression to make its visibility conditional on
the parameter, the detail lines disappear again.
Any pointers gratefully received...Do you want to add the line for each record when DisplaySignOff = "Y"? If so,
right-click on your existing Detail row and choose "Insert Row Below". Now
select this row using its grey selector handle (this selects the entire row).
Now set the Hidden property under Visibility to an expression like:
=iif(ReportItems!DisplaySignOff = "Y", False, True)
Charles Kangai, MCDBA, MCT
"Nick Addison" wrote:
> I have a report that presents the detail lines in two groupings. So I
> have the detail lines as a subset of group1 which is a subset of group2.
> Nothing clever there.
> I want now to add a line to the report that is only visible if a
> parameter "DisplaySignOff" (which will just show a box which users will
> sign) has value = "Y".
> If I add a detail group below the existing detail line and enter an
> expression to make its visibility conditional on the parameter, the
> original detail line becomes invisible too. I tried to change the name
> of the added group, but it insists on having the same group name- I can
> change it (type some other name), but the change is ignored.
> If a add a third group that is a subset of group2 (and just select a
> footer) and enter an expression to make its visibility conditional on
> the parameter, the detail lines disappear again.
> Any pointers gratefully received...
>

Conditional Sum/Runnining Total

I have a simple table in ssrs where data is returned from a stored procedure.

I have detail data group totals of the detail data.

I want to be able to create a sum of the detail data matching certain criteria.

i.e.

I have the following total field

sum(Fields!hours_m2.Value)

what I also want to be able to do is create a conditional formula like ...

sum(iif(Fields!Sort_Order.Value = "E1",Fields!hours_m2.Value,0))

When I create this on my report and preview it I get the following message in the field #Error.

Can someone please tell me where I've gone wrong and how to fix ... I know I can change the stored proc but I have 12 columns which I want to do the same thing with which would mean adding 12 columns to my stored proc.

Hello Derek,

In Visual Studio, click on the preview tab and run your report. Then look in the Output window (Ctrl+Alt+O), it should have a description as to what the error is. Can you post that error message?

Jarret

|||

One of the things I have found out about summing in ssrs is that it treats values of doubles and integers seperatley.

try changing the the "0" to "0.0" this will then tell ssrs it is a double type and your sum should work.

|||

description of error message

[rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox101’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

|||

I have found that using the following

sum(iif(Fields!Sort_Order.Value = "E1" or Fields!Sort_Order.Value = "E2" or Fields!Sort_Order.Value = "F1",cdbl(Fields!hours_m2.Value),cdbl(0.0)))

works

Thanks for you responses

|||

The problem is as Mainiac said. Try this:

=sum(iif(Fields!Sort_Order.Value = "E1", cDec(Fields!hours_m2.Value), cDec(0)))

Hope this helps.

Jarret

Conditional formatting on a text box

Hi all -
I have a report that I need to duplicate in RS. What I'm trying to do is put
some conditional formatting on my detail textboxes.
I want the text to be bold if the field = certain things. ie. If the line
detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc I
need that line to be bolded.
I'm trying to use an IIF statement as an expression for the textbox's
Format/FontWeight property, but am having trouble compiling.
Here's what I've tried:
=IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD, fontweight = NORMAL)
=IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
Of course, I will eventually need to convert this into some kind of OR
statment because there are 7 different possible line details that should be
bolded.
I appreciate any help you could give. Thanks in advance!
-KellyHi Kelly
I have found the following works.
= iif(rtrim(Fields!ObjectCode.Value) = "Your text", "Bold","Normal")
But is has to go in the "FontWeight" property area.
"Kelly" wrote:
> Hi all -
> I have a report that I need to duplicate in RS. What I'm trying to do is put
> some conditional formatting on my detail textboxes.
> I want the text to be bold if the field = certain things. ie. If the line
> detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc I
> need that line to be bolded.
> I'm trying to use an IIF statement as an expression for the textbox's
> Format/FontWeight property, but am having trouble compiling.
> Here's what I've tried:
> =IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD, fontweight => NORMAL)
> =IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
> Of course, I will eventually need to convert this into some kind of OR
> statment because there are 7 different possible line details that should be
> bolded.
> I appreciate any help you could give. Thanks in advance!
> -Kelly
>
>|||For more complex conditionals you can use switch() in place of an iif(). For
example:
=Switch(Fields!linedetail.Value="Trade Sales", "Bold",
Fields!linedetail.Value="Invoiced Sales", "Bold",
Fields!linedetail.Value="Net Sales", "Normal")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fiona Harrison" <FionaHarrison@.discussions.microsoft.com> wrote in message
news:9E52BF14-C73D-4C2F-ABC7-5844C6ABBE19@.microsoft.com...
> Hi Kelly
> I have found the following works.
> = iif(rtrim(Fields!ObjectCode.Value) = "Your text", "Bold","Normal")
> But is has to go in the "FontWeight" property area.
> "Kelly" wrote:
>> Hi all -
>> I have a report that I need to duplicate in RS. What I'm trying to do is
>> put
>> some conditional formatting on my detail textboxes.
>> I want the text to be bold if the field = certain things. ie. If the line
>> detail on the report = "Invoiced Sales", "Trade Sales", "Net Sales"..etc
>> I
>> need that line to be bolded.
>> I'm trying to use an IIF statement as an expression for the textbox's
>> Format/FontWeight property, but am having trouble compiling.
>> Here's what I've tried:
>> =IIF(Fields!linedetail.Value = "NET INCOME", fontweight = BOLD,
>> fontweight =>> NORMAL)
>> =IIF(Fields!linedetail.Value = "NET INCOME", BOLD, NORMAL)
>> Of course, I will eventually need to convert this into some kind of OR
>> statment because there are 7 different possible line details that should
>> be
>> bolded.
>> I appreciate any help you could give. Thanks in advance!
>> -Kelly
>>

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 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
> >>
>

conditional aggregate?

Is there any way to add some sort of condition, or where clause to an
aggregate function? I have a report with three nested lists - the detail is
in the inner list, list3. In the second (middle) list, I want to count the
number of rows for column x where column y is not equal (<>) "This".
I have tried adding a textbox in the 3rd list to tally rows where the
condition satisfies a count or a 'tic', and then attempted to add another
expression in list 2 that would sum the new textbox values in list 3, but am
ripping my hair out with error messages - what is the one about 'aggregates
can only be used on report items in the header and footer'? As far as I
understand, you can't really use Fields in the header or footer anyway - so
how one would use an aggregate function in that manner is beyond me...
Anyway, I know Crystal offers a variety of ways to accomplish this type of
thing - is there anyway to do a sum or a count for field X in a detail group
where field Y in the same detail group meets a certain criteria?
Thanks,Myles:
You can do a count in a group based on the condition or expression.
You can do soemthing like this:
=RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
Or you can have this expression for you count:
CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
Hope this answers your question. For more information, search for
RunningValue in reporting services on msdn.
"Myles" wrote:
> Is there any way to add some sort of condition, or where clause to an
> aggregate function? I have a report with three nested lists - the detail is
> in the inner list, list3. In the second (middle) list, I want to count the
> number of rows for column x where column y is not equal (<>) "This".
> I have tried adding a textbox in the 3rd list to tally rows where the
> condition satisfies a count or a 'tic', and then attempted to add another
> expression in list 2 that would sum the new textbox values in list 3, but am
> ripping my hair out with error messages - what is the one about 'aggregates
> can only be used on report items in the header and footer'? As far as I
> understand, you can't really use Fields in the header or footer anyway - so
> how one would use an aggregate function in that manner is beyond me...
> Anyway, I know Crystal offers a variety of ways to accomplish this type of
> thing - is there anyway to do a sum or a count for field X in a detail group
> where field Y in the same detail group meets a certain criteria?
>
> Thanks,|||thank you sam, I will give that a try!
"sam" wrote:
> Myles:
> You can do a count in a group based on the condition or expression.
> You can do soemthing like this:
> =RunningValue(IIf(y.value<>"This",X,Nothing),Count,"grpName")
> Or you can have this expression for you count:
> CountDisctinct(IIf(y.value<>"This",X,Nothing),"grpName")
> Hope this answers your question. For more information, search for
> RunningValue in reporting services on msdn.
> "Myles" wrote:
> > Is there any way to add some sort of condition, or where clause to an
> > aggregate function? I have a report with three nested lists - the detail is
> > in the inner list, list3. In the second (middle) list, I want to count the
> > number of rows for column x where column y is not equal (<>) "This".
> >
> > I have tried adding a textbox in the 3rd list to tally rows where the
> > condition satisfies a count or a 'tic', and then attempted to add another
> > expression in list 2 that would sum the new textbox values in list 3, but am
> > ripping my hair out with error messages - what is the one about 'aggregates
> > can only be used on report items in the header and footer'? As far as I
> > understand, you can't really use Fields in the header or footer anyway - so
> > how one would use an aggregate function in that manner is beyond me...
> >
> > Anyway, I know Crystal offers a variety of ways to accomplish this type of
> > thing - is there anyway to do a sum or a count for field X in a detail group
> > where field Y in the same detail group meets a certain criteria?
> >
> >
> > Thanks,