Show Dynamic list columns using JXLS template.

RSnewuser · 2020-04-28 15:37:39

Hi everybody,

I hope you can help me, I'm working with dynamic list and JXLS template. I want to build a JXLS template that would only show the selected columns so my question is Is there any variable or object that contains the selected columns list?

For rows I use Data variable.

eduardo · 2020-04-29 07:02:13

Hi RSnewuser,

I am not sure if I completely understand your question, but your JXLS template determines which columns / data are shown. So you can show them using standard JXLS constructs.

Regards,
Eduardo

unsi · 2020-06-11 18:28:59

yes, i would need the same, a template that automatically populate columns and headers.

it seems it could be done via the grid command
http://jxls.sourceforge.net/reference/grid_command.html

sample
http://jxls.sourceforge.net/samples/dynamic_grid.html

the question for the admin is, are the context variables headers and data prepared in the dynamic lists in reportserver?

eduardo · 2020-06-12 06:10:41

Hi unsi,

thanks for pointing to this interesting sample. Currently, this is not supported, but I think this would be a very interesting feature. I raised ticket RS-4208 for this purpose and I will update here when I have more information.

Regards,
Eduardo

unsi · 2020-06-12 07:06:50

thanks a lot Eduardo, it would be handy indeed

regards

Last edited by unsi (2020-06-12 07:07:07)

Felix · 2023-06-12 08:31:37

Hi!
Eduardo told me that I can help

We've already stumbled upon a very similar idea and needed to "work around" the (currently) missing jxls grid command:

1) "data" and "headers" are not provided... therefore we had to inject them! we created a "HOOK" that is called every time an jxls-report is generated. So we've inserted the condition that a Report has to contain DynJXLS in its name... just to be safe
As you see in the code below we not also inject headers and "data2" (wich will be used as "data" element in the jxls)... we also create variable like "len"... as you might see... this code was some sort of wild experimenting

 import net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.hooks.JxlsContextVariableProvider

def HOOK_NAME = "JXLS_GRID_HEADERS_HOOK"
def ctx = null
def max_idx = 0
def callback = [
    adaptContext       : { context -> 
      if( context.getVar("parameters")["_RS_REPORT_NAME"].contains("DynJXLS")){ // guard to prevent this hook on other jxls reports

          println("adapting Context: " + context)
          def data_raw = context.getVar("jdbc").exec(context.getVar("parameters")["query"])
          def headers = []
          def data = []
          data_raw.each{ bean ->

          def idx = 0

          bean.getMap().each{ elem ->

            if (!headers.contains(elem.key)){
              headers[idx] = elem.key
            }

            idx += 1
            max_idx = Math.max(max_idx, idx)
          }

        }    
        //context.putVar("data2",data_raw.collect{ Arrays.asList(it.getMap()) }) 
        //println("props: " +  headers.join(",") ) 
        context.putVar("data2", data_raw.asList() ) 
        //context.putVar("data2", [new Test("A",1)])
        context.putVar("props","type,name")
        //context.putVar("props", headers.join(","))
        context.putVar("len", max_idx)
        context.putVar("it", 0)
        context.putVar("lenChar", ((char) (max_idx+65)))
        context.putVar("headers",headers.asList())
        println("Context: " + context)
          ctx = context
      }
    },
	adaptLegacyContext : { beans -> println("adapting Legacy Context")},
	finishedExport     : { _ -> 
      println("finished Export")
    }
] as JxlsContextVariableProvider

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, JxlsContextVariableProvider.
class, callback)

More information about hooks can you find here

In the JXLS-Template you can now create two nested loops like seen in the following image:

The first loop creates the headers and in the second loop we access those headers

Hope this helps!

kind regards,
Felix

unsi_2 · 2023-06-12 11:20:03

many thanks Felix

i try to use your solution, but gives this error,
maybe related with the line def data_raw = context.getVar("jdbc").exec(context.getVar("parameters")["query"]):
RS4.5.0-6087

