SSRS display certain field to another field.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS display certain field to another field.

Author

Topic

ShySQLTeamFantasy
Starting Member

4 Posts

Posted - 2012-10-18 : 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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 14:04:08

I didn't quite follow what you are asking - so this is only a guess - see in red

SELECT 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

52326 Posts

Posted - 2012-10-18 : 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

4 Posts

Posted - 2012-10-18 : 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

4 Posts

Posted - 2012-10-18 : 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 red

SELECT 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources