Home Forums Discussions Feedback and Recommendations Custom Reports merging multiple tables

has anyone had any success generating custom reports merging multiple tables within the SQL database? I have used the export/search function in v5.0 but need a more expansive method to pull the custom report data. I hoped to use the SDK help file but that documentation was sparse.

I need to merge demographics, encounter billing information (EMLineitem, superbill, superbillaccount), with the CPT and ICD9 tables to generate the report to send to my billing company. An especially important field is the SavedDate field within EMLineitem as this allows me to filter just those encounters that need to be billed for a certain period of time.

Eric,
If you are wanting to generate data for billing yo should talk with my husband who has managed to make it seamless somehow. POm and ill send his number

OOOPS That's you and PM

I was able to figure this out but I don't really like the method enough to publish it as a procedure I would recommend. I had to buy a password reset program for SQL server and reset a password for a particular system ID. Then I had read access to the tables of the v5.0 database and could do the custom reports. I'd prefer using another method so I'm not dependent on that system ID existing on the SQL id/password table. If anyone else has an idea, I'd really like to hear it.

Hello Eric,
I work with a clinic in Sacramento CA and I have created their entire AC system from the ground up. I have created a custom reporting system that merges data from AC?s SQL tables and data that we need to modify for Workers Comp doctors first reports and what we call PR-2?s.

I have also created custom reports that work for our admin staff to produce reports ?custom letter with letter head kind of a mail merge? for various patients needs.

As for your needs, I have created custom data dumps that can be sent to our billing company that the import into their Centricity system by day or date ranges.

In short all of what you?re looking to achieve is doable and there is no need to crack that MS-sql tables to do it. You just need to wrap your head around what all the tables mean in AC and either import the required data to something that is a little easier like MS Access or Crystal reports.
If I can be of any help feel free to drop me a line

Aaron
asicams@gmail.com

Another approach that I have somewhat mentioned here before:

1. Install a second instance of SQL Server on your Amazing Charts Server. In this case, you could install an instance of SQL Server 2008 Standard Edition.

2. Then, you could have a SQL Server Integration Services packages that shuts down the SQL Express (Amazing Charts), mounts the AC database in your SQL Standard Instance, copies all the AC data into a database in your SQL Server Standard instance.

3. You could then use SQL Server Reporting Services to make your own custom reports.

The process of moving a copy of the AC data into another SQL Server database (steps 1 and 2) can be scheduled to occur at 5am or something.

The cost involved in my approach is the cost of a SQL Server 2008 Standard Edition license and whatever you need to get the programming done for SQL Server Integration Services (SSIS) and the reports using SQL Server Reporting Services (SSRS).

You can also use SSIS to pull data from AC into your billing software. SQL Server Integration Services is known as an ETL - Extract, Transform, Load. And that's exactly what it does.

JamesNT

@James..I checked out SQL Server Reporting Services (SSRS) but understood that the ID/password would still be required. Even copying the data to another SQL server database seemed to need a password. I may have missed something though in the SSIS documentation.

@Aaron I will email you offline to better understand your approach.

thanks for the responses. I think if this is solved the reporting was on Bert's list of improvements (number 74?).

Eric,

With my approach you bypass the password AC has on its database. Remember, you are mounting the AC database under YOUR instance of SQL Server, not theirs, then copying the data over.

Keep in mind that whether you go with my approach or Aaron's that they are both not for the feint of heart.

JamesNT