Customized Reports made Simple, Beautiful with MS Word Plug-in from iTech Pros
Customized Reports made Simple, Beautiful with MS Word Plug-in from iTech Pros
Before we begin, can you give us a quick rundown of iTech pros, who you guys are, what you do, what makes you special? Yeah, absolutely. So we do primarily, uh, server-wide consulting development training, and a couple of years ago, we bought out IT2B, so we also handle the server-wide components, and we’re adding some NG components as well. Okay, so that’s plugins, components, beans, libraries, the whole kitchen sink. Yeah, awesome. So most people know me, I’ve been working with server-wide for about 10 years, and with me, I have Paul Bromwell. He is our senior developer, and he’s actually the author of the reporting feature that we’re going to demo today. Okay, thanks for coming, Paul. The wizard behind the curtain. You know what, man, behind the curtain. So can you guys, well, first of all, for, you know, the tradition of the tech series, as we like to show the demos first, and, because I think that’s why people want to come, and then we do an overview afterwards. So I’ve asked these guys to prepare a few demos. Scott, can you tell us a bit about what you’re going to demo today? Yeah, so we’ve got three of them. The first one is just going to be kind of basic expressions, for how to get data to show up on your reports, and then we’ll get into more complex examples like charts and doing like a common invoice. So at this point, I’ll stop sharing and we’ll turn it over to you, Paul. Sounds good. You see my screen, okay? Yeah, we’ve got it. So I have a Northwind little example here, just customers to orders to order items. And we’re going to first look at the expression demo. I already have the word document pulled up over here on the right, but from the code perspective, I’m just grabbing a found set, learning the record, setting up the output file. The key is doing this open document call, which is pointing towards this template file. You use this new call called create link data source. You pass in the found set you want to use and what you want the found set to be called inside the template so you can see the shippers over here matches the shippers over here. Then you basically say I want to merge the link template, save the file, show the PDF. You can see the raw values over here on the left, the operation I’m doing in the middle and then what it looks like on the right. So I’m taking the ID one and I’m actually formatting it with the thousands digits. So you can see over here the formatted same for uppercase, you put the colon upper. Same for lowercase, you can do capitals on the first letter of each. You can do the first letter or the first letter, the first word capital. You can turn regular text and hyperlinks, you can format dates, you can format times. So that’s just a simple expression demo and you can see over here on the right, it’s pretty simple to set up each of those. It’s pretty much the name of the field that you want in brackets, colon and then the format for that. Nice. Moving on to the invoice from the code perspective, I’m just making sure the order that you click on has a invoice record and I set that up if it’s not there. Open up the opening up the template. The new piece with this is that you can add relationships to it. So while I pull up the. So the main found set is called invoices, but when I add the orders to customers, you can rename it to customers. They don’t have to type all that out. So I’m basically setting up all the joins that I want to be available in the report. So I’m going to do that. So the, at the top, I’m printing out information about the company that’s producing the invoice. On the right, I’m doing the date invoice number of customer ID, built to populate with customer information. And I’m actually looping over each order item in the relationship, writing out the product name, the quantity, the unit price in the total amount with proper formatting. With the reporting engine, do you have access to aggregates. So down here, I say total line items and I want the number of line items in there using this dot count. That’ll give me the three thumb looking for. And you also have access to the some aggregate. So I want to sum up the quantity of every. Order item I have here. I want to print out there. With this new reporting engine, you also have access to calculations. So I have a calculation there called calc sub total formatting to number in the plug in runs the calculation stores or prints it to the report there. And you could do the same thing with pure link expressions. Here I’m creating a variable inside the template, storing that to sub total. I’m basically getting the sum of the quantity times the unit price. Printing out the sub total multiply by the tax rate. So there are two ways to get that done with this new reporting engine. Paul, can you a multiple house on those relations, did I see that right? Yeah. So I have my invoices found set and hopping from invoices to orders, then orders to order details, then order details to products. The key is just to add every step of the hop. So make sure you add invoice to orders and then orders to order details and then to products. And then the second argument there in the string versus customer employee details products. That’s like an alias right. Yep. So you can see invoices to orders called order. Yeah, right there. I get it. Yeah. So that’s nice because then your, your, the person who’s going to be modifying the template or designing the template doesn’t really have to know the names of things in your database. So you get sort of control on how to expose those. Yeah, it provides the abstraction. So say you need to even rename a relationship. You now don’t have a hundred templates that you need to go through to update that relationship. Right. Right. Yeah. That’s, that’s really nice. So our last example. A little report. With the reporting engine, you can actually do filtering, grouping and ordering. So basically what I have here is a list of all the order. Let me go to the code first. So I’m grabbing the orders found set. I need order details to get the price of things and then I’m merging the template there. So what I’m doing here is I’m filtering where the order date is, or the year of the order date is 2006. I want to group by year and month and then order by the year and then by the month to make sure that the years go in their right order and then the months. Also go in the right order. So since we have group data here, we’re going to loop over that group data for now the year. I do the plus 1900 here because the get your call on timestamps returns the number of years since 1900. So I wanted to print the correct date there. Months are also zero index. So we want to have that appear correctly. So I’m going to give me the count of the total orders and then I’m summing up the price of each individual order and then summing that up so you can see that in January we had $4,085 and that’s aggregating all that data inside of the template. So also sort of build this, do you themselves like a data set. And convert that to a found set pass in. And in memory found set should work. The plugin doesn’t currently support data sets. Okay. And there if, if, for those of you that are either evaluating Servoyor new to servo, what Paul and Scott just talked about is a way to sort of have arbitrary kinds of data sets where the data source could be from a special query. Or from a web service or anywhere really. And what they’re saying is because they’re the report template engine is based on Servoyfound sets that you have the flexibility to take advantage of all the other features of Servoywhen you when you pass that in. So if you want to pre, you know, calculator aggregate things, you could do that as well. So that’s kind of just for those of you who aren’t deep into servo, that’s what they’re talking about. And the last thing I have to show is you can actually. Generate charts based off of this data. So I’m saying my x axis is the month slash year. And then my y axis is basically the same thing from up here. Just give me the sum for that month. You can see here that it charted the data from the data set. So that’s what I’m saying. Excellent. Thanks Paul. No problem. All right. Let’s go back to some slides. Okay. Can everybody see my screen again? Yes. All right. So we’ll sort of have a diagram of just how the different pieces fit together. So if you’re going to get a Servoy application and then you’ll be using the word plug in. And like you saw on Paul’s demo, you’ll pass like as parameters, the found sets, relations that you want and the path to the word template file, which is just a normal word file setup like Paul showed the plug in. We’ll then open up that file merge your data and then write it back out to the file of your choice. And then we’ll just use what the format of that file is. So it could merge it into another word document or it can also produce like HTML and PDFs and those a couple other options as well. So how does it work. As found set based like we talked about your data sets are supported through in memory found sets, relations, calculations, HTML images charts, all that sort of thing is supported. It would execute it server side on the ng client and the web client and then on the smart client it will get executed clients as you would expect. So the templates are in Microsoft Word like Paul showed is similar to mail merge if any of you guys have used that before but it’s a little bit simpler because you don’t have to go in at all the fields you can just use the, you know, less than greater than sort of tags for the link syntax. So there’s a lot of more advanced option like Paul showed so you can do looping there’s lots of built in functions. And we’re going to have good documentation on what’s available, but we can also become so directly to expose and see kind of the under the hood stuff of what’s available as well. Support wise it’s available from Servoysix to current version. And then we, why did we build this thing as many of you know there’s there’s lots of other reporting tools out there. They’ve got the Servoynative that really doesn’t have ng support anymore. And the feature set is a little bit limited. We don’t see too many people using that anymore. You’ve had Jasper reports, which is really an excellent advanced tool you can do almost anything with Jasper reports, but are really requires some advanced knowledge. So you’re going to need to be a developer and really learn the I report or Jasper reports studio tools in order to build those reports. Next up, we have velocity reports, another great tool it’s really. Great for web developers so it’s HTML based templates, but it does require a little bit more advanced knowledge again because you’re going to have to understand CSS and HTML in order to really build your reports. And I think that really the special use case for this plugin is user customizable reports. Of course, you can do some general reporting like call showed, but because the reports are done in Microsoft Word, it really makes it easier for like it sort of your average customer to be able to customize their own report. And so that’s just case wise, but we think is that like a SAS solution where maybe you have like some basic report templates and voices or grand summary kind of things. You’ll have a base report that will get billed by the developers, and then the customers would have the ability to sort of download that template and tweet it to how they want styling it, moving things around, however they want. So you really have their own permutation of that report that’s really branded and customized them without you having to build that for them. We’re also working on a sample solution that will come with a data dictionary. So for those of you unfamiliar with that term it will it will go through all the found sets and relations that you’re passing into your report. And it will build a like a PDF report that you could share with your customer that will show what all the names are for the data objects and what the column games are that are available, and then you could tweak that to your, to your liking. And so we’re going to be copying that. So we think the developers going to build the standard report template. You might go ahead and add some calculations to your solution just to make the hops a little simpler for the user or some of the formatting options. And then we also think you would build a system that would allow the end user to see a list of your reports and then download that report for the. And we’re going to show that next. So yeah, end user would come in, they would download the report template, they’d open it up and Microsoft Word, they make their changes they uploaded back in, do your system and they would click preview, and then they would be able to see their report. And so that’s kind of a workflow of how we see that possibly working as a bonus, you could integrate with serve ways office 365 component. So when they go to edit that template, you could actually pop that up in the office 365 online editor and they kind of could stay within your solution, do those edits. And you could hook into those changes and pull those back into your system. So they wouldn’t have to launch like an external editor. So some of the limitations currently only the equality operators are supported on the joins, so no greater than or less than. Constance on joins are supported yet. We are working on that. And some reports are not supported yet, but that’s something that we are working on. So, so Scott, when you say the joins, you mean the relations that are are passed in. Right. So like in Paul’s example, he passed in orders to order details. So that needs to be equal operators with no constants. I couldn’t do like customers to orders, you know, last week or something. No, the way you would do that, currently would be to use the filter options in the looping that Paul showed in the template. Or, or on the found set itself, I guess. Yeah, absolutely. Okay. So current status, we got a beta release in April 2nd. So if you do test this out, you would need to use the beta release in the IT to be installed as integrated into the existing word plug in. So it’s not, it’s not a new plugin. It’s going to be a free update for everybody that is a current user with an active subscription. We’re working on building sample solutions and better documentation. And we’ve got free trials at the website, sir, what components.com. All right. So Q&A portion, and we’ve got some useful links here. So our website for consulting and developing. a pros.com and sort of what components.com for your plugins. Excellent. Well, thank you, Paul, for, for the wonderful demo and for developing this, this wonderful extension. And thank you, Scott, for introducing it to us. The questions are coming in. So let’s go over a few. First question is can, can users upload their own templates? Absolutely. So you really just have to pass in the path to where that template would be. So by default, Servoyis file upload plugin allows you to specify like the default upload path. So you can, you can handle that. However, you wanted in your backend solution. Okay. And another question is this functionality part of the standard word plugin or is it a new plugin when we’ll be available. I think you covered that on the last slide. Okay. Then, is this available only in the ng client or is it also available in the smart client? Smart web and ng. Okay. So, so for those of you that don’t know the difference. ng clients are HTML5 client web clients is our classic client. So the more legacy version for the browser and then the smart client is a thin desktop. And this product is available in all three. Can templates be multi tenant or multi user? Yeah, absolutely. So in the system we described, you really have your, you can decide, you can do many ways, you can store the templates in the data. base. If you wish or you can store them in the file system. And then when the user clicks to actually run that report, you would just decide how you want to handle that. So it could be like a folder based system where you go and look for that version of the report in their tenant ID folder. And then you run that one. And if not, then you run the standard one. That’s one example, or you could have those actually stored in the database and then go out and get the right report based on to the user is in their session. Right. So this is on server side, I guess it’s up to you. You can do it however you like. Right. Okay. One asks, is it only for in memory one, I don’t quite know what, what you mean. So can you maybe expand on that while we take some other questions. Can reports be scheduled. Sure, plug in doesn’t have any features for that, but the headless client would be supported as well. That’s one we don’t talk about too often, but you can definitely have a batch processor running server side that takes a look at a database to figure out what user wants what reports and what time they should be sent out. And then you could make the plugin calls to execute those reports and then send them off to the user via attachment or like. Yeah, that would be like the complete version of this. So, so just so you guys know the topic Scott was talking about is available and I think an older webinar about the headless client or doing batch processes or scheduled tasks. So if you rewatch that from the archives and combine the learning from from that with this webinar, then you have the answer there. Another question, can you use barcodes or QR codes in a word document. If the yes, so if the word has that support so via a plugin or extension or wherever you’re embedding those, it can basically just pull from the merge data just like anything else just like images for example. I don’t think we showed an image but yeah, like a normal image, you can pass that kind of do. And another question, does it require word to be installed on the client. So is it natively interacting with Microsoft Word on the desktop or something. Not also word doesn’t need to be on the server or the desktop and the client doesn’t even have to use words for that matter. If they’re using like an open source Libre office as long as the template is formatted correctly and it saved as a dot doc x file it will work. And he said when he entered he saw that it only supports memory data sources. So I think he came in while we were talking about that. Just I’ll answer this one just to reiterate this is based on Servoy found set so all of the all of the little business objects that you have available in a Servoy solution that are available in a Servoy found set will work and those can be. And so we have a lot of examples which are like an in memory implementation or it could be bound to any relational data source. So I think that’s it you mentioned we had one question about office 365 but you did mention that it can work with office 365 in that you can you can pull the templates out of 365 and use them. And so we get a lot of requests for that we might consider you know adding that as one of the sample solutions right right okay all right it looks like we answered all of the questions. Some some comments also from some fans of of iTech pros. These guys have been working for many years in the Servoy community and really know what they’re doing and I think some of your customers are here because you guys are here so I want to thank I want to thank you guys for joining this webinar. And you know this is really the kind of stuff we like to show like to show what’s possible and we like to show what other people are doing with Servoy and with sort of all the ancillary technologies so really really great job Paul. Thank you very much Scott again the recording will be available and we’ll be back again in two weeks thanks everyone. Thanks.