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