You are not logged in.
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
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
Actually it is 2 different databases with different tables?? Then also is it possible??
Offline
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
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
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
Do we have any alternate option for programming rather than groovy??
Offline
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
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
Hi Eduardo,
I already created dynamic list for database queries so i just want to call that in script datasource file
Thanks
Athira
Offline
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
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
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
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
Hi Eduardo,
Thanks alot......
Could u please send me the package for ReportExecutionConfig??
Thanks
Athira
Offline
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
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
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
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
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
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