i-net Clear Reports – Tips and Tricks for the Designer

this is our report designer and I’m just gonna walk through and just create just real simple report to start with and then we’ll kind of make it more and more complex as we go along so to create a new report you just click here on new report from template you can choose one of these nice layouts so let’s say we want a data table of using our sample database which just has very simple sample data kind of like a Northwind type thing on our customers so we drag in the customers table this has an ID for the customer it has a company name city country and all that so as we go in we can say what columns were interested in just to start with of course we can always change this later once we have the report so let’s say we’re interested in the company name where the customers at and which city in which country just to start with so here we have our generated report once we hit result it runs the report and so here we see all of our different customers throughout the world and our sample database and so one thing you’d said you would be interested in is is parameters and prompts so something we can do with parameter is we can say okay we’re only interested in customers in a certain country so instead of creating multiple reports and saving each one one report for Germany one report for England and all that we create a parameter over here and say okay we’re going to call this country and we’re going to use this parameter to then filter the data and say we only want customers from that country so to start off we’ll just leave it like this go into our record selection formula over here that’s this button up here to filter my data so what I need to enter into here is a formula for filtering data so what I do is I say I want the customer city a customer country to equal my parameter so it’s that simple and then if we click down here on this sequel statement it’ll show you what the where part of the statement will will be converted into so these this placeholder here will be replaced by the string I enter when I run the report so when I run it now it asks me for a country so now if I type in for example Germany it now only shows me the customers from Germany now of course this is not exactly very user friendly because if I accidentally have a typo in here or something then it’ll end up showing me no data I have to type in the exact right strings so what I can do instead is I can actually provide default values now we used to only have static value so I would then actually type in Germany all that here but for a few versions now we’ve had something with dynamic default values what we can do is we can fill in default values from the database so that we don’t have to type it in anymore and that that those are these things right here we can say the value field is country and so then this will make sure that at run time when I run the report it’ll fill in the default values so we go back in here and reload the report it’ll fill in the default values for this field right here so these are the four countries that I have in my database I can choose it hit OK and I’m done so now let’s say we’re interested not only in the in filtering by country but now we also want to be able to drill down all the way into cities as well but if what I would only change this to city I would get this really long list of cities throughout the world and so instead what we can do is something called a cascading prompt so you create a new one you call it city you fill it with the cities now if I would run this what happens all right let’s go ahead and add this into our filter here and city equals cities now if I run this it’ll once again give me the default values here but here as you see it still gives me the cities throughout the world not only not only the city from the country that I picked so if we want to make it cascading what we got to do is you simply go back in here we select the country as its cascading parent and what that means is it will dynamically filter the cities that have fetches from the database by the country that I picked in the in the first prompt so now when we run this again you’ll see that if this is turned into a tree because the values

that will show us for city will be filtered by the country that I pick so if I pick like let’s say for example the USA and I go into the city then it only shows New York City and Chicago because what it did was it looked at the country that I had chosen so if I pick Germany and then it filters the the default value data that it gives me for here so now if I click Berlin I’ll only see the countries that are other the companies that are in in that city so so far so good let’s go ahead and take it a step more complex let’s say we’re interested in getting them in our report more information about each company as a sub report because what you can do is you can embed entire reports then inside reports so that you have these so-called sub reports so we click on the separate port thing up here we can go ahead and just drag in an area here and what it’ll do is it’ll create a support element in our report we can call it orders by this customer for example and we’ll call it an on-demand sub report what that means is it becomes a link that we can click on that then opens up that report so I can then open sub report here and now you have an extra tab up here and basically this is just simply another extra report with its own set of data with its own set of everything right now it’s empty that will then be added in and so when we click on it and in the report we click on it it opens up the extra report now of course this is all still empty because we don’t have anything so let’s say we want to show then all the orders that this customer made made so we go back into this report go into the database wizard up here to choose the data pick our sample database that we had before and we’re interested in the orders so we drag the orders table in there so this is the orders and now we just simply drag in the columns we’re interested in order ID for example and say the order date so but we want to filter this now if I leave it like this we can just real quick just for making clear what’s going on here if we click on here what actually happens is it shows not only the orders by this customer but all orders because we’ve done absolutely no filtering in this sub report we’re simply showing all of the records of all the orders we but what we want is only the customer that is currently this record so we need some way to link this main report specifically this record that is currently going on here with this SUP report and to do that you use something called sub report links which is right here under open sub report and then what you have is here’s the main report and you can drag in what you’re wanting to link with the sub report so if we drag in the customer ID over here then down here for the sub report we can say ok we want to select data in the suburb court for a certain field so it noticed that we just dragged in a string field and so it says okay do you want to filter by that field if so pick which column you want me to filter so we want to filter by orders customer ID which is one of the columns in the sub report and that’s it now when we go back inside go ahead and reload click on here you’ll notice before we had 99 pages or whatever of all the different orders now we’re only seeing the orders that were made by this customer that I specifically clicked on right here so here is an entirely different set here are the orders by the one customer here are the orders by the other customer so that enables us to really quickly just filter out data that we’re interested in or drill down and get really powerful reports where we can look at a lot of our data all was just one report now of course what we have here is we have a certain customer ID that we linked with so if we put that in here then it might be helpful for our report to see exactly which customer this is now of course all we have here is an ID now we may want to actually know the company name we don’t have that in the orders table we only have the customer ID so it just what we can do is add in a second sub report link over here link in the company name and instead of selecting or filtering data we just leave it and it generates a prompt field actually which is then used automatically filled with a parameter right when we click on that sub report I

hope this is making sense so basically when what we’ve now done is this is because we just added in the sub report link this has created a parameter field here not only for the ID but also for the company name and this parameter field then now has the name of the company inside because it’s its then when we click on the sub report it then fills in the value it just doesn’t do any filtering because there’s no filtering to be done with the company name I’d only see just a simple ID but I actually see the name of the company that I’ve that I’ve opened up the separate port form you’ll notice that by default a sub report even if it’s just a link is the in this box if I want to change that it’s very simple to to go in and change layout and properties and stuff I just go in properties go on sub no go on border take out the border and let’s say for example I just want to turn it into a hyperlink type thing I can go over here to font change the fonts to blue make it underlined and as simple as that then I now have a simple hyperlink in my report so if I tap on that then it turns it into a separate point and this time we’ll say what we’re interested in is we’re interested in the individual orders that have gone out in our sample company here samples database or whatever we drag in the orders table and we’re gonna be interested in the order ID and when it went out and so let’s go ahead and click on finish but we’re gonna not be interested in all orders but once again we’re gonna want to filter it and this time we’re not gonna filter by company or anything but we’re gonna filter by date range so we’re gonna only be interested in in orders in a certain range of dates in a certain time period so we say we create another parameter field here it’s a date parameter go into Advanced Options say we want it to be a range not just individual dates but a range of dates and we’re done with that parameter and then once again we’re gonna want to filter up here and say we only want to see the records where the order date is in our date range so now when we run the report it will pop open this prompt dialog and we can then choose what date range we’re interested in now I happen to know that in our sample data we actually only have data back in 2003 or something so I’m gonna pick a date range starting in 2003 and there you see we have data starting here in 2003 and it’s 41 pages of data if I would change this date range now to for example say from January to the end of January 3 then you’ll see this will drop down it’s only three pages now we only see the data that’s that’s in 2003 so that’s just a real simple way to to filter data also by dates not only by strings or whatever and what we can do is if we’re interested in changing this or manipulating this in any way that was another question I think you you all have is is there a way to just manually hack in a sequel query or copy in a sequel query what we can do is there’s this button here in the date database wizard called – sequel and what that does is it takes our report and generates a sequel statements that it normally would be sending to the database only then we can manually manipulate it change it whatever and then have a query rather than tables and joins and stuff we’ll have a manual query that then creates what we want so really if we don’t only want the order ID and the order date we can just go ahead and say select star we don’t need to have the alias there and just say select star from orders where and then we could of course order by or whatever we want to do in here we could we could put in a manual sequel statement when we click OK what then nope and what I do here oh oh the database we’re using doesn’t allow us to have a between okay so that was a bad choice but we were just trying to do something that the underlying database that our sample database is based on

it’s not able to do so that was a silly idea sorry my fault so we’ll go ahead and cancel that we’ll just leave it the way it is but that just shows how you can do you but then the downside of that is as I just basically demonstrate it is you’re gonna have to know you’re secretly it’s gonna be then raw sequel that will be sent to the dating database for that report okay let’s how about I try and show you guys a little bit of a like an advanced trick that actually we learned from a customer they were the ones that came up with this trick and I kind of really like it when we have so many different rows here it can get we have this long list of values it can get kind of overwhelming and one way to make it a lot more readable is to have alternating highlights of the colors so we make a gray white gray white and the way to do that is you just go into the detail section properties go here to background and then you can create formulas to basically dynamically generate these values for each of these for the sections or whatever we want so if we want to dynamically determine the background color of our detail section we would just go into detail hit the formula button and then what we would just simply say is if record number which is that’s the current record number that we are on modulo two equals zero in other words if it’s an even number then use I think actually I can just type in gray yeah then gray else white so it’s very simple so what we’re saying is if we’re on an even record then have the background color be gray if it’s an odd number then have the background color be white so when we tap here and result there you go a lot more readable we could obviously pick a much lighter shade of gray or whatever but it was fun because a customer sent in a report and a support call one time and we were like wow that’s a great trick so we remembered that one so that’s just one way to to make your make your reports a little bit more readable and see what else do we have okay let’s say we go in here and we will be interested in the order amount that happens so go ahead a little bit of room here okay now one thing in here is you’ll see I’m having issues because my two fields are already too big so if I wanna if I want to make this a little smaller all I have to do is just kind of move these around what I can do is I can highlight two of them like so no don’t need that one and then I can go on the line or resize so if I want to resize it to the narrowest then what that’ll do is it’ll take my order date and resize it automatically so there’s a little bit more room it’ll resize it according to the order ID and now all of these are kind of a little bit on different levels so another thing I can do is I can just highlight all three and say aligns the tops and then automatically make sure that they’re all aligned on the same height so that’s just those are really helpful things kind of when you have a lot of different elements and you’re trying to align them and make them be more readable that though you just simply right-click on the elements and then you can and if you have more than one element selected then you can align and resize so we run this we’ll see the amounts here on the right and let’s say we’re only interested in even seeing orders of a certain magnitude we don’t really necessarily want to filter the data but we want to filter its display so we might still want those values to be in our report internally for example for charts if we have those or whatever but we’re not going to want to see them and so what we can do is over here go back into the section properties I can go ahead and take back out the alternating colors but instead what we’re gonna do is we’re gonna suppress this section which means do not display it don’t show it in our report in a certain condition so we can say we want to suppress this section in case for example the order amount is just negligible let’s say less than 500 or something so it’s important to kind of note the difference here this is different than in our record selection formula we still will have these records fetched from the database but they’re not going to actually display in our report when we hit here you’ll notice none of these values now are less than 500 because what happened internally is it fetched all the data and then after it fetched all the data any detail section was suppressed and that’s why I don’t know if you can see that on Google+ but it’s slightly grayish here and what that means is

under some circumstances this section is going to be suppressed and those sections were then simply removed from the report one other little trick maybe especially if you have reports that are a little bit longer you may want to for example on multiple pages if you’re going to print them out have the elements be maybe more on the left on even pages and more on the right on odd pages all of that works what you what you can do is you can create to detail sections and simply suppress the one so we’ll go ahead and highlight those hit ctrl-c ctrl-v for copy and paste and then move them over there and so then what we’ll say is on even pages we’re gonna want to display the the first section on odd pages we want to want to display the second section of course you’re gonna want to do that for some of the other elements as well and that way you can kind of when you print it out you’ll have a much nicer layout for a binder or whatever you want with the various customers want different things and it just yeah it’s one way to do that so if we go in here we can say under the suppress section not only less than 500 but also so if it’s less than 500 or if the page number modulo 2 is 0 so in other words we’re saying okay either it’s a negligible amount or it’s an even page and if so then we want to have it suppressants so we can go ahead and copy that out and then go over here to the B section and put that in as a suppress only this time we’re gonna want to have it B equals one so suppress it if it’s less than 500 or if the page number that we’re on is odd these are all different properties whether to add in an extra page before the section or after the section whether to display this section at the bottom of the page and all these different layout options not only are they little checkboxes but you have formulas here on the right to dynamically then determine whether or not that happens so okay that was that and then what are you thinking here this is my friend Gary my colleague Gary you know I had a couple of other ideas who wanted to show you yeah when you go into the record selection formula John typed in so that’s special work for for a formula in our system and what you can do you basically see all the functions that we support on the right side here on the right hand side and you can just search field type in like in and it fills out all the options that you’ve got and for example he’s got the in string x and y or substitution I don’t know in range in the X yeah sure it’s about any X and so you can just hit something you’re looking for like date functions and then you can see all the dates functions or the constants so you can filter real quick into you into what you want to do basically you most of the time you’ve got an idea of what you want the report to achieve and you’re looking for the right things for the function for the formula stuff and you can find right here another useful thing is when you when you highlight a function like in up there you can documentation and what you see is the page from our documentation which says how to use it what is an example about it but what will return that works with all the functions operators and so forth that you have on the right side there are special words to the system in addition they’re all highlighted in a different color so you notice okay that’s a special function you can’t use it for variables but X for example okay yeah so that’s what I’m gonna show you