Error: An error occurred during report execution.
The report could not be executed: NullPointerException: Cannot invoke method exec() on null object
To get help please contact an administrator.
Details
Report could not be executed. {"error":"NullPointerException: Cannot invoke method exec() on null object","report_id":xxxxx,"report_name":"xxxxx(DynJXLS)","base_report_id":xxxxx,"base_report_name":"test","executing_user_id":999999,"report_output_format":"TABLE_TEMPLATE","report_uuid":"xxxxx"} net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The report could not be executed: NullPointerException: Cannot invoke method exec() on null object at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:280) at net.datenwerke.rs.core.service.reportmanager.ReportExecutorServiceImpl.execute(ReportExecutorServiceImpl.java:161) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.executeReport(ReportExportServlet.java:563) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.doExportReport(ReportExportServlet.java:444) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReport(ReportExportServlet.java:367) at net.datenwerke.rs.core.server.reportexport.ReportExportServlet.exportReportViaSession(ReportExportServlet.java:358) at

Last edited by unsi_2 (2023-06-12 11:31:44)

Felix · 2023-06-12 13:13:16

Hi unsi,

You're right I forgot to mention that i'm using a script datasource in this example (and doing some funky things )!

if you are using a "normal" datasource (like a relational database) "context" does not contain "jdbc" but "data"...so you can simply skip the part and access the Data directly:

//def data_raw = context.getVar("jdbc").exec(context.getVar("parameters")["query"])
  def data_raw = context.getVar("data")

MAYBE you want to have your headers ordered in your own way... the order per row may differ. In this little script, the headers are taken from the first row. Depending on those headers you can sort alphanumerical OR add something like pre- or suffixes to determine the correct order.

Sorting an array in groovy can easily be done using

def arr =[1,"A","B"].sort()
def arr =[1,"c","B"].sort(false, {c1,c2 -> c1 <=> c2 } as Comparator) // or -1 | 0 | 1 for less, equal, greater ... as Integer instead of Comparator

unsi_2 · 2023-06-12 15:00:32

Thanks Felix!!

now it worked, with some caveats:

did some test using Alias, and columns get repeated.

for the ordering, is there a way to respect the order of the columns specified in the dynamic list selection?

kind regards

Last edited by unsi_2 (2023-06-12 17:46:39)

Felix · 2023-06-13 04:53:38

unsi_2 wrote:

For the ordering, is there a way to respect the order of the columns specified in the dynamic list selection?

As I mentioned... this is a (quick and hacky) workaround for the missing grid functionality. Unfortunately i don't see a way to respect the ordering of the selected columns or even access the current configuration (selection) at this point... we will definately discuss that!

The project this was originaly made for does not need a selection via dynamic list... and has suffixed column names that determine the order... so i am able to sort them properly.

Columns shouldn't get repeated... thats what my "if-block" is avoiding

if (!headers.contains(elem.key)){
              headers[idx] = elem.key
}

(only insert headers IF the current header is not in the list of headers)

you also may have a predefined list of headers (in order?!) and remove those which are not in the current data?
This may give you some sort of usable ordering but is not the same order as you configured in the dynamic list.

Maybe have a look at the "export as JXLS Template" functionality? You configure your dynamic list and export a jxls-template for later use or to be used in a variant of this report. You can change this template (adding color, spacing, fonts, etc... ) and add it back to your Report?!

kind regards

Felix

unsi_2 · 2023-06-13 07:31:45

Many thanks Felix.

The duplication of columns appears because when you specify a specific alias for a column in the configure list in a dynamic list, two different keys are received for that column (should not behave like that). For instance, you receive 'NAME', and 'Client Name' (alias). And the two columns appear in the final report.

isnt't there something like context.getVar("metadata") or something for accessing the configure list of the variant right?

For getting the headers reading just from the first row, not all the rows, should the code be changed like this? commenting the each.
Suppose would be more efficient:

  // data_raw.each{ bean ->
        bean=data_raw[0]

          def idx = 0

          bean.getMap().each{ elem ->

            if (!headers.contains(elem.key)){
              headers[idx] = elem.key
            }

            idx += 1
            max_idx = Math.max(max_idx, idx)
        }
 //}

Last edited by unsi_2 (2023-06-13 08:33:24)

Felix · 2023-06-13 10:35:49

Hi unsi,

For getting the headers reading just from the first row, not all the rows, should the code be changed like this? commenting the each.
Suppose would be more efficient:

