Parsing Error : Invalid Token Line 1 Offset 16,\

Question

with MEMBER [Measures].[ReportingLevel] as

IIF([Measures].[Level]=1,[PERIOD].[PERIOD].currentmember.Properties("YEAR MST - VAL" ),

IIF([Measures].[Level]=2, [PERIOD].[PERIOD].currentmember.Properties( "HALF YEAR MST - VAL" ),

IIF([Measures].[Level]=3,[PERIOD].[PERIOD].currentmember.Properties("QUARTER MST - VAL" ),
IIF([Measures].[Level]=4,[PERIOD].[PERIOD].currentmember.Properties("MONTH MST - VAL" ),
IIF([Measures].[Level]=5,[PERIOD].[PERIOD].currentmember.Properties("WEEK MST - VAL" ),[PERIOD].[PERIOD].Currentmember.Properties( "VAL" ))
 ) ) ) )

Member [Measures].[Name] as [PERIOD].[PERIOD].Currentmember.name
Member [Measures].[Level] as [PERIOD].[PERIOD].Level.Ordinal
member [Measures].[CoveredOutlet] as[Measures].[VISIT]
member [Measures].[SalesManCompliance] as round((([Measures].[ATTENDENCE COUNT]/[Measures].[ATTENDENCE])*100),2)
member [Measures].[OutletTot] as [Measures].[COVERAGE Count]
member [Measures].[ProdOutletTot] as[Measures].[PROD ID]
member [Measures].[ProdOutletPerc] as([Measures].[PROD ID]/ [Measures].[VISIT]) *100

member [Measures].[CoveredOutletPerc] as([Measures].[CoveredOutlet]/[Measures].[OutletTot] ) *100

member [Measures].[DropSize] as Round(([Measures].[SALE QTY]/[Measures].[Sale Prod Drop Outlet Count]),1)

 

member [Measures].[SALE QTYCov] as ([Measures].[SALE QTY])

select  {[Measures].[SalesManCompliance],[Measures].[ATTENDENCE],[Measures].[SALE QTYCov],[Measures].[DropSize],[Measures].[ProdOutletPerc],

[Measures].[CoveredOutletPerc], [Measures].[CoveredOutlet],[Measures].[OutletTot],[Measures].[ProdOutletTot],

[Measures].[ReportingLevel],[Measures].[Name]} on 0,
([OUTLET].[WD].[WD].members)*([OUTLET].[TOWN].[TOWN].Members)
on 1
FROM
(
SELECT  strtomember(@user)* strtoset(@OutletCube)*{STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate)} ON 0
FROM
[GPIL]

)

Friends , the time i add the  strtomember(@user) Parameter in the sub Query it is showing the below error

The following Syntax error occuredduring parsing  Invalid Token Line 1 Offset 16,\

Please help ..

Answer 1

FROM (SELECT  strtomember(@user) on columns
FROM (SELECT (strtoset(@OutletCube) on columns 
FROM (SELECT STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate) ON columns 
FROM 
[GPIL]
)))

try this

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 2

Raunak, will u please provide the complete Syntax after the 

([OUTLET].[WD].[WD].members)*([OUTLET].[TOWN].[TOWN].Members)

because it is still showing same .

Answer 3

Suggestion - Simplify your query first and try follow the syntax given in this article -

http://msdn.microsoft.com/en-us/library/ms146022.aspx

Answer 4

Raunak, while executing the query with this in MDX Editor ..It is running fine . But when i put it in SSRS Report DataSet Query . It is Showing "The Syntax for 'on' is incorrect. Please help .

Answer 5

Karan, i tried with using this . but it is showing the same .INvalid token Offset ...

Answer 6

Raunak, while executing the query with this in MDX Editor ..It is running fine . But when i put it in SSRS Report DataSet Query . It is Showing "The Syntax for 'on' is incorrect. Please help .

Backtrack. Run the SQL Profiler while you execute the report. Otai the MDX from SQL Profiler and now debug the MDX obtained from the it.

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 7

NO Raunak , i did it .. but at last came the same Erro Invalid Token Offset 16

this is my Parameter details that are Passing ---

"

with MEMBER [Measures].[ReportingLevel] as
  IIF([Measures].[Level]=1,[PERIOD].[PERIOD].currentmember.Properties( "YEAR MST - VAL" ),
   IIF([Measures].[Level]=2, [PERIOD].[PERIOD].currentmember.Properties( "HALF YEAR MST - VAL" ), 
    IIF([Measures].[Level]=3,[PERIOD].[PERIOD].currentmember.Properties( "QUARTER MST - VAL" ),
     IIF([Measures].[Level]=4,[PERIOD].[PERIOD].currentmember.Properties( "MONTH MST - VAL" ),
       IIF([Measures].[Level]=5,[PERIOD].[PERIOD].currentmember.Properties( "WEEK MST - VAL" ),[PERIOD].[PERIOD].Currentmember.Properties( "VAL" ))
      )
     )
    )
   )
Member [Measures].[Name] as [PERIOD].[PERIOD].Currentmember.name
Member [Measures].[Level] as [PERIOD].[PERIOD].Level.Ordinal

member [Measures].[CoveredOutlet] as
[Measures].[VISIT]
member [Measures].[SalesManCompliance] as
round((([Measures].[ATTENDENCE COUNT]/[Measures].[ATTENDENCE])*100),2)
member [Measures].[OutletTot] as
[Measures].[COVERAGE Count]
member [Measures].[ProdOutletTot] as
[Measures].[PROD ID]

member [Measures].[ProdOutletPerc] as
([Measures].[PROD ID]/ [Measures].[VISIT]) *100

member [Measures].[CoveredOutletPerc] as
([Measures].[CoveredOutlet]/[Measures].[OutletTot] ) *100

member [Measures].[DropSize] as
Round(([Measures].[SALE QTY]/[Measures].[Sale Prod Drop Outlet Count]),1)

member [Measures].[SALE QTYCov] as
([Measures].[SALE QTY])

select  {[Measures].[SalesManCompliance],[Measures].[ATTENDENCE],
[Measures].[SALE QTYCov],[Measures].[DropSize],[Measures].[ProdOutletPerc],
[Measures].[CoveredOutletPerc],
 [Measures].[CoveredOutlet],[Measures].[OutletTot],[Measures].[ProdOutletTot],
[Measures].[ReportingLevel],
[Measures].[Name]} on 0,

([OUTLET].[WD].[WD].members)*([OUTLET].[TOWN].[TOWN].Members)
on 1

FROM (SELECT strtomember(@user)on 0 FROM (SELECT strtoset(@OutletCube)on 0 FROM
(SELECT STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate)ON 0 FROM [GPIL]
)))

<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>OutletCube</Name>
          <Value xsi:type="xsd:string">{ [OUTLET].[REGION].[OUTLET].&amp;[98450],[OUTLET].[REGION].[OUTLET].&amp;[98451],[OUTLET].[REGION].[OUTLET].&amp;[98452],[OUTLET].[REGION].[OUTLET].&amp;[98453],[OUTLET].[REGION].[OUTLET].&amp;[98454],[OUTLET].[REGION].[OUTLET].&amp;[98455],[OUTLET].[REGION].[OUTLET].&amp;[98456],[OUTLET].[REGION].[OUTLET].&amp;[98457],[OUTLET].[REGION].[OUTLET].&amp;[98458],[OUTLET].[REGION].[OUTLET].&amp;[98459],[OUTLET].[REGION].[OUTLET].&amp;[98460],[OUTLET].[REGION].[OUTLET].&amp;[98461],[OUTLET].[REGION].[OUTLET].&amp;[98462],[OUTLET].[REGION].[OUTLET].&amp;[98463],[OUTLET].[REGION].[OUTLET].&amp;[98464],[OUTLET].[REGION].[OUTLET].&amp;[98465],[OUTLET].[REGION].[OUTLET].&amp;[98466],[OUTLET].[REGION].[OUTLET].&amp;[98467],[OUTLET].[REGION].[OUTLET].&amp;[98468],[OUTLET].[REGION].[OUTLET].&amp;[98469],[OUTLET].[REGION].[OUTLET].&amp;[98470],[OUTLET].[REGION].[OUTLET].&amp;[98471],
[OUTLET].[REGION].[OUTLET].&amp;[184452],[OUTLET].[REGION].[OUTLET].&amp;[184453],[OUTLET].[REGION].[OUTLET].&amp;[184454] }</Value>
  
     </Parameter>
        <Parameter>
          <Name>StartDate</Name>
  
       <Value xsi:type="xsd:string">[PERIOD].[PERIOD].[DAY].&amp;[732]</Value>
 
       </Parameter>
        <Parameter>
          <Name>EndDate</Name>
   
      <Value xsi:type="xsd:string">[PERIOD].[PERIOD].[DAY].&amp;[734]</Value>
   
    </Parameter>
        <Parameter>
          <Name>User</Name>

       
 <Value xsi:type="xsd:string">ABCDOMAIN\wiproadmin</Value>
      
 </Parameter>
      </Parameters>

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

<Catalog>GPI_Cubes</Catalog>
          <LocaleIdentifier>1033</LocaleIdentifier>
    
     <Format>Tabular</Format>
          <Content>SchemaData</Content>
          <Timeout>25</Timeout>
  
       <ReturnCellProperties>true</ReturnCellProperties>
          <DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>
   
   </PropertyList>

"

Answer 8

Hi Rakesh,

Try to replace the subquery like below -

FROM (SELECT { strtomember(@user) } on 0
      FROM (SELECT { strtoset(@OutletCube)} on 0
             FROM  ( SELECT {STRTOMEMBER(@StartDate):STRTOMEMBER(@EndDate)} ON 0
                     FROM [GPIL]
)))

The above part comparing with yours is adding some braces on 0 axies. If same error occurred, try them by adding one by one to see which one caused the error.

Hope this helpfully,

Regards,
Jerry

Answer 9

Hiiiiii Jerry ,all friends ......<o:p></o:p>

Greetings,<o:p></o:p>

I tried to do the steps as mentioned by you .... Below mentioned ERROR is causing error while Adding <o:p></o:p>

SELECT { strtomember(@user) }  is causing the Error <o:p></o:p>

my user name parameter value comes like this   -- "[USER].[User].&[DOMAINNAME\gabbot]" --

In the Parameter Name " User " code is written as below :

ParameterName(User) = Code.Username()

my Function code is :

-- > Public function username()  try Return Report.User!UserID catch return "System" end try End Function<o:p></o:p>

In the Query there are 2 cases tested .: 

Ist :

FROM (     SELECT strtomember(@user) on 0 

from [GPIL]  )

is causing the Error "Line 1 . Offset 16

2nd  :

where 

(strtomember(@user))  

My Query runs fine for this . But still  i also need the Rest parameters also like @WD and DateParameters 

Please help  ..

regds

Rakesh<o:p></o:p>

Answer 10

   Wowwwww.......At Last , I found the Solution .The root cause(Username) which was disturbing me madly and not even sleep Properly ......:) !!!!! 

   Thanks ,Thanks . a lot to all friends in this Loop. God Bless ....