Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports
This is the forum to discuss the Wrox book Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports by Paul Turley, Robert M. Bruckner; ISBN: 9780470563113
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 14th, 2011, 09:39 PM
Registered User
 
Join Date: Jul 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cube Browser Member report

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
Reply With Quote
  #2 (permalink)  
Old December 27th, 2011, 03:36 PM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Similar Issue with Cube Browser Report

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!

Last edited by GWadeCF; December 27th, 2011 at 05:11 PM. Reason: Left out an important word - 'cube' in first line.
Reply With Quote
  #3 (permalink)  
Old December 27th, 2011, 05:22 PM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default I think I may have found the issue

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.
Reply With Quote
  #4 (permalink)  
Old January 13th, 2012, 06:43 AM
Registered User
Points: 24, Level: 1
Points: 24, Level: 1 Points: 24, Level: 1 Points: 24, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2010
Posts: 4
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old January 16th, 2012, 11:34 AM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Code from Cube Member Dataset1

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.
Reply With Quote
  #6 (permalink)  
Old January 16th, 2012, 11:45 AM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Another significant, possibly related issue

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
Reply With Quote
  #7 (permalink)  
Old January 16th, 2012, 03:08 PM
Registered User
Points: 15, Level: 1
Points: 15, Level: 1 Points: 15, Level: 1 Points: 15, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2011
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Found several issues, may have resolved the problem

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.
Reply With Quote
  #8 (permalink)  
Old September 10th, 2012, 03:03 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i generate report from OLAP cube manoj_k79 Classic ASP Basics 0 January 24th, 2007 11:42 PM
Accessing cube in VB ashwinik VB Components 1 January 25th, 2005 05:09 AM
OLAP Cube question Frank79 SQL Language 1 November 3rd, 2004 02:50 PM
crystal report cached on browser renee_santoscoi Classic ASP Professional 3 May 20th, 2004 12:49 PM



All times are GMT -4. The time now is 07:19 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.