#1 2019-03-22 11:09:04

Athira
Member
Registered: 2019-03-22
Posts: 13

Can we compare 2 query result of different datasource in postGreSql

Can we compare 2 query result of different datasource in postGreSql and generate a single report based on conditions??

For  eg : I have 2 datasources A, B , first I just want to execute the query in datasource A and get the result of A, next I need to execute the different query in datasource B and get the result of B. Then I need to compare these result based on certain condition and with that final result i need to make a report?? Is this possible in report server??

Offline

#2 2019-03-22 11:23:53

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

yes, this is possible in ReportServer, you can use script datasources for this: https://reportserver.net/en/guides/scri … tasources/

Regards,
Eduardo

Offline

#3 2019-03-22 11:40:24

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Actually it is 2 different databases with different tables?? Then also is it possible??

Offline

#4 2019-03-22 11:54:28

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

yes, this is possible with script datasources. Almost everything is possible with these.
You can write a script that reads the different databases, compare the values you need, and create a table in the format needed by reportserver.

Regards,
Eduardo

Offline

#5 2019-03-25 09:18:59

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Okay thanku, I have one more doubt

I have 2 columns in database like a and b, it has integer values like 8,9. During report generation  i need to convert column a,b in to 8 bits such as 8=0001000 and 9=0001001. Then I need to group 3 bit  from right for 8 (0,001,000) and for 9 (0,001,001) then again make it as different column like (000,100,010,100,0)(reverse order). So for the last bit of 8 it will take remaining 2 bit from 9 and so on. So the final output should contain group 1 as 000, group 2 as 100 etc.

Query Result will be

a   b
8   9


My final report will be like

   G1          G2  ......(Groups)   ---  First header   
G A R      G A R                       --- Sub header
0  0  0      1  0 0                     ---- values/result

Offline

#6 2019-03-25 09:24:34

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

since in script datasources you use groovy for programming, you can achieve any conversions you need with the corresponding groovy/java tools and language constructs.
You may have to rethink only the headers/subheaders, since the RSTableModel allows "simple" tables like dynamic lists, where you don't have a header and subheader.
When you have the results as a simple table (like a dynamic list), you may use any dynamic list construct, like aggregation, grouping, etc, to achieve your grouping requirements: https://reportserver.net/en/guides/user … mic-Lists/, 6.8. Aggregation

Regards,
Eduardo

Offline

#7 2019-03-25 09:52:29

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Do we have any alternate option for programming rather than groovy??

Offline

#8 2019-03-25 10:18:34

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

scripts are programmed in reportserver in groovy. Groovy is very similar to java, so if you prefer java, you can write java and groovy will understand your program with some minor changes.
More information on scripts here:
https://reportserver.net/en/tutorials/t … scripting/
https://reportserver.net/en/guides/script/main/

Regards,
Eduardo

Offline

#9 2019-03-25 11:02:33

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

         I have one more doubt, I have analysed script datasources and in that they are getting xml dataset, instead of that i need to call database(postgresql) query and get the result. How can I achieve that??

Thanks
Athira

Offline

#10 2019-03-25 11:11:23

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

         I already created dynamic list for  database queries so i just want to call that in script datasource file

Thanks
Athira

Offline

#11 2019-03-25 11:46:40

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

the xml is just an example.

You can use all java/groovy libraries/language constructs for your specific purpose.
Specifically, you can use groovy sql's constructs: https://reportserver.net/en/guides/scri … Reporting/ (5.7. Working with Datasources)
http://docs.groovy-lang.org/latest/html … l/Sql.html

You can call the sql for the reports you need and create the RSTableModel that is used by reportserver analogously to the example 5.7. Working with Datasources.

If you prefer to call the dynamic lists previously created, you can also use something similar to:

ReportService reportService = GLOBALS.getInstance(ReportService.class);
ReportExecutorService reportExec = GLOBALS.getInstance(ReportExecutorService.class);

Report report = reportService.getReportById(12334); //the id of your dynamic list
RSTableReport reportCompiled = (RSTableReport) reportExec.execute(report, "RS_TABLE", ReportExecutionConfig.EMPTY_CONFIG);

Then you can iterate the RSTableReport and use the values there. Check the javadocs for RSTableReport available in the sourceforge downloads.

Regards,
Eduardo

Offline

#12 2019-03-26 04:08:18

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

            Could you please tell me the folder name where i can find RSTableReport?? and to which folder i need to add this file ???How can i debug that file?? How to execute it?? Can you give a sample script file for generating report using dynamic list in backend?

Thanks
Athira

Last edited by Athira (2019-03-26 05:43:06)

Offline

#13 2019-03-26 07:21:33

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

Could you please tell me  packages need to import for below classes.

1)ReportExecutorService
2)Report
3)RSTableReport


How to import packages in script??
Thanks
Athira

Last edited by Athira (2019-03-26 08:40:25)

Offline

#14 2019-03-26 08:40:51

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

1) import net.datenwerke.rs.core.service.reportmanager.ReportExecutorService
2) import net.datenwerke.rs.core.service.reportmanager.entities.reports.Report
3) import net.datenwerke.rs.base.service.reportengines.table.output.object.RSTableModel (my mistake with RsTableReport).

you can download the javadocs here: https://sourceforge.net/projects/dw-rs/ … p/download
There you can find all classes/services you may use together with all information needed.

Regards,
Eduardo

Offline

#15 2019-03-26 09:26:27

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

Thanks alot......
Could u please send me the package for ReportExecutionConfig??

Thanks
Athira

Offline

#16 2019-03-26 09:35:59

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

please just download the ApiDocs from here: https://sourceforge.net/projects/dw-rs/ … p/download
There, you can find all classes, including ReportExecutionConfig.

Regards,
Eduardo

Offline

#17 2019-03-26 09:37:12

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

Could you please send me  a sample code for getting values from dynamic list and iterating it(with all packages).

Thanks
Athira

Offline

#18 2019-03-26 09:39:14

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

here you should find all information needed for this:

https://reportserver.net/en/tutorials/t … scripting/
https://reportserver.net/en/guides/script/main/

Regards,
Eduardo

Offline

#19 2019-03-26 09:42:24

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

Sample code: https://forum.reportserver.net/viewtopi … 4555#p4555
This code shows also how to get data out from a RSTableModel (check the printTable method).

Further, please read the thread I linked to. It shows some information on the approach you are trying to achieve.

Regards,
Eduardo

Offline

#20 2019-03-26 10:44:36

Athira
Member
Registered: 2019-03-22
Posts: 13

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Eduardo,

          Thank you so much.....

I need one more clarification regarding parameters. Can we use parameters in script, Basically I need to get the data from 2 datasource in same server in script and combine result to form final report. I need to execute queries based on parameters. Is it possible in report server??  If possible how we can add parameters in script?


Thanks
Athira

Offline

#21 2019-04-02 12:30:25

eduardo
Administrator
Registered: 2016-11-01
Posts: 1,079
Website

Re: Can we compare 2 query result of different datasource in postGreSql

Hi Athira,

when you call and execute the report in your script by modifying the parameters sent in the script, e.g.:

Set<ParameterInstance> parameterSet = report.getParameterInstances();
    
    for(ParameterInstance param : parameterSet) {
      String name = param.getDefinition().getName();
      if(param instanceof TextParameterInstance) { // in this case we change a text parameter value
        TextParameterInstance textParam = (TextParameterInstance) param; 
        if(name.equals("MY_PARAMETER")){           // we change the parameter with name "MY_PARAMETER"
          textParam.setValue("MY_VALUE");              // set the value
        }
      }   
    }

RSTableReport reportCompiled = (RSTableReport) reportExec.execute(report, "RS_TABLE", ReportExecutionConfig.EMPTY_CONFIG);

Regards,
Eduardo

Offline

Board footer

Powered by FluxBB