Help with StrToSet

Who is online?  0 guests and 0 members
Home  »  Forums   »  microsoft business intelligence   »  bi query languages (mdx, dmx)   » Help with StrToSet

Help with StrToSet

Topic RSS Feed

Posts under the topic: Help with StrToSet

Posted: 1/17/2012

aganatra
aganatra
Jedi Youngling 4  points  Jedi Youngling
  • Joined on: 11/23/2010
  • Posts: 2

Hello,

Listed is the MDX statement I am using in one of my reports. When I execute this in management studio, I am getting the values for column1 however, when I execute the same mdx in ssrs, column 1 is empty. Listing 1 is the mdx query and listing 2 is the profiler trace of this query.

Listing 1:

SELECT

{

[Measures].[Sales Units]

,[Measures].[Sales Coupons]

,[Measures].[Cost Of Sales]

,[Measures].[% Parent Inventory (Value)]

,[Measures].[Inventory Value]

,[Measures].[Turn]

,[Measures].[GM Ratio]

,[Measures].[Average Unit Cost]

}

ON COLUMNS,

{

 

 

StrToSet("[Item].[Number Of Users].[Number Of Users]")

*[Item].[Division].[Division]

*[Item].[Department].[Department]

*[Item].[Category].[Category]

}

ON ROWS From

(

Select StrToSet("[Item].[Division].[All]") on Columns From

(

Select StrToSet("[Item].[Department].&[PERSONAL ITEMS]") on Columns From

(

Select StrToSet("[Item].[Category].[All]") on Columns From RFP)))

Where

({StrToSet

("[Location].[Cat-Loc].[All]")},

{(

StrToMember("[Time].[Date].&[2011-09-15]",CONSTRAINED):StrToMember("[Time].[Date].&[2011-09-19]",CONSTRAINED))})

Listing 2:

SELECT

{

[Measures].[Sales Units]

,[Measures].[Sales Coupons]

,[Measures].[Cost Of Sales]

 

--,[Measures].[Average Inventory Value]

,[Measures].[% Parent Inventory (Value)]

,[Measures].[Inventory Value]

,[Measures].[Turn]

,[Measures].[GM Ratio]

,[Measures].[Average Unit Cost]

}

ON COLUMNS,

{

 

--[Item].[Targeted Gender].[Targeted Gender]

 

StrToSet(@pAttributeClass)

*[Item].[Division].[Division]

*[Item].[Department].[Department]

*[Item].[Category].[Category]

}

ON ROWS

From

(

Select StrToSet(@pDivision) on Columns From

 

--(Select StrToSet(@pAttributeClass) on Columns From

(

Select StrToSet(@pDepartment) on Columns From

(

Select StrToSet(@pCategory) on Columns From RFP)))

 

--)

Where

({StrToSet(@pLocation)},

{(

StrToMember(@pStartDate,CONSTRAINED):StrToMember(@pEndDate,CONSTRAINED))})

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">

<Parameter>

<

Name>pDivision</Name>

<

Value xsi:type="xsd:string">[Item].[Division].[All]</Value>

</Parameter>

<Parameter>

<

Name>pDepartment</Name>

<

Value xsi:type="xsd:string">[Item].[Department].&amp;[PERSONAL ITEMS]</Value>

</Parameter>

<Parameter>

<

Name>pCategory</Name>

<

Value xsi:type="xsd:string">[Item].[Category].[All]</Value>

</Parameter>

<Parameter>

<

Name>pAttributeClass</Name>

<

Value xsi:type="xsd:string">{[Item].[Number Of Users].[Number Of Users]}</Value>

</Parameter>

<Parameter>

<

Name>pLocation</Name>

<

Value xsi:type="xsd:string">[Location].[Cat-Loc].[All]</Value>

</Parameter>

<Parameter>

<

Name>pStartDate</Name>

<

Value xsi:type="xsd:string">[Time].[Date].&amp;[2012-01-09]</Value>

</Parameter>

<Parameter>

<

Name>pEndDate</Name>

<

Value xsi:type="xsd:string">[Time].[Date].&amp;[2012-01-15]</Value>

</Parameter>

</Parameters>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">

<

Catalog>RFP_Amit</Catalog>

<LocaleIdentifier>1033</LocaleIdentifier>

<Format>Tabular</Format>

<

Content>SchemaData</Content>

<Timeout>0</Timeout>

<ReturnCellProperties>

true</ReturnCellProperties>

<DbpropMsmdFlattened2>

true</DbpropMsmdFlattened2>

</PropertyList>
Page 1 of 1 (1 items)