I am using iMIS 15.1.1.3632 on SQL Server 2005. For our Finance Department, I have been asked to create a report listing all transactions relating to a specified product code, or to a specified user. Unfortunately, we cannot use one of the built-in Crystal reports as none of them allow you to see all transactions for a given user. I have tried to write a 2-part SQL script to get the information but I am not clear how the tables link together. For the first part, which is all transactions for one-off orders, I am trying to link the three tables dbo.Name, dbo.Invoice, dbo.Trans and dbo.Orders. For the second part, which is transactions from repeating subscriptions, I am trying to link the three tables dbo.Name, dbo.Subscriptions and dbo.Trans. Neither seem to give anything like the same results as the iMIS front end, so I guess I haven't linked the tables right. I also tried to find out how the built-in Crystal reports link the tables, but I couldn't work it out. Does anyone know how these tables link together? Thanks for any help you can give.
Subscriptions Info
Hi one thing to note - the subscriptions (usually) get overwritten at least every year - so using Subs table for report will only get "current" data.
If your dues are accrual dues then Invoice lines are created and the invoice lines hold the individual products billed in the subs year.
If your dues are raised on a cash basis - no invoices are created - so to retrieve actual spend use the Activity record.
Note that when DUES ( subscriptions) , Orders etc are paid an Activity is created - so linking via the activity table might be a better solution.
Activity Type DUES and SALES are the the ones to link to and the subs item or Order product are stored in the product_code column for that activity.
For Events/ Meeting spend , dependency is on whether the meeting is closed or not.
Open meetings stored the spend by product_code in order lines table, total spend for the meeting for an id is stored in the orders table ( of course not by product code). For closed meetings the total spend for the meeting is stored in an activity type = 'MEETING'. So you will have to create a query that "unions" data from closed and open meetings for a particular ID.
Note I rarely, if ever, use the transaction table to try to report on financial data as this data is the raw data used to construct the summary data in Invocies, Activity etc.
I have created total spend reports based on activity and adding in code for Open meetings based on this.
HTH
Dave Johnson
SQL Server and iMIS Consultant
Melbourne Australia