THE CREATIVE USE OF SPACE Relying on Breaks Instead of Sections To Build WebI Reports Deborah L. Lennington
AGENDA • Format requirements for a key management report • Defining the formatting issues • Understanding breaks - in and out of the box • One-table reporting – the breakthrough solution • Conclusion • Q&A
FORMAT REQUIREMENTS FOR A KEY MANAGEMENT REPORT When the look of the report just couldn’t change - No matter what!
MOST IMMEDIATELY OBVIOUS FORMATTING REQUIREMENTS • Centered table titles • Indented section titles • Table headers to show once only on page • Project grant numbers & descriptions to show once only for break titles • All columns must print on one page • Grand totals shown with double accounting lines
DEFINING THE FORMATTING ISSUES Where I will need to trick Web Intelligence into doing what I want !
ESTABLISHING THE MAIN SECTION AND MORE Project Grant kept here to allow custom sort Concatenated Formula will allow removal of duplicate values when set as break.
A LITTLE BIT ON CONCATENATION • A Web Intelligence break is limited to one field at time • But, can concatenate variables to create that one field! • Custom sorting not possible on concatenated field • But, can custom sort on individual “original” fields! • Can “pretty-up” concatenation for use as title, or not • Can “hide” from view any unwanted fields
PICTURE THIS: CONCATENATION Note Custom Sort Concatenated Formula for Titles Fields to “hide”
HIDING “STUFF” IN PLAIN SIGHT • White format a column’s cell font • White format a column’s background color • Remove all column borders • Reduce column width to 4 pixels • PDF-saved version – no column! • Excel-saved version – column there, but keeps Web Intelligence formatting. Go ahead and delete!
BACK TO REPORTING WOES Looking (unhappily) at column widths…
BACK TO REPORTING WOES Look what happened with Save as Excel… Colored Cells automatically “merged” when saved
MY PERSONAL “ISSUES” • Can’t seem to automatically center report titles, or “step-in” break headings • Autofitted column widths are varying between sections • Grand total row positioning can’t shift when column widths do • Since break values must be included on report rows, how is a report to be printed on one page across when using multiple breaks? • Additional time requirements (whenever report runs) to reformat Excel-saved versions • …and where are those double accounting lines?
UNDERSTANDING BREAKS – IN AND OUT OF THE BOX Long story short…It’s all about space!
WHAT BREAKS USUALLY LOOK LIKE • Break values shown on the first line of its break – (assuming values not duplicated) • Break headers disabled for any break levels column headings not wanted for • Break footers enabled only when subtotals required
AN EXISTENTIALIST VIEW OF BREAKS • Most simply, breaks are a means of manipulating space and formula contexts • Break values can be made visible anywhere within an overall area defined as a header or footer • Break-based reports can look like “master/detail” ones – while providing enhanced abilities for: • Formatting • Providing Sub and Grand Totals • Saving into Excel • Breaks are very cool!
ONE-TABLE REPORTING – THE BREAKTHROUGH SOLUTION And now you too can learn the magic!
BUT FIRST – A FEW WORDS ON MERGES • You can merge left or right! • You can merge up or down! • You can stagger merges between lines and columns! • You can even set “dummy” columns or rows to extend your merging options!
MERGES IN ACTION Vertical Merge Horizontal Merge
WHAT I DID – THE DETAILS • Properties tab: • Headers Enabled • Whole table • First two of the three breaks • Footers Enabled • Whole table • All three breaks • Page Layout set to Ledger/Tabloid, Landscape • (Provides additional workable space) • (Can and will print to letter though) • Fonts throughout table sections reset between 6 through 10 pixels, and row heights reduced as well • (Which is effective size that “Print to Page” resulted in anyway)
WHAT I DID – MORE DETAILS • Inserted two lines above column title line in Table Header • “Cleared Cell Contents” for all cells in break headers • Merged: • All cells across in each of the Table Header’s two inserted lines • Centered text • First four cells of first break header • Second and third cell of second break header • Copied break variables into merged cells in appropriate break headers • Formatted cells holding first and second break variables • White font • Narrow Width
ABOUT THOSE DOUBLE LINES • Format borders with lines for the applicable cells on the totals row • Insert row – beneath the totals row (this new row will have copied the format and borders of the row it was inserted beneath) • Reduce the row height of the inserted row
Back to Sections for a Moment • Can be useful when both charts and graphs wanted together. • Can set a section without being “obvious” about it: • Establish section by dragging field over. • Delete section cell, but not the section. • Hide section header in Properties • If desired, include section value in a table/break title.
Fitting Report Width to One Page: The WebI Report Piece • Set Page Layout(use icon) to see actual page. • Set fonts in headers, footers, and body to 6 or 7 points. (If you think about it, that was the effective size anyway when Page Setupwas set to Fit to Print- 1 page widein Business Objects). • In Page Properties, set Page Sizeto Legal or Ledger ,and set Paper Orientationto Landscape.
Fitting Report Width to One Page: WEBI Printing Piece • Request printing in WebI (use icon) • On Make Printable Versionpopup - establish Page sizeas Legal or Ledger, and set OrientationtoLandscape.
Fitting Report Width to One Page: Adobe Printing Piece • Request printing in Adobe (use icon or File-Print) • On Print Popup – Page Handling, set Page Scaling to Reduce to Printer Margins. • Still on Print Popup– enable Properties
Fitting Report Width to One Page: Adobe Printing Piece • In Print Properties, on the Paper Quality Tab, Paper Options– set size as Letter. • On Finishing Tab– set Orientation to Landscape. • Go ahead and print already!