I’m having an issue with my Generic Inquiry report. I need to retrieve the beginning balance from the last period activity, but that period is outside my selected period range. Because of this, the report is very slow to load. Is there any workaround for this, or is there another table I can use to get the beginning balance more efficiently?
can you check my xml file if you can do something about it?
Solved
Generic Inquiry (Begining Balance of APHistory)
Best answer by jhoncandare04
Just remove the unnecessary relations.
| Alias | DAC/Table | Purpose |
|---|---|---|
| Vendor | Vendor / VendorR | Main table so all vendors can possibly appear |
| APBegHistory | APHistory | Candidate beginning balance record before Period |
| APLaterHistory | APHistory | Used to check if there is a more recent record before the selected period |
| APRangeHistory | APHistory | Jan–Dec movement / current year activity |
1. Vendor → APBegHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| Vendor.BAccountID | Equals | APBegHistory.VendorID |
| APBegHistory.FinPeriodID | Is Less Than | @Period |
2. APBegHistory → APLaterHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| APBegHistory.VendorID | Equals | APLaterHistory.VendorID |
| APLaterHistory.FinPeriodID | Is Greater Than | APBegHistory.FinPeriodID |
| APLaterHistory.FinPeriodID | Is Less Than | @Period |
3. Vendor → APRangeHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| Vendor.BAccountID | Equals | APRangeHistory.VendorID |
| APRangeHistory.FinPeriodID | Is Greater Than or Equal To | @Period |
| APRangeHistory.FinPeriodID | Is Less Than or Equal To | @EndPeriod |
| Bracket | Data Field | Condition | Value | Operator |
|---|---|---|---|---|
APLaterHistory.FinPeriodID | Is Empty | And | ||
( | APBegHistory.FinPeriodID | Is Not Empty | And | |
APBegHistory.FinYtdBalance | Does Not Equal | 0 | Or | |
APRangeHistory.FinPtdDrAdjustments | Does Not Equal | 0 | Or | |
APRangeHistory.FinPtdCrAdjustments | Does Not Equal | 0 | Or | |
APRangeHistory.FinYtdBalance | Does Not Equal | 0 | ) |
Beginning Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))
January Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))
February Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '02', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '02', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))Just continue this to Dec
+20
there is no changes in the one that you uploaded because the query on beginning balance is still the same my problem is it is in Max so it will get the highest value not the latest financial period before the range
Just remove the unnecessary relations.
| Alias | DAC/Table | Purpose |
|---|---|---|
| Vendor | Vendor / VendorR | Main table so all vendors can possibly appear |
| APBegHistory | APHistory | Candidate beginning balance record before Period |
| APLaterHistory | APHistory | Used to check if there is a more recent record before the selected period |
| APRangeHistory | APHistory | Jan–Dec movement / current year activity |
1. Vendor → APBegHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| Vendor.BAccountID | Equals | APBegHistory.VendorID |
| APBegHistory.FinPeriodID | Is Less Than | @Period |
2. APBegHistory → APLaterHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| APBegHistory.VendorID | Equals | APLaterHistory.VendorID |
| APLaterHistory.FinPeriodID | Is Greater Than | APBegHistory.FinPeriodID |
| APLaterHistory.FinPeriodID | Is Less Than | @Period |
3. Vendor → APRangeHistory
Use Left Join.
| Parent Field | Condition | Child Field |
|---|---|---|
| Vendor.BAccountID | Equals | APRangeHistory.VendorID |
| APRangeHistory.FinPeriodID | Is Greater Than or Equal To | @Period |
| APRangeHistory.FinPeriodID | Is Less Than or Equal To | @EndPeriod |
| Bracket | Data Field | Condition | Value | Operator |
|---|---|---|---|---|
APLaterHistory.FinPeriodID | Is Empty | And | ||
( | APBegHistory.FinPeriodID | Is Not Empty | And | |
APBegHistory.FinYtdBalance | Does Not Equal | 0 | Or | |
APRangeHistory.FinPtdDrAdjustments | Does Not Equal | 0 | Or | |
APRangeHistory.FinPtdCrAdjustments | Does Not Equal | 0 | Or | |
APRangeHistory.FinYtdBalance | Does Not Equal | 0 | ) |
Beginning Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))
January Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))
February Balance
=Max(IsNull([APBegHistory.FinYtdBalance],0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))+Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '02', IsNull([APRangeHistory.FinPtdCrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '01', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))-Max(IIf([APRangeHistory.FinPeriodID] = Left([Period],4) + '02', IsNull([APRangeHistory.FinPtdDrAdjustments],0), 0))Just continue this to Dec
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.