Forward Calculated Field with airSlate SignNow

Forward calculated field

welcome to this wise owl report builder tutorial in this video we'll explain how to add a basic calculated field to your reports we'll begin with a quick look at how to add a simple ad hoc expression to a table and explain how to use the expression builder to insert column names and operators well then move on and explain why a calculated field has some advantages over an ad-hoc expression and explain how to use the results of that calculated field to display them in a table to sort that table and also to filter a data set at the end of the video we'll also explain how you can add calculations to the underlying SQL query which populate your data sets and explain a few of the advantages and disadvantages of that approach so let's get started I've started by creating a branch a new blank report in report builder and the first thing I need to do is create a connection to the Wisel movies database just a quick reminder that if you don't already have a copy of that database you can use this video which we created to help you install it and you'll find a link in that video's description to download the script to help you do that anyway assuming you've already got the database installed let's create a new data source in the report by right-clicking on the data sources folder and choosing add data source I'll call this one movies and then we can choose to use an embedded connecting in the report it's going to use Microsoft sequel server of course I'll click the build button rather than write out my connection string from scratch I'll type in a reference to my instance of sequel server which is called SQL 2017 in my case and then from the drop-down list towards the bottom of the dialog I should be able to select the movies database having done that I can click OK and ok again and that's the data source created next let's add a data set which will include the fields from which we'll calculate our new values we can do this by right clicking on the movies data source and then choose our data set we can give the data set a sensible name I'll call this one films and then rather than writing out my query I'll click the query designer button to help me build it from the tables folder i'll select the film table and then from the film table i'm going to include the title the run time minutes the budget dollars box-office dollars oscar nomination and Oscar wins I'm also going to include a few filters in this data set I'm going to click the add filter button first and the first filter I'm going to add will be based on the budget dollars column and I want to make sure that I select any film whose budget dollars is not null I then want to do the same thing for the box office dollars column so I'm going to select box office dollars in a new filter and then set the operator to is not and then the value to null finally I want to make sure that I only include oscar-nominated films in this list so I can add one more filter I can set the column or field name to Oscar nominations and then I can set the operator to is more than and then enter a value of zero for the Oscar nominations having done that I can click OK click OK again and there's my data set created now let's add a table to display the columns we've selected I'll begin by tidying up the reports by removing this title placeholder and then I can right click on the page footer and do to remove that I'll then right-click into the body of the report and choose insert table and then having dragged that up into the top left-hand corner I can simply start assigning the columns in the order I've selected them so title run time minutes budget dollars once I've run out of columns I can either right click and choose to insert new ones or even more simply I can click and drag column names from my data set and attach them to the right-hand side of the table once I've done this I'm going to quickly just run the report to check that I can see all the things I expect to see and as I expected I've encountered this little text display book which has been haunting me for the last few months with reporting services and simple way to make sure that all the text appears if I head back to the design view and then highlight all the cells in the table if I simply change the font pretty much any property of the font I think but if I change the font name to a different font and then simply Reese elect the one that was already selected if I head to the run option again I should now see all the valleys from that table wonderful so back into the design view and now we can start thinking about how to add our calculated fields the first calculation I'd like to add will work out the film's profit by subtracting his budget from his box office there are several ways I could add that calculation in report builder one simple way is to almost treat this table as a miniature Excel worksheet if I wanted to have a new column to show the film's profit I could click somewhere inside the table in the box-office column and then right-click on its header to choose insert column to the right in Excel it then just clicking to the the cell and start typing my formula beginning with an equals operator that doesn't quite work in reporting services or report builder what I need to do here is right-click into that empty cell and choose to view the expression builder so I can click the FX button next to the word expression the expression builder then shows me the equals operator and I could begin just typing in my formula longhand but it's much more convenient to use a list of options in the bottom left-hand corner to select the various components of this calculation the first thing I need to do is refer to the value of the box office dollars field in the dataset so if I find the fields category next to the name of the date set films on the list on the right hand side I can simply double-click box-office dollars to insert a reference to its value I could then manually type in the subtract symbol or if I was feeling particularly lazy and couldn't be bothered taking my hand off the mouse I even have a category of operators and inside there is a subcategory called arithmetic and if I select that I could just then double click the subtract operator to insert it into the into the expression that's a bit of an extreme example that one of course but um it's nice to know what list of operators you have available so you can see all the operators in these various categories here now what I would like to do is insert a reference to the budget dollars field so I can head back to the fields list next the film's dataset and then double click budget dollars to complete the expression and you should end up with something looking a little bit like this having done that I can click OK to create my calculation and to see the results all I need do is run the report and check that I see a list of numbers I'm not going to do the mental arithmetic to check that that's correct I'm just going to trust that it's doing the right thing so that works is is fairly easy to create but there are a few downsides to it first of all I guess we don't get the column heading at the top of us although that's a fairly simple thing to solve we could just type in a column adding of more concern is is how I would reference that value later on in the report what if for example I wanted to sort this table so that the most profitable film appeared at the top of the list I don't currently have an easy way to reference this value that I've created so although ad-hoc expressions that we've just created are fairly easy they're not necessarily the best choice for writing calculations in a report rather than using an ad-hoc expression then we'll move on and create a proper calculated field in our films data set I'm going to start by selecting the cell containing the expression I've created and hit the Delete key to remove it then I'm going to right click on the film's data set name and choose to add a calculated field I need to start by giving the field a new name so I'm going to call this one let's call it profit dollars don't include spaces in your field names and then if I want to build the expression I can once again just click on the FX symbol to launch the expression builder the calculation is exactly the same so let's head to the fields list double-click box-office dollars I'll type in the subtract symbol this time and then simply double click budget dollars having done that I can click OK a couple of times and then my new field appears in the fields list of the data set this means that I can add it to visuals in the report by either clicking and dragging it into the visual or I can even use the field selector in a table that will show a list of calculated columns alongside any query columns so if I choose profit dollars that will also automatically add the column header which is nice and if I then run the report I'll end up with exactly the same results I saw earlier but with the added advantage that I can reuse this profit let's call them in multiple parts of the same report let us add a bit of basic formatting to our dullest columns just to make the values a little more readable ideally I'd like to use the text box properties dialog box to do this for all three cells at the same time but sadly I can't do that if I select multiple text boxes I can't view the properties dialog box so what I'm going to do instead is format the first dollars value using the text box properties dialog box so I can right click budget dollars and choose text box properties I can then head to the number tab on the left hand side and choose the currency category I'll reduce the decimal places to zero and I'll also include the thousands separator I then want to change the currency symbol view so I'm currently using my regional settings English United Kingdom if I change this to US dollars I can scroll upwards to find English or dollars English United States and select that and then click OK now what I'd like to do is quickly copy that same format to the other two cells one way to do this is to head to the review tab in the ribbon and view the properties window with the budget dollars column selected or the budget dollar self selected I can scroll downwards in the properties window to find the number format property so this shows me the custom number format that I've created by using the dialog box if you know the syntax of these well enough you can just type these directly in but if you wanted a bit of help you can use the dialog box as I have just done and then just copy and paste the custom number format the advantage of the properties window is that it remains visible no matter how many objects you've got selected suppose if I select the other $2 columns box office and profit I can scroll downwards and find the number format property and then just paste in what I've just copied having done that if I head back to the home tab in the ribbon and then run the report again I should now see a slightly more readable version of the same values the next calculated fields I'll create is going to divide the Oscar wins by Oscar nominations to work out the success rate so to do that let click on the film's data set and choose out calculated field I'll call the new field success rate again and without using spaces then hit the FX button on the right hand side to launch the expression builder this is a fairly simple calculation again I can head straight to the fields list double-click Oscar wins type in the forward slash for / and then double-click Oscar nominations so your end expression should look a little like this if I then click OK a couple of times and then I can drag this success rate field into my table and attach it to the right hand side if I then run the report I will indeed see a result for each film so currently it's being displayed as a decimal of course this is the idea the underlying value that I've just calculated as a decimal data type ideally I'd like to format this as a percentage so rather than 0.75 it'll say 75% instead let's head back to the design view and again this is fairly easy to achieve we've only got one column to form at this time so I can right click success rate I can do text box properties then I can head to the number page on the dialog box choose the percentage category use two decimal places and then click OK a quick run of the report and that should look a lot neater at this stage the final calculated fields will create in this data set is going to demonstrate that you can use an existing calculator field in a subsequent expression so I'd like to work out how much profit our films made per minute I'm going to do that by dividing the profit in dollars by the run time in minutes so I can right click on the film's data set and choose out calculated field I'll call this one profit per minute and then click the FX button on the right hand side to it launch the expression builder so if I head to the fields category for the film's data set because I've already added the profit dollars field to it I can simply select that to refer to it in a new expression I can then divide that by the runtime minutes which is one of our original query columns and then click OK and ok again I'll add that value add that column into my table I'll inserted just after the profit dollars but before the Oscar nominations and then I think I'd like to apply a bit of a bit of basic formatting to that so let's say I want to right click on that cell and choose text box properties head to the number time in the dialog choose currency and then I'll use decimal places this time so I'm not going to use exactly the same format as the other three columns referring to dollars I'll use the thousands separator and I will change the currency symbol from English to dollars English United States so having done that I can click OK and then a quick run of the report and I should see a new value based on one of our existing calculated columns so that's all fairly nice and straightforward as well as simply displaying the results of your calculated columns in visuals in the report you can also use them to do things like sorting and filtering so for example let's say I wanted to show the film with the highest profit per minute at the top of this table if I select something inside the table to expose the gray boxes around the outside I can then right click on one of those gray boxes and choose to view tablets or table X properties in the dialogue box I can then after the sorting page I can then click Add and then I can choose to sort by any of the existing calculated columns in that data set so I'll go for profit per minute and choose Z to a order if I click OK and then run the report I'll see a slightly different list and apparently avatar has the highest profit per minute of any film in the data set I can also use the results of calculated columns to apply filtering to the table so if I head back to the design view rather than filtering the table this time let's apply the filter to the film's data set if I right click on the data set I can choose data set properties then I can head to the filters page and choose to add a filter now there is one thing you need to be slightly careful of when selecting calculated fields for a filter if I start by selecting one of my built-in query fields so let's say for example of run time minutes you'll notice what happens to the data type that's selected on the right hand side I choose run time minutes the filter implicitly knows that that is an integer or a whole number again have I changed to a different option never changed a title you knows that title is definitely text now that doesn't happen when you select a calculated column if I head to the drop-down list here and let's say I wanted to I want to show any films with an Oscar success rate of at least 50% if I choose the success rate field the dialog box doesn't recognize the data type of that calculated column so I have to make sure I select this myself now the success rate of Oscars is generated as a as a decimal value or a float as is referred to in this list of data types in report builder so I need to make sure I select float otherwise the filter simply won't work I can then choose my operator I'll go for greater than or equal to and then for the value make sure you refer to the underlying value that's calculated so if you think back to what we saw the first time we added the success rate field to the table that was shown as a decimal number so a success rate of at least 50 percent will be 0.5 having done that I can click OK and then run the report just have a quick look at the results and I should now be seeing only films which have at least a 50 percent success rate okay so there we go for the final part of this video I just like to quickly talk about some of the differences between adding calculated fields to a report builder data set and adding expressions to the underlying SQL of your query which populates that data set to do this I'm going to make a duplicate of the film's data set first I'm going to right click on it and choose data set properties then I'm going to quickly copy the entire query from the query dialog all the query text box and then click OK I'm then going to right click on the movies data source and choose to add a new data set I'll call it new data set films - can't think of any more inventive names in that at this point and then paste in the query that I've just copied into that query text box if I then click OK I'll have essentially a version of the data set that we started with before we added any calculated fields to it next I'd like a new table which will display the results in the same way as my current table so let's do a bit of copy-paste here again if I select that table and then choose ctrl C to copy ctrl V to paste just because I don't have any calculated fields in this film's 2 data set yet I need to get rid of any existing calculated fields from the table so let's get rid of profit dollars and profit per minutes I can highlight those two columns and then choose to delete them then also get rid of success rate there as well I'll delete the success rate column what I can then do is I also need to remove the sort from that table as well so I can view the tablet or table properties head to the sorting page and choose to remove the sort for profit per minute while I've got the tablet's properties dialog box open I can head to the general page and then simply change the data set which populates this table to films too if I then click OK I'll then flows down the properties window to get give myself a bit more space and then I'm going to drag this new table over to the right-hand side of the existing one just so that eventually we'll be able to compare the rows side-by-side so having done that if I quickly run the report just to check that it works there we go so obviously the order of the films is different this Simon we're seeing a different number of films because of the filter that's applied to original films data set but we've got the basics now to start adding some calculated fields but added to the SQL of the underlying query let's recreate the profit dollars column in our new films - data set if I right click on the films - data set and choose data set properties rather than adding the calculated field I can simply write the SQL code required to calculate the correct value now to do this in the query text box is just a it's just a plain text box you don't get any help with syntax checking or intellisense you don't get the ability to insert column names etc you have to write everything longhand as it were if you prefer you might like to use a tool such as sequel server management studio write your query in that tool which has all the fancy tools enabled and then simply copy and paste your query text back into this dialog for this simple example I'm quite happy typing this one in so if I just zoom in a little bit so you can see what I'm doing I can give myself a blank line in the Select list and then add a new column starting with a comma I could then just copy and paste the existing column names here let's just write this one out I'm gonna say film dot box office dollars the case here isn't important - film dot budget dollars and then to give this an alias I can say as and then profit dollars okay so having two methyls zoom back out then click okay and find my new calculated column has been added to this query if I drag profit dollars into my second table I'll drop it next door to box office so in between box office and Oscar nominations I'd also like to format that cell in the same way as the budget and box office so I can right click and choose text box properties head to the number page choose currency 0 decimal places a thousand separator and then dollars English US for the for the currency symbol if I then click OK and preview the report I'll run it I should see a new column of results I'll just scroll along so we can read it more clearly showing the result so just as we'd expect I suppose the advantage of doing things this way is that there are performance advantages basically to writing your calculated columns in the data set query rather than adding them as calculated fields later of course the calculated fields that we've added to our data set are still useful you don't always get the option to write calculated columns in your SQL for example if you were using a store procedure as the basis of your data set you couldn't add any SQL to it you simply select the store procedures name from the list they're so calculated columns or calculated fields are still useful but if you have the choice it's probably better to do it in the underlying SQL query there are a few things that are slightly more awkward to do in SQL compared to basic calculated fields in report builder data sets which may means you lean slightly more towards the easy way of doing things let's show you a quick example I want to recreate the success rate column that we created earlier but in SQL this time so let's right-click on the films 2 data set and choose data set properties and then I can create a new column I'll zoom in again so that you can see what I'm doing and then if I add in a new calculation which will be film adopt Oscar wins / film dr. Oscar nominations I'll give this a quick alias as success rate so we've got to consistency in our column means having done that I'll click OK and then I can add that new column to my table if I right click to insert a new column to the right and then I can simply select this one using the field selector success rate I'll just apply the basic percentage formatting to this text box as well so I can right click text box properties head to the number page choose percentage with two decimal places if I then click OK and then run the report if I scroll along to the right hand side although I can clearly see that I've calculated somebody's the answers aren't quite exactly as you'd expect so for Jurassic Park actually does correct 3/3 is 1 100% the Spider Man as well that's correct 2 0 divided by 2 is 0 but things like King Kong 3 divided by 4 should be coming out as 75% and that's clearly not the case so the problem with writing our calculation in SQL is that the expression has to follow all the rules of SQL and one of the main rules it has to follow those to do with data type precedents in SQL if I create a calculation which all the values have the same data type so for example Oscar nominations and Oscar wins are both whole numbers both integers if I divide one integer by another integer in SQL the result will also be an integer so it must be a whole number so the only two possibilities here are either 0 or 1 thus clearly different with our calculated field that we added to our data set earlier clearly there's some kind of implicit conversion going on to convert our whole number data types into decimal values if we want to achieve the same thing in SQL we have to explicitly convert our data types so just to show you how to do that if I go back to the design view and then right-click the film's to data set and choose data set properties if i zoom in a little bit so we can see what's going on let's see technically we only need to change one of the data types of either Oscar nominations or Oscar wins into a decimal type because of the data type precedence rules all decimal types are more important than whole number types or integer types I'll make sure that I've converted or cast both of my Oscar wins and Oscar nominations into the float data type so I can do this by passing the Oscar wins field first into the cast function so I can say cast followed by open some parentheses and then at the end of the field name I can say as and then the data type I want to use so in this case I'll go with float I'll do the same thing for Oscar nominations I can say cast filmed Oscar nominations as float and then close the parentheses a success rate I can then click OK and having done that I can run the report and see hopefully at the end there we go we've got the correct values now being calculated another thing that was easy to do with our calculated fields in our dataset was to refer to an existing calculation for example for our profit per minute expression that's not quite so easy to do in in an SQL query let's see how we can do it if I switch back to the design view and then go back to the data set properties for films 2 I now want to add in a new calculated column at the end of my select list am i zoom in again so we can see a little more clearly what I'm doing at a new column and the first thing I'd like to do here is refer to the profit dollars value I want to divide profit dollars by runtime minutes now although I've created that expression and given it a name profit dollars I can't reference this in the same select list so if I wanted to replicate or use the result of that calculation I have to write that one out again so I'm going to open some parentheses and say film dot box-office doll again of course I could just copy and paste this and then I'll say - film dot budget dollars I'll spell that correctly and then I will divide that by film dot run that time and minutes now another thing we have to consider here is the fact that box office budget and run time are all integer fields so the result of this calculation will also be an integer or a whole number to make sure that I get a decimal result I need to make sure that at least one of these values is a decimal type or a float something like that so just for convenience here I'm going to make sure that I've cast a film run time minutes as float I can then give that column an alias I can say as and then I'll call this profit per minute so I can achieve the same results it's just a lot more awkward to write when you have to know your SQL syntax quite well in order to make that work so assuming that I do know my syntax well enough I click OK and then I can add my profit per minute column to my table I'll drop that in just after profit dollars and then if I were to run the report I'll hopefully find that I get my profit per minute calculation working that properly it could do with a bit of tidying up with formatting and then I could apply this sorting and the filtering as I've done previously so that all works in the same way if I head back to the design view I can sort this new table now by its profit per minute field so I can add a sort sort by profit per minute and I'll go Z to a sort descending order again and I can also add a filter to this data set I right-click on the film's to dataset and choose dataset properties if I head to the filters page I can click add and then choose to filter this for the profit per minute or beg your pardon alaska with a success rate one of the nice things about using SQL calculations in the under lying query is the data types will be detected for you automatically so I go for greater than or equal to 0.5 I can then click OK and then run the report one more time and apart from the formatting that I could apply to the to the profit per minutes column the results here are the same now as we saw for the calculated fields that we added to our data set earlier so there we go some basic techniques for adding new calculated values to your report builder data sets just to quickly summarize you can add a calculated field to a data set using report builders expression builder and this has the advantage of allowing you to insert column names and operators etc so they're slightly easier to create you can also add a calculated field no matter how you've created your data set so whether it's been populated with a simple ad hoc query such as this one or whether it's been populated with a store procedure you can still add a calculated field to it you can also add expressions to the underlying SQL query of a data set and these are slightly more difficult to create you have to know your SQL syntax quite well but I do have the benefit of better performance one of the downside is that the you can't always add these query expressions to your data sets for example if you've chosen a stored procedure you don't get the option to add a calculation in your underlying SQL query so in your report builder career you'll probably use both techniques from time to time I guess it's a case of picking which one suits the job best hopefully this video gives you a bit of an overview of what the differences are so you can make the best choice thanks for watching see you next time