tfertil
Posts: 43
|
| Posted: 06/30/2011, 1:22 PM |
|
Hello everyone,
I know what I want, but don't know exactly how to implement.
ENVIRONMENT: I'm using CodeCharge Studio 4.3, SQL-Server 2008 R2, PHP.
Heres the problem:
I had a stored procedure that creates a result data set with two fixed columns (code and description) and then a variable number of columns with daily information. Something like that:
Code | Description | Day 1 | Day 2 | ... | Day n.
001 Item 1 ...
002 Item 2 ...
What I need is to create a report that can display the variable number of columns created by the procedure.
The number of columns AND their names are not know at design time.
After digging deep in the forums and google, I'm convinced the best way is to create a Custom Template Block inside the report or grid, and use it to create the columns and labels inside them to show the data.
But ... one thing is to know WHAT I need and other is to know HOW to do it.
Can someone give me some directions:
* I'm guessing I need to create one block for the column headers and one block inside the ROW block from CCS to create the data columns.
* Where can I do replace the variables inside my custom block? ( I mean, in the before show event of the grid? in the after initialize event of the page?)
* I read about nesting blocks: AFAIK the "nested" block are not parsed when the parent block is parsed. Is that right?
* How can I get the column names for my dataset?
Thanks: any answer you give me will be a progress...
|
tfertil
Posts: 43
|
| Posted: 07/01/2011, 3:29 PM |
|
SOLUTION:
As I'm using MS-SQL Server 2008, this solution uses specific function calls that you can change for the database of your choice.
My stored procedure returns 3 or more columns: the two first are code and description, and the third and beyond contains daily data, but you can use this to report on any procedure or table retorning a variable number of columns.
STEPS:
1. I created a report based on a table similar to the result of my stored proc, having columns code, description and a numeric field. I included row number in my reporte, so I ended with a report with 4 columns.
2. I changed the name of the item in the 4th column to "dynamic_dtl".
3. In the HTML code, I enclosed the {dinamic_dtl} item in a Custom Template Block,
From this: <td>{dynamic_dtl}</td>
To this: <td><!-- BEGIN dynamic_dtl -->{dynamic_dtl}<!-- END dynamic_dtl --></td>
4. Then in my report php code, I changed the Open() function. The original query execution line was this: $this->query($this->SQL).
I changed that line to this four lines:
$query_result = $this->query($this->SQL);
$cols = @array_keys(@mssql_fetch_assoc($query_result));
# This is very important: go back to row 0 for the report not to lose it.
mssql_data_seek($query_result,0);
$Query_ID = $this->Query_ID();
(All variables are global).
5. In the BeforeShow event for the item dynamic_dtl
$Tpl->SetBlockVar("dynamic_dtl",""); # Set template custom block
$count_col = count($cols); # Get column count
// Skip the first three columns: Row No, Code, Description
for($i = 3 ; $i < $count_col ; $i++) {
$Tpl->SetVar("dynamico_dtl",mssql_result($Query_ID,$kRecNo,$i)."</td><td>");
$Tpl->Parse("dynamico_dtl",True);
}
$Tpl->SetVar("dynamico_dtl","</td>");
$Tpl->Parse("dynamico_dtl",True);
$kRecNo += 1; # Global row number, set to ZERO in Common.php.
6. Ready! When the report is executed, the original column for dynamic_dtl is "splitted" into as many columns as the dataset requires.
The same principle can be applied to split the headers, just change the SetVar value to $cols[$i] and you get the column name in the header.
7. Additional work:
I'm adding a last empty column with no content just because I need to close the last <td> with a </td>. You can modify the loop to NOT add the <td> in the last column.
Also I'm working to extend the technique so I can use sum's and coun'ts on the created columns.
Hope somebody found this useful.
Regards,
|
