Grouping 3 Expression Values to display one Value

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 2005 Forums

Analysis Server and Reporting Services (2005)

Grouping 3 Expression Values to display one Value

Author

Topic

PeeBee
Starting Member

1 Post

Posted - 2013-06-06 : 02:33:10

Hi All,
Using SRS 2005 Expressions I am trying to Aggregate or Group 3 Different row Values to Display 1 row Value and total $ of 3 rows below X10, X21, X23.

Dataset

Select
SADIVO AS Division, 
SAWHSL AS Location,
SUM (SSAJUN) as ActualSales,
SUM (SCAJUN)as ActualCosts,

FROM DATA.SASALES
WHERE   (SADIVO IN ('029', '022', '021')) AND (SAWHSL IN ('X10','X21','X23','B20','C30','D40')) 
GROUP BY   SADIVO, SAWHSL, SADIVS

my expression:


=IIF((Fields!Location.Value="A")=0 AND (Fields!Location.Value="X23" Or Fields!Location.Value="X10" OR Fields!Location.Value="X21"),"A", iif(fields!Location.value = "D40", "D", iif(fields!Location.value = "B20", "B",iif(fields!Location.value = "C30", "C",fields!Location.value))))

I wish to group all "A" into one "A" and total Sales
how can I achieve this have tried several different combinations No Go!
appreciate any help
thanks
Regards Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 02:43:05

you need to bring in a derived field in SELECT to make all those location as A

something like


Select
SADIVO AS Division, 
CASE WHEN SAWHSL IN ('X23','X10','X21') THEN 'A'
     WHEN SAWHSL = D40 THEN 'D'
     ...
END AS Location,
SUM (SSAJUN) as ActualSales,
SUM (SCAJUN)as ActualCosts,

FROM DATA.SASALES
WHERE   (SADIVO IN ('029', '022', '021')) AND (SAWHSL IN ('X10','X21','X23','B20','C30','D40')) 
GROUP BY   SADIVO, CASE WHEN SAWHSL IN ('X23','X10','X21') THEN 'A'
     WHEN SAWHSL = D40 THEN 'D'
     ...
END, SADIVS

then group using it inside your matrix

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources