1. 

   jw1n5, Member

   I swear I had this working last night but perhaps I was only selecting one value when doing my testing. However, I'm receiving a subquery error when attempting to pass multiple values from a checkbox parameter to a sub report in an ajax tab. If I select only one value in the checkbox list, it works fine. I can see why this doesn't work but what's the solution to passing a comma separated list of values between reports based on an initial set of checkbox values? I also tried using 'in' but that didn't work.

   Error:

   Msg: message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

   Example:

   CREATE PROCEDURE sp_DBR_SummaryBacklog
   @location varchar(200)
   AS
   BEGIN
   declare @sql nvarchar(200)

   create table #loc_tmp (
   id int
   )

   select @sql = 'insert into #loc_tmp select t_location_id from [remote].[m1536_t].dbo.t_location where t_location_id in ('+@location+')'

   exec sp_executesql @sql

   select 'dbr.tab', 'Company Summary';
   select 'dbr.tab', 'Location View', 'sp_DBR_LocationBacklog', location=(select id from #loc_tmp)
2. 

   myDBR Team, Key Master

   The error is coming from the fact that you are essentially trying to use following SQL query:

   exec sp_DBR_LocationBacklog (select id from #loc_tmp)

   This works if the subquery (select id from #loc_tmp) returns just one value, but fails with multiple ones.

   What are the parameters for sp_DBR_LocationBacklog? A varchar(200)?

   Most likely you want to use format:

   select 'dbr.tab', 'Location View', 'sp_DBR_LocationBacklog', @location;

   and move the @location parsing into the sp_DBR_LocationBacklog.

   --
   myDBR Team
3. 

   jw1n5, Member

   That worked, thanks. I could have sworn I tried passing the variable directly at one point but it failed. I may have had some formatting issues.

   I'll post in a separate question if you want to keep the issues separated but I looked through documentation and didn't see anything specifically reported on this but is it possible to export all tabs of a multi-tabbed report to XLSX/CSV?

   I tried:

   select 'dbr.export.options', 'worksheet', 'Summary' select 'dbr.tab', 'Location Summary'; select 'dbr.tab', 'Location View', 'sp_DBR_LocationBacklog', @locationid

   Select ... from ... where ...

   select 'dbr.report', 'sp_DBR_LocationBacklog', @locationid select 'dbr.export.options', 'worksheet', 'Location View'

   but that didn't seem to work. I seem to only get the 'summary' tab of the report when exporting to XLSX/CSV
4. 

   myDBR Team, Key Master

   myDBR by default creates the exports as seen by the user. If you have tabs, it will export the selected tab.

   To export all the tabs, you can use inExportFormat automatic parameter to detect the export and simply not to use tabs when export is done. If your tabs are Ajax-tabs you need to decide what to export on those occasions.

   --
   myDBR Team
5. 

   jw1n5, Member

   I think exporting based on tab would potentially be a fine solution to my issue but in this case, when I export from this multi-tabbed report, it always exports the first tab, not the selected tab.
6. 

   myDBR Team, Key Master

   Yes,
   you are correct. The Excel-export exports the report's all tabs (whereas the PDF export exports the report as visible to user).

   The Ajax-tabs are not automaticallty fetched as the content may depend on other things happened in the report before the Ajax-call is made (same sequence cannot be guaranteed).

   If you know the Ajax content (query does not change), you can use the inExportFormat to make the call in Excel-export.

   See demo.

   --
   myDBR Team

Reply

You must log in to post.