April 17, 2012 at 4:00 am
This is a simplified example but in a nutshell I have a tablix which is counting male customers, female customers against products A, B, C. I've added a total to this, via the 'Add totals' in SSRS, so the report looks something like this:
Product Total Male Female
A 100 80 20
B 50 40 10
C 40 10 30
Now, instead of having say 40 in the second row for Male and product B I'd want to have 90% (and the same for all other male/female entries).
The current expression to produce the values is : CountDistinct(Fields!customer_id.Value)
What's confusing me is the total column has the same expression... I've looked here:
http://msdn.microsoft.com/en-us/library/bb630415%28v=sql.100%29.aspx at the examples of specifying different scopes but I can't seem to get it to work, nor do I fully understand how it works.
I can see it must have something to do with the grouping - the fact that both expressions produce different (and correct) results, but I'm not sure how I use this in an expression I create.
Any help anyone?! Thanks!
April 17, 2012 at 8:31 am
Please list both the Total and the Male & Female expressions. It doesn't look like you have any grouping here (hard to tell) but if you don't then scope shouldn't really be an issue. If you are using some grouping, what are your group names? It just occurred to me that you may be using column groups, are you?
Are you trying to accomplish this as an output?
Product Total Male Female
A 100 80% 20%
B 50 80% 20%
C 40 25% 75%
April 17, 2012 at 8:46 am
Thanks for the reply, I should have responded sooner, I sussed out how to do this, I've basically been tweaking it and playing with it since. The problem was actually due to naming conventions really, so rather than the group name being 'gender' for example it was matrix1_gender,
=CountDistinct(Fields!Customer_id.Value)/CountDistinct(Fields!customer_id.Value,"Product_Description")
For example, with some slight changes, basically did what I was after.
The following page: http://technet.microsoft.com/en-us/library/dd255256.aspx was most useful, especially further down "Example Aggregate Expressions for a Table Data Region" section.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply