CompiledReport - How to read values of hidden column in script?

Patryx · 2019-04-02 13:19:25

Hi,
I want to read value of hidden column from CompiledReport.
I have found on this forum the idea how to read value of column.
So for test I did in hook variable "values" with all values from column index 0:

    def tableModel = (RSTableModel)compiledReport;
    def modelData = tableModel.getData();
    def values = "";
      for(RSTableRow row in modelData) { 
         values += row.getAt(0).toString() + ";"
      }

Unfortunately there are only values from visible columns (I checked their names using def tt = row.getTableDefinition().getColumnNames();).

1. How can I get values from hidden columns??
2. When I use "values += row.getAt("id").toString() + ";" ?"
Why cannot I find column even if the name exists?

3. another problem is: Is there any possibility to add a hidden column dynamically (maybe in hook? Which one?) in report?
Where should I look for solution?

eduardo · 2019-04-03 07:40:32

Hi Patryx,

1. currently, hidden column values can not be used from within a RSTableModel in an easy way. What exactly are you planning to do with these values? Why are they hidden ?

2. row.getAt(String) doesn't exist. I suppose you mean row.getValueFor(String). If yes: this method uses the internal reportserver names for the queries, e.g. values += row.getValueFor("xx__rs_col_0") + ";"
This also doesn't work for hidden values.

3. You could try to create a new variant based on the current variant, and modify it using the available methods. This would be an "offline" variant, created on-the-fly.

Regards,
Eduardo

Patryx · 2019-04-04 07:30:19

Hi,
Thank you for information.

1. In general all my problems are connected with: https://forum.reportserver.net/viewtopic.php?pid=5539.
I have to get values of one column (let's say ID) in, let's say, report A and B. I need to get them in HOOK (I am not sure which one, maybe in hook for adding new output) after generated report because I have to save such values in database.
In general I need id values of one column to save in database (for example as hook for output when user choose new button Export to db) because after that in our application I want to use that filtered id values for some operations.
Problem is that user can remove id column (Report Execution View) or hide the column, so in that way I cannot get this column values in hook...

I think the best solution will be to find out hook and possibility to check if such a column exists in report. If not, manually in script add such a column before generationg report. When report is generated, read the column values, save in database and remove such a column from generated report - so user finally will have only column which chose before generating.
Unfortunately i have a lot of problems and cannot find any hints in documentations, code or forum.
Could you suggest how can I resolve them?

2. You are right, my mistake in writing example in forum. I meant row.getValueFor(String) and I tried this. Unfortunately it didn't work - even if the column exists and it;s not hidden. I get exeption about null (I see that this column is filled in every case, not null values there).

3. I think it's a bit connected with my idea from point 1 but if I add column as hidden I still need to have possibility to read such a value.
Can I get any clues which hook should I use, any examples?

Do you have a correct example: https://forum.reportserver.net/viewtopic.php?pid=5548 ? Because that one in documentation doesn't work.
As I told you, I want to create my own TableOutputGeneratorProviderHook which save column values to database. Unfortunately I can do nothing because of the internal error.

Last edited by Patryx (2019-04-04 07:51:02)

Patryx · 2019-04-04 11:31:05

eduardo wrote:

Hi Patryx,
3. You could try to create a new variant based on the current variant, and modify it using the available methods. This would be an "offline" variant, created on-the-fly.
Regards,
Eduardo

Till now I can use ReportExecutionNotificationHook because as I meant Output hook doesn't work.
You mentioned about modification in report.

I did sth like that:

...
notifyOfReportExecution : { report, parameterSet, user, outputFormat, configs -> 

  if (outputFormat == "RS_TABLE") {
     def tableReport = (TableReport)report;
     def Column newColumn = new Column();
     newColumn.setName("id2");
     newColumn.setType(SqlTypes.BIGINT);
     tableReport.addColumn(newColumn);
    
    report = tableReport;
   }
  
},

In my report (chosen variant) there are some added column, except colum "id2" (BIGINT), so I want to add this column manually.

Unfortunately no error and no data visible on screen (what's interesting I cannot see rows but in the summary bar below table there is written number of rows etc.
What's more notifyOfReportsSuccessfulExecution is fired...but no data visible.

What more should I set?

Last edited by Patryx (2019-04-04 11:31:34)

eduardo · 2019-04-04 12:28:06

Hi Patryx,

thanks for the detailed explanation. I am now understanding better what you are trying to achieve.

I think the first step for solving this would be:
"Do you have a correct example: https://forum.reportserver.net/viewtopic.php?pid=5548 ? Because that one in documentation doesn't work.
As I told you, I want to create my own TableOutputGeneratorProviderHook which save column values to database. Unfortunately I can do nothing because of the internal error."

This should be the correct way to start. As our documentation example is not working, I have to take a look what have changed here and how to correct this. I raised ticket RS-3380 for this. At the moment, we are working on some bugfixes for the next 3.0.6 patch version, but we will take a look at this as soon as possible. Thanks for your patience.

Regards,
Eduardo

jalbrecht · 2019-04-04 12:45:21

Dear Patryk,

i think i miss something here. The question we have is why do you have to hide the columns in the first place ? The hide column feature of the dynamic list was created to suppress for example sort columns, aggregation categories or sensible information in a simple way for the output. Why can't you "unhide" the columns needed in the variant i.e. why do they have to be hidden ?

wbr jan

Patryx · 2019-04-04 12:51:38

Great, it sounds optimistic
What about getValueFor?

According to my last post how to change report in notifyOfReportExecution (add column?)?
Let me add that I found out a manner how to change parameter value (set different than the original value) and it generates right report with changed parameter.
How to achieve this when it comes to changing column by adding new one?

Last edited by Patryx (2019-04-05 10:01:53)

Patryx · 2019-04-04 13:04:30

jalbrecht wrote:

Dear Patryk,
i think i miss something here. The question we have is why do you have to hide the columns in the first place ? The hide column feature of the dynamic list was created to suppress for example sort columns, aggregation categories or sensible information in a simple way for the output. Why can't you "unhide" the columns needed in the variant i.e. why do they have to be hidden ?
wbr jan

I understand that hidden column was a bad idea, so I quit the idea and I want to find other solution...

As I mentioned to resolve my problem I think now about manually adding expected column before generating report (lets say column "ID" type BIGINT) if such column has not been chosen by user. I want to do it in TableOutputGeneratorProviderHook - Eduardo created a ticket why the example from documentation doesn't work in current version.
Till that time (solution) how to write TableOutputGeneratorProviderHook, I want to use ReportExecutionNotificationHook as I wrote in my post (for practising and finding out how to operate in report).
The problem is that I don't know how to do it (I wrote in the post my example).

eduardo · 2019-04-08 07:11:24

Hi Patryx,

"Unfortunately no error and no data visible on screen (what's interesting I cannot see rows but in the summary bar below table there is written number of rows etc."
-> And in the logs? Do you have any error/information on the tomcat logs ?

Regards,
Eduardo

Patryx · 2019-04-08 09:48:21

Unfortunately a file log: "reportserver.2019-04-08.log" is empty when I test this example now.
I attach a screenshot:

What's more I can see in summary 2 rows but no data are showed.
The number of selected/visible columns is 9 and my dynamically added column was the 10th, so in summary it wasn't added.

Last edited by Patryx (2019-04-09 06:21:45)

eduardo · 2019-04-08 11:30:08

Hi Patryx,

and in the rest of the logs? Please check all server logs. This is strange. Could you post a simple example using the demo data for example? Then we can reproduce it.

Regards,
Eduardo

Patryx · 2019-04-09 06:29:42

Hi,
I cannot see any errors in server logs.
First of all you didn't tell me if my code to add a new column (I didn't find any example...) is written in proper way:

...
notifyOfReportExecution : { report, parameterSet, user, outputFormat, configs -> 

  if (outputFormat == "RS_TABLE") {
     def tableReport = (TableReport)report;
     def Column newColumn = new Column();
     newColumn.setName("id2");
     newColumn.setType(SqlTypes.BIGINT);
     tableReport.addColumn(newColumn);
    
    report = tableReport;
   }
  
},

Maybe there is a problem that I miss sth.
How should I dynamically add new column (let's say in ReportExecutionNotificationHook)?
If you confirm that this one should be OK I will create your database and try to reproduce this case.

eduardo · 2019-04-10 10:51:19

Hi Patryx,

this is working in my case:

import net.datenwerke.rs.core.service.reportmanager.exceptions.*
import net.datenwerke.rs.core.service.reportmanager.hooks.*
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.entities.Column
import net.datenwerke.gxtdto.client.utils.SqlTypes
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableRow;

def HOOK_NAME = "ADD_COLUMNS"

def callback = [
    notifyOfReportExecution : { report, parameterSet, user, outputFormat, configs -> 
      
      if (outputFormat == "RS_TABLE") {
         def tableReport = (TableReport)report;
         def Column newColumn = new Column();
         newColumn.setName("f_myid");
         newColumn.setType(SqlTypes.BIGINT);
         tableReport.addColumn(newColumn);
    
    	  //report = tableReport;
          System.out.println("report executed!!!!!"); 
        }
    },
      
      
      
      
    notifyOfReportsSuccessfulExecution : { compiledReport, report, parameterSet, user,
outputFormat, configs -> 
    
      if (compiledReport instanceof RSTableModel) {
        def tableModel = (RSTableModel)compiledReport;
        def modelData = tableModel.getData();
        def values = "";
        for(RSTableRow row in modelData) { 
           values += row.getAt(2).toString() + ";"
        } 
        System.out.println("Values: " + values);
      }
    
    },
    notifyOfReportsUnsuccessfulExecution : { e, report, parameterSet, user, outputFormat,
configs -> },
	doVetoReportExecution: { report, parameterSet, user, outputFormat, configs ->
    }
] as ReportExecutionNotificationHook

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

I add the column "f_myid" dynamically to the report. It doesn't show in report execution, but I don't get an empty window as you get. I can access this column in notifyOfReportsSuccessfulExecution with values += row.getAt(2).toString() + ";"

Please note that the columns get added at the end, so getAt(0) won't work in this example. Also, please note that the report gets executed more than once, so check if the column was already added to the report.

Regards,
Eduardo

eduardo · 2019-04-10 10:57:26

Hi Patryx,

ok, if I put this script into onstartup.d, I see the blank window you mention. I also see the values of the new id column in the output. Is this the behavior in your case?
If you run the script with "exec yourscript.groovy" it works, correct ?

Regards,
Eduardo

eduardo · 2019-04-10 12:09:41

Hi Patryx,

please try with this script:

import net.datenwerke.rs.core.service.reportmanager.exceptions.*
import net.datenwerke.rs.core.service.reportmanager.hooks.*
import net.datenwerke.rs.base.service.reportengines.table.entities.TableReport
import net.datenwerke.rs.base.service.reportengines.table.entities.Column
import net.datenwerke.gxtdto.client.utils.SqlTypes
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel
import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableRow;
import net.datenwerke.rs.core.service.reportmanager.ReportExecutorService;
import net.datenwerke.rs.core.service.reportmanager.engine.config.ReportExecutionConfig;
import net.datenwerke.rs.core.service.reportmanager.engine.CompiledReport;

reportExec = GLOBALS.getInstance(ReportExecutorService.class);


def HOOK_NAME = "ADD_COLUMNS"

def callback = [
    notifyOfReportExecution : { report, parameterSet, user, outputFormat, configs -> 
      
      if (outputFormat == "RS_TABLE" && !report.getName().equals("TEMP_TABLE")) {
        
        TableReport tableReport = (TableReport)report;
        
        System.out.println("Creating temporary variant");
        TableReport variant = tableReport.createTemporaryVariant(report);
        
        Column newColumn = new Column();
        newColumn.setName("f_my_id");
        newColumn.setType(SqlTypes.BIGINT);
        variant.addColumn(newColumn);
        variant.setName("TEMP_TABLE");
        
        CompiledReport reportCompiled = reportExec.execute(variant, "RS_TABLE", ReportExecutionConfig.EMPTY_CONFIG);
        
        def tableModel = (RSTableModel)reportCompiled;
        def modelData = tableModel.getData();
        def values = "";
        for(RSTableRow row in modelData) { 
           values += row.getAt(2).toString() + ";"
        } 
        System.out.println("Values: " + values);
   
        }
        
    },
      
      
    notifyOfReportsSuccessfulExecution : { compiledReport, report, parameterSet, user,
outputFormat, configs -> 
    
    },
    notifyOfReportsUnsuccessfulExecution : { e, report, parameterSet, user, outputFormat,
configs -> },
	doVetoReportExecution: { report, parameterSet, user, outputFormat, configs ->
    }
] as ReportExecutionNotificationHook

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

The idea is to create a temporary variant (a clone of the report), execute it, and finally get the results from this cloned variant instead from the "real" report. If you modify the "real" report you may have problems, as you see in your example. With temporary, disconnected variants, you can perform the operations you need. It is important to choose a unique name for the temporary variant (TEMP_TABLE in my example)

Also, consider using the notifyOfReportsSuccessfulExecution method instead of the notifyOfReportExecution method.

Regards,
Eduardo

Patryx · 2019-04-10 12:23:45

Thanks for reply.

System.out.println("report executed!!!!!") - where should I find such an entry (cannot find any entry in reportserver<date>.log?
My script has extension .rs not .groovy but I suppose it's not a problem.

Last edited by Patryx (2019-04-10 12:38:30)

eduardo · 2019-04-10 12:30:34

Hi Patryx,

you should use the logger for this, here is an example:

import java.util.logging.Level;
import java.util.logging.Logger;
  
logger = Logger.getLogger(getClass().getName());
logger.log(Level.WARNING, "WARN Message!");
logger.log(Level.SEVERE, "SEVERE Message!");

The .groovy extension is correct, yes.

Regards,
Eduardo

Patryx · 2019-04-10 13:31:42

Great!
This solution works! Thank you!
I would use it in notifyOfReportsSuccessfulExecution until you resolve ticket with output hook problem. Then I will use this solution in output hook to put additional column and write down values from such a column in my database.

So is there no possiblity to add column dynamically without executing a report query again?

How to check if column "f_my_id" exists in report?
row.getValueFor("column_name").toString() doesn't work - null pointer exception, so no data return in row.getValueFor("column_name").
When I do row.getValueFor("f_my_id") in your example after executing temporary variant report it throws the same exception.

In the worst I can do foreach and compare every name with "f_my_id" but I hope there is a better built-in solution.

Last edited by Patryx (2019-04-10 13:44:25)

eduardo · 2019-04-10 13:47:15

Hi Patryx,

adding/removing columns dynamically implies a new query, so this is the correct solution in this case. Modifying the real report causes problems, since the other components expect the "unmodified" version of the report.

row.getValueFor("column_name").toString(): you get a nullPointerException in case the column *doesn't* exist. And if it *does* exist? Do you get the name of the column ? If yes, you can just check if it is null:
if (null == row.getValueFor("column_name") )
// column doesn't exist
else
// column exists

Regards,
Eduardo

eduardo · 2019-04-10 13:51:37

Hi Patryx,

correction:
"row.getValueFor("f_my_id") " won't work, since this method uses internal names: row.getValueFor("xx__rs_col_0"), row.getValueFor("xx__rs_col_1"), etc.

Yes, please use getColumns() and compare the names. This is not "worst case" since we have a list and you are looking for one value inside the list.

Regards,
Eduardo

Patryx · 2019-04-11 06:46:13

Thank you Eduardo for resolving my problems from this topic!
I will follow your clues and suggestions ;-)

Last edited by Patryx (2019-04-11 06:46:29)

Patryx · 2019-04-15 08:44:40

eduardo wrote:

import java.util.logging.Level;
import java.util.logging.Logger;
  
logger = Logger.getLogger(getClass().getName());
logger.log(Level.WARNING, "WARN Message!");
logger.log(Level.SEVERE, "SEVERE Message!");

When I try to log with level WARNING or INFO there is no entries in a log file. Only Level.SEVERE works.
Unfortunately somewhere should be set that minimum level as Level.SEVERE.
How can I log with level different than SEVERE (WARNING, INFO, CONFIG , FINE)?

Last edited by Patryx (2019-04-15 08:45:22)

eduardo · 2019-04-16 10:33:07

Hi Patryx,

please try changing the file logging-rs.properties in a standard bitnami installation
e.g.

.level = FINEST
net.datenwerke.level = INFO

Regards,
Eduardo

Patryx · 2019-04-17 12:53:56

Thank you, it works

ReportServer Forum

#1 2019-04-02 13:19:25

CompiledReport - How to read values of hidden column in script?

#2 2019-04-03 07:40:32

Re: CompiledReport - How to read values of hidden column in script?

#3 2019-04-04 07:30:19

Re: CompiledReport - How to read values of hidden column in script?

#4 2019-04-04 11:31:05

Re: CompiledReport - How to read values of hidden column in script?

#5 2019-04-04 12:28:06

Re: CompiledReport - How to read values of hidden column in script?

#6 2019-04-04 12:45:21

Re: CompiledReport - How to read values of hidden column in script?

#7 2019-04-04 12:51:38

Re: CompiledReport - How to read values of hidden column in script?

#8 2019-04-04 13:04:30

Re: CompiledReport - How to read values of hidden column in script?

#9 2019-04-08 07:11:24

Re: CompiledReport - How to read values of hidden column in script?

#10 2019-04-08 09:48:21

Re: CompiledReport - How to read values of hidden column in script?

#11 2019-04-08 11:30:08

Re: CompiledReport - How to read values of hidden column in script?

#12 2019-04-09 06:29:42

Re: CompiledReport - How to read values of hidden column in script?

#13 2019-04-10 10:51:19

Re: CompiledReport - How to read values of hidden column in script?

#14 2019-04-10 10:57:26

Re: CompiledReport - How to read values of hidden column in script?

#15 2019-04-10 12:09:41

Re: CompiledReport - How to read values of hidden column in script?

#16 2019-04-10 12:23:45

Re: CompiledReport - How to read values of hidden column in script?

#17 2019-04-10 12:30:34

Re: CompiledReport - How to read values of hidden column in script?

#18 2019-04-10 13:31:42

Re: CompiledReport - How to read values of hidden column in script?

#19 2019-04-10 13:47:15

Re: CompiledReport - How to read values of hidden column in script?

#20 2019-04-10 13:51:37

Re: CompiledReport - How to read values of hidden column in script?

#21 2019-04-11 06:46:13

Re: CompiledReport - How to read values of hidden column in script?

#22 2019-04-15 08:44:40

Re: CompiledReport - How to read values of hidden column in script?

#23 2019-04-16 10:33:07

Re: CompiledReport - How to read values of hidden column in script?

#24 2019-04-17 12:53:56

Re: CompiledReport - How to read values of hidden column in script?

Board footer