February 14, 2006 at 3:53 pm
I'm trying to build a simple report in SSRS 2005. It lists the Store Contacts from the sample AdventureWorks database using the following query:
SELECT Sales.Store.Name, Person.ContactType.Name AS Type, Person.Contact.LastName, Person.Contact.FirstName, Person.Contact.Phone
FROM Sales.Store INNER JOIN
Sales.StoreContact ON Sales.Store.CustomerID = Sales.StoreContact.CustomerID INNER JOIN
Person.Contact ON Sales.StoreContact.ContactID = Person.Contact.ContactID INNER JOIN
Person.ContactType ON Sales.StoreContact.ContactTypeID = Person.ContactType.ContactTypeID
ORDER BY Sales.Store.Name, Person.Contact.LastName, Person.Contact.FirstName
I'd like to show two totals in the footer of the table: one for the total number of stores and the other for the total number of contacts. I'm not interested in grouping on the store name since this is just a simple alpha list.
I can get the total number of contacts by using the =CountRows() expression in the footer. How can I get the total number of stores? (A store may have more than one contact).
February 16, 2006 at 7:20 am
I would look into using the CountDistinct aggregate function for displaying the total number of stores
February 16, 2006 at 8:18 am
Thanks Thomas.
=CountDistinct(Fields!Name.Value) works like a champ.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply