Conditional totals for matrix report

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

Conditional totals

I work in higher ed and have totals for student levels (undergrad, grad,
professional) and then total by institution and system. I have to total
systems that contain only community college information where there are only
undergrads. I would like to remove one of the totals based on the condition.
My example:
School Student Level #
**Community College First Time Freshmen 2100
Sophomore 1800
Total Undergrads 3900
Total at **Community College 3900
I would like to conditional remove the "Total Undergrads" for only community
colleges, but leave it for the universities within the state. Is there a way
of doing this so I don't have two totals giving the exact same total?
Thanks
--
ChrisI forgot to mention this is in a matrix format, where the sub-total give me
the situation in which I seek an answer.
Thank you much,
--
Chris
"cmcdavid" wrote:
> I work in higher ed and have totals for student levels (undergrad, grad,
> professional) and then total by institution and system. I have to total
> systems that contain only community college information where there are only
> undergrads. I would like to remove one of the totals based on the condition.
> My example:
> School Student Level #
> **Community College First Time Freshmen 2100
> Sophomore 1800
> Total Undergrads 3900
> Total at **Community College 3900
> I would like to conditional remove the "Total Undergrads" for only community
> colleges, but leave it for the universities within the state. Is there a way
> of doing this so I don't have two totals giving the exact same total?
> Thanks
> --
> Chris

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