Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report Builder 3.0 - Shared Dataset - Computed Field If statement error? Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 9:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
I'm doing a proof of concept on self-service BI with report builder for a department here at work. As a user of BIDS for a long while I'm just messing around with Report Builder to see how self-service it can actually be. I can create shared datasets and reports pretty easily, but I'm trying to create a computed field to see if I can do a 'case like' statement on some data, as is pretty common for end users. I'm using an IF statement. Pretty common. I'm getting a weird error, and my google fu has not helped me solve this problem.

My set up: 2008 R2: Cube to Report Model to Report Builder 3.0.

My simple IF statement:



Error text:

===================================

An error occurred while executing the query.
An error occurred when the query ran. Refer to the inner exception for details. (Microsoft SQL Server Report Builder)

===================================

An error occurred when the query ran. Refer to the inner exception for details. (mscorlib)

------------------------------
Program Location:


Server stack trace:
at Microsoft.ReportingServices.RsClient.RsCommand.ExecuteRsDataReader()
at Microsoft.ReportingServices.RsClient.RsCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Microsoft.ReportingServices.RsClient.RsCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.RsClient.RsCommand.ExecuteReader()
at Microsoft.ReportingServices.DataExtensions.ReportServerDataProvider.RSDPCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.<>c__DisplayClass1.<ExecuteActiveQueryCallback>b__0()
at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)

===================================

The remote server returned an error: (500) Internal Server Error.
<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsSemanticQueryEngineError</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">Semantic query execution failed. </Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsSemanticQueryEngineError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=10.50.4000.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">10.50.4000.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>Microsoft.ReportingServices.SemanticQueryEngine</Source><Message msrs:ErrorCode="rsSemanticQueryEngineError" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsSemanticQueryEngineError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=10.50.4000.0" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Semantic query execution failed. </Message><MoreInformation><Source>Microsoft.AnalysisServices.Modeling</Source><Message> A grouping expression must be either a scalar field or an entity. </Message></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" /></detail>

===================================

The remote server returned an error: (500) Internal Server Error. (System)

------------------------------
Program Location:

at System.Net.HttpWebRequest.GetResponse()
at Microsoft.ReportingServices.RsClient.RsCommand.ExecuteRsDataReader()



Any ideas? I've tried IIF (returns error - Report Builder uses IF) I've tried ; instead of , but it doesn't like the semicolon either. I've tried much more simple formulas like ID + 1 and I get the same error. I find it odd I can find a handful of people talking about this on the net, but nobody has solved it.


-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1501245
Posted Thursday, October 3, 2013 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
I think the issue is this dataset is being created from a report model based on a cube. Possibly the cube is asking for MDX, but report builder can't convert. Same reasoning by which a cube based report model can't be edited or built using a report model project in BIDS?

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1501296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse