1 / 51

Calculated Columns

Calculated Columns. Enhance your reports with template and script columns. PRESENTER: Andrea Dawkins | R&D Project Manager, Entrinsik | 3-4-2012. Calculated Columns. Template vs. Script What’s the difference? 4 Template Examples How to use them, HTML tricks

penney
Download Presentation

Calculated Columns

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Calculated Columns Enhance your reports with template and script columns PRESENTER: Andrea Dawkins | R&D Project Manager, Entrinsik | 3-4-2012

  2. Calculated Columns Template vs. ScriptWhat’s the difference? 4 Template Examples How to use them, HTML tricks 5 Script Examples How to use them, JavaScript tricks FunctionsEstablish global Scripts for your users

  3. What is the Difference?

  4. Template Columns Written with plain text and HTML Other columns can be used as placeholders for the real value (like a WordMergeletter)

  5. Template Examples ConcatenationJoining columns together (like first and last name) Website LinksMaking the perfect hyperlink for Informer In-line picturesShow an image right in the report row Embed a MapCopy/paste an HTML block, like a Google Map

  6. Script Columns Written with JavaScript More advanced -- can do anything a Template column can do, plus calculations and conditional output Generally, use a script column when you cannot solve your problem with a template

  7. Script Examples Color CodingDefine thresholds and highlight low/high numbers with color coding Projected SaleCalculate the projected dollar amount of a sale based on probability Balance DueShow balance due on an order Counting DaysCalculate the number of days since an event Total Multi-valuesAdd up a list of multi-values in a column

  8. Template Examples

  9. Template #1: Concatenation One column for first and last name

  10. Template #1: Concatenation How? Drag & drop column headers into the Expression box:${first_name} ${last_name} Produces: Doug Leupen

  11. Template #1: Concatenation Style with HTML Last name bold:${first_name} <B>${last_name}</B> Produces: Doug Leupen

  12. Remember… Do not remove the original columns. Hide them instead.

  13. Template #2: Website Link The perfect hyperlink for Informer

  14. Template #2: Website Link Step 1 – Easy, a basic link A hyperlink in HTML:<a href=“http://www.linkedin.com”>LinkedIn</a> Produces: LinkedIn

  15. Template #2: Website Link Step 2– Drag in your columns LinkedIn ID and the person’s name<a href = “http://www.linkedin.com/${linkedin_id}”> ${first_name} ${last_name}’s Profile</a> Produces: Sharon Shelton's Profile

  16. Template #2: Website Link Step 3– Choose your sorting value Sort by the person<a name=“${first_name} ${last_name}” href= “http://www.linkedin.com/${linkedin_id}”> ${first_name} ${last_name}’s Profile</a> Otherwise, Informer will just sort by the HTML alphabetically

  17. Template #2: Website Link Step 4– Stay on your report Use target=_blank to open in a new browser window<a name=“${first_name} ${last_name}” href= “http://www.linkedin.com/${linkedin_id}” target=“_blank”> ${first_name} ${last_name}’s Profile</a> You may also use target=“AnyWindowNameHere” to open all links with that target in the same new window.

  18. Template #2: Website Link Done! The perfect Informer link Uses columns from the report Sorts by what you see Opens in a different window

  19. Template #3: In-line Picture Show an image right in the report row

  20. Template #3: In-line Picture Step 1 – Easy, a basic image An image in HTML:<imgsrc= “http://delivery.entrinsik.com/icon/profiles/logo.png” /> Produces:

  21. Template #3: In-line Picture Step 2 – Drag in your columns An image in HTML:<imgsrc= “http://delivery.entrinsik.com/icon/profiles/${first_name}.png” /> Produces:

  22. Template #3: In-line Picture Step 3 – Control the size Specify the height or width<imgsrc= “http://delivery.entrinsik.com/icon/profiles/${first_name}.png” height=100/> But do not change both unless you want the image distorted!

  23. Template #4: Embed a Map Place a Google Map in the row body of a report

  24. Template #4: Embed a Map Step 1 – Make an address template column Concatenate all the pieces of an address into one column:${street}, ${city}, ${state}, ${zip} Produces: 7501 Creedmoor Rd, Raleigh, NC, 27613

  25. Template #4: Embed a Map Step 2 – Copy paste the link from Google Maps Put your address column in<iframe width="800" height="500" frameborder="0" scrolling="no" marginheight="0" marginwidth="0" src="http://maps.google.com/maps?hl=en&output=embed&t=m&z=14&iwloc=A&q=${address}"> </iframe>

  26. Template #4: Embed a Map Step 3 – Hide/Show in Row Body

  27. Script Examples

  28. Script Examples Color CodingDefine thresholds and highlight low/high numbers with color coding Projected SaleCalculate the projected dollar amount of a sale based on probability Balance DueShow balance due on an order Counting DaysCalculate the number of days since an event Total Multi-valuesAdd up a list of multi-values in a column

  29. Script Reference JavaScript Operators

  30. Script #1: Color Coding AKA, how to use if/else in JavaScript to color code a value

  31. Script #1: Color Coding If/Else Statements Assign colors based on some condition.if(condition1) { do this; }else if(condition2) { do this; }else { do this; }

  32. Script #1: Color Coding If/Else Statements Assign colors based on some condition.if( probability <= 25 ){ "<div style='color:red'>“ +probability+ "%</div>";} else if( probability >= 75 ){ "<div style='color:green'>“ +probability+ "%</div>";} else { "<div>“ +probability+ "%</div>";}

  33. Script #1: Color Coding Remember the sorting trick Add name to the div so the column sorts on the value"<div name='" +probability+ "'>" +probability+ "%</div>";

  34. Script #2: Projected Sale Calculate the projected dollar amount based on probability

  35. Script #2: Projected Sale • Multiply by probability percentage divided by 100amount * ( probability / 100 ); • Set the column as a number and apply currency formatting

  36. Script #3: Balance Due Calculate the balance of tuition paid

  37. Script #3: Balance Due • Subtract the amount paid from the totaltotal – amount_paid

  38. Script #4: Counting Days Calculate the number of days since a date

  39. Script #4: Counting Days Use Java Calendar Calculate up the relevant dates in milliseconds:var calendar = java.util.Calendar.getInstance();varrightNow = calendar.getTimeInMillis();calendar.setTime(INVOICE_DATE);varstartTime = calendar.getTimeInMillis();varoneDayInMillis = 24 * 60 * 60 * 1000;(rightNow- startTime) / oneDayInMillis;

  40. Script #4: Counting Days Highlight past-due invoices Use both your calculated columns:if(balance > 0 && daysSinceInvoice > 90) { "<div style='background-color:red;color:white'> Past Due </div>";}else { "";}

  41. Script #5: Total Multi-values Total up all the values in a multi-value column

  42. Script #5: Total Multi-values Arrays An array is a list of values inside a single variable They come from Multi-value fields, Multi-key joins, Remote joins If you ever see this in your script column, you’re dealing with an array: [Ljava.lang.Object;@1e07cdbte] Pick an item out of an array by using square brackets: myColumn[3]

  43. Script #5: Total Multi-values Array Indexes start at 0

  44. Script #5: Total Multi-values Looping To process every item in an array, you will need to use for loops for (vari=0; i< mvColumn.length; i++) { mvColumn[i]; }

  45. Script #5: Total Multi-values Calculate total Add each value up in the for loop vartotal = 0; for(vari=0; i<creditHours.length; i++) { if(creditHours[i] != null) { total += parseInt(creditHours[i]); } } total;

  46. Remember… Check for null values, or your script might break.

  47. Functions

  48. Functions Set up under Mappings tab

  49. Functions Now users can access it from any report

  50. Resources HTML http://www.w3schools.com/html/html_examples.asp JavaScript http://www.w3schools.com/js/js_examples.asp CSS (styles) http://www.w3schools.com/css/css_examples.asp Forums: http://www.entrinsik.com/forums/Sub-forum for Calculated Columns has more examples

More Related