Need help calculating a summary report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Need help calculating a summary report

Author

Topic

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-16 : 01:19:24

Hello everyone,
I'm trying to build a report that calculates a summary of all my applicants based on the center they're enrolled in.

I have built a table that displays all applicants and sorts them by the center and I can use the count function to get a count of ALL applicants for all centers, but not a count of each center in one report:
example:

Applicant1 Field1 Field2 Field3 Center 1
Applicant2 Field1 Field2 Field3 Center 1
Applicant3 Field1 Field2 Field3 Center 1
Applicant4 Field1 Field2 Field3 Center 1
Applicant5 Field1 Field2 Field3 Center 2
Applicant6 Field1 Field2 Field3 Center 2
Applicant7 Field1 Field2 Field3 Center 2

I need the count of all applicants from center 1, center 2, etc..
In this example I need 4 for center 1, 3 for center 2 etc..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-17 : 12:57:22

Use SUM(IIF(condition)) in your expression

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-17 : 14:13:42

Thanks for the information. That does not work.

This is my table definition:

(Table Header) Applicant Name Date of Physical Date of Hearing Center ID
(Table Detail) =Fields!Name.value =Fields!Expr2.Value =Fields!Expr2.Value =Fields!idCenter.Valu

When I run this report, I get 50 applicants, some of them don't have physicals or hearing tests, so I get 42 for physical and 40 for hearing. out of this 50, abot 10 are from center 1, 20 from center 2, etc..

I need to return how many physicals from center 1, how many from center 2 etc.. The total should add up tothe total number of physicals for all centers.

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 07:20:32

Are you planning to show totals & deatils together?

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-18 : 12:11:40

No, just the totals.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 12:17:54

Then you can very well do this in SSRS with SUM(IIF()) expresiion. Just group on the required field and give expression of type SUM(IIF(Field!Centre.Value="Centre1" and <condition for physical here>,field,0)) to get the reqd sum.

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-18 : 12:28:28

I did try that and it kind of worked, now the issue is: In the example above, I need to specify the "Center #" for each expression, is there a way to do this for all centers? in other words can I do this in a table that uses the specific center number and shows me for example 10 totals, if there are 10 centers?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 12:54:45

You mean to concatenate the number to Centre as Centre1,Centre2,...?
This can be one using function RowNumber(). just concatenate this with text "Centre"

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-18 : 13:08:17

No, currently my expression looks like this:
=Sum(IIf((Fields!idCenter.Value=11) And (IsDate(Fields!Expr2.Value)) ,1, 0))

In other words for center #11, if there is a value in field Expr2, I add one. This gives me the correct total for center #11.
But this expression is outside of a table. I need to do this in a table for all centers.

Thanks for all your help.

CaliDavid
Starting Member

9 Posts

Posted - 2008-03-19 : 15:03:31

Problem Solved...

I had to re-group my table based on the Center. I even added a drilldown feature so I can see the data as well as the summary.
"Visakh16" thanks for your help and advise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources