SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS display certain field to another field.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ShySQLTeamFantasy
Starting Member

USA
4 Posts

Posted - 10/18/2012 :  13:27:03  Show Profile  Reply with Quote


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

5155 Posts

Posted - 10/18/2012 :  14:04:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/18/2012 :  14:15:08  Show Profile  Reply with Quote
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/

Go to Top of Page

ShySQLTeamFantasy
Starting Member

USA
4 Posts

Posted - 10/18/2012 :  15:15:14  Show Profile  Reply with Quote
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/



Go to Top of Page

ShySQLTeamFantasy
Starting Member

USA
4 Posts

Posted - 10/18/2012 :  15:16:21  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000