crystal reports sharpen your superhero powers (michitsch systems bcit)

Sharpen your Superhero Powers

Cathy Michitsch, CRCP, Michitsch Systems / BCIT

Copyright © 2006 Business Objects S.A. All rights reserved.Slide 2

Breakout Information (Hidden Slide)

Sharpen your Superhero PowersNeed your Crystal reports to really stand out and leap tall buildings in a single bound? Looking for more tricks to help you battle all the evil report requests that pile up on your desk? Join us for an Advanced Crystal Reports design session to see a variety of reports built by "super" users who've pushed Crystal Reports to the limit. See the "power" of conditionally formatting a crosstab. Learn how to "speed" up processing by using arrays instead of sub-reports. Find out how to build a chart that includes "invisible" data. Learn how to "mesmerize" through exciting drill-down reports with conditional formatting techniques. Attend this session to push your report design powers to the limit.

Room 2020, Tuesday, November 7, 1:45pm -2:45pmAlso Room 2007, Wednesday, November 8, 9:45am -10:45am

B1_ID81_Q350


IntermediateCharting on “Invisible” DataDrill Down & Underlay Conditionally Formatting Crosstabs

AdvancedWrapping CrosstabsDynamic Crosstab LegendsDynamic Cascading Prompt MagicArray Replacement for SubReportsDo It Yourself Multi-pass Reporting

Q&A

Topics


Review each employee’s order totals for a yearProduce a bar chart to show the sales by employee

Issue: show each employee on the chart even if they had no sales

Intermediate: Charting on “Invisible” Data

Task:



$134,374.05$511,745.82$29,016.94Dodsworth

$137,920.32$450,705.03$71,701Davolio

000Buchanan

000Brid200520042003Employee

Employee sales per year

Brid & Buchanan are not Sales Representatives in the Xtreme Database, this is for illustration purposes only

Filter on any year and Brid & Buchanan become “Invisible”


Intermediate: Charting on “Invisible” DataChart that shows data using a Left Outer Join and filtered on the year of the orders – “invisible employees!”


The join happens before the filteringThe link is on the employee IdsNo employee Id exists in the orders table for Brid and BuchananTherefore no data to show

Need the filtering PER table to occur BEFORE the joinsAll employees from one tableAll orders summed by employee from the orders tableTHEN link the two tables

Intermediate: Charting on “Invisible” DataWhy we lose the employees that had no sales in chosen year


Main report showing the employees and a sub-report with the orders that belong to employees and show no sub-report when the employee ID doesn’t link

SQL command with two SELECT statements and a Left Outer Join (using a sub-query)

SQL command using a union statement to collect all the employees from one table and all theorders from the order table and show Null for “invisible” employees

Filtering after the join (year of order date isNull (0))


Four different possible solutions for consideration:


Intermediate: Charting on “Invisible” DataFiltering after the join (year of order date isNull (0)) solution



Chart that shows employees even if they don’t have sales!


IntermediateCharting on “Invisible” DataDrill Down & UnderlayConditionally Formatting Crosstabs


Q&A

Topics


Drill down on employee photos like a web pageTwo employee photos per row to eliminate extra ‘white space’Employee summary Information to showDrill down on employee photo to see the details

Intermediate: Drill Down and UnderlayTask


1. Put the photo (blob field) in a Group Header

2. Put the Group Header name field on top and stretched across the blob field

3. Suppress the Group Header name field

4. In preview mode double click on photo to drill down to details

How to drill down on a photo

Design

Preview

Intermediate: Drill Down and Underlay


5. Place in detail fields and summary Field6. Use Underlay for the Group Header #1b section for the

details to show beside the photo.



7. Place detail headers in Group Header8. Suppress on DrillDownGroupLevel <>1 for each Header



9. Have the Report Option of Show All Headers on Drill Down turned on



10. Format Groups with multiple column should be checkedIntermediate: Drill Down and Underlay


10. Put a Page Footer in big enough to even out columns in preview mode

11. Suppress it when the PageNumber = TotalPageCount



12. Preview report and double click on any employee’s photo to drill down to their detail information





Q&A

Topics


Various ways to highlight cellsHighlight expertConditional formatting

Intermediate: Conditionally Formatting Crosstabs

Task: highlight a Crosstab cells to draw attention to

a specific cell, row, or a column


Intermediate: Conditionally Formatting CrosstabsVarious ways to highlight cells

Use the Highlight Expert on cell


Intermediate: Conditionally Formatting CrosstabsVarious ways to highlight cells

Conditional formatting on a Crosstab cell using GridRowColumnValue function


Intermediate: Conditionally Formatting CrosstabsCrosstab formatting

Final results (see sample report)




Q&A

Topics


Advanced: Wrapping CrosstabsTask: How to make Crosstabs “wrap” to avoid whitespaces and jumping to the next page


Advanced: Wrapping Crosstabs1. SQL command must have data summarized and sorted


Advanced: Wrapping Crosstabs

2. Each region formula:

While reading records, set your formula to increment regions by 1 as a counter



3. Set the second formula using EvaluateAfter Function to group on each region and calculate out when to “wrap”(after 6 regions)


Advanced: Wrapping Crosstabs4. Create two groups onCountry and the @GroupRegions

formula and place the Crosstab in the Group Header

(Include the fields in details for testing purposes)



If you want to do this for multiple countries, you need to placeCrosstab in a sub-report and process a sub-report per country Otherwise the final result of this simple wrapping example is:




Q&A

Topics


Advanced: Dynamic Crosstab Legends

To have a crosstab legend dynamically change based on data, for example: Nancy Davolio’s column header is now red and the legend shows red:

Task



1. Use conditional formatting on the column header cell, background colour, to display colour and basically tofill the arrays:



2. Calculate how big the array will be and prepare arraysto pass to subreport



3. Create a subreport using a Top(N) command object

-- (see knowledge base # C2012542 for more details

on how to pass print time data to a subreport)

-- Tip use Top(N) command object – used to pre-filter data so we don’t have to do any out of bounds error checking



4. Link Subreport to main report



5. In subreport pivot data to show as rows of data



6. Display legend names and colour in details



7. Run the report

Colours from background RGB number from the linked array values




Q&A

Topics


Advanced: Dynamic Cascading Prompt (DCP) MagicTask: helping users know which prompt value topick if confusing levels of cascading prompts exist.

Using Dynamic Cascading prompts is very powerful in Crystal Reports XI allowing report users to see only the values linked to each parameter level.

This example shows parameters on the country, region and city. Users are allowed to pick <All> as a value for any of these parameters or a specific country, region, or city.

Challenge for the user, what if they do not know the country or region that the city is in? Do they guess? Or run the full report (which could take hours) just to find the country, region, and city (cities) they want to report on?


Advanced: Dynamic Cascading Prompt (DCP) Magic

Technical challenge

How to show the user,DYNAMICALLY, which city belongs to which region and country in a parameter drop down list, dependent on values in the data source.


Advanced: Dynamic Cascading Prompt (DCP) MagicTheory: Use a second Crystal Report to create command object

Use Crystal Reports to build a separate dynamic command object that can be used with ALL the combinations of the values from the data source

Cut and paste this commandinto your live report

This sample report is included online and is called the DCP_Flexible-All-Command-Maker.rpt.It is described in your handout.


Advanced: Dynamic Cascading Prompt (DCP) MagicSteps

1. Reset the SQL statement formula before you begin



2. Create your Parameter fields to get the field names, field types, and table name to be used in the command object

Field names and field types prompts allow multiple values, table name is discreet with no multiple values



Example when setting up for country, region and city combinations



3. Create placeholder formulas joining the parameter values together for the field names and field types



4. Put these fields in the Report Header



5. Calculate out the Binary Combination of Field Names based on the number of records using a Custom Function of Decimal to Binary function (review in sample report)

000001010, etc



6. Build the layers of the union statements based on the Binary Combinations



7. Add in the carriage returns and line feeds



8. Show the SQL command


Advanced: Dynamic Cascading Prompt (DCP) MagicSteps (#1 - #8 in handout)

9. Run the report to generate the SQL commandCut the SQL statement generated and paste it into a Command object of a new Crystal Report and dress it up with concatenated fields!



10. In the new report, add in your other tables or command objects but do NOT link them although the following messages will appear:



11. In new report, create formulas for each of the parameter levels (dependent on how you want to show the concatenation of the parameter values)



12. Use formulas and parameters in your selection formula



13. Run your report and check out the dynamic cascading prompts!


IntermediateCharting on “invisible” dataDrill Down & UnderlayConditionally Formatting Crosstabs

AdvancedWrapping CrosstabsDynamic Crosstab LegendDynamic Cascading Prompt MagicArrays Replacement for SubreportsDo it yourself Multi-pass Reporting

Q&A

Topics


Advanced: Arrays Replacement for Subreports

Task: To calculate the sum of sales > $5,000 for each

Employee and chart on it without using a Subreport

By-passing the DBA concept:

Pre-process and pre-calculate data, before the Crystal data engine “receives it” without using Stored Procedures / Views, etc, to prepare data that would not normally be available at read time.



Reading a single dataset twice as shown by the ReadTime Record Count compared to the records filtered by record selection.



The While Reading Records (WRR) formula has the same end results the as the running total, but is finalized pre-printtime, and because of the >$5000 condition is it different from a standardSUM.



1. Create a command that gets two distinct records ('a‘ vs 'b')

Steps



2. Link it to the primary table as a Inner != Enforced Join(this is what actually causes the replication to occur)



3. Separate the record selection into “category b” and areference-able record selection formula.

Note: the “@Record Selection” formula will most likely still goserver-side as a WHERE clause in newer versions of Crystal Reports.

* Category B is the second pass of data that we actually want in the report.



4. Create a ReadTime “conditional manual running total”



5. In pass “b” simply output the corresponding results of pass “a”



6. Insert a standard chart being sure to select “Don't summarize”as the final value(s) are already summarized.

Results! :


IntermediateCharting on “invisible” dataDrill Down & UnderlayConditionally Formatting Crosstabs


Q&A

Topics


Advanced: Do It Yourself Multi-pass Reporting

To see the status of an order on a various datesThe order is usually only read once and therefore would show up on the report only once, but we want to show the order multiple times like below:

Task

Order ID Order Date Date “Walker” Shipped Date Status


Advanced: Do It Yourself Multi-pass ReportingOr .. group on the date and have the records read more than once!



1. Add a new Datasource from the Xtreme Database (orders table for this example)



2. Add a new datasource (command object) to your report for 31 replications of the data (aka 1 month):



3. Link the IntLink field with the original datasource– Make sure the new command is on the RIGHT when linking for

this example (makes a difference)– Set the link type to “NOT EQUAL” and click OK (ignore the

datasource warning)



4. Create a “Date Walker” formula to add 1 to the order date to increment by each day

Note: This formula is 100% dependent on the order of the tables (collating)


Advanced: Do It Yourself Multi-pass Reporting5. Create a “status” formula calculate the status based on

the date of the “Date Walker” against the Shipped Date



6. Run the report (this version is showing sorted by order Id)



7. Or run the report grouped and sorted by date!


QuestionsCathy Michitsch, CRCP• President, Michitsch Systems Inc.• Instructor, British Columbia Institute of Technology

Contact information (optional)Email: [email protected]: 604.876.9204 x201

Q&A

crystal reports sharpen your superhero powers (michitsch systems bcit)

Documents