August 20, 2010 at 3:04 pm
I'm having a problem setting up a measure to show the count of people who have a certain access level or higher. The person table has an accessID field, and there's a table of access levels with ID/Name.
What I need to see is a view that shows something like this:
Level1 : 500 ppl
Level2 : 235 ppl
Level3 : 40 ppl
I want each lower level to include all of the people who have the higher levels as well. I haven't been able to figure this out yet so figured I'd ask here.
August 21, 2010 at 5:36 am
This doesn't seem to have anything to do with Integration Services. But I will try to answer.
I would think that your query would be something like:
select sum( case when Level >= 1 then 1 else 0 end) as Level1,
sum( case when Level >= 2 then 1 else 0 end ) as Level2,
sum( case when Level >= 3 the 1 else 0 end) as Level3
from table
Russel Loski, MCSE Business Intelligence, Data Platform
August 23, 2010 at 11:49 am
I meant to put this under analysis services. Currently I'm pre-filtering the data for this view w/ a sql script because I was unable to figure out how to do it in the analysis. But that's limiting the ways I can view this info.
My current filtering query looks like this. I'd like to avoid doing it this way though and have analysis services do it, so I can group and filter the data by other columns.
SELECT RetentionId, COUNT(*) +
(SELECT COUNT(*) AS Expr1
FROM (SELECT DISTINCT c.Id, c.RetentionId
FROM Players AS c INNER JOIN
Characters ON c.Id = Characters.PlayerId
) AS b
WHERE (RetentionId > a.RetentionId)) AS RetentionTotal
FROM (SELECT DISTINCT c.Id, c.RetentionId
FROM Players AS c INNER JOIN
Characters AS Characters_1 ON c.Id = Characters_1.PlayerId
WHERE ) AS a
GROUP BY RetentionId
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply