wieb
Posts: 15
|
| Posted: 05/12/2010, 9:24 PM |
|
Sorry for this dumb question, I spend hours to figure out but still can't resolve
I have monthly_target_label which should has values from database table.
I add this custom code in Before Show Event:
global $monthly_target;
$db = new clssales_mainDataSource($this);
$SQL = "SELECT (yearly_target/12) as monthly_target FROM sales_main";
$db->query($SQL);
$Result = $db->next_record();
if ($Result) {
$monthly_target = $db->f("monthly_target");
$sales_main->monthly_target_label->SetValue($monthly_target) ;
}
$db->close();
The result is all 12 months labels has FIRST RECORD/12 value
Try change the code like bellow:
global $monthly_target;
$db = new clssales_mainDataSource($this);
$SQL = "SELECT (yearly_target/12) as monthly_target FROM sales_main";
$db->query($SQL);
while($Result = $db->next_record()) {
$monthly_target = $db->f("monthly_target");
$sales_main->monthly_target_label->SetValue($monthly_target) ;
}
$db->close();
The result is all 12 months labels has LAST RECORD/12 value
Please help, what should I do to make each month label to has the correct value from recordset?
|
 |
 |
Gena
Posts: 591
|
| Posted: 05/12/2010, 11:19 PM |
|
add WHERE into your SELECT statement...
_________________
Gena |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 12:21 AM |
|
Quote Gena:
add WHERE into your SELECT statement...
Change the code and add WHERE like bellow.
$SQL = "SELECT (yearly_target/12) as monthly_target FROM sales_main WHERE id >= 1";
But the result still the same, got FIRST RECORD when without WHILE or got LAST RECORD if with WHILE
Please enlight me, how to resolve this.
|
|
|
|
Gena
Posts: 591
|
| Posted: 05/13/2010, 12:40 AM |
|
ok
but what is your DB table structure and what you are going to have in your monthly_target_label ? could you please post here some example of output?
_________________
Gena |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 12:52 AM |
|
Quote Gena:
ok
but what is your DB table structure and what you are going to have in your monthly_target_label ? could you please post here some example of output?
Thank you for the response.
It is just simple mySQL database with structure like this:
- id
- description
- yearly_target
I want to make a simple report which contains these columns:
- description
- yearly target
- monthly target
The Monthly Target values calculated from 1/12 Yearly_Target
|
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 12:58 AM |
|
It should be easy because mySQL calculated "(yearly_target/12) as monthly_target" automatically.
My problem, it could not iterate through recordset.
Because of this trouble I always have FIRST RECORD/12 values or LAST RECORD/12 values.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 12:59 AM |
|
Hi
Not exactly sure what your form looks like.
First I think you need to have 12 different form labels to put the data in.
So create your 12 monthly labels on your form like
monthly_target_label_1, monthly_target_label_2,monthly_target_label_3..... so on...
Now try your DB code like so.
$db = new clssales_mainDataSource();
$c=0;
$SQL = "SELECT (yearly_target) FROM sales_main";
$db->query($SQL);
while($Result = $db->next_record()) {
$c++;
$monthly_target = $db->f("yearly_target")/12;
$label=' sales_main->monthly_target_label_'.$c.'->SetValue(" '.$monthly_target.' ")';
eval ($label) ;
}
$db->close();
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 1:04 AM |
|
I would be curious if that helps...
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 1:12 AM |
|
Quote jjrjr1:
I would be curious if that helps...
Thank you for the help jjrjr1
But I still don't understand why I could not only make 1 label and fill it with the query result , it more simple solution instead have to make 12 different labels.
in pure PHP we could do this by:
while($row_monthly_ target = mysql_fetch_array($result_monthly_ target, MYSQL_ASSOC)) {
Can I do like this in CCS?
BTW, this is not a forms but a simple report.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 1:20 AM |
|
Hi
I made a few changes to the code above for you to try.
Yes you could do that in CCS if you we displaying it in a grid. Doing that you would not need this iteration code.
CCS uses HTML Templates for it's page output. And since each control is a separate object, you have to create the target label objects where you will be placing data.
Does that make sense.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 1:22 AM |
|
Just saw your comment that this is a report.
I would not think in a CCS report you need this iteration code.
Hard to say without seeing the report you created.
Now I am confused... 
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 1:46 AM |
|
Quote jjrjr1:
Now I am confused...
I can't upload an image here, but here is the detail of my report:
The database structure is like this:
- id
- description
- yearly_target
The report contains these columns:
- description
- yearly target
- monthly target
The database contain values like this:
- 1, Product A, 12000
- 2, Product B, 24000
- 3,Product C,36000
The report will be like this:
Product A 12000 1000
Product B 24000 2000
Product C 36000 3000
1000, 2000, and 3000 is from 12000/12, 24000/12, 36000/12
To build that report, I use CCS Report Builder but to make Monthly Target column (which the value is not stored in database but calculated separately) I have to make custom code on Before Show Even.
But my problem with that custom code, I only have YEARLY TARGET FIRST RECORD or YEARLY TARGET LAST RECORD.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 1:51 AM |
|
Did you try in the before show row event for the report
$Container->monthly_target->SetValue($Container->yeary_target->GetValue()/12);
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 2:00 AM |
|
Thank you so much jjrjr1....
It work perfectly now
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 2:08 AM |
|
Great!!! Glad to help
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 8:00 AM |
|
Hello jjrjr1,
Sorry to bother you again but I am stuck and don't know how to add a link to label.
Here is the details:
The database structure is like this:
- id
- description
- yearly_target
The report contains these columns:
- description
- yearly target
- monthly target
The database contain values like this:
- 1, Product A, 12000
- 2, Product B, 24000
- 3,Product C,36000
The report will be like this:
Product A 12000 1000
Product B 24000 2000
Product C 36000 3000
My question is how to add URL link on 1000,2000, 3000 text ?
So the users could click on 1000, 2000, 3000 text and then go to monthly sales detail page.
How to insert <a href='product_a_monthly_sales_detail'></a> on:
$Container->monthly_target->SetValue($Container->yeary_target->GetValue()/12);
Sorry if this is a dumb question, I am new with CCS and still learn on how to use it.
However now I know that using CCS saving a lot of time than writing whole code from scratch which I usually did.
But still I need sometime to getting use to it.
|
|
|
|
andy
Posts: 183
|
| Posted: 05/13/2010, 8:29 AM |
|
It's pretty straightforward (you'll be pleased to know).
Here's how:
• Click on the label that you want to turn into a hyperlink
• Right-click and from the pop up menu, select the "Change to" submenu, and from this select "Link"
• Your label is now a link but you need to tell it where to link to and add any parameters to pass to the next page.
• With your newly converted link selected, from the Data tab in Properties set:
Href Type: Page
Href Source: myhyperlinkpage.ccp
• If you want to add parameters (e.g. your landing page is a Record and you want to bring up the particular record of the item you just clicked on), then click on the three dots next to your defined page in Href Source
• Click on the Parameters tab in the Href Source dialog box.
• Click on the + button to add a parameter
• Set your parameter. For example:
Source Type: DataSource Column
Parameter Source: mykeyfieldid
Parameter name: mykeyfieldid
What happens is that when you click on the link it goes to the defined page and adds on to the end of the url:
myhyperlinkpage.php?mykeyfieldid=xx (where xx is the value of that record's key field)
In your Record form on your landing page you then click on the Record's datasource and add a WHERE condition
Where mykeyfieldid (control) = mykeyfieldid (url)
Hope that helps.
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/13/2010, 8:43 AM |
|
Thank you for your help, Andy
It is so simple, now it works.
|
|
|
|
andy
Posts: 183
|
| Posted: 05/13/2010, 9:34 AM |
|
Glad I could help!
Happy coding 
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 05/13/2010, 5:46 PM |
|
wieb,
If your question/issue has been resolved could you please add [RESOLVED] or [SOLVED] to the thread title. Thanks.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/13/2010, 6:13 PM |
|
wieb
Yes.... CCS has a high leaning curve but it's worth the effort
Glad we all could help...
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/19/2010, 8:55 AM |
|
Here is the details:
The database structure is like this:
- id
- month
- last_year_sales
- current_year_sales
The database contain values like this:
1, Jan, 1000, 3000
2, Feb, 2000, 1000
3, Mar, 3000, 5000
4, Apr, 3000, 4000
5, May, 3000,
6, Jun, 3000,
7, July, 3000,
8, Aug, 3000,
9, Sept, 3000,
10, Oct, 3000,
11, Nov, 3000,
12, Dec, 3000,
The report contains these columns:
- month
- last_year_sales
- current_year_sales
- sales_growth
The report will be like this:
1, Jan, 1000, 3000 2000
2, Feb, 2000, 1000 -1000
3, Mar, 3000, 5000 2000
4, Apr, 3000, 4000 1000
5, May, 3000,
6, Jun, 3000,
7, July, 3000,
8, Aug, 3000,
9, Sept, 3000,
10, Oct, 3000,
11, Nov, 3000,
12, Dec, 3000,
month, last_year_sales, current_year_sales column generated from database table.
sales_growth is current_year_sales - last_year_sales which get the value using custom code in Before Show Event
$salesreport->salesgrowth_label->SetValue($salesreport->current_year_sales_label->GetValue()-$salesreport->last_year_sales_label->GetValue());
My question, how to SUM sales_growth column?
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/19/2010, 9:54 AM |
|
In a CCS report you can create label and set properties to perform the sum.
Needs to be a report label. You should see those properties in the properties pane
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
wieb
Posts: 15
|
| Posted: 05/19/2010, 7:14 PM |
|
Quote jjrjr1:
In a CCS report you can create label and set properties to perform the sum.
Needs to be a report label. You should see those properties in the properties pane
Created report label with name TotalSum_SalesGrowth and set SUM function in data properties
Add custom code like bellow in Before Show Events:
$salesreport->TotalSum_SalesGrowth_Label->SetValue($salesreport->current_year_sales_label->GetValue()-$salesreport->last_year_sales_label->GetValue());
But it won't work, the result is some value which not the sum of sales growth.
Please help.
|
|
|
|
morowind
Posts: 46
|
| Posted: 06/03/2010, 4:38 AM |
|
just an ideea:
1. make a new label "test_sale" near to your sale_growth label.
2. go to "data source" report --> visual query and add new alias row :
SUM(first_sum - second_sum) AS test_sale
3.assign test_sale row to control label test_sale
see what happend. :)
same calculation (ex .for each report title/category/column) can be done in same way .
|
|
|
|
|
