Filter Measure based on Dimension Data 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 2008 Forums

Analysis Server and Reporting Services (2008)

Filter Measure based on Dimension Data Value

Author

Topic

thisisboni
Starting Member

6 Posts

Posted - 2012-07-25 : 08:49:30

ABC is a Wholeseller and PQR is retail - the requirement is to report sales of PQR and the amount invoiced by ABC to PQR (This is done by reporting ABC's sales to PQR)

Dimension - Company Structure

CName Clocation
-------------------
ABC PQR
PQR

How do I include the invoiced amount (ABC's Sales to PQR) in the Query below ?

select {[Measures].[Amount Sold]} on 0,
([Date].[Quarter].children,[Date].[Month].children) on 1

FROM ( SELECT ( STRTOSET("PQR", CONSTRAINED) ) ON COLUMNS
from [SampleDW])

thisisboni
Starting Member

6 Posts

Posted - 2012-07-26 : 10:59:16

ok! so got this to work with the query below - how do I use a parameter to this for ''PQR'

with member [Measures].[Inv] as
(
SUM([CompStruct].[CName].&[ABC]*STRTOSEt('[CompStruct].[Clocation].&[PQR]')
,[Measures].[Amount Sold])
)

select {[Measures].[Amount Sold],[Measures].[Inv] } on 0,
([Date].[Quarter].children,[Date].[Month].children) on 1

FROM ( SELECT ( STRTOSET("PQR", CONSTRAINED) ) ON COLUMNS
from [SampleDW])

The query below returns NULL in the [INV]

with member [Measures].[Inv] as
(
SUM([CompStruct].[CName].&[ABC]*STRTOSEt("[CompStruct].[Clocation].&["+@param+"]")
,[Measures].[Amount Sold])
)

select {[Measures].[Amount Sold],[Measures].[Inv] } on 0,
([Date].[Quarter].children,[Date].[Month].children) on 1

FROM ( SELECT ( STRTOSET(@param, CONSTRAINED) ) ON COLUMNS
from [SampleDW])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources