|
thisisboni
Starting Member
4 Posts |
 Posted - 07/25/2012 : 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
4 Posts |
Posted - 07/26/2012 : 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])
|
 |
|
Filter Measure based on Dimension Data Value
