Reportapalooza is just moving along with our third challenge, Lessons From A Reporting Maestro. A few weeks ago you had the opportunity to participate in a poll listing which Basic and Advanced Crystal Reports subject you most wanted to learn more about. Then last week, my fellow Reportapalooza Reporting Experts took your top picks and created tutorial videos for you. You can check out all the videos at the Reportapalooza web site:
http://bit.ly/9PqTyW
My topics were Subreports (Basic feature) and Running Totals (Advanced Feature). Personally I think that is backwards, but hey, you guys picked the topics. I didn’t short change either topic, though, and I hope you enjoy the video tutorials. Better yet, I hope y’all learn something. I know I did. First, directing your brother and sister-in-law is hard. Second, acting is even harder. If you want to see what I look like and how wooden I can act, you definitely need to see these video tutorials.
I won’t bore you with how I created the videos, other than to say I used a MiniHDV camcorder, a chromakey green screen studio in my basement, performed a location shoot with the above mentioned very patient and supportive family members, and tied it all together using Pinnacle Studio 14 Ultimate for the editing, titles, effects, soundtrack and video rendering. Overall, I am very pleased with how the video tutorials turned out. I believe they are both educational and entertaining and hope you do too.
Unfortunately, there are some limitations which I discovered after having completed my video tutorials, Subreports and Running Totals. The tool I used to capture the screen actions did not reproduce text very well. At that point it was too late to try to correct the problem, so I decided the next best thing was to post here in my blog the step by step actions taken in the video tutorials. So here are the details of how I built the reports described in the video tutorials. Please feel free to ask me any questions you might have.
Reportapalooza Lessons From a Reporting Maestro
Basic Feature – Subreports
1. Start with blank Crystal Reports Start Page
2. Open Sales by Country and Region report
3. Select Design tab
4. Select Insert | Subreport menu option
5. Select Create New Subreport and name it Customer Count
6. Click the Report Wizard button
7. Expand the Xtreme Mountain Bikes data source
8. Expand Tables
9. Select the Customer table and move it to the Selected list
10. Click the Finish button
11. Select the Link tab
12. Select Customer.Country and move it to the Field(s) to link To list
13. Click OK
14. Place the subreport in Group Header #1
15. Use the Border tool to remove the border from the subreport and reposition the object
16. Right-click the subreport object and select Format Subreport
17. Select the Subreport tab
18. Click Suppress Blank Subreport
19. Click OK
20. Right-click the Group Header #1 section and select Section Expert
21. Unselect Suppress and select Suppress Blank Section
22. Select the Customer Count design tab.
23. Expand the Parameters item in Field Explorer
24. Select the Report | Selection Formulas | Record to view record selection formula
25. Click Save and Close
26. Right-click the Report Header B section and select Delete Section
27. Right-click the Report Footer B section and select Delete Section
28. Right-click the Detail section and select Section Expert
29. Select Suppress
30. Select Report Header and then select Suppress
31. Select Report Footer and then select Suppress
32. Click OK
33. Right-click Formula Fields in the Field Explorer and select New
34. Enter the formula name: Count Customers
35. Click OK
36. Enter the expression: shared numberVar CustomerCount := count({Customer.Customer_ID})
37. Click Save and Close
38. Place the Count Customers formula in the Report Footer section
39. Click the Design tab
40. Right-click Formula Fields in the Field Explorer and select New
41. Enter the formula name: Show Customer Count
42. Click OK
43. Enter the expression: shared numberVar CustomerCount
44. Click Save and Close
45. Create a Text Object in the Group Footer #1 section, aligned under Region: Total # Customers in Country:
46. Use the Border tool to add a top border to the text object
47. Place the Show Customer Count formula into the Group Footer #1 section next to the text object
48. Extend the length of the text object over the Show Customer Count formula object
49. Select the Show Customer Count formula object
50. Select Preview tab
51. Click the Decrease Decimals toolbar button twice to remove the decimals
52. Click the Align Left toolbar button to left-justify the object
53. Select the Design tab
54. Select Insert | Subreport menu option
55. Select Choose an existing report a browse for the Global Sales Report.rpt
56. Click Open
57. Click OK
58. Place the subreport object in the Report Header section
59. Use the Border tool to remove the border from the subreport and reposition/size the object to the width of the page
60. Right-click the Report Header section and select Section Expert
61. Unselect Suppress
62. Select the Paging tab
63. Select New Page After
64. Click OK
65. Select the Preview tab and browse the first two pages of the report
66. Select the Design tab
67. Right-click the Report Footer section and select Insert Section Below
68. Select Insert | Subreport menu option
69. Select Choose an existing report a browse for the Consolidated Income Statement.rpt
70. Click Open
71. Click OK
72. Place the subreport object in the Report Footer B section
73. Use the Border tool to remove the border from the subreport and reposition/size the object to the width of the page
74. Right-click the Report Footer B section and select Section Expert
75. Select the Paging tab
76. Select New Page Before
77. Select Landscape orientation
78. Select the Page Footer section
79. Select the Common tab
80. Click the Suppress expression formula button
81. Enter the expression: OnLastRecord
82. Click Save and Close
83. Click OK
84. Select the Consolidated Income Statement subreport and extend to edge of page
85. Select the Preview tab and browse the last two pages of the report
The quicker of you may have noticed that the reports in these video tutorials were created using the Crystal Reports sample Access database for Xtreme Mountain Bikes. That means you can view these reports and review how the steps outlined above and below work. To be fair, I did slightly modify the reports from their original design so that I could build the tutorial around them, but for the most part, you can replicate everything I did. Here are the steps from the second video tutorial.
Reportapalooza Lessons From a Reporting Maestro
Advanced Feature – Running Totals
1. Start with blank Crystal Reports Start Page
2. Open Customer Sales by Country report
3. Select Design tab
4. Select the text object to the right of the YTD 2010 Sales column header and enter: Shipped Sales RT
5. Select the text object to the right of the Shipped Sales RT column header and enter: Unhipped Sales RT
6. Select both text objects and click the Align Right toolbar button to Right-justify the objects
7. Right-click Running Total Fields in the Field Explorer and select New
8. Enter the Running Total Name: Shipped Sales
9. Select Orders.Order Amount from the Available Tables and Fields list and move it into Field to summarize
10. Show the list of available Summary Functions
11. Accept SUM as the default Type of Summary.
12. In the Evaluate section, select Use a formula
13. Click the evaluation expression formula button
14. Enter the formula expression: Orders.Shipped = TRUE
15. Click Save and Close
16. In the Reset section, select on change of group
17. Select the default Group #1: Customer.Country
18. Click OK
19. Place the Shipped Sales running total object into the Detail section under the Shipped Sales RT column and size
20. Place the Shipped Sales running total object into the Group Footer #1 section under the Shipped Sales RT column and size
21. Use the Border toolbar tool to add a top border to the running total object in the Group Footer #1 section
22. Right-click Running Total Fields in the Field Explorer and select New
23. Enter the Running Total Name: Unshipped Sales
24. Select Orders.Order Amount from the Available Tables and Fields list and move it into Field to summarize
25. Accept SUM as the default Type of Summary.
26. In the Evaluate section, select Use a formula
27. Click the evaluation expression formula button
28. Enter the formula expression: Orders.Shipped = FALSE
29. Click Save and Close
30. In the Reset section, select on change of group
31. Select the default Group #1: Customer.Country
32. Click OK
33. Place the Unshipped Sales running total object into the Detail section under the Unshipped Sales RT column and size
34. Place the Unshipped Sales running total object into the Group Footer #1 section under the Unshipped Sales RT column and size
35. Use the Border toolbar tool to add a top border to the running total object in the Group Footer #1 section
36. Click the Preview tab and look at the report.
37. Click the Design tab
38. Right-click Running Total Fields in the Field Explorer and select New
39. Enter the Running Total Name: Total Shipped Sales
40. Select Orders.Order Amount from the Available Tables and Fields list and move it into Field to summarize
41. Accept SUM as the default Type of Summary.
42. In the Evaluate section, select Use a formula
43. Click the evaluation expression formula button
44. Enter the formula expression: Orders.Shipped = TRUE
45. Click Save and Close
46. In the Reset section, select the default Never
47. Click OK
48. Place the Total Shipped Sales running total object into the Report Footer section under the Shipped Sales RT column and size
49. Right-click Running Total Fields in the Field Explorer and select New
50. Enter the Running Total Name: Total Unshipped Sales
51. Select Orders.Order Amount from the Available Tables and Fields list and move it into Field to summarize
52. Accept SUM as the default Type of Summary.
53. In the Evaluate section, select Use a formula
54. Click the evaluation expression formula button
55. Enter the formula expression: Orders.Shipped = FALSE
56. Click Save and Close
57. In the Reset section, select the default Never
58. Click OK
59. Place the Total Unshipped Sales running total object into the Report Footer section under the Unshipped Sales RT column and size
60. Select both the Total Shipped Sales and Total Unshipped Sales running total report objects
61. Right-click the report objects and select Format Field
62. Select the Border tab
63. Under Line Style, set the Top border to Double
64. Select the Font tab
65. Change the Style to Bold
66. Select the Preview tab and go to the end of the report
So there you have it. How I saved Andy’s job and made Mrs. Wantitnow look good in just minutes. I hope you liked the video tutorials and found them educational. But you too can be a reporting hero, and in fact may already be one. Our second Reportapalooza challenge has been underway since the start, and it is all about you. Tell us a story about a time when you saved the day with Crystal Reports or Crystal Dashboards. You could win a music poster made from your submission! To enter, just go here: http://bit.ly/cAPKvH
Click the Tweet link to tweet about your participation in Reportapalooza and you could win $200 in concert tickets! My next task will be to create a new Crystal Dashboard for a very special charity. Check back here for updates on my progress!
I hope you enjoyed this blog post and have been following Reportapalooza. If so, I would love to hear from you. If you want to find out more about me, Reportapalooza or Crystal Reports, you can visit follow me on Twitter @David_Deitch, become my professional friend on Facebook (http://bit.ly/9HaNKj) or connect with me on LinkedIn (http://linkd.in/cBQzEv).
You can also visit Crystal Connections Atlanta on Facebook (http://bit.ly/ciXH0T) and coming soon, my new web site, http://crystalconnectionsatl.com. And of course, you are invited to join over 2600 Crystal Reports professionals on my Crystal Reports group on LinkedIn (http://linkd.in/9stmUW) and join the over 2100 members of the Crystal Reports Users Group on Yahoo Groups (http://bit.ly/bW2YuN).
Finally, if you tweet about this blog (and please do), be sure to include the #Reportapalooza tag in your tweet and help us get the word out. This has been a long post, and if you have read this far (please let me know if you have), thank you. If you have visited the Reportapalooza web site, thank you. And if you voted for my Lessons From A Reporting Maestro tutorial videos, THANK YOU.
David Deitch
Senior Crystal Reports Developer
SAP Reportapalooza Maestro
