how to add percent to matrix AND on it's own row? Very Hard

Question

Post reply

how to add percent to matrix AND on it's own row? Very Hard

KoldCoffee

SSCoach

Points: 18971
More actions
April 1, 2015 at 12:44 pm

#295631

I think the key reason I am having trouble getting a % within the matrix is that I have row groupings which restrict the available data, at the time that I need to make a comparison between that row groupings total and the ungrouped data's metric.
or said another way, is it possible to create an switch statement that says, give me a count of all "External" with ReferralID=1 and divide that by a count of All Referral IDs and pivot by month. Am willing to use a second dataset or use any clever mechanism to make it happen.
would love someone to review and see if they have ideas on this.
how to add % rows to a matrix?
Need SSRS to look like this.
Ref Type--------------row description----------------Jan----------Feb----------Mar----------Apr--->
1--------------------------contacted--------------------2-------------1-------------1-----------0----->
1-------------------------%contacted-----------------66%----------100%---------50%----------0%---->
1--------------------------interviewed------------------1-------------0-------------1------------1----->
1-------------------------%interviewed---------------33%----------0%-----------50%----------100%-->
2--------------------------contacted-------------------3-------------5--------------2-----------3----->
2-------------------------%contacted-----------------75%---------83%----------100%---------100%-->
2--------------------------interviewed-----------------1-------------1---------------0-------------0----->
2-------------------------%interviewed---------------25%----------16%----------0%-----------0%---->
I am able to build the matrix but without the % rows. I am using an IIF expression to accomplish that as follows:
=iif(Fields!row_description.Value = "Internal", count(Fields!ReferralID.Value),
count(Fields!ReferralID.Value)
)
But I don't know how to build it out to return the % ROWS underneath corresponding row_descriptions :
RDL attached.
DDL for table and data
--create table
CREATE TABLE [dbo].[Referral](
[ReferralID] [INT] NOT NULL,
[RefDate] [SMALLDATETIME] NULL,
[RefType] [VARCHAR](20) NULL,
[row_description] [VARCHAR](20) NULL)
--data for table
INSERT INTO [dbo].[Referral](ReferralID, [RefDate] ,[RefType], [row_description])
VALUES
(1,'2015-01-01',1,'contacted'),
(2, '2015-01-01',1,'interviewed'),
(3,'2015-01-01',2,'interviewed'),
(4,'2015-01-01',2,'contacted'),
(5,'2015-01-01',2,'contacted'),
(6,'2015-02-01',1,'contacted'),
(7,'2015-02-01',2,'contacted'),
(8,'2015-02-01',2,'interviewed'),
(9,'2015-02-01',2,'contacted'),
(10,'2015-02-01',2,'contacted'),
(11,'2015-01-15',1,'contacted'),
(12,'2015-02-20',2,'contacted'),
(13,'2015-01-15',2,'contacted'),
(14,'2015-02-20',2,'contacted'),
(15,'2015-03-01',1,'interviewed'),
(16,'2015-03-12',2,'contacted'),
(17,'2015-03-04',1,'contacted'),
(18,'2015-03-22',2,'contacted'),
(19,'2015-04-01',1,'interviewed'),
(20,'2015-04-12',2,'contacted'),
(21,'2015-04-04',2,'contacted'),
(22,'2015-04-22',2,'contacted');

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

Looks like if you created a subreport for this part:

1--------------------------contacted--------------------2-------------1-------------1-----------0----->

1-------------------------%contacted-----------------66%----------100%---------50%----------0%---->

1--------------------------interviewed------------------1-------------0-------------1------------1----->

1-------------------------%interviewed---------------33%----------0%-----------50%----------100%-->

You could do the % under that, and you'd be good to go, right?

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

That's the defacto report. Do you mean something SSRS-y when you say 'sub_report'?

I'm able to create the above matrix, minus the % rows and it's the % rows I need to incorporate.

Also, I can't have the % at the very bottom. They need to be ordered as I have shown.

Does that make sense? Doable?

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Grrr... I hate SSRS... in a minute I'm going to give up and try this in Excel!!! SSRS absolutely BLOWS!!!

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

I am going to change my query, that's how frustrated I am. I have begun, and am testing.

pietlinden SSC Guru Points: 63428 More actions · Answer 5

I give up (for now)... this is as close as I got. Can't figure out how to do the

Count

Percent

part. The rest works a champ.

pietlinden SSC Guru Points: 63428 More actions · Answer 6

I think I got it... and it's not even an April Fool's joke...