August 11, 2008 at 2:39 pm
Hello,
I have a Matrix report that is based on the following query in Reporting Services:
-- This view selects the count of total purchases in a given month during a fiscal year
-- I only included the stmt for the first month of the year and only one fiscal year, but
-- the view uses this same sql for all twelve months and multiple fiscal years.
SELECT COUNT(pr_key) AS PRCount, DELIVERY_DATE, PURCHASER, DELIVERY_STATUS,
CASE WHEN DELIVERY_STATUS_V.DELIVERY_DATE BETWEEN '10/1/2007' AND
'09/30/2008' THEN '2008' END AS PromiseYear, CASE WHEN CAST(datepart(month,
DELIVERY_STATUS_V.DELIVERY_DATE) AS varchar(2)) = '1' THEN 'JAN' END AS PromiseMonth
FROM DELIVERY_STATUS_V.DELIVERY_DATE
GROUP BY DELIVERY_DATE, DELIVERY_STATUS, CASE WHEN DELIVERY_STATUS_V.DELIVERY_DATE BETWEEN '10/1/2007' AND
'09/30/2008' THEN '2008' END, CASE WHEN DELIVERY_STATUS_V.DELIVERY_DATE
CAST(datepart(month,DELIVERY_STATUS_V.DELIVERY_DATE) AS varchar(2)) = '1' THEN 'JAN' END, PURCHASER
HAVING (DELIVERY_DATE BETWEEN @StartDate AND @EndDate)
Using this query I've set up a matrix with the following Row Group and Column Groups:
row group:
matrix1_purchaser - grouped on "PURCHASER"
column groups:
matrix1_PromiseYear - grouped on "PromiseYear"
matrix1_PromiseMonth - grouped on "PromiseMonth"
matrix1_DELIVERY_STATUS - grouped on "DELIVERY_STATUS"
I've set the matrix up this way because I'm trying to determine the percentage of "DELIVERY_STATUS" of MET/NOT met for a particular month in a particular year for each Purchaser. However, the matrix is returning the same percentages on a per month basis for each purchaser:
2008
OCT
MET NOT MET
Purchaser1 83% 17%
Purchaser2 83% 17%
Purchaser3 83% 17%
In the data field of the matrix, I'm using the following expression to get the percentage for each Purchaser:
=Format(sum(Fields!PRCount.Value, "matrix1_DELIVERY_STATUS")/sum(Fields!PRCount.Value, "matrix1_PromiseMonth"),"P2")
Can anyone point me in the right direction so the percentages I'm looking for will be calculated on a per purchaser basis, like this:
2008
OCT
MET NOT MET
Purchaser1 83% 17%
Purchaser2 72% 29%
Purchaser3 45% 55%
Thank you,
Mark
August 12, 2008 at 11:55 am
Mark Eytcheson (8/11/2008)
... I've set up a matrix with the following Row Group and Column Groups:row group:
matrix1_purchaser - grouped on "PURCHASER"
column groups:
matrix1_PromiseYear - grouped on "PromiseYear"
matrix1_PromiseMonth - grouped on "PromiseMonth"
matrix1_DELIVERY_STATUS - grouped on "DELIVERY_STATUS"
I've set the matrix up this way because I'm trying to determine the percentage of "DELIVERY_STATUS" of MET/NOT met for a particular month in a particular year for each Purchaser. However, the matrix is returning the same percentages on a per month basis for each purchaser:
2008
OCT
MET NOT MET
Purchaser1 83% 17%
Purchaser2 83% 17%
Purchaser3 83% 17%
In the data field of the matrix, I'm using the following expression to get the percentage for each Purchaser:
=Format(sum(Fields!PRCount.Value, "matrix1_DELIVERY_STATUS")/sum(Fields!PRCount.Value, "matrix1_PromiseMonth"),"P2")Can anyone point me in the right direction so the percentages I'm looking for will be calculated on a per purchaser basis, like this:
2008
OCT
MET NOT MET
Purchaser1 83% 17%
Purchaser2 72% 29%
Purchaser3 45% 55%
Thank you,
Mark
You don't need to specify the delivery status scope.
sum(Fields!PRCount.Value)/sum(Fields!PRCount.Value, "matrix1_PromiseMonth")
should give you the % of met/no met within a month. if possible, specify a format on the textbox rather than the expression to make maintenance easier.
August 12, 2008 at 12:22 pm
Thanks Antonio,
But eliminating that scope gives me the percentage of "Met" or "Not Met" for each month per the year for each user.
Ex:
2008 2008 etc... 2008
OCT NOV Total
Met Not Met Met Not Met Met Not Met
Purchaser1 10% 35% 12% 2% 100% 100%
Purchaser2 50% 25% 5% 15% 100% 100%
I was trying to calculate the percentage of "Met" and "Not Met" for just the particular month per each purchaser.
2008 2008
OCT NOV
Met Not Met Met Not Met
Purchaser1 10% 90% 59% 41%
Purchaser2 35% 65% 5% 95%
August 13, 2008 at 7:13 am
Mark Eytcheson (8/12/2008)
Thanks Antonio,But eliminating that scope gives me the percentage of "Met" or "Not Met" for each month per the year for each user.
Ex:
2008 2008 etc... 2008
OCT NOV Total
Met Not Met Met Not Met Met Not Met
Purchaser1 10% 35% 12% 2% 100% 100%
Purchaser2 50% 25% 5% 15% 100% 100%
I was trying to calculate the percentage of "Met" and "Not Met" for just the particular month per each purchaser.
2008 2008
OCT NOV
Met Not Met Met Not Met
Purchaser1 10% 90% 59% 41%
Purchaser2 35% 65% 5% 95%
a matrix cell can't directly access another portion of the matrix. for example, "OCT" can't access "NOV". that's why "Met" can't access "Not Met". DELIVERY_STATUS should be represented as seperate columns in your query and the DELIVERY_STATUS grouping should be eliminated.
SELECT COUNT(pr_key) AS PRCount,
COUNT(case when DELIVERY_STATUS = 'X' then pr_key end) as PRMet,
COUNT(case when DELIVERY_STATUS != 'X' then pr_key end) as PRNotMet,
DELIVERY_DATE, PURCHASER, DELIVERY_STATUS,
...
FROM DELIVERY_STATUS_V.DELIVERY_DATE
GROUP BY DELIVERY_DATE, --DELIVERY_STATUS,
CASE WHEN ...
Add two detail columns under MONTH as PRMet / PRCount, and PRNotMet / PRCount.
August 13, 2008 at 7:30 am
Thanks Antonio, although I was afraid you would say that:)
I'll give this a shot. Thanks again for your help.
August 21, 2008 at 9:06 am
Hi Antonio,
I got pulled away from this particular issue for a few days so I hadn't had time to work on it. Just got back to looking at your solution and it worked perfectly. Thanks again for pointing me in the right direction!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply