Reporting Tool

Reporting Tool

User’s Guide

Introduction

The reporting tool allows you to elaborate your reports and, using the Acrobat PDF tool, publish them on the intranet/internet. As with the other LDB tools, this tool is based on the Excel technology; the reason being that Excel is generally available in each of the statistical institution where the LDB is installed.

The tool is composed by a main program with its parameters and a template on which all the reports will be based. Results can be either dynamic allowing you to automatically update the results each time the report is executed based on modifications in the database or data warehouse or static, for example for editing purposes on the web.

This tool is coming in two versions. The first one is based on the data warehouse concept allowing you to query your data after having processed the cube(s). The second version is database based. The choice of the version is institution dependant, depending on the nature of the data and the windows regional parameterization. At this time, only the data warehouse reporting tool allows you to access multiple frequencies. We will provide the users with an equivalent database reporting tool in the near future.

The following notes will describe the generic model that you will use to build your reports (part I of this user’s guide)

Then, this guide will describe the use of the program, its options and the different parameters that you need to setup in order to allow an automatic processing of your reports (part II of this guide)

It is really important at this stage to have a deep knowledge of Excel (formulas, links between sheets, …) in order to fully benefit of this application.

PART I: Generic model – Conception of your report

The generic model is an Excel spreadsheet, called TEMPLATE, on which all your reports will be based. The spreadsheet contains, by default, the following sheets:

-QryList

-QryResult

-ControlInfo

-Report

The QryList sheet will contain your query parameters; QryResult will contain the results of the querywhose parameters have been defined in the QryList sheet; the Report sheet will contain your final report. Finally, theControlInfo sheet will contain all information related to the connection to the data warehouse or the database plus reporting information.

You can add as many sheets as you want on the template for temporary calculations but the first 4 ones need in any case to be present for the template to work.

Please note that you should never work directly with a template. Always make a copy of it to work with.

The QuryList Sheet:

In order to create your report, you need to be able to answer the following questions:

-What are the indicator codes that your report needs?

-on which time range do they need to be calculated?

-for which country are you computing these indicators?

These questions are called criteria search

The QryList sheet will contain these criteria search.

It contains 4 columns and one button, called «Run Query», as the following figure shows. The «Run query» button allows you to test your report.

Country / Group / Series / TimeRange

The general guideline to follow is described hereafter:

In the “Country” columns, place the country names, one after the others. (example: Senegal)

In the «Group» columns , place the name of a or multiple country group names for which you want to the report to produce results;

In the «Series» column, indicate the list of the indicator codes participating to the query.

Finally, in the «TimeRange» column, place the list of all time periods, ensuring that the following syntax is respected:

-Each year will be codes by using the YRyy format where yy represents the last 2 digits of the year (dozen and unit). For example, the year 2000 will be represented by YR00, 2002 by YR02, 1988 by YR88 and so on.

-Each semester will be represented by using the format SAssyy where yy is using the year convention and ss represents the semester in the year. The convention which is used by the system is to indicate 01 for the first semester and 02 for the second one. For example, the second semester of the year 1999 will be written SA0299;

-Each quarter will use the format QTqqyy where qq can take only the following values: 01, 02, 03 or 04;

-Each monthly information will use the MOmmyy convention where the month is coded on 2 digits. For example, MO0101 represents January of year 2001.

-Each week will be coded by WKwwyy where ww represents the week in the corresponding year;

-Finally, each day is coded with DYdddyy where dd dis the three digits code for the day in the corresponding year.

Time periods are separated by semi columns.

The following examples will be considered as correct by the system:

1)YR96;YR97;YR98;YR99;YR00;YR01

2)MO0100;MO0200;MO0300;MO0400;MO05;MO0600;MO0700;MO0800;MO0900;MO100;MO1100;MO1200 for the 12 months of year 2000;

The last example shows how tedious it will be to code the monthly information about a specific year. For that reason, you can use a shortcut to indicate time ranges to the system, as the following examples show:

1)YR96:YR01

2)MO0100:MO1200

You can mix different time periods together, as in the next example:

YR96:YR00;QT0196:QT0400;MO0100:MO1200;YR01;QT0100:QT0401;MO0101:MO1201.

The system will accept that syntax. Note, however, that the results will appear in the order of the indicated sequence ! (not sure, to check)

Here is a working example from one report used at the World Bank:

Country / Group / Series / TimeRange
Lithuania / PX.REC.REER / YR96:YR01
PX.REC.REER.EU / MO1297:MO1201
PX.REC.REER.CS / QT0196:QT0401
FP.CPI.TOTL
FP.PPI.TOTL

