1

If I have two Dimensions: Origin and Destination how can I count the number of times Origin = Destination?

SELECT [Location] ON COLUMNS
FROM [DELIVERIES]
WHERE ( [Origin] = [Destination] )

[Origin] = [Destination] obviously isnt the answer...

The following code produces the correct result:

SELECT
    SUM(Transfers) AS Same_Skill_Transfers
FROM (

    SELECT 
        CONVERT(varchar(30),"[From VQ].[LOB].[LOB].[MEMBER_CAPTION]") As From_VQ, 
        CONVERT(varchar(30),"[To VQ].[LOB].[LOB].[MEMBER_CAPTION]") As To_VQ, 
        CONVERT(integer,"[Measures].[Transfers]") As Transfers
    FROM
        OPENQUERY(TRANSFERS,'
            SELECT 
                {[Measures].[Transfers]} ON COLUMNS,
                Filter(NonEmptyCrossjoin(  [From VQ].[LOB].Members,  [To VQ].[LOB].Members),
                            [From VQ].[LOB].[LOB].[Life Cycle].Properties(''Caption'') =  [To VQ].[LOB].[LOB].[Life Cycle].Properties(''Caption'')
                ) ON 1
            FROM 
                [Transfers]
            WHERE (
                [Date].[Date Hierarchy].[Month].[July 2014], 
                [From VQ].[Hierarchy].[AOB].[Consumer],
                [From Agent].[Employee Id].&[612117]
            )

        ')
) A
WHERE
    To_VQ = From_VQ

Lets say I have a [FROM_VQ].[LOB] = "BOTH" and I want to include in the SUM when [FROM_VQ].[LOB] = "Both" and [TO_VQ].[LOB] is either A or B?

Gemione
  • 65
  • 7
  • 1
    It looks like you are new to MDX. [Here's](http://www.mssqltips.com/sqlservertip/2916/comparison-of-queries-written-in-tsql-and-sql-server-mdx/) a good comparison of MDX vs SQL. You're going to want to include [Dimension].[Attribute] for each field. Otherwise, the Multidimensional query engine will guess which field you mean and you may not like the answer. If you can tell me the dimension and the field for location and destination I can answer your question. Also, are these origin and destination role playing dimensions so they have the same key and structure for the location field? – mmarie Sep 03 '14 at 01:58
  • 3
    I'm newer to MDX than Rob Ford is to sobriety. Im not sure if the Keys are the same, but the captions are. To be honest, I don't even know what information to give you such that you are able to answer my question effectively. Super String Theory is easier for me to wrap my head around. – Gemione Sep 03 '14 at 15:05
  • Can you browse the cube in Excel or SSMS and get a screenshot of the Location, deliveries, origin, and destination fields? Also, here's a link to help you convert sql to MDX http://www.mssqltips.com/sqlservertip/2916/comparison-of-queries-written-in-tsql-and-sql-server-mdx/ – mmarie Sep 03 '14 at 15:13

1 Answers1

3

Your question is missing some info, but I'll take a stab and then update once you provide more info. I'm assuming that there are two dimensions, origin and destination, and that each of these dimensions have a location attribute.

In general, you can use a filter statement for this. If your location attribute has the same key in each dimension, you can do this: 

select [Origin].[Location].children on 0, 
Filter(NonEmptyCrossjoin( [Origin].[Location].Children, [Destination].[Location].Children), 
[Origin].[Location].Properties('Key') = [Destination].[Location].Properties('Key')
) on 1 from [DELIVERIES]

If the keys aren't the same, but the label you see for the field is you can switch Key for Caption.

Here's an example where someone did this with dates.

mmarie
  • 5,598
  • 1
  • 18
  • 33