Solved

SSRS Count Function

Posted on 2009-04-08
5
176 Views
Last Modified: 2012-08-13
In SQL Server Reporting Services, I have a column "Status" in my report. Somewhere in the footer of the report, I would like to Count how many "New" jobs are there.

In the Status column, the possible values are (New, Cancelled, Rescheduled).

Somewhere in the footer I want to see:
NEW: ##
RESCHEDULED: ##

Is there a way to do this with the help of "Expressions"?
0
Comment
Question by:pzozulka
  • 3
  • 2
5 Comments
 
LVL 8

Author Comment

by:pzozulka
ID: 24102162
Just in case, I wanted to insert the SQL code used for this report. Also, here are all the options for the Status column.

CASE
WHEN Jobs.Status = 800 THEN 'Request New'
WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'
WHEN Jobs.Status = 803 THEN 'Confirmed'
WHEN Jobs.Status = 806 THEN 'Turned In'
WHEN Jobs.Status = 812 THEN 'Cancelled'
WHEN Jobs.Status = 807 THEN 'Billed'
WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'
END AS 'Status'
IF DatePart(Weekday,Getdate()) = 2

BEGIN

SELECT Jobs.JobNo,

Convert(Varchar,Jobs.JobDate,101) as JobDate,

Convert(Varchar,Jobs.Entered,101)  AS 'Entered',

CASE

WHEN Jobs.Status = 800 THEN 'Request New'

WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'

WHEN Jobs.Status = 803 THEN 'Confirmed'

WHEN Jobs.Status = 806 THEN 'Turned In'

WHEN Jobs.Status = 812 THEN 'Cancelled'

WHEN Jobs.Status = 807 THEN 'Billed'

WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'

END AS 'Status',

Jobs.OrderedBy AS 'Ordered By',

"Contacts with Firm".Contact_DisplayName AS 'Ordering Contact',

"Contacts with Firm".Contact_MainPhone AS 'Contact Phone',

"Contacts with Firm".Contact_Email AS 'Contact Email',

"Contacts with Firm".Firm_FirmName AS 'Firm',

"Contacts with Firm".Firm_City AS 'Firm City',

CASE WHEN Cases.CaseName IS NULL THEN 'Case Name NOT in RB'

ELSE Cases.CaseName END AS 'Case Name',

CASE WHEN R.DisplayName IS NULL THEN '.Bluebird - (New Client)'

ELSE R.DisplayName END AS 'SalesRep',

"Contacts with Firm".Firm_Source AS 'Firm Source',

Case 

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_FirmName

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocName

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_FirmName

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_FirmName)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.LocName

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.LocName)

END AS Location,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_Address

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocAddr

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_Address

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_Address)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.Address

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.Address)

END AS Address,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_City

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocCity

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_City

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_City)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.City

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.City)

END AS City,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_State

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then (Select State.StateName

        From RB8.dbo.JobsLoc

        JOIN RB8.dbo.State ON State.StateNo = JobsLoc.LocStateNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

        GROUP BY State.StateName)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_State

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_State)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select State.StateName

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        JOIN RB8.dbo.State ON State.StateNo = Locations.StateNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY State.StateName)

END AS State,

U.DisplayName AS 'EnteredBy'

FROM 

(RB8.dbo.Jobs Jobs

JOIN RB8.dbo."Contacts with Firm" "Contacts with Firm" ON

Jobs.OrdContactNo = "Contacts with Firm".Contact_ContactNo

LEFT OUTER JOIN RB8.dbo.Cases Cases ON Jobs.CaseNo = Cases.CaseNo

JOIN RB8.dbo.JobsLoc JobsLoc ON Jobs.JobNo = JobsLoc.JobNo

LEFT OUTER JOIN RB8.dbo.Resources R ON Jobs.SalesRep = R.RsrcNo

LEFT OUTER JOIN RB8.dbo.Users U ON U.UserNo = Jobs.EnteredBy)

LEFT OUTER JOIN (

SELECT DISTINCT JSL.JobNo, 'Resch' as 'R'

FROM JobsStatusLog JSL

WHERE JSL.Remarks LIKE 'Rescheduled%') JSL1 ON JSL1.JobNo = Jobs.JobNo

WHERE 

Jobs.Entered>=Convert(Varchar,Getdate()-3,101)

And Jobs.Entered<=Convert(Varchar,Getdate(),101)

ORDER BY R.DisplayName

END
 
 
 

									ELSE
 
 
 

BEGIN

