karen
Posts: 99
|
| Posted: 04/28/2010, 6:59 PM |
|
Hi all, I'm just wondering if this is possible with CCS or we're better off using a reporting tool.
I would like to create a report which basically has 3 columns:
1. sums for locations for period 1 (user inputs the date selection)
2. sums for locations for period 2 (user inputs another date selection)
3. difference between column 1 and 2
As you can see, the select statement will have to select a combination of both date periods (1 & 2) but in the summing, I will need to specify the criteria.
If anyone has any tips or ideas, I would love to hear about it. Even if someone tells me it can't be done with CCS, that would be good because that means I don't have to keep trying in vain. Thanks everyone for your time!
Cheers,
Karen
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 04/28/2010, 8:46 PM |
|
karen,
By any chance would you be willing to share more information with us? Like what database and version you are using?? The tables that will be used in the report?? Depending on this information we should be able to give you a better answer.
|
|
|
|
karen
Posts: 99
|
| Posted: 04/28/2010, 9:51 PM |
|
Thanks for your quick response, mambobrown.
I'm using MySQL5 (ver 5.1.33 on my local server).
The tables involved look something like these:
Table location - fields: location_id, location_desc, location_state, etc.
Table amount - fields: amount_id, location_id, week_submitted, amount
The amounts are submitted weekly for each location. The report is kind of like a crosstab summing the amounts in two periods (for column 1 and 2 of the report) and then calculating the difference between the 2 periods.
What do you think? Does this look do-able? I do realise that CCS is not a reporting tool so perhaps I need to use some other tool. Or I've read that perhaps I should create a stored procedure within MySQL? Not sure how it all works though. Thanks for any advice!
Cheers,
Karen
|
|
|
|
andy
Posts: 183
|
| Posted: 04/29/2010, 2:03 AM |
|
Hi Karen
You should be able to do it, but I think you need to do most of the work in your SQL query statement.
I recommend experimenting with various queries using your SQL program (e.g. phpmyadmin or Navicat...) until you start to get meaningful results.
Here's a basic select query that joins your two tables with an inner join.
Not sure I fully understand your db structure or query requirements but this might get you going. Change the join type accordingly.
NB I haven't tested these queries
SELECT location.location_id,
location.location_desc,
location.location_state,
amount.amount_id,
amount.location_id,
amount.week_submitted,
amount.amount FROM location
INNER JOIN amount ON location.location_id = amount.location_id
You can also create aliases (pseudonyms) using AS and then calculate fields to create a new one such as:
SELECT location.location_id,
location.location_desc,
location.location_state,
amount.amount_id,
amount.location_id,
amount.week_submitted,
amount.amount AS amount1,
value.amount AS amount2,
amount.amount - value.amount AS amount 3
FROM location
INNER JOIN amount ON location.location_id = amount.location_id
Then you can use WHERE to add conditions
I think you should be able to do quite a lot in the CCS report but a lot of the logic needs to go into the SQL query.
Good luck
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
karen
Posts: 99
|
| Posted: 04/29/2010, 4:41 AM |
|
Thanks for your ideas, Andy. I will have to do some testing and report back...or maybe more questions later :P
|
|
|
|
Waspman
Posts: 948
|
| Posted: 04/29/2010, 12:52 PM |
|
Karen this looks like a job for the reporting tool. Bit of custom code and you'd crack it, need any help let me know...T
_________________
http://www.waspmedia.co.uk |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 04/29/2010, 3:08 PM |
|
karen,
( no problem on the quick response) You also might be able to pull this off with views. More than likely most of the work will be done by the database and then you could use CCS reporting to pull it all together.
|
|
|
|
karen
Posts: 99
|
| Posted: 04/29/2010, 9:03 PM |
|
Thanks for all the responses. I will check out all the options (it will take some time) and hopefully something will work. I've exploring JasperReports now to see how that works. Will update...
|
|
|
|
|
