Report Builder 3.0 - Shared Dataset - Computed Field If statement error?
-
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 -
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply