Creating a Template Report for a Bank in Belarus

1. Navigate to https://reporting-hope-dev.unitst.org/admin/ ⧉

2. Scroll down to "Queries"

3. Click on "ADD QUERY"

4. Add "Belarus" to the "Country office" field.

5. Fill in the "Name" field with "Template for a bank".

6. Paste this code in order to get the required information.

from django.db.models import ExpressionWrapper, F, DecimalField
households=list(conn.select_related(
"head_of_household",
"business_area",
"parent",
"household"
).filter(
    business_area__slug="belarus",
    parent__unicef_id="PP-0630-24-00000017"
).values(
"entitlement_quantity",
"head_of_household__given_name",
"head_of_household__given_name",
"head_of_household__family_name",
"head_of_household__middle_name",
"head_of_household__sex",
"household__address",
"head_of_household__phone_no",
"household__unicef_id"
).annotate(
    commission=ExpressionWrapper((F('entitlement_quantity')*5/100)*1.20, output_field=DecimalField(decimal_places=2) ))
)
result = []
for household in households:
    address_no_spaces = household["household__address"].replace(" ", "")
    household["locality"] = address_no_spaces[:29]
    household["street_name"] = address_no_spaces[30:59]
    result.append(household)

7. We will produce a report based on the Payments made recently in Belarus. So, we choose the Payment table as the target for our report.

8. Save and continue editing.

9. Click "Queue" button to generate the data.

10. Click "DATASETS" to preview the results and ensure things are correct

11. We now need to create a formatter as requested by the bank. This will allow us to produce a text file that resembles the one required by the bank. Navigate to Home > Power Query and click "Add" on the "Formatters" row.

12. Give the template a name "Template for a bank in Belarus"

13. Click the "Code" area.

14. In the code area, paste the following html code:

<table></table>

15. Select the "text/html" option for the file type since we want a simple html file. Save.

16. Go back to the Menu > Power Query and add a Report Configuration

17. Don't forget to choose the country office the report belongs to. Choose the report title and the appropriate query.

18. Click "Template for a bank in Belarus" as formatter and move it to the Chosen formatters.

19. Click "Choose"

20. Save.

21. And "Queue" in order to generate the requested template

22. Click "VIEW ON SITE" if you want to look at the result.

23. Click "view"

24. Click "Template Report For Bank"