April 2, 2012 at 12:58 pm
Hello All,
I have a report that has three grouping levels (1st Level, 2nd Level & Details). At the Details level I have three column values in my report (Column 1, Column 2 & Column 3). I am trying to determine if there is a way to see if a value is duplicated in Column 1 of the Details level. If the value is duplicated, I wanted to conditionally format the entire details row where each of the duplicates exist?
Do you have any pointers or suggestions on how I could achieve this functionality?
Thanks Steve
April 2, 2012 at 2:59 pm
You should be able to use a Row_Number or Rank function in your SQL to order your data and build your conditional visibility.
I wrote a blog on using Row_Number to resolve discrepancies in aggregation level that may get you pointed in the right direction.
April 3, 2012 at 12:48 pm
Thanks for the suggestion but I am having trouble wrapping my head around the concepts. I used the AdventureWorks DB below to help illustrate my problem. But there one important difference between the database I have to use and the AdventureWorks database there is no primary key in my table to search against.
So my first step was to find all of the employees who have been rehired.
SELECT DISTINCT A.FirstName, A.LastName, A.StartDate, A.EndDate, 'Y' AS Rehired
FROM dbo.DimEmployee A
JOIN dbo.DimEmployee B ON
A.FirstName = B.FirstName AND
A.LastName = B.LastName
WHERE ISNULL(A.EndDate, '01/01/1900') <> ISNULL(B.EndDate, '01/01/1900')
But I can't for the life of me figure out how to generate a query which would give me the opposite of the set above. I figured once I had that query I could UNION the two queries for my SSRS report and highlight those rows with Rehired = "Y".
Any suggestions?
Thanks Steve
April 3, 2012 at 1:46 pm
I am a bit confused. In your initial question you talked about 3 levels of grouping. I don't see data that would have 3 levels of grouping in your example query. Are you on to a different issue?
Also what do you mean by the opposite of the query you posted? If you can't figure out how to do it with code, can you put it into words? Right now your where clause contains a simple <>, to me an = sign is the opposite of that. But if you union all that data together that will be all the data in your source so there is no need for the where clause to begin with.
April 4, 2012 at 5:10 am
Sorry that I confused you. My intent was to show my issue using a sample db since I am not able to send out information on my db. The only difference is that this sample db has a primary key that I could use to resolve this issue quickly and the db that I have to work with does not have a primary key. That being said, let me restate my scenario based on this dataset.
I have a listing of all employees in the company that I am reporting against. I am grouping this dataset by DepartmentName (1st level) and Title (2nd level) and then in the details I am including the employees' LastName, FirstName, StartDate and EndDate.
I would like to enhance the report by highlighting those employees that have been rehired by the company. So I came up with
SELECT DISTINCT A.FirstName, A.LastName, A.DepartmentName, A.Title, A.StartDate, A.EndDate, 'Y' AS Rehired
FROM dbo.DimEmployee A
JOIN dbo.DimEmployee B ON
A.FirstName = B.FirstName AND
A.LastName = B.LastName
WHERE ISNULL(A.EndDate, '01/01/1900') <> ISNULL(B.EndDate, '01/01/1900')
My problem is how do I join the rehired column back into the original dataset so that I can use it in my report?
April 4, 2012 at 7:08 am
OK, I think I understand. Would this work for you?
SELECT DISTINCT A.FirstName, A.LastName, A.DepartmentName, A.Title, A.StartDate, A.EndDate,
CASE WHEN ISNULL(A.EndDate, '01/01/1900') <> ISNULL(B.EndDate, '01/01/1900')
THEN 'Y'
ELSE 'N' END AS Rehired
FROM dbo.DimEmployee A
JOIN dbo.DimEmployee B ON
A.FirstName = B.FirstName AND
A.LastName = B.LastName
April 4, 2012 at 7:29 am
Sorry that doesn't work, the results set contains everything in my first query but it also contains all of the records from the DimEmployee table with Rehired set to 'N' (i.e. DimEmployee contains 296 total rows, my query returns 11, and your query returns 307).
The only way I can think of doing this is to try and figure out a query that returns all of the rows in DimEmployee that were not part of me query's result set and then union this result with my original query result. But without a unique identifier for the rows I am beginning to think that this may not be possible.
April 4, 2012 at 10:50 am
So if I understand correctly, my query returns 11 employees twice, one each with a Y and one each with an N?
If my query did not return the 11 employees when they are marked with an N, would that be the result set you want?
I assume there is a new employee record created when an employee is rehired.
April 5, 2012 at 5:04 am
Yes you are correct, 11 are duplicated and a new record is created each time an employee is hired.
I was able to get the result set I wanted with the query below but I had to use a primary key which I do not have in the view that I am dealing with.
I had to attached the code as image since the forum looked up when I tried to add it into the post normally.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply