Enhance Reports with Calculated Columns

1. Calculated Columns 2010 Copyright Entrinsik, Inc. Enhance your reports with template and script columns PRESENTER: Andrea Dawkins | R&D Project Manager, Entrinsik | 10-11-2010

2. 2010 Copyright Entrinsik, Inc.

3. Two Kinds of Calculated Columns Template Script 2010 Entrinsik, Inc.

4. Template Columns Other columns can be used as placeholders for the real value, like a WordMerge letter Written with plain text and HTML Examples: Joining columns together (like first and last name) Making a link to another website In-lining pictures 2010 Entrinsik, Inc.

5. Script Columns More advanced -- can do anything a Template column can do, plus calculations and conditional output. Written with JavaScript Examples: Color-coding values based on a threshold Calculating taxes and remaining balance on an order Counting the number of days since a certain date 2010 Entrinsik, Inc.

6. Template Example # 1: Joining Columns 2010 Entrinsik, Inc.

7. Joining Columns: Drag & Drop Drag & drop the column headers into the Expression box to get this: ${firstName} ${lastName} Produces: Doug Leupen 2010 Entrinsik, Inc.

8. Joining Columns: Mix in HTML Insert HTML into your column to change the style: ${firstName} ${lastName } Produces: Doug Leupen 2010 Entrinsik, Inc.

9. Remember Dont remove the original columns. Hide them instead. 2010 Entrinsik, Inc.

10. Template Example # 2: Link to a Website 2010 Entrinsik, Inc.

11. Link to a Website: Using the a tag A hyperlink tag in HTML: Entrinsik Produces: Entrinsik 2010 Entrinsik, Inc.

12. Link to a Website: Add in Columns Use columns in the URL and in the text to display: ${firstName} ${lastName}s Profile Produces: Sharon Shelton's Profile 2010 Entrinsik, Inc.

13. Link to a Website: Choose the Window Open every link in a new window using target = _blank : ${firstName} ${lastName}s Profile Use target = MyWindowName to open links in the same window. 2010 Entrinsik, Inc.

14. Sorting Trick Add this to the beginning of any calculated column to choose sorting value: 2010 Entrinsik, Inc.

15. Template Example # 3: In-line Pictures 2010 Entrinsik, Inc.

16. In-line Pictures: Use the img tag An image tag in HTML: Produces: 2010 Entrinsik, Inc.

17. In-line Pictures: Add in Columns Use columns in the image URL: Specify the height or width: 2010 Entrinsik, Inc.

18. Template Example # 4: Embed a Map 2010 Entrinsik, Inc.

19. Embed a Map: Make the Address Start by making a template column with an address separated by commas: ${my_street}, ${my_city}, ${my_state} ${my_zip} 2010 Entrinsik, Inc.

20. Embed a Map: Go to Google Maps Grab the link for Paste HTML to embed in a website Substitute the address in that text with your column variable. 2010 Entrinsik, Inc.

21. Embed a Map: Insert Address The result should look something like this: Make sure to set the column to Hidden and Show in row body. 2010 Entrinsik, Inc.

22. Embed a Map: Result 2010 Entrinsik, Inc.

23. Script Example # 1: Color Coding 2010 Entrinsik, Inc.

24. Color Coding: If Statements Use an if statement to assign colors based on some condition. if ( condition1 ) { do this } else if ( condition2 ) { do that } else { do the other thing } 2010 Entrinsik, Inc.

25. Color Coding: Setting the Threshold Actual code using a column called probability: if ( probability <= 10 ) { "

26. Color Coding: Result JavaScript from the previous slide produces: 2010 Entrinsik, Inc.

27. Avoiding null Empty values in your script cause null to appear, unless you wrap your code with this: 2010 Entrinsik, Inc. if ( probability != null ) { //code here }

28. Script Example # 2: Using Numbers 2010 Entrinsik, Inc.

29. Using Numbers: Calculate Balance Due Get the balance by subtract the amount paid from the order total (both of which are columns in a report) orderTotal amountPaid; Assign the column as Numeric and right-aligned. 2010 Entrinsik, Inc.

30. Using Numbers: Balance Due? Yes or No Does the client have a balance due? Figure it out by combining an if statement with your calculation: if ( orderTotal amountPaid > 0 ) { Yes }; else { No; } Now you can group on Yes or No 2010 Entrinsik, Inc.

31. Using Numbers: Paying Taxes Determine if they paid taxes or not by the state they live in: if ( state == NC || state == VA ) { orderTotal * 0.07; } else { 0; } 2010 Entrinsik, Inc.

32. Script Example # 3: One Multi-value 2010 Entrinsik, Inc.

33. One Multi-value: Inside an Array Multi-values appear to a Script column as an array , or a list of values inside a single variable. If you ever see this in your script column, youre dealing with an array: [Ljava.lang.Object;@1e07cdbte] Pick an item out of an array by its position in the list surrounded by square brackets: myColumn[3] 2010 Entrinsik, Inc.

34. One Multi-value: Array Indexes Start counting at 0 (the first index): 2010 Entrinsik, Inc. You write in Which g ives you orders[ 0 ] the 1 st multi-value orders[ 3 ] the 4 th multi-value orders[ orders.length-1 ] the last multi-value orders[ orders.length ] error , out of range.

35. Looping To go through every item in an array, youll need to use for loops . 2010 Entrinsik, Inc. for ( var i = 0; i < orders.length ; i ++ ) { orderTotal [ i ] = orderTotal [ i ]*1.07; }

36. Script Example # 4: Counting Days 2010 Entrinsik, Inc.

37. Counting Days: What is Today? Java has built-in date and time functions that do these calculations for us, and we can access them through the Script column Todays date in Java: var today = new Date(); Date in milliseconds (since 1970): myDate .getTime(); 2010 Entrinsik, Inc.

38. Counting Days: Subtracting Milliseconds To count the days past since a date: var today = new Date(); var diff = today .getTime() - myDate .getTime(); diff / (1000 * 60 * 60 * 24); The variable diff gives the millisecond difference. Number of days: divide by (1000 * 60 * 60 * 24 ) Number of weeks: divide by (1000 * 60 * 60 * 24 * 7 ) Number of years: divide by (1000 * 60 * 60 * 24 * 7 * 52 ) 2010 Entrinsik, Inc.

39. 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 Reports from today: http://trial.entrinsik.com:8080 YouTube examples: http://www.youtube.com/entrinsikinc More examples: http://delivery.entrinsik.com/portal/index.php?board=6.0 2010 Entrinsik, Inc.

40. Thank you! Any questions? 2010 Entrinsik, Inc.