Monday, November 20, 2006

AAH/TrixBox CDR Billing Report v1.0

CDR Billing/Invoicing for AAH/TrixBox...

We had a request that we'd seen bouncing around the AAH/TrixBox community for sometime, and that was a simple billing/invoice solution. Spent a few moments and put together a simple Microsoft Access query & report that generates statements by Account Code for a given Year, Month & Per Minute Rate!

Solved a friends problem & gave them a template to customize from as desired. Maybe it'll help some others out as well ... regardless, enjoy!

Add a MySql user for reporting purposes...

1) Log into the TrixBox web interface, and navigate to the phpMyAdmin screen.

2) Click "Privileges" near the bottom

3) Click "Add a new user" also near the bottom

4) For "Username" enter a name (e.g. reporting), for "Password" enter a password, and for the "Global Privileges" section at the bottom -- since I know very little about MySql security I chose "Check All" as it worked flawlessly!

5) Click "Go" in the lower right to save your new user


Configure ODBC on your Windows machine for access...

6) Download & run the MySql ODBC Driver setup (popup link: click here)

7) Once installed, click "Start -> Control Panel -> Performance & Maintenance -> Administrative Tools -> Data Sources (ODBC)"

8) You may choose whether you want a User, System or File DSN as desired, however for this example we will create a "User" DSN by clicking the "Add..." button to the right.

9) Scroll to the bottom of the list, and select "MySQL ODBC 3.51 Driver" then click "Finished"

10) When the dialog pops up asking for the database information fill in the following:
Data Source Name: TrixBox CDR
Description: Call Detail Reporting
Server: 192.168.0.10 (put your server domain or IP address here!)
User: reporting (or whatever username you created in phpMyAdmin previously)
Password: xxxxxxx (again whatever you entered before goes here)
Database: asteriskcdrdb

11) You can click "Test" if you like to make sure the connection is working

12) Click "Ok" to save and finish

13) Click "Ok" again to close the ODBC dialog as well


Let's get the report!

14) Download the Microsoft Access 2003 database ZIP file (popup link: click here)

15) Unzip the file to wherever you wish

16) Double-click to start the database (you may have to answer a couple dialogs about "unsafe expressions" and ultimately a security dialog to "open" the database -- please do so, just part of Microsoft's attempts to patch holes in their security)


Let's connect the database to your server...

17) Open the database & click "Tables" on the left of the window

18) Select the linked table called "cdr" & delete it (toolbar button above "X")

19) Right-click on the white area and from the menu select "Linked table..."

20) From the dialog click the dropdown at the bottom & scroll to the very end, select ODBC Databases(), and click OK

21) Your ODBC datasources list dialog appears, you may have to click the second tab "Machine Data Sources" where you will find the "TrixBox CDR" DSN we created earlier -- select it & hit OK

22) Another dialog appears with a list of tables from your TrixBox, it should only list "cdr" -- select it & hit OK

23) A last dialog will appear asking you to pick a unique key, click the "CallDate" and "src" fields (so they are blue/selected) & hit OK

24) Your Done! Ready to run a report! Just click "Reports" on the left and double-click "Monthly Billing" ... answer the 3 prompts & Voila!


The report uses a query (in the "Query" section) that will prompt you for a Rate, Year, and Month to generate billings for the given month by Account Code. Play around with it, and modify the report layout as you wish!

Again, not a comprehensive solution but a clean/simple fix to an easy problem. Have fun.

posted by Kennon Software at 10:32 AM | 16 comments

This is a brilliant idea... I've had it with the very complicate a2biilling and over the top VOIP billing software for Asterisk ... All I want to do is bill my existing extensions ... Trouble is its not working .. I'm getting "ODBC-call failed" when trying to run the report... The test was successful when downloading the MySql ODBC driver.

posted by  Anonymous

on 11/21/2006 3:09 PM 

Make sure you have set the priveleges correctly at your Asterisk MySql correctly. We got the same error, repeatedly (although "test" worked) until we gave "all priveleges" to the account and then it worked.

ksDevGuy

posted by  Kennon Software

on 11/21/2006 5:18 PM 

Actually, has been a while since using Access -- you need to relink the data table to the database to get things going (Access limitation with linked tables when moving from one database source to another, even if named the same).

I added steps to assist you in the How To, so you should be good to go.

Have fun all!

ksDevGuy

posted by  Kennon Software

on 11/21/2006 5:48 PM 

Me again ... (anon that is) ... It seems to work if I generated my own report so maybe there's something about the one provided that doesn't like my system- I guess I'd better learn Access - and fast!

posted by  Anonymous

on 11/22/2006 7:00 PM 

Thanks alot!I worked!

posted by  Anonymous

on 1/15/2007 3:51 AM 

Seems to crash Access everytime I try to link to the DB connection.

posted by  Joshua

on 1/29/2007 9:39 AM 

Hi Kennon,

This works great, I just tested and played around with it. Even got some sample reports. Great project!

Quick question though, how can I assign 'account code' for clients? Any thoughts any one?

Thanks in advance.

-Olga

posted by  Anonymous

on 2/09/2007 11:04 AM 

Thanks for the kind words!

You can set the account codes in FreePBX in two ways --

1) For each extension

2) Using Pin Sets and attaching those to Outbound Routes. This allows 'accountcode' to be updated based on a client (for T&M billing for example in a Law Firm).

Hope that helps.

posted by  Kennon Software

on 3/22/2007 12:18 PM 

Hey, Brilliant template but, how do I call in a rate file?

We are looking to rate card a PBX by mobile, national and international etc....

Replies welcomed.

Andy M.

posted by  Anonymous

on 5/14/2007 9:59 AM 

The solution is mostly a starting point. In the "Monthly" query in the Access database you will notice it prompts you for the per minute rate used for the rest of the reporting process. Of course you can handle this programmatically, add more prompts for interactive use, etc. as you see fit.

Hope that helps a little.

ksDevGuy

posted by  Kennon Software

on 5/14/2007 12:50 PM 

Hi,

I have a problem with this under access 2007, 2003 works fine no problems.

2007 there is a problem with the Year() Month() It just does not work when entering the date.

Does anyone know what MS changed that would affect this?

Thanks, Clint.

posted by  clint

on 5/29/2007 10:08 PM 

This post has been removed by a blog administrator.

posted by  <a href="http://m1.aol.com/MacBoyd45/index6.html">premature baby saved by viagra</a>

on 10/26/2007 11:02 AM 

THANK YOU! :) I have been struggling with this for a couple of days. Because the column accountcode wasn't showing up in the CDR report. I'm using pinsets. First I wanted to rewrite the php script. But this is a better solution.
Thanks again.

Daniel

posted by  Daniel D

on 3/31/2008 7:13 AM 

Why would "billable" column show up as 0 after I've specified a billrate? Help! Please email me adam@thinair.net.nz

posted by  Anonymous

on 4/01/2008 2:21 PM 

I play around with the report and did some things.
For example if you want result between 2 dates replace the Year([calldate]),Month fields with field calldate and the [Enter Year:], [Enter Month:] with:

Between [Enter Date from: ΧΧ-ΧΧ] And [Enter Date to: ΧΧ-ΧΧ].

Also if you want to put a value in Year you can replace the [Enter Year:] with "2008".
I want to put a value in billing rate. I don't want to ask me to give the rate. Any idea...

posted by  Anonymous

on 4/12/2008 1:28 AM 

Hi,

I am having the same result as Daniel... My billable column shows as "0". Any ideas?

Thanks...

posted by  Anonymous

on 11/10/2008 9:20 AM 

Post a Comment