Presenter: John Dempsey – Crystal Reports

• • • • • • •

Crystal overview Building a simple report Adding groups and subtotals Adding formulas Adding parameters Subtotals vs running totals Stored procedures

Crystal Reports • Button functions – – – –

Standard windows functions to open, save, copy paste, zoom, search, undo Formatting functions alignment, font, date and numeric options Crystal functions Field explorer, report explorer, repository Crystal functions database expert, group expert, group sort, search filter, section export, formula tool – Crystal functions text box, group summary, cross tab, draw line, draw box, image, chart, map

Crystal Reports • Use vertical and horizontal tabs for easier alignment – Added columns will snap to the tab – Exports will have consistent columns in excel – Add additional group sections for easier formatting or suppression

• Landscape from printer setup for more real estate

Crystal Reports • Select tables and views

Crystal Reports • Link tables by common key

Crystal Reports • Drag columns into the report design • Report design includes: – – – – – –

Report header Page header Details Report footer Page footer Potentially group headers and footers

Crystal Reports • Insert a group based on item category • Groups will determine sort order • Groups will allow subtotals

Crystal Reports • Select a column to subtotal • Click the “insert summary” button • Choose the location for the summary

Crystal Reports • Click to add a formula and provide a name • Select database columns and operators • Save formula and then drag formula into report

Crystal Reports • When using division, ensure the denominator is not 0 with an IF statement

Crystal Reports • Add a parameter to prompt the user – Parameters are dates, numbers, text, or true/false

• Parameters can filter data or control the report appearance

Crystal Reports • Right click on the Details section and select Section Expert • Check the suppress and add a formula to suppress item detail if the user does not check the “item details” box

Crystal Reports • Add the view “vi_inv_hist_qty” for sales and receiving totals • Link the view based on the item number and location • Add parameters for starting and ending date

Crystal Reports • Add formula to sum qty sold from the inventory history view • Formulas could be built for sales $, cost, receivings, adjustments, etc etc

Crystal Reports • Use the Group expert to insert two groups based on the inventory location and item number • This will allow us to summarize the qty by location and item

Crystal Reports • Move the columns from the detail row to the item footer row • Add a sum of the qty sold into the item footer and category footer

Crystal Reports • Notice from the Preview tab, the qty on hand does not add correctly • By adding the inventory history, we are adding the qty on hand for each historical transaction in the date range

Crystal Reports • Remove the Qty on hand sum from the category footer • Add a running total for the qty on hand • Running totals allow you to control when a sum is executed

Crystal Reports • Grouping by location, item and category will allow for subtotals of detail records, and running totals of group information

Crystal Reports • Use stored procedure to pre-total values instead of running totals • Use stored procedures to filter historical data • The following select statement will compile item, inventory and historical data

Crystal Reports • Build a stored procedure based on the previous sql statement • Create parameters in the stored procedure for a date range • Use the date range to filter historical data within the procedure • Allow for filter tabs with the item and inventory tables • Package the select statement in apostrophe’s to separate the select statement from code • Be sure that your select statement includes any columns required in the report

Crystal Reports

Crystal Reports • Create a new report and select the stored procedure as it’s basis

Crystal Reports • • • •

Select columns Add groups by item and category Add summaries of on hand, qty sold and sales Prebuild historical summaries are available for % of total formulas unlike running totals

Crystal Reports • Modify the start and end date parameters in the crystal report to clean up the prompting text • Add report to counterpoint menu – Menu type is custom report – Browse to the top level\company\configuration\reports folder for report

Crystal Reports • File locations – Stock reports are saved in the top level\system\reports folder – Stock point of sale forms are saved in the top level\system\psforms folder – Modified reports should be saved to the top level\company\configuration\reports folder – Modified reports should be saved to the top level\company\configuration\psforms folder

Crystal Reports • File locations – Stock reports are saved in the top level\system\reports folder – Stock point of sale forms are saved in the top level\system\psforms folder – Modified reports should be saved to the top level\company\configuration\reports folder – Modified reports should be saved to the top level\company\configuration\psforms folder

Crystal Reports • Table linking filters data – Inner joins produce output with common records in both tables – Left outer joins produce output with all records in the left table and corresponding records that exist in the right table • In this case we will see all inventory regardless of whether there is history or not

Crystal Reports • To create a parameters tab add the comment – //promptforparameters:no

• To eliminate an order by on the report filter screen that may conflict with the reports grouping add the comment – //showorderby:none