Need help on how to create a Banked Services' Hours Usage Report

I need help in creating a template to print a report for a customer to show the actual dates of when they exceeded their pre-paid service hours banks. The report needs to be in a running-tally format like this:

Date Source Hours Hours Balance


11/06/2006 Service Bank 5.00 5.00
11/17/2006 WO# 8194 - 1.50 3.50
11/27/2006 WO# 8218 - 2.00 1.50
12/04/2006 WO# 8221 - 2.00 - .50
12/05/2006 Service Bank 5.00 4.50
12/11/2006 WO# 8246 - 3.50 1.00
12/15/2006 WO# 8251 - 1.25 - .25

What grid canI use to create this template?

Thanks,
Bob

Hi Bob

The Banked Service grid (available from within the Client, Head Office and Unit entry screen) is where you can presently view this information,and does not have printing capabilities. We will be providing printing of the displayed fields of the Banked Service grid as a new feature in the next maintenance update. We do not have an ETA at this time.

  • Joyce

Hi Joyce,

I don’t mean to stray off the report-topic here but the information in the Service Bank screen isn’t useful because the dates the hours-balances went negative can’t be viewed due to the running-tally being calculated on the Entered Date as opposed to Effective Date.

I was hoping to create an exportable report to Excel where I can manually change the Entered Date equivalent to the Effective Date and the come up with a running-tally that will show exactly on what dates the hours went negative.

This customer presently has 427 Banked Service entries and entering them all manually into a spreadsheet is going to be a time consuming task.

Do you know of any other methods or ways to get this information before a maintenance release?

I know I probably shouldn’t ask thisbut is there a safe way to hack :w00t: the Entered Date to be equivalent to the Effective Date? This would solve the problem.

Thanks,
Bob

EBCS (12/16/2006)Hi Joyce,

I don’t mean to stray off the report-topic here but the information in the Service Bank screen isn’t useful because the dates the hours-balances went negative can’t be viewed due to the running-tally being calculated on the Entered Date as opposed to Effective Date.

I was hoping to create an exportable report to Excel where I can manually change the Entered Date equivalent to the Effective Date and the come up with a running-tally that will show exactly on what dates the hours went negative.

This customer presently has 427 Banked Service entries and entering them all manually into a spreadsheet is going to be a time consuming task.

Do you know of any other methods or ways to get this information before a maintenance release?

I know I probably shouldn’t ask thisbut is there a safe way to hack :w00t: the Entered Date to be equivalent to the Effective Date? This would solve the problem.

Thanks,
Bob

Hi Bob, I’m not sure I fully understand what you need out of it, but it might help to understand how it works:

Effective date is purely for information and reporting purposes only neither it, nor the entered date have any affect on the balance, even if those dates were not there the balance would still be calculated at the moment that it’s entered.

This is a standard way of keeping a balance in software, what I gather you are proposing is some sort of method to enter in an update that won’t actually update that table until it’s effective date?

In other words that list you are viewing is a static list of what is in the database for that object. No calculations are performed to display that list.

When you enter in a value of +50 in the service bank, the moment you save that record the entire servicebank table is locked in such a way that no one else can affect it, the previous balance is retrieved, the value of 50 is added to that balance and inserted as part of the new row in the servicebank table and then the table is unlocked so others can affect it.

While it might be possible to devise some sort of sql query that would recalculate balances and write them out it would be disastrous to attempt it if even one other user made any entry that affected balance while you were doing it so I would not recommend it. We have a lot of very carefully written code that ensures the balance can not be wrong even with many different users all adding to or consuming from that table nearly simultaneously.

That being said, what you may be able to do is use the new reporting ability coming for that list to calculate your own balance however you wish, i.e. sort the list by effective date and calculate your own balance in the report.

Or if you are more comfortable with using Excel you can simply export that report data to excel, and calculate it any way you wish which sounds like it might be more ideal for what you require.

Hi John,

I appreciate you taking time to explain how the balance is calculated.

Up until now we have always been able to see an accurate running-tally when sorting by the Entered Date column because we had always enter the newest block within a day or so of it being purchased. Unfortunately Accounting didn’t enter the customer’s new block of hours until after it had been used up and we are unable to tell the customer on what date their hours went negative.

My wish wasonlyfor a facility whereby I could create a template that would allow meto generate such a report. :wink:

I look forward with much anticipation to the new reporting ability!

Take Care,
Bob

Hi Bob

Sample report template compatible with the latest release AyaNova v3.3.3 available at http://forum.ayanova.com/Topic2476-103-1.aspx

Refer to the Known Issues & Updates section of this forum to update to AyaNova v3.3.3.0 to use.

  • Joyce