| Author |
 Topic  |
|
|
ShySQLTeamFantasy
Starting Member
USA
4 Posts |
 Posted - 10/18/2012 : 13:27:03
|
This is how I got the percentages for column the '%Change of most recent year".
=((Last(Fields!Quantity.Value,"Child") - First(Fields!Quantity.Value)) / First(Fields!Quantity.Value))
= ((54675 - 55968)/55968 ) = -2.31%
= ((54675 - 57849)/57849) = -5.49%
It will always take the first year '2012' in this case and get the percentages against each other year. If I enter the years 2005,2004,2003,2002,2001 it will always take the first year and do a percentages against each additional year. 2005 to 2004, 2005 to 2003, 2005 to 2002 and so on. I can have as many as 2 column (year) to many columns.
I need to do it for the Total and Subtotal but it won't work because it's in a different scope.
data is = row Child group
Sub Total: = row Parent group
Total: = row Total group
Year = Column Period group
Query use to get result - It's a whole bunch of unions join together to get the data. This is the first union statement.
SELECT MEMBERSHIP_CODE
, PERIOD, COUNT(DISTINCT ID) AS Distinct_ID
, SUM(QUANTITY) AS Quantity
, '01-Personal' AS Child
, '01-Overall' AS Parent
, 'Total' as Total
FROM vf_Sshot AS vfs
INNER JOIN vProd AS vP ON vfs.PRODUCT_CODE = vP.PRODUCT_CODE
INNER JOIN vMem_Type vMT on vMT.Member_Type = vfs.Member_Type
WHERE (PERIOD IN ( (SELECT Val from dbo.fn_String_To_Table(@Periods,',',1))))
AND (vMT.MEMBER_TYPE NOT IN ('a','b','c'))
AND (vfs.STATUS IN ( 'A', 'D', 'C'))
AND (MEMBERSHIP_CODE NOT IN ('ABC', 'DEF' ))
and vP.PROD_TYPE in ('DUE','MC','SC')
and vMT.Member_Record = '1'
GROUP BY MEMBERSHIP_CODE, PERIOD
I would even be happy to know how I could 57,540 to show up in the blank total fields.
TOTAL: 57,540 57,540 58994 57,540 61,114 57,540
If I could get that I know I can get the rest!
Any ideas would be greatly appreciated? |
Edited by - ShySQLTeamFantasy on 10/18/2012 14:25:12
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/18/2012 : 14:04:08
|
I didn't quite follow what you are asking - so this is only a guess - see in redSELECT MEMBERSHIP_CODE,
PERIOD,
COUNT(DISTINCT ID) AS Distinct_ID,
SUM(QUANTITY) AS Quantity,
'01-Personal' AS Child,
'01-Overall' AS Parent,
SUM(SUM(QUANTITY)) OVER() AS Total
FROM vf_Sshot AS vfs
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/18/2012 : 14:15:08
|
From what I understood you should be using expression based on Inscope operator. something like
IIF (Incope("YourChildGroup"),your current expression, your totalexpression)
see
http://visakhm.blogspot.com/2010/01/inscope-operator-in-sql-reporting.html
total expression would be something like
=((Last(Fields!Quantity.Value,"Parent") - SUM(Fields!Quantity.Value,"Child")) / SUM(Fields!Quantity.Value,"Child"))
I guess
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ShySQLTeamFantasy
Starting Member
USA
4 Posts |
Posted - 10/18/2012 : 15:15:14
|
By using Inscope()
How would I produce this output?
TOTAL: 57,573 58,941 57,573 61,188 57,573 61,175 57,175
quote:
Originally posted by visakh16
From what I understood you should be using expression based on Inscope operator. something like
IIF (Incope("YourChildGroup"),your current expression, your totalexpression)
see
http://visakhm.blogspot.com/2010/01/inscope-operator-in-sql-reporting.html
total expression would be something like
=((Last(Fields!Quantity.Value,"Parent") - SUM(Fields!Quantity.Value,"Child")) / SUM(Fields!Quantity.Value,"Child"))
I guess
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
ShySQLTeamFantasy
Starting Member
USA
4 Posts |
Posted - 10/18/2012 : 15:16:21
|
This is the output that I want. If I can get this output I can then take it and do my calculations.
TOTAL: 57,573 58,941 57,573 61,188 57,573 61,175 57,175
quote:
Originally posted by sunitabeck
I didn't quite follow what you are asking - so this is only a guess - see in redSELECT MEMBERSHIP_CODE,
PERIOD,
COUNT(DISTINCT ID) AS Distinct_ID,
SUM(QUANTITY) AS Quantity,
'01-Personal' AS Child,
'01-Overall' AS Parent,
SUM(SUM(QUANTITY)) OVER() AS Total
FROM vf_Sshot AS vfs
|
|
|
| |
Topic |
|
|
|
SSRS display certain field to another field.