SELECT Jobs.JobNo, 

Convert(Varchar,Jobs.JobDate,101) as JobDate,

Convert(Varchar,Jobs.Entered,101)  AS 'Entered',

CASE

WHEN Jobs.Status = 800 THEN 'Request New'

WHEN Jobs.Status = 801 AND JSL1.R IS NULL THEN 'New'

WHEN Jobs.Status = 803 THEN 'Confirmed'

WHEN Jobs.Status = 806 THEN 'Turned In'

WHEN Jobs.Status = 812 THEN 'Cancelled'

WHEN Jobs.Status = 807 THEN 'Billed'

WHEN JSL1.R IS NOT NULL THEN 'Rescheduled'

END AS 'Status',

Jobs.OrderedBy AS 'Ordered By',

"Contacts with Firm".Contact_DisplayName AS 'Ordering Contact',

"Contacts with Firm".Contact_MainPhone AS 'Contact Phone',

"Contacts with Firm".Contact_Email AS 'Contact Email',

"Contacts with Firm".Firm_FirmName AS 'Firm',

"Contacts with Firm".Firm_City AS 'Firm City',

CASE WHEN Cases.CaseName IS NULL THEN 'Case Name NOT in RB'

ELSE Cases.CaseName END AS 'Case Name',

CASE WHEN R.DisplayName IS NULL THEN '.Bluebird - (New Client)'

ELSE R.DisplayName END AS 'SalesRep',

"Contacts with Firm".Firm_Source AS 'Firm Source',

Case 

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_FirmName

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocName

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_FirmName

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_FirmName)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.LocName

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.LocName)

END AS Location,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_Address

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocAddr

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_Address

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_Address)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.Address

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.Address)

END AS Address,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_City

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then JobsLoc.LocCity

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_City

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_City)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select Locations.City

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY Locations.City)

END AS City,

Case

 When JobsLoc.SameLocOrdering = 1 Then "Contacts with Firm".Firm_State

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

 Then (Select State.StateName

        From RB8.dbo.JobsLoc

        JOIN RB8.dbo.State ON State.StateNo = JobsLoc.LocStateNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 0

        GROUP BY State.StateName)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1 

 Then (Select "Contacts with Firm".Firm_State

        From RB8.dbo."Contacts with Firm" "Contacts with Firm",

        RB8.dbo.JobsLoc

        Where JobsLoc.FirmNo = "Contacts with Firm".Firm_FirmNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 1

        GROUP BY Firm_State)

 When JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

 Then (Select State.StateName

        From RB8.dbo.Locations JOIN RB8.dbo.JobsLoc

		ON JobsLoc.LocNo = Locations.LocNo

        JOIN RB8.dbo.State ON State.StateNo = Locations.StateNo

        AND JobsLoc.SameLocOrdering = 0 AND JobsLoc.LocLinkType = 2

        GROUP BY State.StateName)

END AS State,

U.DisplayName AS 'EnteredBy'

FROM 

(RB8.dbo.Jobs Jobs

JOIN RB8.dbo."Contacts with Firm" "Contacts with Firm" ON

Jobs.OrdContactNo = "Contacts with Firm".Contact_ContactNo

LEFT OUTER JOIN RB8.dbo.Cases Cases ON Jobs.CaseNo = Cases.CaseNo

JOIN RB8.dbo.JobsLoc JobsLoc ON Jobs.JobNo = JobsLoc.JobNo

LEFT OUTER JOIN RB8.dbo.Resources R ON Jobs.SalesRep = R.RsrcNo

LEFT OUTER JOIN RB8.dbo.Users U ON U.UserNo = Jobs.EnteredBy)

LEFT OUTER JOIN (

SELECT DISTINCT JSL.JobNo, 'Resch' as 'R'

FROM JobsStatusLog JSL

WHERE JSL.Remarks LIKE 'Rescheduled%') JSL1 ON JSL1.JobNo = Jobs.JobNo

WHERE

Jobs.Entered>=Convert(Varchar,Getdate()-1,101)

And Jobs.Entered<=Convert(Varchar,Getdate(),101)

ORDER BY R.DisplayName

END

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24102308
try
=Sum(IIF(Fields!Status.Value = "New", 1, 0))
 etc.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 24102362
That worked, but how do I put into that box along with that expression the literal word "New" in front of the expression?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24102436
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24102464
this should work
don't know what happend that last post
="New: " & Sum(IIF(Fields!Status.Value = "New", 1, 0))

Open in new window

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now