Forum Discussion
Unable To Render Paginated Report once published.
I have a paginated report that works fine locally. It connects to SQL server, once published it goes via our Enterprise gateway using a connector that works for our regular published Power BI reports. The credentials and the connector work correctly as our other reports refresh fine and I have used the same credentials locally without issue. It is just once published the paginated report does not work. It allows me to select my input parameters (which themselves are queries against the SQL server) and then when I select View Report I get the following long error message:
A data source used by this report returned an error. Received error payload from gateway service with ID 68403: Async operation a6e6f486-d0ee-45ba-be05-e114b3df6971 is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.DataAccessException: An exception encountered while accessing the target data source GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError GatewayVersion=3000.194.16 ---> [1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: ArgumentNullException:Value cannot be null. Parameter name: key GatewayPipelineErrorCode=DM_GWPipeline_UnknownError GatewayVersion= InnerType=ArgumentNullException InnerMessage=Value cannot be null. Parameter name: key InnerToString=System.ArgumentNullException: Value cannot be null. Parameter name: key at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument) at System.Collections.Generic.Dictionary`2.FindEntry(TKey key) at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.GetTableDescriptor(IDataReader dataReader, IDbCommand command) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_1.<<ExecuteAdoQueryAsync>b__1>d.MoveNext() InnerCallStack= at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument) at System.Collections.Generic.Dictionary`2.FindEntry(TKey key) at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.GetTableDescriptor(IDataReader dataReader, IDbCommand command) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_1.<<ExecuteAdoQueryAsync>b__1>d.MoveNext() ([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:) at System.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument) at System.Collections.Generic.Dictionary`2.FindEntry(TKey key) at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.GetTableDescriptor(IDataReader dataReader, IDbCommand command) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_1.<<ExecuteAdoQueryAsync>b__1>d.MoveNext() --- End of inner exception stack trace --- ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.DataAccessException.StackTrace:) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_1.<<ExecuteAdoQueryAsync>b__1>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.Metrics.PipelineMetricUtils.<MeasureQueryOperationAsync>d__6`1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass4_0.<<ExecuteAdoQueryAsync>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<DispatchImpl>d__1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<>c__DisplayClass0_0.<<Dispatch>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayOperationResultSpooler.<RunOperationImplAsync>d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayOperationResultSpooler.<>c__DisplayClass7_0.<<RunOperationAsync>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInTopLevelActivity>d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.Internal.DiagnosticsContext.DiagnosticsContextExecutor.<ExecuteInTopLevelActivity>d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayOperationResultSpooler.<RunOperationAsync>d__7.MoveNext(). An exception encountered while accessing the target data source Value cannot be null. Parameter name: key.
Since I can access the drop down for my parameters which are simple SELECTs against tables it doesn't seem like it doesn't work at all. My queries are as follows:
Param1:
SELECT DISTINCT [param1] FROM [DB].[Schema].[Table1] ORDER BY [param1] ASC
Param2:
SELECT DISTINCT [param1],[param2] FROM [DB].[Schema].[Table1]
WHERE [param1] = (@param1)
ORDER BY [param2] ASC
(Again, these two parameter queries appear to work as they appear in the drop down to be selected)
Main Data:
SELECT * FROM FROM [DB].[Schema].[Table2]
WHERE Table2.Field1 = 0 AND Table2.Field2=1 AND Table2.Field3 = (@param2) AND Table2.Field4 = (@param1)
Any help appreciated. Searching pieces from the error message hasn't been fruitful as it seems to be generic and overlap with other problems from different products.
4 Replies
There is no issue with the gateway, we have other reports both standard Power BI reports and Paginated reports refreshing via this gateway.
Hi MOVC ,
If I understand correctly, the issue is that you couldn’t render paginated report when you published the report. Please try the following methods and check if they can solve your problem:
1.Check data source settings in Power BI Service.
2.Ensure that the parameters in the report are correctly configured to accept the values. In the rdl file in Parameter Properties, you need to select it as well as allow null values and republish the report to the Power BI service.
Solved: unable to render paginated report with text param... - Microsoft Fabric Community
If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format or screenshot to make a deep troubleshooting? It would be helpful to find out the solution.
Looking forward to your reply.
Best Regards,
Wisdom Wu
Apologies for not closing this out sooner (Christmas period...). We found the cause of the issue which is that Gateway refreshes do not support "Complex" data types. In this case we were importing a SQL Server data Type Geometry which is required for the Paginated Report map visual but this doesn't appear to be supported in the web service. Unfortunately we will have to find an alternative method.
