Cube Browser Member report

travis1585 · July 14th, 2011, 09:39 PM

When I open up the cube browser member report and open up the dataset in the query designer, I get the following error when I attempt to execute the query:
Parser: The syntax for ',' is incorrect.

The report actually renders, but I was just curious about the reason I get that error when trying to run the dataset in the query designer...

Regards,

Travis

GWadeCF · December 27th, 2011, 04:36 PM

I have Adventure Works DW 2008 R2 cube on installed on my server, and I'm running into a similar issue. When I try to change the "Date" dimension by clicking on the date field after "for" in the report title of the Cube Browser report, I get the error message "Query execution failed for dataset 'DataSet1'. Parser: The syntax for ',' is incorrect.

I think the error is coming from the DataSet1 code in the Cube

Grant Paisley, are you there?

Thanks!

GWadeCF · December 27th, 2011, 06:22 PM

Hmm... this is a pretty major issue as I can't seem to make the Cube Browser report work without the Cube Browser Member report.

I thought the issue might be in the "Rows" section of the MDX query for DataSet1. Code has

Code:

 NON EMPTY {
 (
--  STRTOMEMBER(@pDateMbr) : STRTOMEMBER(@pDateMbr + ".lag(" +  Str([Measures].[Lag] - 1) + ")" )

--*
--     STRTOSET("{" + [Measures].[RowFocus] + ".parent.siblings, "+ [Measures].[RowFocus] + ".siblings, " + [Measures].[RowFocus] + ".children}" )

 STRTOSET(
   "{" +
   GENERATE( 
      ASCENDANTS(STRTOMEMBER([Measures].[RowFocus]) )
      ,STRTOVALUE([Measures].[RowFocus] + ".Hierarchy.CURRENTMEMBER.Uniquename")
      ,".siblings, "
   )
   + ","
   + [Measures].[RowFocus] + ".children"
   + "}"
 )

 )
} 
ON ROWS 

FROM [Adventure Works]

There's a comma after the word 'siblings' in the literal ".siblings, ", and then another one in the '+","' line.

However, when I comment that extra '+","' out, I get an error when trying to run the Cube Browser Member report, and the Cube Browser errors out as well.

Any help much appreciated.

angrykoala · January 13th, 2012, 07:43 AM

The "Cube Browser Member report" is utilized by the main report to select another member value for Date or the Filter.

It *does* have a bug where it did not work for dimensions with no All level. This did not give the problem you described but it would not show the current member siblings when you opened the report.

Fix this one line of code as shown below:

STRTOSET(
"{" +
GENERATE(
{ASCENDANTS(STRTOMEMBER([Measures].[RowFocus]) ) }
,
STRTOVALUE([Measures].[RowFocus] + ".Hierarchy.CurrentMember.Uniquename")
, ".Siblings, "
)
+ ".Siblings," -- <=== add the .siblings here! (for hierarchies with no ALL level)
+ [Measures].[RowFocus] + ".children"
+ "}"
)

Note: I'm not sure of the reason for the error described below. If you could post the actual query you are attempting to run in query designer including the parameters I can look into this further...

Grant

GWadeCF · January 16th, 2012, 12:34 PM

Hi, Grant,

Thanks for your response. I'm really looking forward to getting this report running. It's going to be extremely helpful.

Here's the code for DataSet1 of the Cube Member report, straight from the download:

Code:

------------------------------------------------------------------------------------------------------------------------------------
-- Grant Paisley
-- Angry Koala
-- 28 Nov 2008
------------------------------------------------------------------------------------------------------------------------------------
WITH
--/*
MEMBER [Measures].[Mbr_Key]   AS iif(@pDriver="Date", @pDateMbr ,
                                                           iif(@pDriver="Rows", @pRowMbr,
                                                           iif(@pDriver="Columns", @pColMbr,
                                                           iif(@pDriver="Filter", @pFilterMbr,
                                                           @pDateMbr ))))
--*/
--MEMBER [Measures].[Mbr_Key]   AS StrToValue(@pMbrKey)


MEMBER [Measures].[Measure_Value] AS StrToValue(@pMeasure)

MEMBER [Measures].[Measure_Label] AS StrToValue(@pMeasure + ".Member_Name")

MEMBER [Measures].[RowFocus] AS -- returns hierarchy all level value if changing rows otherwise current focus
       strtovalue( '"' +  [Measures].[Mbr_Key] + '"' )

-- MEMBER [Measures].[Row_Key]   AS StrToValue( @pRowMbr + ".Hierarchy.Currentmember.Uniquename" )
MEMBER [Measures].[Row_Key]   AS StrToValue( [Measures].[Mbr_Key]  + ".Hierarchy.Currentmember.Uniquename" )

MEMBER [Measures].[Row_Label] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.CurrentMember.Member_Caption" )
MEMBER [Measures].[Row_Level] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.CurrentMember.Level.Ordinal" )
MEMBER [Measures].[Row_Level_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.Level.Name" )
MEMBER [Measures].[Row_Hierarchy_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.Name" )
MEMBER [Measures].[Row_Hierarchy_UniqueName] AS StrToValue( [Measures].[Mbr_Key] + ".Hierarchy.UniqueName" )
MEMBER [Measures].[Row_Dimension_Name] AS StrToValue( [Measures].[Mbr_Key] + ".Dimension.Name" )
MEMBER [Measures].[Row_Dimension_UniqueName] AS StrToValue( [Measures].[Mbr_Key] + ".Dimension_Unique_Name" )


MEMBER [MEASURES].MbrIsAncestor AS  
       strtovalue("IsAncestor( " + [Measures].[Mbr_Key] + ".hierarchy.currentmember, " + [Measures].[Mbr_Key] + " )"
                      + " or ( " + [Measures].[Mbr_Key] + ".hierarchy.currentmember is " + [Measures].[Mbr_Key] + " )"
       )


SELECT NON EMPTY {

  [Measures].[Measure_Label] ,

 [Measures].[Mbr_Key],
 [Measures].[RowFocus],
 [Measures].[Row_Key],
 [Measures].[Row_Label],
 [Measures].[Row_Level],
 [Measures].[Row_Level_Name],
 [Measures].[Row_Hierarchy_Name],
 [Measures].[Row_Hierarchy_UniqueName],
 [Measures].[Row_Dimension_Name],
 [Measures].[Row_Dimension_UniqueName],
 [Measures].[MbrIsAncestor],

 [Measures].[Measure_Value] 


} ON COLUMNS, 

NON EMPTY {
 (
--  STRTOMEMBER(@pDateMbr) : STRTOMEMBER(@pDateMbr + ".lag(" +  Str([Measures].[Lag] - 1) + ")" )

--*
--     STRTOSET("{" + [Measures].[RowFocus] + ".parent.siblings, "+ [Measures].[RowFocus] + ".siblings, " + [Measures].[RowFocus] + ".children}" )

 STRTOSET(
   "{" +
   GENERATE( 
      ASCENDANTS(STRTOMEMBER([Measures].[RowFocus]) )
      ,STRTOVALUE([Measures].[RowFocus] + ".Hierarchy.CURRENTMEMBER.Uniquename")
      ,".siblings, "
   )
   + ","
   + [Measures].[RowFocus] + ".children"
   + "}"
 )

 )
} 
ON ROWS 

FROM [Adventure Works]

And here's the error message I get when I try to run the query in the Query Designer:

TITLE: Microsoft Visual Studio
------------------------------

Query preparation failed.

------------------------------
ADDITIONAL INFORMATION:

Parser: The syntax for ',' is incorrect. (Microsoft SQL Server 2008 R2 Analysis Services)

------------------------------
BUTTONS:

OK
------------------------------

I get the same issue if I replace the last STRTOSET code with the code you provided.

Thanks again for your help.

GWadeCF · January 16th, 2012, 12:45 PM

Hi, Grant,

The Cube Browser report returns no data results even before the Cube Member report is accessed. It returns a raft of warnings, all variations on the theme "Warning 1 [rsMissingFieldInDataSet] The dataset âDataSet1â contains a definition for the Field âMeasure_Valueâ. This field is missing from the returned result set from the data source. C:\teamserver\SQL Reports\Secure\BI\Cube Reports\Cube Browser.rdl 0 0
"
I was attributing this to the Cube Member report issue, but now I'm realizing that this is probably a separate issue.

Thanks

GWadeCF · January 16th, 2012, 04:08 PM

Hi, Grant,

Good news. I think I may have found the issue that brought me to a standstill.

The first issue (which is my fault) was that I had started trying to point the Cube Browser report at one of my cubes, and set the default measure to one of my cube measures.

I reverted these back to the Adventure Works values, but the Cube Browser still didn't run properly.

Then I noticed the default date was [Date].[Calendar].[Month].&[2004]&[4]. The Adventure Works DW 2008R2 cube data begins in July, 2005. When I manually updated the default date to [Date].[Calendar].[Month].&[2005]&[7], presto, the Cube Browser report works properly.

Not only does the Cube Browser report work properly, but the Cube Member report works properly when called from the Cube Browser report with a valid date.

I changed the date parameter default in the Cube Member report, and it works properly as a stand-alone as well. Better and better.

I'm still having trouble adding the bug fix you shared. When I try to edit the Cube Member DataSet 1 query, I still get the "syntax for ',' is incorrect." error. Any thoughts on how to make that work?

Incidentally, the "Angry Koala" versions with the graphs in them also have the issue with the default parameters. I've changed the default date parameters in all reports, and they're all now producting results. However, the Cube Metadata and Angry Koala Cube Driver reports sometimes show incomplete results and do not link back to the calling report properly. I'm looking into this.

Thanks again for your help.

djphatic · September 10th, 2012, 03:03 PM

I'm just working through 2-3 Cube Metadata and I was getting a similar error message when inputting the query string via an expression:

SELECT * FROM $System.MDSCHEMA_CUBES WHERE CUBE_SOURCE =1

If you switch the query designer to DMX using the pick hammer icon and enter the query it works fine. Hope that helps.