It will be more efficient! But keep in mind that with your solution every row has to have the same column names! In the original Project (where this script came from) had different column names per row... so i had to assure that every header was present.

The duplication of columns appears because when you specify a specific alias for a column in the configure list in a dynamic list, two different keys are received for that column (should not behave like that). For instance, you receive 'NAME', and 'Client Name' (alias). And the two columns appear in the final report.

Ah I understand! Maybe try whitelisting those headers you need or blacklist those you dont want.

isnt't there something like context.getVar("metadata") or something for accessing the configure list of the variant right?

Accessing saved configurations from variants may be possible... perhaps i can spend some Minutes on this today (or tomorrow). Accessing the current configuration of a "preview" report at this point could be very dificult ~~or impossible~~.

kind regards,

Felix

unsi_2 · 2023-06-13 10:57:24

thanks Felix, would be great if you found something.

For the moment, as a workaround for the ordering of columns I provide an extra parameter, with the list of columns so the user can sort them, with a default order for them. But if the standard configure list could be used the better.

Felix · 2023-06-13 11:36:09

Try this :

import net.datenwerke.rs.jxlsreport.service.jxlsreport.reportengine.hooks.JxlsContextVariableProvider

import net.datenwerke.rs.base.service.reportengines.table.entities.TableReportVariant
import net.datenwerke.rs.core.service.reportmanager.interfaces.ReportVariant
import net.datenwerke.rs.core.service.reportmanager.ReportService

def HOOK_NAME = "JXLS_GRID_HEADERS_HOOK"
def ctx = null
def max_idx = 0
def callback = [
    adaptContext       : { context -> 
      if( context.getVar("parameters")["_RS_REPORT_NAME"].contains("DynJXLS")){

          println("adapting Context: " + context  + " " + context.getVar("data"))
		def reportservice = GLOBALS.getInstance(ReportService)
		def report = reportservice.getReportById(context.getVar("parameters")["_RS_REPORT_ID"])
          def data_raw = context.getVar("data")
          //def data_raw = context.getVar("jdbc").exec(context.getVar("parameters")["query"])
          def headers = []
          def data = []
		
		 if(report instanceof ReportVariant && report instanceof TableReportVariant){
			 headers = report.columns
		 }else{
			 
			
			data_raw.each{ bean ->
			  
			def idx = 0
			  
			bean.getMap().each{ elem ->
				
			  if (!headers.contains(elem.key)){
			    headers[idx] = elem.key
			  }

			  idx += 1
			  max_idx = Math.max(max_idx, idx)
			}

		   }
		 }	   
        //context.putVar("data2",data_raw.collect{ Arrays.asList(it.getMap()) }) 
        //println("props: " +  headers.join(",") ) 
        context.putVar("data2", data_raw.asList() ) 
        //context.putVar("data2", [new Test("A",1)])
        context.putVar("props","type,name")
        //context.putVar("props", headers.join(","))
        context.putVar("len", max_idx)
        context.putVar("it", 0)
        context.putVar("lenChar", ((char) (max_idx+65)))
        context.putVar("headers",headers.asList())
        println("Context: " + context)
          ctx = context
      }
    },
	adaptLegacyContext : { beans -> println("adapting Legacy Context")},
	finishedExport     : { _ -> 
      println("finished Export")
    }
] as JxlsContextVariableProvider

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, JxlsContextVariableProvider.
class, callback)

especially this part:

		 if(report instanceof ReportVariant && report instanceof TableReportVariant){
			 headers = report.columns
		 }else{ ... }

This also keeps the columns in order like you configured it! MAYBE this may also work for TableReport and not only TableReportVariant

Unfortunately my jxls shows no data (only headers).

you can have a look on our documentation and maybe you find something useful!

cheers!

Felix

unsi_2 · 2023-06-13 14:05:58