As soon as you have filled these columns out, click on the “RUN QUERY” button the results in the QryResult sheet.

The QryResult sheet

As soon as you have pushed on the «RUN QUERY» button, the results of the query will appear in this sheet, as long as there are no specific issues or errors in the list or in the connection parameters. The result will always appear in that sheet, in the form of a two dimensional array.

Regarding the last example, here is a subset of the corresponding Qryresult sheet:

1996 / 1997 / 1998 / 1999 / 2000 / 2001 / Q1-1996 / Q2-1996 / Q3-1996 / Q4-1996 / Q1-1997 / Q2-1997 / Q3-1997
Lithuania / PX.REC.REER / 106.9870602 / 118.9480963 / 159.088991 / 149.3929 / 142.6754 / 144.9134 / .. / .. / .. / .. / .. / .. / ..
Lithuania / PX.REC.REER.EU / 171.66 / 189.91 / 179.91 / 190.3 / .. / .. / .. / .. / .. / .. / .. / .. / ..
Lithuania / PX.REC.REER.CS / 34.15 / 38.14 / 72.47 / 69.48 / .. / .. / .. / .. / .. / .. / .. / .. / ..
Lithuania / FP.CPI.TOTL / 113.1 / 122.6004 / 125.5428096 / 125.92 / 127.68 / 130.3168 / .. / .. / .. / .. / .. / .. / ..
Lithuania / FP.PPI.TOTL / .. / 167 / 175 / .. / .. / .. / .. / .. / .. / .. / .. / .. / ..

…

Mar-01 / Apr-01 / Jun-01 / May-01 / Aug-01 / Jul-01 / Sep-01 / Dec-01
.. / .. / .. / .. / .. / .. / .. / ..
.. / .. / .. / .. / .. / .. / .. / ..
.. / .. / .. / .. / .. / .. / .. / ..
.. / .. / .. / .. / .. / .. / .. / ..
114.11 / 114.11 / 117.91 / 115.94 / 117.54 / 117.44 / 120.13 / 117.72

It is from these results that you will have to build your final report.

The Report Sheet

That sheet will contain your final report, as it will appear in your publication. You need to directly or indirectly reference all the result values coming from the QryResult sheet in the report sheet.

The following schema explain the flow work in order to build your report:

As soon as your report is built an dis functional, you can use the program allowing you to automatically generate the report for other countries (part II of this guide)

The parameters sheet (ControlInfo)

This sheet will contain all the parameters needed for connection with the cube (or the database). These parameters can be divided into three main parts:

-general parameters

-reporting parameters

-connection parameters

Query Sheet

/ QryList
Result Sheet / QryResult
Control Sheet Name / ControlInfo
Server Database Name / PN506692
Database Name / ECA
Write Result Sheet Start Row / 2
Query Country Column / A
Query Country Start Column / 2
Query Series Column / C
Query Series Start Column / 2
Query Year Column / D
Query Year Start Column / 2
Write Result Year Column / 3
Write Result Year Row / 1
Query Group Column / B
Query Group Start Column / 2
Data Version
Data Source
Owner
Region Name / Europe and Central Asia region

Parameters description:

1. General parameters

Query Sheet:Indicates the name of the sheet containing the query criteria;

Result Sheet:Indicates the sheet name that will contain the results of the query;

Control Sheet NameName of the Excel sheet containing the parameters.

1. Reporting parameters

Write Result Sheet Start RowFirst line where the result will appear;

Query Country Column and

Query Country Start ColumnCell position for the country name to appear;

Query Series Column and

Query Series Start ColumnCell position for the indicator codes to appear;

Query Year Column and

Query Year Start ColumnTime position in the result sheet for the time;

Write Result Start Row:Line number where the first results will appear

Query Group ColumnGroup of countries position in the Result sheet.

1. Connection parameters

Server Database NameServer Name containing the data warehouse;

Database Name database name containing the data warehouse;

Data VersionCurrent version for the query. If empty, the system will use the nominal version;

Data SourceData source for the query. If empty, the system will consider the nominal source;

OwnerData owner. Nominal value by default if empty;

Region NameRegion Name. By default the system will take the value «Sub-Saharan Africa Region»

Possible Errors

Here is a list of errors that you might encounter while using this tool, with potential solutions:

 No time period idicated with the Query.

 No indicator codes for this query.

 No country/Group of countries for this query.

 One of the connection parameters is incorrect, the system is unable to connect to the data warehouse.

 Technical issue (to be solved by IT group) : generally, this message is provoked by the inability to connect to the data warehouse. As for now, we suggest to go to the VBA environment and debug the line where the error appeared. If the faulty line is cube.populate cube, that means that there is a connection issue with the cube. Check the connection parameters. Otherwise, the most common error is that you are trying to query some indicators that do not exist in the data warehouse. You will have to review the indicator list.

