Report Builder‎ > ‎

Creating Your First Report

This page will go from start to finish through how to create a very basic report in Report Builder. The aim here is to get a grasp of the process and a basic understanding of the following:
  1. How to open Report Builder
  2. What a Data Source and Data Set are
  3. Using the query designer to select the fields for your report
  4. Adding filters
  5. Creating the layout
  6. Running a report
  7. Creating a link to GL so when you click in the ser.ref or person name, it goes to GL service case of that person or person record

Opening Report Builder

Opening Report Builder is almost identical to the steps you went through to download it earlier on. To start this process, in Windows open Microsoft Internet Explorer and navigate to https://afsdw.afsglobal.org/reports

You will now be prompted to sign in.

To do this, sign in with:

Username: IOC code so an example would be INT or MEX

Password: Your partner ODBC password (the password you use in Crystal Reports to refresh reports)

NOTE: remember to check the 'Remember my credentials'




You will now be authenticated into the Report Builder Site (an altered version of this can be found in AFS Global under the Reports tab which will be discussed in later pages).

Once it has opened click on the 'Report Builder' option in the top menu under the Contents option





Provided you have completed the installation described in the 'Installing Report Builder' page above, the Report Builder software should open automatically.


Create a Data Source

Once the software has opened you need to create link to the database that you want to get the information from. In Report Builder this is called creating a Data Source.

To do this, click on 'New' and then 'Data Source...'



Once the Data Source Properties box has opened, at the bottom of it click 'Browse' and in the Select Data Source box which will appear open the 'Models' folder.




In this folder you should find a few file with different titles. This are  pre-made models for creating different reports and in future you can select the one which most acuratly fulfils your needs i.e. if you are creating a travel report select TRAVEL_INFO_YOURIOC

In this example we will create a placement report so select the PLACEMENT_INFO model and click 'Open'



This model will now be added as a Data Source in the Data Source Properties box and will remain here for you to use again in future.

To move forward, select the model, give it a name (something like YOURIOCconnection) and click 'OK'.



You have now created a link to the database (defined a Data Source) and the next thing you need to do create a Data Set (report) inside it

Creating a Data Set (a report)


In Report Builder there are five different types of report you can create. These are:

  • Table
  • Matrix
  • Chart
  • Gauge
  • List
For this report we are going to create a simple Table report. To do this, in the top menu click on 'Insert', then click on 'Table' and 'Table Wizard'



Now select the Data Source which you used earlier and click 'OK'.

The Query Designer will now open. This is the wizard for designing reports and you can select the information you want in your report by double clicking on the fields in the left hand menu



For this report search though the list and double click on the following fields to add them:

  1. PAX Title
  2. PAX ENGLISH FIRSTNAME
  3. PAX ENGLISH SURNAME
  4. SERVICE TYPE
  5. SERVICE STAGE
  6. SERVICE STATUS
  7. SELECTED PARTNER IOC CODE
  8. SELECTED PROGRAM CODE
Once you have selected the fields for your report you need to add some filters so that you only see the information you want to. To do this click on the 'Filter' option at the top of the box



For this report we are going to add a filter to the Program Code so that we only see records for YPscNH10.

To do this, in the list on the left double click on 'SELECTED PROGRAM CODE' and in the box that appears type 'YPscNH10' and click on 'OK' in the bottom right hand corner




You will now be taken back to the Query Designer so that you can test you Report. To do this click on the red exclamation mark next to the filter option and after a moment you should receive the information for your report. This means that is it working.




Once you have seen that your report is bringing up some information click on the 'Next' option in the bottom right hand corner of the Query Designer.




Now that we have created our basic report we need to design the layout of the information.

In the the next page which appears drag and drop all of the fields into the 'Value' box with the exception of the SELECTED PARTNER IOC CODE field which you should drag into the 'Row groups' box. This means that the information will be ordered by the country of destination.

Don't worry if this does not make sense at the moment. It will become clear when we run the report




In the next box uncheck the options highlighted. This will stop the system from adding totals to the report (total number of students in each country, total number of students in the list, etc). Then click 'Next'.




The select a theme (colour) for your report and click 'Finish'.



You should now see the following things listed in the left hand menu:
  1. The Data Source

  2. The Data Set

  3. The Data Set properties
The final thing you need to do is to run the report. To do this make sure you are in the Home tab at the top of the page and click 'Run' in the menu of options.



When the report appears you will see that the information in it is stored by the destination country which is down the left hand side.


Click on 'Design' in the top left to get back to the design view.

This covers all of the basic step to create a report and should give you a basic understanding of the core steps which you need to go through. To continue learn more about report writing click on the next tutorial which is on how to create a general placement report for hosted participants.


To create a link to GL when you click in the person name or serv. ref.

Go to >> "Text Box Properties.."  >> "Action"  >>  "Go to URL"


"Select URL:"  insert the formula.  ="https://www.afsglobal.org/AFSGlobal/GlobalLink/EveryOne/PersonDetail.aspx?pID="&(Fields!PERSON_ID.Value)







Comments