May 29, 2012 at 12:40 pm
Hello,
I have the following tables:
create table match
(
matchid int Identity,
customerid int,
profileid int,
)
Create table customer
(
customerid int identity,
customerfirstname varchar(250),
customerlastname varchar(250)
)
Create table profile
(
profileid int identity,
profilefirstname varchar(250),
profilelastname varchar(250)
)
what the match table has i wrote a script where it will compare the profile table first name and last name to see if it matches any customers in customer table, and if there is a match, then add match data like the customerid and profile id to the match table and of course have a unique id to the match for table purposes.
what I need help is i am new to writing expressions on ssrs, I have everything populated like this:
select b.*, a.*
from customer a
Join profile b
on a.profileid = b.customerid
so i get everything that matches thats fine, works great, want to thank Sean Lange again for his help including showing me what i need to do for posting. Anyways what i would like is there will be duplicate matches for example
Bob dillen might show up twice on the match of course i will only see numbers on the match table which i can reference back to customer and profile table:
match table:
matchid customerid profileid
1 1 7
2 1 7
3 1 8
4 1 15
5 2 9
6 3 12
7 4 Null
8 5 Null
etc. what i would like the SSRS to do is say if customer shows up more than once, and has more than 1 profileid, then make it red, if there is a match but is only once, then make it yellow, else no match then just make the background white like the match id of 7 and 8.
Hope this explains it all please let me know.
Thank you in advance
May 29, 2012 at 2:57 pm
I would just make my dataset query like this:
SELECT [customerid]
,COUNT([profileid]) AS ProfileCount
FROM [match]
GROUP BY [customerid]
Then just base your color expression off of the value of ProfileCount
May 29, 2012 at 2:58 pm
I suppose if you want to expose all the detail data rather than my solution above, you could add a grouping at the customer level and then Count the detail rows at the group level and have that show your various colors.
May 29, 2012 at 3:26 pm
hey Daniel,
thanks for your replies,
yes please, I should do a group by customer id, then use count for the profile id correct? then the count will determine the color, does that make sense, how would i write that in an expression?
thank you again.
May 30, 2012 at 6:52 am
In loose terms
SWITCH(Fields!ProfileCount.Value > 1, 'Red",Fields!ProfileCount.Value = 1,"Yellow",1=1,"White")
I did not check to see if these are all valid colors. Also you would have to use this at the group level. You might also consider adding a visibility expression for the detail on the groups with more than 1 result so you can see exactly which records are duplicates.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply