CiviCRM Community Forums (archive)

Having got to the end of the post I realise that this is really a question about MySQL, but perhaps someone will be kind enough to provide an answer in spite of that. 

We currently use a non-web-based proprietary CRM, and I have full access to all the data through MSSQL Server Management Studio.  I use that access to manipulate/extract data that it would be hard to get through the CRM interface.  I am concerned that I might not be able to get the same information if we switch to CiviCRM. 

As an example, at the beginning of each month I need to produce the following (simplified) table which tells me how many active trainees or counsellors there are in each of three States:

role	State 1	State 2	State 3
trainee	23	145	72
counsellor	89	364	156

I have done this by:

• creating a view that includes the ID, role (which will be relationships I think in civiCRM) and State (all from one table) for current volunteers meeting certain criteria.  This data is further filtered to ensure only currently financial volunteers are included. The  financial status information comes from a different table
• creating a second view based on the first that provides a list of the count for each role in each state
• creating a stored procedure that creates a table from the second view using a pivot function to give the layout I want.
• scheduling the stored proc to run at 1 am on the first of each month

When I get time to compile the stats I can copy and paste that table as required without worrying about new enrolments, resignations or subs paid since the first of the month.

Am I going to be able to get that same table if we switch to CiviCRM?  We will be having the database hosted by 'a company'  and I know we will need a VPS.  Obviously I will need to learn new syntax etc.

Assuming everything is set up correctly, in  MYSQL :

• can I create a view using data from more than one table
• (if so, can I update a view that contains data from more than one table)
• is there a Pivot function or equivalent to give the required layout
• will I be able to schedule jobs to run when I want

As I said at the beginning, this post is really about MySQL not civiCRM, but if I can't get the info I need then CiviCRM will not be for us.

Thanks for your reply Xavier.  I can see I need into delve into civireport.  Am I correct in thinking that I need access to the server civicrm is installed on to do that?