thanks Felix! it is really close, i manage to get the data with the below code. The only thing is only works when you save the variant, not for interactive changes in configure lists before saving it

       if(report instanceof ReportVariant && report instanceof TableReportVariant){
        	 headers0 = report.getVisibleColumns()
             def idx = 0
             headers0.each{ elem ->
             headers[idx] = (elem.getAlias()  == null ? elem.getName() : elem.getAlias() )
             idx += 1
             max_idx = Math.max(max_idx, idx)
          	}

maybe this line can be changed to reference the current interactive execution somehow:
def report = reportservice.getReportById(context.getVar("parameters")["_RS_REPORT_ID"])

Felix · 2023-06-14 06:53:52

Hi Unsi,
Maybe change

if(report instanceof ReportVariant && report instanceof TableReportVariant)

to

if( report instanceof TableReport)

as well as the correct Import (net.datenwerke.rs.base.service.reportengines.table.entities.TableReport) ?

this might work for the "preview" report.... since we access inherited methods like "getColumns()" or "getVisibleColumns()" from TableReport!

maybe this line can be changed to reference the current interactive execution somehow:
def report = reportservice.getReportById(context.getVar("parameters")["_RS_REPORT_ID"])

everything inside of "context" are information about the current executed report or reportvariant including the "preview"-report!
I Think the problem is the "preview" report since I'm not sure about how selected columns are persisted when we try to access the preview with the ReportService. Thats also why saving your report (aka creating a variant) is working.

You can try the solution above (changing von TableReportVariant to TableReport) and cross the fingers!

btw: i love your idea to get the alias as header if existing!

eduardo · 2023-06-14 08:54:07

Hi unsi_2,

the normal dynamic list "Excel export" exports the current column configuration, without having to save the variant on beforehand, so you can use this as a template for your approach.

You can use the temporary variant available in the session if you need this.
You can implement it in an analogous way as done here:
https://github.com/infofabrik/reportser … .java#L317

 ReportSessionCacheEntry entry = sessionCacheProvider.get().get(tid);
      if (null == entry)
         throw new IllegalStateException("Could not retrieve entry from session");

      String outputFormat = entry.getOutputFormat();
      Report adjustedReport = entry.getAdjustedReport();
      ReportExecutionConfig[] configs = entry.getExecutorConfigs();
      Long reportId = entry.getId();

the "adjustedReport" is the temporary variant in the session.

Regards,
Eduardo

eduardo · 2024-03-02 18:05:26

Hi unsi_2,

good news! ReportServer 4.7 will support the JXLS Grid command out-of-the-box in the JXLS template of the dynamic list: https://jxls.sourceforge.net/samples/dynamic_grid.html

The defined column order, visibility, aliases of the dynamic list variant are used here out-of-the-box.

Regards,
Eduardo

ReportServer Forum

#1 2020-04-28 15:37:39

Show Dynamic list columns using JXLS template.

#2 2020-04-29 07:02:13

Re: Show Dynamic list columns using JXLS template.

#3 2020-06-11 18:28:59

Re: Show Dynamic list columns using JXLS template.

#4 2020-06-12 06:10:41

Re: Show Dynamic list columns using JXLS template.

#5 2020-06-12 07:06:50

Re: Show Dynamic list columns using JXLS template.

#6 2023-06-12 08:31:37

Re: Show Dynamic list columns using JXLS template.

#7 2023-06-12 11:20:03

Re: Show Dynamic list columns using JXLS template.

#8 2023-06-12 13:13:16

Re: Show Dynamic list columns using JXLS template.

#9 2023-06-12 15:00:32

Re: Show Dynamic list columns using JXLS template.

#10 2023-06-13 04:53:38

Re: Show Dynamic list columns using JXLS template.

#11 2023-06-13 07:31:45

Re: Show Dynamic list columns using JXLS template.

#12 2023-06-13 10:35:49

Re: Show Dynamic list columns using JXLS template.

#13 2023-06-13 10:57:24

Re: Show Dynamic list columns using JXLS template.

#14 2023-06-13 11:36:09

Re: Show Dynamic list columns using JXLS template.

#15 2023-06-13 14:05:58

Re: Show Dynamic list columns using JXLS template.

#16 2023-06-14 06:53:52

Re: Show Dynamic list columns using JXLS template.

#17 2023-06-14 08:54:07

Re: Show Dynamic list columns using JXLS template.

#18 2024-03-02 18:05:26

Re: Show Dynamic list columns using JXLS template.

Board footer