Reporting in Servoy with Jasper Reports

Welcome to today’s webinar of August 22nd with Servoy 30 minutes of a technical topic. Today we’re going to do a somewhat old topic, but it’s been requested a lot of times to recap it and make sure that we’re all up to date on the latest technologies to be used. It’s going to be about reporting how to get started with reporting in Servoy, best practices, and building reports for all the different clients in in Servoy. And with me is again, Sean Deaf and for number 42 reporting. Okay, well let’s get started. Again, this is a 30 minute technical topic, so we don’t go into very great detail. And I was looking back over past webinars and noticing that we are missing a webinar about reporting entirely. So I thought I would take the opportunity to do a small presentation about the simplest and fastest way to generate powerful reports, right, right in Servoy. Again, this is sort of about what’s possible. It’s not really a training, but we have a nice example and some explanation and a chance for you guys to ask questions. Also links to more information. Of course, we’ll start with a demo and then we’ll recap what we’ve seen. So let’s get right into it. I’m going to show you an example report with a simple launch screen that I built that’s based on the example database that ships with Servoy, showing orders and the ancillary information. So what I’ve done on this screen is created some filter parameters here over on the left. So we can select when we start and, you know, different customers, etc. On the right-hand side, you’ll see that the data is updated and at the bottom here we have a button where we can run the report and it will generate a PDF output that we can see. So let’s give it a shot here. Perhaps we want to choose a year. So let’s get everything after 1998. You can see the results are updating here. Maybe we want to pick a few countries here. So we’ll start with maybe some English-speaking countries because we have a IE-K-NAN surprise later and maybe one of the sales reps. You can see that I’ve sort of filtered it down to a few records here. If I’m satisfied with this, I might say, great, I want to generate a report of this. You can see that a PDF was downloaded and I can open it right here in my browser and here’s the report that we generated. This report is showing the orders. It’s showing the order totals here. You also see some grouping. We have grouping by quarter and year and also by country. And as I scroll down, you can see that we have the country totals and the quarterly totals in the summary sections of the report. Now, this was just a small set of results if I were to come back here and maybe clear a few filters. Then… Oops. Sorry about that. And we can run the report again. It generates quickly. Now you can see that we actually get quite so many more pages still with a grouping. The results of the report are dynamic, depending on what we put in as the filter. Quickly, I’d like to show you how the report was built because that actually uses a different technology outside of Servoy. So right now I’m opening the Jasper reports studio, which is an IDE for generating reports. And the reason we’ve selected this is that Servoy wants to offer you the best tools available and we don’t want to go and build our own reporting engine when there are numerous reporting engines out there that we can integrate with. So for this example, I’ve picked Jasper report and they have an IDE for designing the layout of the report and I’ve used that here to build this report. Now one thing that I want to point out about the setup here is that we are exposing from the Servoy side many of the objects that we’ve created and we’re able to reuse them right here in the report. Typically, report integration solutions between an application tool like Servoy and another reporting technology, including Jasper, the report becomes very complex. You have to do all the data access yourself. You have to do the SQL queries usually embedded in the report. So the report has to be built by a developer or a database person. It has to be connected to the database while they’re building it. What we’ve done here is we’ve stripped all of that complexity out of the report and we have really just a layout or a templating engine and we’re just laying fields on the screen and setting the values for those fields commonly with an expression. So for example, I’m showing the company name for the customer on the order here in the details section in the field and you can see that what I’ve done is defined a field in the report called orders to customers dot company name and I’m just putting a reference or an expression to that field here in the field placement. You also notice that the there’s grouping added to the report and again, we’re just putting some, some of the summary information here. In this case, it’s summarizing by the order total in the country group. The order total itself also comes from the application and that’s a calculation being passed in. So if I were the report developer and I was writing this report from scratch without the aid of the integration plug-in, I’d have to write that into this equal query. I’d be summarizing all the line items for each order, multiplying quantity by unit price, all that sort of thing. It would all be built into the query. In this case, we get to reuse that object right out of Servoy. So if I switch back to the Servoy ID, we look at what did it take to run this report. You can see it’s just a one-liner here and it’s calling that Jasper plug-in to run the report. And what I’m passing in here is the found set, which is the result set that I’m working with after I did the filtering, as well as the report name and then some parameters. It’s worth pointing out that because it’s based on the found set, all of the relations that I’ve used, any calculations that I’ve used are automatically available. I don’t have to even pass them in. They’re just kind of implicit. When I’m designing the report, all I need to do is reference them by name and they automatically appear. So for example, there was a calculation here displaying the year and the quarter, which is actually based on two other calculations here, a year and quarter. So I had created these calculations in Servoy and I get to reuse them in the report. The same is true of the order total. There it is. Take me a minute to find it. Same file. Again, this I’ve had in sort of my example solution for years and I’m able to reference it you also would have noticed that I was able to use the relation order details to customers. This is a simple join between the order stable and the customers table. I didn’t have to write any SQL joins or anything inside the report. So that all gets leveraged for you when you use this found set based reporting. Another thing that I’d like to show you if I go back to the application here is that Servoy is supports I18N or internationalization. So it’s very easy for us to switch local language, time zone, etc. You would probably want the same capability from your reports if your application does that. And so again, we allow that complexity to be stripped out of the report because we already handled that inside the application. So I have over here my language selector. I’m going to switch this report over to Spanish and also to the local of Spain. And you can see that when I do that, of course my application switches all of the labels here are now in Spanish. But if I generate the report and maybe just for effect, we’ll pick some Spanish countries or Spanish speaking countries. I think I have Argentina in here too. Yeah, there we go. Now I generate a new report. You can see that the report is also in Spanish. The even the currency symbol and format switch to because I set my locala Spain switch to a European format. All of the labels here are translated into Spanish. And the way that that was done, if we go back again to the IDE, the report IDE, you can see that for each one of these labels, I’m just choosing what’s called a resource bundle. But it’s really just a reference to another thing that servoit passes in. And all I have to do is get that I18N message by name and it will translate. So if we go to the servoit IDE, you can see I have my I18N messages, these were the keys that I created for this application. And so you can see that for that country label, I have both the Spanish and the English translation there. And that’s all it took. So whatever effort you spend to make your application multi-lingual, multi-time zone, etc, you can sort of mirror that over to your reports with very little effort. And again, you don’t need to be a developer to do it. I think that pretty much is the example that I wanted to show. I want to leave time for questions. I’ll go through an overview of what we’ve seen here. I guess another thing I wanted to do was show you the setup. I think it’s important to see that. The first thing is that I installed the plugin from the clear some of these reports out of here, from the servoit forage website. This is actually an open source third-party community website that’s run by advocates in the servoit community. And one of the projects there is this JASP reports plugin and servoit and others contribute to that. So this is where you can get that plugin. There’s also various flavors of the plugin to different builds and different options. For example, if we look at the files it can be downloaded, I downloaded the basic one. There’s one that has more extended support for charts. Also internal scripting languages that you can use for some of those expressions you saw. This is really a full-fledged BI platform. So there are really a lot of options beyond what I just showed you. There’s a build here which provides the barcode capability. So you can just generate barcodes in report. That’s really easy to do. So you want to pick the version of the plugin that’s right for you. And when you download it, all you do is you drop it into the plugins directory of your installation. And that’s when you’ll see the plugin show up in code complete when you saw that I called plugins.JASPUR. Then it becomes available. The other thing that you’ll probably want to do is configure the plugin. If I go to my Servoyadministrator page, this is sort of the admin page for my application server but also available when you’re running the IDE, you’ll see that there’s a section called server plugins. And what I’ve done there is configure my JASPUR report directory. This means that the report file gets saved to a directory. And when I run the application, I’m reading it from that. You’ll see that I only refer to it by the name. I didn’t refer to it by an absolute path because of course a production time. You just want to all of your reports and won’t go and you want it to be externalized. But this makes it really easy to build and test. You’ll see that if I were to go in here and I don’t know change a label here. I’ll just put a plain text to show you where’s my palette. I’ll just put some static text up here. And I’m going to save that. I can come back here because I saved it to that report directory. It’s always going to look there. And if I come back here and rerun the report, you can see that my label was added. So it makes the process of developing and testing your reports pretty seamless. You don’t have to recompile things or do any obstacles to do that. Again, it also means that a non-developer can build these reports as well. So you download the plug again, you install it, you configure where your reports directory are, and then your reports go there and there really just XML files that the plug-in reads. And the real power is that most of the hard logic is still inside the application. So the real benefits of the plug-in are that the complexity that you normally have in a report is reduced or at least it’s translated over to the Servoy application which handles that complexity really well. That includes accessing data. Of course, I didn’t have to write any SQL to run those filters. Normally you have the query in the report itself. The report developer gets to focus really on layout and design and very little on logic or data access. We see with this approach the cost for developing reports and many of our customers have ERP applications with hundreds or even thousands of reports. The cost to develop reports like that goes way down. And that’s because it doesn’t take very long and it can also be done by a non-programmer. The plug-in itself allows you to reuse stuff that you may have already built in Servoy or that you can build in Servoy easily more easily than you can in a BI tool. And that includes the data binding that’s already implicit. You saw the found set that was on that form where I showed the data is the same one that I passed to the report engine. So it’s pretty seamless there. You get to leverage APIs and extensions that we already have like searching and filtering. You get to use relations, calculations, aggregates, valueless, global variables, security settings, things like that. And really handy if you’re doing a multi-lingual application as you get to reuse all those I18N translations. Again, you install the plug-in from Servoy forge. You pick the right version depending on what your needs are. There’s documentation there on what’s available. You do have to install the JASP report studio from JASP or SOF. So you do have to go outside of Servoy to develop the report. But it’s a pretty intuitive, wizzy wig to design the report. Do a little bit of configuration on the reports plug-in and then you can start building and testing right away. That’s a duplicate slide. And there’s when you’re running the report, there’s a variety of output options and formats. Other things you can do, I showed a pretty simple example. The example I showed just one line of code and it automatically generates a PDF and downloads it right there in the HTML5 client. But of course, you can generate the PDF or whatever other format like Excel or something. You can generate that kind of in background. You can email it as attachments. You can push it to content servers and file servers. We see a lot of our customers. They run sort of monthly reports or quarterly reports or something on a scheduled. We have a scheduler plug-in and the ability to run batch job server side. And we see big complex reports running on a schedule where there’s no end user and those get pushed out to customers or put on a web server or something like that. So that’s a common integration with the rest of the servoit platform. There’s also other many other features available that I showed you a pretty simple report. But there’s in the JASP reports engine, there’s cross-tab reports, there’s sub-reports which are like a report within a report where it’s contextual on the outer report. There’s charts, there’s dynamic ability. You can do scripting. You can show and hide things depending on certain conditions and all of that can link back to the servoit environment. If you want more information, go to the reports plug-in project site and please post feedback and questions about today’s webinar and the example you saw on our forum. And you can catch up on recording of this or other recordings of other webinars we do every two weeks on the tech series homepage. Y’all do I have any questions? Yes, there are a couple of questions coming in and people feel free to post a few more. Let’s see here. Oh, there was one about chart reports in barcode, I guess it was ancidology during, by using this additional plugins. Can we do sub-reports in JASP Yeah, absolutely. Does servoit integrate with any other report generator? Yeah, that’s a good question. Again, this webinar focused on getting started the sort of the fastest way to build powerful reports. I believe is with JASP reports because we have a plugin that really tightly integrates with that reporting engine. But there are definitely other reporting engines out there and many of our customers integrate with other BI software other than JASP or so for example, we have a similar plugin for Crystal Reports which allows you to do found set-bit reporting and pass in all of the objects from your servoit application into a Crystal Reports. There’s other integrations with velocity reports which is more like a templating engine, Pentaho Reports, Tableau, and I think there’s a few others as well. So by any means, this is not the only solution but this is meant to be a quick start like where to get started if you want to build a report and run it quickly, this is probably the fastest way to get started. Yeah, it’s also free, also royalty free. If you keep a few items in mind in terms of GPL software distributions, if you need help on that then please do contact us. But in the basis JASP is a free tool. There is a more advanced version that allows you to ad hoc reporting which is paid for but you can also OEM it through a servoit. So again, if you want to learn more about that, feel free to reach out to me or to Sean to get more info on that. Is it possible to write a SQL query to populate the report? Yeah, absolutely. So today we focused on what we call found set base reporting which is really taking the data binding environment from servoit and reusing that for your report. There are the, in fact, the original way that you would write a report in JASPERS to write the SQL into the report and then the report engine itself connects to the database and you pass in parameters. So the things that you would have filtered on, you actually pass those in and then the query to generate everything is written in the report. That’s certainly still an option. There are reasons why you might want to do that in certain examples. Sometimes performance is a consideration or sometimes just the complexity of the report could be a consideration. Or maybe you’re just a big fan of writing SQL. Yeah, it could be. All right. Do keep in mind that if you’re writing your own SQL as opposed to servoit datasets that you will probably become database dependent so there can be a choice that you make. There’s a question from Imra who has been using the sort of the predecessor iREPOT and his question is, is JASPERS studio fully compatible with the deprecated iREPOT? Does it require any best practices tweaks when comforting? Yeah, I’m probably not the best person to answer that question. My impression was that upgrading to, you know, from the legacy sort of report builder called iREPOT to JASPERS studio is pretty seamless so you can operate on on older reports in JASPERS studio. There is some documentation on the on about our plugin about what versions of the JASPERS report spec are supported by which versions of the plugin so we peg our plugin versions to to the JASPERS spec versions. So you do want to keep that in mind. But I think that switching the IDE is pretty seamless and the new JASPERS studio is pretty nice. It’s based in Eclipse like servoit. Okay. And Louis actually has a question about that. Does the report you need to be installed on the production server or JASPERS development server? I’m sorry, the report builder or the the report. Yeah, so the way you build your reports with. Yeah, only only for developers and again, it could be done by a non developer and done on a separate workstation as long as those reports are available at runtime, then it’s no problem. And typically what people do is they build the reports under revision control. So it becomes part of their revision control system. And if you use something like the servoit QA pass or another build automation tool, then changes in the reports also trigger automated build and test and and deployment. So you can you can sort of pipeline all that and automate it. Excellent. Yeah. So Louis, the answer is so you don’t need the studio on your production server because the templates get fast up. You do need the plugins on your production server because that does the magic integration between the two. Can we install servoit and JASPERS in the same eclipse? This must be an advanced user. Yeah, technically you can because both are just plugins to eclipse. I’ve never tried that. I imagine you have to check to make sure compatibility with the eclipse version that you want to use is going to work for both both plugins. But in theory, you can do that. I actually had done that in the past, I think, when with the old iReport, he was able to do something like that. And I had done that once before, but that was a long time ago. And so it’s it’s an advanced topic. All right. There’s another one which may be to advance for this session, but maybe you can build something small from sender. Can you show starting a new report from scratch and not sure if there’s too much work for now or if you can build a simple reboot with two or three columns in it and show us how it’s done from scratch? Yeah, if we have time, this is should be kept around 30 minutes. But if I were to do a new JASPERS report, pick a blank template. We’ll call this test. JaxML. All right. So you can see that it comes with already the report sections here. There’s no grouping involved yet. What I’m going to do is create a field. And I’m going to the field name has to align with something that that will be implied when I pass in that found set. So in this case, I’ll pick borders to it seems to remember my old my old values from the other report. That makes it easy. So if I pick that field name orders to customers company name, which is a string, I get that company. So there I’ve declared the field. Now what I can do is I can drag that field onto my report. You can see that it puts a it also adds a column header and a column footer. What I could do is I could make this the Iteen Enki as well. So dollar are, let’s the engine know that this is what’s called a resource bundle. And then I put in the key name, which I think is example dot dot company could be wrong. I think that’s what it is. So I’ll shorten up this detail section so we don’t get such a a tall report. And I’m going to save that. And why is it not saving? I don’t know. I didn’t save. So now if I look at my reports directory, which would be in here, you can see there’s test JR XML. And if I go back to the source code of my sample solution to that run report method, I’m going to change this from order listing to test. And I should be able to go to the running application because everything broadcast into the client and rerun this. And I get a new report. And I guess I had the wrong. I know what I did for the Iteen Enki, but you can see all the company names there. The Iteen Enki should have been a field and not a label because it’s interpreting that literally. If I go into my my go to meeting consoles in the way here, if I go to my palette and add instead of static text, actually add a text field to the column header. And this one should be that dollar are example dot company. I’m going to save that again. And rerun this report. And you can see while my local is still in Spanish, but you can see that that did update the Iteen Enki. So that’s how I was able to do the Iteen Enki. Those pretty cool terms of it. Literally in one minute you build a report from scratch and it gives me a lot better idea on how this would work from scratch. Obviously, the other report has a lot more complexity to it, but I think this is a great starting point just as groups. The other report has groups and and summaries and that’s, you know, that’s just another few minutes before we have that. So, but you get the idea I think. Excellent. Well, it looks like with this webinar, most people also have the base training set to get started with Jasper reports and start building your own powerful reports that will run in both a smart client in web client and G client or even on mobile devices where you can push PDF files or any other output. I believe this can output to anything, isn’t it, Sean? Yeah, XML, Excel, PDF, HTML. The name of you. Alrighty. Excellent. Well, we’re running a bit over time. So we’ll end it here. Head to the forum. If you have additional questions, we will be keeping an eye on the forum in the next few days. So feel free to answer any of the questions you have in addition to what you’re seeing here on the forum. We will post the recording as soon as possible and we look forward to seeing you all again in two weeks with again another great topic and this ongoing thread of and please also keep posting suggestions for topics. So if you maybe want to expand more on reporting or on BI, then please post it on the forum so that we are aware of what your needs are and we can post these kind of tutorials and tech webinars so that you can learn more about the Servoy. Thanks again for attending today. It was a pleasure hosting again and Sean. Thank you very much for all the hard work and preparing and in presenting this session. Thank you all.

Close