PART II: REPORTS MANAGEMENT.

This second part of the manual will describe how to manage your reports in order to automatically execute them.

The purpose of the reports management is to offer the end user an automatic way to publish reports based on generic models which have been built by the user. This module will automatically execute the selected templates using the specified parameters and will allow you to visualize them before printing.

When launching the program , you will see the following window appearing on your screen:

Before explaining the different available options, we need to describe the two other sheets which exist in this application. These sheets are respectively the Info and Cntrl sheets.

The Cntrl Sheet

This sheet contains the different directory names that this application needs in order to locate the programs, templates and different tables. You need to indicate the directory information for each of the following components:

Program Directory
Template Directory
Table Directory

Program Directory represents the physical directory where the reporting tool stands;

Template Directory is the directory where all the templates reside;

Table Directory replasents the physical directory where the reports will be saved, after execution of the templates.

For example, the following parameters:

Program Directory / C:\ECAReports\programs
Template Directory / c:\ECAReports\Templates
Table Directory / c:\ECAReports\Tables

indicate the respective locations of the different part of the reporting tool. These locations can be network based.

The Info Sheet

This sheet allows the application to define the different countries or groups of countries that are needed for publishing, the name of the generic (template) spreadsheets and the extension that each report will have after publication.

The sheet format is the next one:

Country / Code / Cat / Report Titles / File Name / Report Save Extention
Lithuania / LTU / C / MacroEconomic Profile / ECAMacroEconomicProfile.xls / ECA
Latvia / LVA / C

Column A indicates the full name of the different countries for which you wish a report;

Column B indicates the repspective country codes. This code is the one used by the LDB. Ask the data administrator for the corresponding values.

Column C represents the category of the countries. This column accepts only 2 values, which are: C for country and G for group of countries.

Column D indicates the title of each of the reports, as i twill appear while executing the reporting tool.

Finally, column E will contain the Excel name of the different templates that have been created for the different reports.

Please note that only column A and B are related.

This being said, the following options are available through the main menu:

Generate Tables:Executes the reports with the latest available data;

View Tables:Visualize the contents of the updated reports;

Print Tables:Print the corresponding reports;

Exit:Quits the application.

Option «Generate Tables:

By choosing this option, you will launch the following window on the screen:

Vous remarquerez que les pays apparaissant sont ceux définis dans la feuille Info.

You NEED to select at least one country to produce a report. The “FREEZE” option allows you to discard all sheets from the final report except the ones whose name begins with Report. This option will be used by you to publish and disseminate the final versions of the report.

The «PRINT» option allows you to print the report on a printer; by clicking on the “OK” button, the following window will pop up on your screen:

ivante:

This window contains the name of all generic models that have been defined whose names have been written in the Info sheet (report Titles)

You need to select the reports you want to edit and click on the OK button, After execution, the application will automatically save the reports in the corresponding directory (see the Contrl sheet for this)

Option «View Tables»

This option allows you to visualize all the reports (tables) that have been computed in order to sdee their contents.

Option «Print Tables»

This option allows you to print the finished reports.

Option «Exit»

Quit the current application

Technical note:

The reporting application uses a dynamic link library (DLL) to get a connection to the cube containing the data for the reports. That dll is externally called TESTAFRCUBE.dll and needs imperatively to be defined in the Excel environment. Two other dlls are requiresd in order for the application to work with the data warehouse. These are:

Microsoft ActiveX Data Objects (multi-dimensional) 2.6

Microsoft ActiveX Data Objects 2.5

In order to declare these dlls under Excel proceed as indicated:

In the generic model (template), click on ALT+F11 to open the Visual Basic for Application environment;

Choose Tools, then References in the menu. The following window appears on the screen:

Select the three libraries, then click on OK.

If you cannot find the two Microsoft libraries, you will have to browse in the

c\Program files\Common Files\System\ADO\ directory. The external names for the libraries is msado25.dll and msadomd.dll

Remarks:

1) The library AFRLDBCUBE has been tested under Excel 97,Excel 2000 and Excel XP under Windows NT and Windows 2000. We know that, under Windows XP, there are some incompatibilities. Moreover, do not use other version of the Microsoft dlls indicated above as they have not been tested for this tool. If the data warehouse solution does not work, envisage theuse of the corresponding database tool;

2) The source code is currently provided to the end user because of some error handling mechanism still missing in it. Consultants need to ensure that i twill be protected by a password as soon as tested on the client’s platform.

Reporting Tool User Guide

1