CQS Fund Reporting Toolkit 1.0

Help & Documentation

1. Overview

2. How to …. (User Help)

3. Auditing Functions

4. Specifications & Limitations

5. Technical Construct

1.Overview

The CQS reporting toolkit has been designed to assist with the creation of CQS monthly fund reports with an ability to quickly and easily make subtle changes without the need for major re-design work or skilled reporting design knowledge. The essence of the system is a simple to use DTP style front end that enables a ‘designer’ style view of the resulting report. Individual manual and proc data items can be added, deleted, moved or hidden quickly whilst the system takes care of where on the physical report page each data item fits, and dynamically changes its position according to the overall requirements and each data item’s varying size. The toolkit is written in VBA and the functionality is available entirely within Excel to any person with access to the report. Stored procedure data is (re)acquired each month by a second system requiring a simple Excel installation (please speak to CQS technical department for instructions on the necessary installation)

2.How to…

N.B. The userform mentioned in these instructions is accessed by pressing the ‘Utilities’ button on the CQS Reporting Tab which will automatically be present when any report is opened. Remember to enable macros when you open the workbook or the functions will not be available

Re-Arrange the items in my report

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Designer’ from the list of control sheets

2. Drag the object(s) to be moved to their new positions relevant to the other objects in the report. The exact position is unimportant, which will be calculated when the report is created. Move, create or delete control objects (e.g. Page Breaks) if required.

3. Press the ‘Create Report’ button from the ribbon to check the new format

Hide some items on a current report for one month

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Designer’ from the list of control sheets

2. Select a single item that you do not wish to see on the report

3. From the ‘Report’ tab on the ‘Utilities’ userform press ‘Show / Hide Active Object’.

4. The object will be shown with a bright red border to indicate that it will not be shown. Control objects cannot be hidden.

5. Repeat step 2 for any other objects to be hidden, or depress the button a second time to show the object again

6. Press the ‘Create Report’ button from the ribbon to check the new report

Remove an item from my report

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Designer’ from the list of control sheets

2. Select a single item that you wish to delete

3. From the ‘Report’ tab on the ‘Utilities’ userform press ‘Delete Active Object’. The object and all its associated data (such as user data or SProc instructions) will be deleted. This process cannot be undone.

4. Press the ‘Create Report’ button from the ribbon to check the new report

Insert a new Manual table or chart on a report

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Designer’ from the list of control sheets

2. Insert the cursor in the relative position where the new data should reside (Insert rows on the Excel sheet if required)

3. From the ‘New Data’ tab of the ‘Utilities’ userform, select ‘Data Table (Manual)’ or ‘Chart (Manual)’ from the list of option buttons on the left hand side

4. Enter a meaningful name (e.g. Historic Data) in the object name textbox just above the option buttons. The text must be simple character only (A-Z, a-z, 0-9, dash, space)

5. Set the desired relative or absolute width of the new object in the ‘Width Information’ section on the right hand side of the tab. Remember to select whether or not the item should reside in the container or be anchored to the bottom of the page.

6. Press the ‘Create New Object’ button on the bottom right of the tab

7. From the ‘Goto’ tab of the ‘Utilities’ userform, select your new ‘User’ sheet from the list of User sheets. It will be named the same as the name you gave the object with the prefix ‘User_’, e.g. User_Historic Data

8. Copy or enter the data you exactly as you wish it to be shown on the final report including all formatting. Formula may not refer to any other workbook or worksheets EXCEPT the ‘Variables’ sheet. If inserting a chart, the chart should reference data on the user sheet only.

9. When you create the report, the toolkit will take a snapshot picture of the entire used range used, or in the case of a chart sheet a snapshot of the chart itself, exactly as it is displayed

Insert a new SProc table or chart on a report

1. Up to and including step 6, follow the instructions for creating a manual table or chart above, selecting the relevant SProc option button from the user form

2. In addition a new entry will be made on the ‘Control’ sheet, that informs the system which SProc data to return. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Data Control’ from the list of control sheets.

3. Complete the 4 relevant required details: Stored Procedure, Parameters, Database & Table

4. Import the SProc data (this can only be done through the additional CQS Data tool which will need to be installed separately)

5. From the ‘Goto’ tab of the ‘Utilities’ userform, select your new ‘User’ sheet from the list of User sheets. It will be named the same as the name you gave the object with the prefix ‘User_’, e.g. User_Historic Data

6. Copy or enter the data you exactly as you wish it to be shown on the final report including all formatting. Formula may refer only to the associated SProc sheet (e.g. SProc_Historc Data) or ‘Variables’ sheet. If inserting a chart, the chart should reference data on the Sproc sheet or user sheet only.

7. When you create the report, the toolkit will take a snapshot picture of the entire used range used, or in the case of a chart sheet a snapshot of the chart itself, exactly as it is displayed

Force a page break in my report

1. If no forced page breaks are present the toolkit will automatically insert them when required according to the size of the data and margins. However to manually force a break, from the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Designer’ from the list of control sheets

2. Move the cursor to the desired position

3. From the ‘New Object’ tab of the ‘Utilities’ userform, select ‘Force Page Break’

4. Press ‘Create New Object’

Change exactly what is shown from my user sheet on the final report

1. By default the entre used range is taken as a picture snapshot by the toolkit. In order to display a cut down view, for example to hide some pre-calculations, set the print area of the sheet to that desired. (From the Page Layout / Print Area / Set Print Area)

Understand why I get ‘Integrity’ messages

To help ensure integrity and stability there are several rules around what can be reference sin formulae. These rules are NOT forcibly enforced, but are there for the protection of all users, and to help keep consistency throughout the company. Whenever a user produces a PDF of the report tests are undertaken to check the integrity and any undesirable results are displayed. The following rules are checked …

2. ‘User_’ sheets for manual data and charts may only refer to themselves or the ‘Variables’ sheet

3. ‘User_’ sheets for SProc data and charts may only refer to themselves, their associated SProc sheet or the ‘Variables’ sheet

4. All ‘User_’ sheets may refer to a SProc sheet that has no associated User sheet or a User sheet that is hidden

5. No ‘User_’ sheets may refer to external data (e.g. links)

Insert / Delete or Change the size and underlining of a title

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Title Control’ from the list of control sheets

Subtly alter the positioning of an object

Sometimes the position of an object on the final report needs to be adjusted a little, consider the following options

1. Increase/Decrease the row height of any title just before the object .. this will create additional white space

2. Nudge the picture(s) of the object to the desired position from the relevant Report Sheet in Excel using the up, down, left, right buttons. From the ‘Report’ tab of the ‘Utilities’ userform, select ‘Store Report Nudges’ and check the ‘Apply stored nudges’ checkbox. Each subsequent time the report is created the nudges will be applied.

Change the Width/Location of the Container

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Margin Control’ from the list of control sheets

2. The width and location of the container can be amended from the Margins Page using the Container Object Width and Indent settings

Add a Header/Footer

Headers are not supported in version1 of the reporting tool. Footers are applied at ‘Background’ level …that is to say footer text (such as ‘Confidential’) can be applied to every page with the same background. The footer text for each background is set on the Margin Control sheet

Add Page Numbering

1. From the ‘New Object’ tab of the ‘Utilities’ userform, select ‘Page Numbering’ from the list of options

2. Press ‘Create New Object’

3. This will toggle page numbering on or off from the desired position in the ‘Designer’ sheet. The page numbering text resides alongside any footer text , and can be toggled on or off by inserting

Change the background

1. On the designer sheet, select an area at the END of the page BEFORE the different background is required (By default Background 1 is used at the start, which can be changed by entering a different background at the top of the designer page)

2. From the ‘New Object’ tab of the ‘Utilities’ userform, select ‘Force background’ from the list of options, and choose the appropriate background

3. Press ‘Create New Object’

4. The new background control will appear on the designer sheet. The new background will start on the NEXT new report page when the report is created

Adjust the column widths in my user tables to fit the titles

The toolkit will automatically adjust chart widths and the column widths in User_ sheets before copying them as a picture to the report. To adjust column widths to fit prior to report creation (for example when testing to see if titles will fit) the toolkit can be used resize the columns automatically sp when the report created no column width adjustment is required. One or more columns can be ‘excluded’ from the resizing if required.

1. Select the ‘User_’ page containing the columns to be resized.

2. If all the columns are to be resized equally (by percent) select a cell OUTSIDE the range to be printed. If one or more columns are to be ‘Fixed’ in width, highlight those column(s)

3. From the ‘Advanced’ tab of the ‘Utilities’ userform, select the required relative or specific width

1. Press ‘Resize Column Widths’

Insert commentary that might run over 2 pages

The system will not automatically create a second page to accommodate long text. If text is likely to run into multiple pages then create two or more user text objects and manually split the text across them. If the test ‘sometimes’ goes over multiple pages use the Show/Hide feature to hide any text boxes that are not required to be shown

Format commentary

All commentary is held in text boxes which can be formatted as normal text. If copying a text box from another application remember that the margins INSIDE the textbox can alter the text position (right click and format shape / text box / margins to adjust these settings)

Add a new standard object

1. From the ‘Goto’ tab of the ‘Utilities’ userform, select ‘Standard Objects’ from the list of Advanced control sheets

2. Insert a GRAPHICAL object on to the sheet (This can be also be Excel cells, if they are copied and pasted as a picture)

3. Rename the Graphical object to an appropriate name starting with the text ‘StdObj_’, e.g. StdObj_Logo (Enter the name in the name box just below the ribbon on the left hand side and press enter)

4. Close and reopen the userform, and select the ‘New Object’ tab, from where the new object will be listed

Please note standard objects are NOT resized by the toolkit when the report is created.

Move an item in or out of the container

In version 1 of the reporting tool report objects cannot be moved in or out of the container. The object will need to be deleted and re-created inside or outside (By checking or unchecking the box in the ‘Width Information’ section of the #New Data’ tab

Change an item from user defined to SProc data

In version 1 of the reporting tool report objects cannot be changes from manual to SProc. The object will need to be deleted and re-created in the correct format

Change from Portrait to Landscape

In version 1 of the reporting tool report there is no direct way to change a report from Portrait to Landscape.

3.Auditing Functions:

The system can ascertain that a change has been made to the structure or contents of any SProc_ sheet or User_ sheet in the report since the last time it was approved. This can be useful as a check to make sure (for instance) that SProc data has been updated, that Commentary has changed, or if a new column of data has been introduced into a User_ sheet.

**All comparisons are made using the last time the report was ‘Approved’ as their basis **

At any time a check can be made to see the differences since last approval by pressing ‘Differences Since Approval’ button on the ‘Report’ tab of the userform. A list of the SProc sheets, User_sheets and Text sheets is displayed and are colour coded green if they met the following criteria, otherwise they are coloured red.

SProc Sheets: Content has changed, Size and Structure has remained the same

User_ Sheets: Content has changed, Size and Structure has remained the same

Text_ Sheets: Content of the text box has changed.

*In version 1, differences in Charts or Chart contents are not discerned by the toolkit.

It is possible to override the logic when there is a different expectation in the following ways ,,,

If the content is expected to remain the same (for example a legal notice) then change the

‘No change expected!’ column (Column F) to TRUE on the appropriate line of the Audit sheet. This setting will remain unless it is reversed.

If the structure is ALWAYS expected to change, for example a sproc that returns a new month of data each month it is run, then change the ‘Structure change (e.g. new row/col) always expected’ column (Column G) to TRUE on the appropriate line of the Audit sheet. This setting will remain unless it is reversed.

A 4-Eyes approval can be made that formally records the changes before release to 4-Eyes.

A maximum of 500 lines of Audit information is held. Data is removed from the bottom of the sheet when this limit is exceeded.

Full Audit Approval information is held permanently, subject to the 500 line limit above

4 Eyes Approval information is held until the next 4-Eyes approval is made, subject to the 500 line limit

Changes since last approval information is held until any other approval request is made.

4.Specifications & Limitations

Requirements:

Windows Excel 2007 or Excel 2010 32 Bit with SP2

(API calls will fail on 64 Bit versions, SP2 is required for conversion to PDF)

Limitations:

99 Report Objects

3 Different Background Styles

Maximum of 1 container object size/ position in any report

A4 Paper

5.Technical Construct

The toolkit has been designed to operate entirely within the confines of a standard Excel 2007 installation and requires no special programmatic references. The system is best understood as a series of XML ‘objects’ that reside on the ‘Designer’ worksheet. The XML for each object is held as a custom document property in Excel which can be viewed, extracted, manipulated or imported by external programs if required even if the workbook is closed. As the CDPs are limited to 255 characters a compact format was used, with each XML comprising a single ‘Report Object’ element containing 23 attributes, as described below.

XML Att	Excel Variable	Excel Type	Description
'N'	strName	String	Object Name as shown on the Designer page
'S'	blnShowInReport	Boolean	Whether or not the object is currently displayed in the report
'T'	strType	String	Type of object: Must be one of the following… "SProc Data Table" "Manual Data Table" "SProc Chart" "Manual Chart" "Freeform Text" "Backgound" "Page Break" "Page Nums" "Title" "Std Obj"
'CT'	blnContainer	Boolean	Whether or not the object resides in the 'container' on the report
'DT'	lngDesignerTop	String	Distance from the top of the designer page (Points)
'DH'	lngDesignerHeight	Long	Height of the object on the designer page (Points)
'DL'	lngDesignerLeft	Long	Distance from the left of the designer page (Points)
'DW'	strWidthDescription	String	The width of the object. This can either be directly specified in mm by using "User specified width: " plus the number, or one of the following width descriptions … "User specified width: " "Full column width" "One half column width" "One third column width" "Two thirds column width" "Column container width" "Full width exc. container" "Half width exc. container"
'RS'	intReportSheet	Integer	Distance from the top of the designer page (Points)
'RT'	lngReportTop	Long	Distance from the top of the designer page (Points)
'RL'	lngReportLeft	Long	Distance from the left on the final reprt page (Points)
'RW'	lngReportWidth	Long	Width in points on the final reprt page (Points)
'RH'	lngReportHeight	Long	Height in points on the final reprt page (Points)
'FIX'	blnFixedWidth	Boolean	Whether or not the width can be adjusted automatically by the toolkit to allow for changes in page formatting (e.g. margins)
'LW'	lngLastReportWidth	Long	Width in the last rport created (for future auditing purposes)
'LH'	lngLastReportHeight	Long	Height in the last rport created (for future auditing purposes)
'NL'	lngNudgedLeft	Long	How far (in points) the object has been nudged on the final report by the user
'NLF'	strNudgedLeftFromObj	String	Not currently used
'ND'	lngNudgedDown	Long	How far (in points) the object has been nudged on the final report by the user
'NDF'	strNudgedDownFromObj	String	Used to determin if the object is fixed to the bottom of the report page
'CRC'	strCRC	String	Cyclical Used for Auditing purposes
'OR'	lngOriginalRows	Long	For titles, used to designate which row on the titles sheet the text resides
'OC'	lngOriginalCols	Long	Not currently used

Test Information:

The toolkit was designed in Excel 2010 on Windows 7 where initial testing took place. The toolkit is entirely backwards compatible to Excel 2007. Final testing was on Windows XP. At CQS some Virtual Machines showed errors which were identified as a Windows error. Please see http://support.microsoft.com/default.aspx?scid=kb;en-us;840872 for more information. A VM re-boot appeared to solve the issue most of the time.