<p>I am developing reports for a college facilities department. My boss would like to see the how well the rooms are occupied during a given semester. The grouping levels are Campus--> Building -->Room. Getting the no. of rooms in a building was easy. I did a distinct Count of room number with in the building group. </p><p>Now, my actual question is, getting the no. of rooms with in a particular campus...... When I do the Disitnct count in the campus group level. It wouldn't give me appropriate results because a campus can have several rooms with the same room number but with different buildings. A clear example is below.......</p><p>Campus= Springfield</p><p> Building: Arts</p><p> Room: 101, 102, 104, 105, 108.......</p><p> Building: Science</p><p> Room: 101,201, 104, 105</p><p>From the above example I want the result to be 9, but I get 6 that is because distinct count. I can't do the regular count because every record in the details section is counted.</p><p> </p><p>Thank you.</p>