Sage 50 Canadian
Changing Account Number Containers
Overview
The following changes were made on all Report Containers that have use the Account Number Expression. In the English version it’s called Accountno and in French it’s called N_Compte. The change was to add a “cast” to a portion of the expression that defines the account number. Every report that uses the account number needs to have this change made to its container. Listed below are the existing SQL code and the updated SQL code reflecting the code with the “CAST” added.
Current SQL
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(LEFT(taccount.lId,tcompoth.nActNumLen),CONCAT('-', "0000")) ELSE LEFT(taccount.lId,tcompoth.nActNumLen) END AS Accountno,
New SQL with CAST
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', "0000")) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)) END AS Accountno,
Changing the English Version Containers
Step 1: Changing the Expression
1. In Report Manager double click on a custom copy of the Income Statement -Current Month & YTD Report, Financial Analysis Report or Balance Sheet Current & Prior Year Report.
2. Double click on Union Sub Reports.
3. Right click on OpeningBalance Sub and then click on Go to Sub Report.
4. Double click on OpeningBalance Sub.
5. Double click on the Columns item under OpeningBalance Sub report.
6. Right Click on the AccountNo column and select the option “Go to Expression In Database Administration Tool”.
7. Click on the lookup for the AccountNo Expression as shown below.
8. Replace the SQL for this expression with the following SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', "0000")) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8))
END
9. Once done, click on Ok and Apply your changes.
Step 2: Changing the Container
1. In Report Manager double click on a custom copy of the Income Statement -Current Month & YTD Report, Financial Analysis Report or Balance Sheet Current & Prior Year Report.
2. Double click on Union Sub Reports.
3. Right click on Trial Balance Act Sub and then click on Go to Sub Report.
4. Double click on Trial Balance Act Sub.
5. Double click on Source Container.
6. Right click on a source container and select the “Go to container in Administrator tool” option.
7. Click on the lookup for the container as shown below.
8. In the edit container window use the “Find” tool (Ctrl + F) to locate all expressions called “Accountno”.
9. Replace the SQL for only Accountno expressions.
Locate this SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(LEFT(taccount.lId,tcompoth.nActNumLen),CONCAT('-', "0000")) ELSE LEFT(taccount.lId,tcompoth.nActNumLen) END AS Accountno
Then replace with:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', "0000")) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)) END AS Accountno
Locate this SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(LEFT(taccount.lId,tcompoth.nActNumLen),CONCAT('-', CASE WHEN tactdpt.sDeptCode IS NULL THEN "0000" else tactdpt.sDeptCode END)) ELSE LEFT(taccount.lId,tcompoth.nActNumLen) END AS Accountno
Then replace with:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', CASE WHEN tactdpt.sDeptCode IS NULL THEN "0000" else tactdpt.sDeptCode END)) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)) END AS Accountno
10. Once done click on Ok and Apply your changes.
11. Return to the Report Manager window and right click on Trial Balance Prior Sub and then click on Go to Sub Report.
12. Double click on Trial Balance Prior Sub.
13. Repeat steps 5 through 10 for the Trial Balance Prior Sub report.
Changing the French Version Containers
Step 1: Changing the Expression
1. In Report Manager double click on a custom copy of the État des résultats Mois en cours & CAJ Report, Analyse financière Report or Bilan - Exer. courant & précédent Report.
2. Double click on Sous-rapports combinés.
3. Right click on Solde d'ouverture and then click on Go to Sub Report.
4. Double click on Solde d'ouverture.
5. Double click on the Colonnes item under the Solde d’ouverture sub report.
6. Right Click on the N__compte column and select the option “Go to Expression In Outil d’administration de base de donneés” option.
7. Click on the lookup for the N__compte expression as shown below.
8. Replace the SQL for this expression with the following SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', "0000")) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8))
END
9. Once done, click on OK and Apply your changes.
Step 2: Changing the Container
1. In Report Manager double click on a custom copy of the État des résultats Mois en cours & CAJ Report, Analyse financière Report or Bilan - Exer. courant & précédent Report.
2. Double click on Sous-rapports combinés.
3. Right click on Balance de vérification - Réel and then click on Go to Sub Report
4. Double click on Balance de vérification - Réel .
5. Double click on Conteneur source.
6. Right click on a source container and select the “Aller á conteneur dans l’ Outil d’ administration de base de données” option.
7. Click on the lookup for the container as shown below.
8. In the edit container window use the “Find” tool (Ctrl + F) to locate all expressions called “N__compte”.
9. Replace the SQL for only Accountno expressions.
Locate this SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(LEFT(taccount.lId,tcompoth.nActNumLen),CONCAT('-', "0000")) ELSE LEFT(taccount.lId,tcompoth.nActNumLen) END AS Accountno
Then replace with:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', "0000")) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)) END AS Accountno
Locate this SQL:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(LEFT(taccount.lId,tcompoth.nActNumLen),CONCAT('-', CASE WHEN tactdpt.sDeptCode IS NULL THEN "0000" else tactdpt.sDeptCode END)) ELSE LEFT(taccount.lId,tcompoth.nActNumLen) END AS Accountno
Then replace with:
CASE WHEN tcompoth.bUseDept = 1 THEN CONCAT(CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)),CONCAT('-', CASE WHEN tactdpt.sDeptCode IS NULL THEN "0000" else tactdpt.sDeptCode END)) ELSE CAST(LEFT(taccount.lId,tcompoth.nActNumLen) as char(8)) END AS Accountno
10. Once done click on Ok and Apply your changes.
11. Return to the Report Manager window and right click on Balance de vérification préc. Sub then click on Go to Sub Report.
12. Double click on Balance de vérification préc. Sub.
13. Repeat steps 5 through 10 for the Balance de vérification préc. Sub report.