Chris__T
Posts: 339
|
| Posted: 06/17/2008, 6:51 AM |
|
Anyway to manipulate the way data is viewed in a report?
I have data for employees for a span of 15 days (pay period).
Instead of it going down the page:
Joe Smith 1/1/2008 8 hrs
...................1/2/2008 8hrs
Jim White 1/1/2008 8 hrs.
etc...
I'd like it to span vertically, across the page
.........................1..............2..............3...............4
Joe Smith.....8hrs........8hrs........etc
Jim White.....8hrs....etc..
I think I've tried every way to get this to work, but can't....maybe a limitation with CCS report tool? Or the way the data is stored in the db? I'm having difficulting wrapping my brain around this one? 
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 06/17/2008, 1:45 PM |
|
Please describe table(s) layout.
Also currrent report settings like grouping and such.
'This looks possible to me, but am not sure (yet)
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/18/2008, 11:43 AM |
|
Yeah, I think it might be possible as well, but I'm worried my current DB setup won't allow for it. 
Well, the table looks like this:
DateID (primary key)
EmployeeID
EmployeeFirstName
EmployeeLastName
Date
RegHours
OTHours
and several more types of hour fields
So hours (and dates) are stored by Employee and DateID is primary key, therefore unique.
So for, say, June 1st, 2008, you have 60 records (one for each employee) that holds hourly data
ex.
DateID EmpID...FName...Lname......Date........RegHrs OThrs
1...............1.........Joe.......Smith....1/1/08.........8............0
..
60............60.......Mary......White....1/1/08.........8............2 (then for next days data...)
61.............1........Joe.......Smith....1/2/08.........8............1
..
121...........60.....Mary......White....1/2/08.........8............0
I have a feeling that the way I WANT to set this up, i'd have to have records based like this.
DATE....JSMITHREGHOURS....JSMITHOTHOURS......PETEREGHRS...PETEOTHRS...etc...MaryREGhours.....MaryOThours
So each record is a date instance, and each of these records has 2 (or more) fields for each employee. So if I have 60 employees, and you just do regular hours by itself, than the record will have at least 60 fields! And what happens when you add/delete employees? Have to go in and edit the structure of the db.
Current settings now, I was doing regular reports grouped by name, then one grouped by date, but they just list downward, not sideways.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/18/2008, 12:42 PM |
|
As to table definition:
DateID (primary key)
EmployeeID
EmployeeFirstName
EmployeeLastName
Date
RegHours
OTHours
and several more types of hour fiel
Is ok, except for the redundant storage of
EmployeeFirstName
EmployeeLastName
They should be in a separate table Employees
EmployeeID
EmployeeFirstName
EmployeeLastName
SQL should then join them in whenever needed.
But that is not your problem, I think no need for table altering.
Will have a go at it, anticipating there will be a maximum number of days (15 as you stated) displayed horizontaly.
Maybe you should accept a report format like,
Month/Year + 1 + 2 + 3 + 4 + 5........
Name1 + +
Name2 + +
Name3 +
...
Let you know if I can make anything of it.
think : altering HTML
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/18/2008, 2:28 PM |
|
Yeah, it would be 15 day intervals (16-31 for end of month period).
And I'll try my hand at HTML altering as well,
Thanks Walter
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/22/2008, 5:43 AM |
|
Based on your example table:
DateID
EmployeeID
Date
RegHours
OTHours
ReportBuilder
Step2 select all fields except DateID, Nor Order By, press Next
Step3 Groupfields, EmployeeID, Date (in that order), press Next
Step4 Layout with Groups, Group Left and above, press Next
Step5 skipped for now, press Next
Step6 accepted defaults, press Next
Step7, accepted defaults but non-breaking spaces for empty cells must be checked, press Next
Step8, skipped we might need it later to set the 15 day period, press Next
Step9, accepted suggested Style, Press Next.
I now have only RegHours and OTHours as data columns.
The HTML now looks like
<!-- BEGIN Report chris_t -->
<table cellspacing="0" cellpadding="0" border="0">
<tr>
<td valign="top">
<table class="Header" cellspacing="0" cellpadding="0" border="0">
<tr>
<td class="HeaderLeft"><img alt="" src="Styles/{CCS_Style}/Images/Spacer.gif" border="0"></td>
<td class="th"><strong>Chris T </strong></td>
<td class="HeaderRight"><img alt="" src="Styles/{CCS_Style}/Images/Spacer.gif" border="0"></td>
</tr>
</table>
<table class="Grid" cellspacing="0" cellpadding="0">
<!-- BEGIN Section Report_Header --><!-- END Section Report_Header -->
<!-- BEGIN Section Page_Header -->
<tr class="Caption">
<th scope="col">Employee ID </th>
<th scope="col">Date </th>
<th scope="col">
<!-- BEGIN Sorter Sorter_RegHours --><a href="{Sort_URL}">Reg Hours</a>
<!-- BEGIN Asc_On --><img alt="" src="Styles/{CCS_Style}/Images/Asc.gif" border="0"><!-- END Asc_On -->
<!-- BEGIN Desc_On --><img alt="" src="Styles/{CCS_Style}/Images/Desc.gif" border="0"><!-- END Desc_On --><!-- END Sorter Sorter_RegHours --> </th>
<th scope="col">
<!-- BEGIN Sorter Sorter_OThours --><a href="{Sort_URL}">OThours</a>
<!-- BEGIN Asc_On --><img alt="" src="Styles/{CCS_Style}/Images/Asc.gif" border="0"><!-- END Asc_On -->
<!-- BEGIN Desc_On --><img alt="" src="Styles/{CCS_Style}/Images/Desc.gif" border="0"><!-- END Desc_On --><!-- END Sorter Sorter_OThours --> </th>
</tr>
<!-- END Section Page_Header -->
<!-- BEGIN Section Page_Footer -->
<tr class="Footer">
<td>{Report_CurrentDate} </td>
<td style="TEXT-ALIGN: right" colspan="3">Page {Report_CurrentPage} of {Report_TotalPages}
<!-- BEGIN Navigator Navigator -->
<!-- BEGIN First_On --><a href="{First_URL}">First</a> <!-- END First_On -->
<!-- BEGIN First_Off -->First <!-- END First_Off -->
<!-- BEGIN Prev_On --><a href="{Prev_URL}">Prev</a> <!-- END Prev_On -->
<!-- BEGIN Prev_Off -->Prev <!-- END Prev_Off -->
<!-- BEGIN Pages -->
<!-- BEGIN Page_On --><a href="{Page_URL}">{Page_Number}</a> <!-- END Page_On -->
<!-- BEGIN Page_Off -->{Page_Number} <!-- END Page_Off --><!-- END Pages -->of {Total_Pages}
<!-- BEGIN Next_On --><a href="{Next_URL}">Next</a> <!-- END Next_On -->
<!-- BEGIN Next_Off -->Next <!-- END Next_Off -->
<!-- BEGIN Last_On --><a href="{Last_URL}">Last</a> <!-- END Last_On -->
<!-- BEGIN Last_Off -->Last <!-- END Last_Off --><!-- END Navigator Navigator --> </td>
</tr>
<!-- BEGIN Panel PageBreak -->
</table>
</td>
</tr>
</table>
</p>
<p style="PAGE-BREAK-AFTER: always"> </p>
<p>
<table cellspacing="0" cellpadding="0" border="0">
<tr>
<td valign="top">
<table class="Grid" cellspacing="0" cellpadding="0">
<!-- END Panel PageBreak --><!-- END Section Page_Footer -->
<!-- BEGIN Section EmployeeID_Header -->
<tr class="GroupCaption">
<th style="TEXT-ALIGN: right" scope="col">{EmployeeID} </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
</tr>
<!-- END Section EmployeeID_Header -->
<!-- BEGIN Section Date_Header -->
<tr class="GroupCaption">
<th scope="col"> </th>
<th scope="col">{Date} </th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
<!-- END Section Date_Header -->
<!-- BEGIN Section Detail -->
<tr class="Row">
<td style="TEXT-ALIGN: right"> </td>
<td> </td>
<td style="TEXT-ALIGN: right">{RegHours} </td>
<td style="TEXT-ALIGN: right">{OThours} </td>
</tr>
<!-- END Section Detail -->
<!-- BEGIN Section Date_Footer --><!-- END Section Date_Footer -->
<!-- BEGIN Section EmployeeID_Footer --><!-- END Section EmployeeID_Footer -->
<!-- BEGIN Section Report_Footer -->
<!-- BEGIN Panel NoRecords -->
<tr class="NoRecords">
<td colspan="4">No records</td>
</tr>
<!-- END Panel NoRecords --><!-- END Section Report_Footer -->
</table>
</td>
</tr>
</table>
<!-- END Report chris_t --><br>
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/22/2008, 5:45 AM |
|
This is the altered version:
<!-- BEGIN Report chris_t -->
<table cellspacing="0" cellpadding="0" border="0">
<tr>
<td valign="top">
<table class="Header" cellspacing="0" cellpadding="0" border="0">
<tr>
<td class="HeaderLeft"><img alt="" src="Styles/{CCS_Style}/Images/Spacer.gif" border="0"></td>
<td class="th"><strong>Chris T </strong></td>
<td class="HeaderRight"><img alt="" src="Styles/{CCS_Style}/Images/Spacer.gif" border="0"></td>
</tr>
</table>
<table class="Grid" cellspacing="0" cellpadding="0">
<!-- BEGIN Section Report_Header --><!-- END Section Report_Header -->
<!-- BEGIN Section Page_Header -->
<tr class="Caption">
<th scope="col">Employee ID </th>
<th scope="col" colspan="99">Date </th>
</tr>
<!-- END Section Page_Header -->
<!-- BEGIN Section Page_Footer -->
<tr class="Footer">
<td>{Report_CurrentDate} </td>
<td style="TEXT-ALIGN: right" colspan="99">Page {Report_CurrentPage} of {Report_TotalPages}
<!-- BEGIN Navigator Navigator -->
<!-- BEGIN First_On --><a href="{First_URL}">First</a> <!-- END First_On -->
<!-- BEGIN First_Off -->First <!-- END First_Off -->
<!-- BEGIN Prev_On --><a href="{Prev_URL}">Prev</a> <!-- END Prev_On -->
<!-- BEGIN Prev_Off -->Prev <!-- END Prev_Off -->
<!-- BEGIN Pages -->
<!-- BEGIN Page_On --><a href="{Page_URL}">{Page_Number}</a> <!-- END Page_On -->
<!-- BEGIN Page_Off -->{Page_Number} <!-- END Page_Off --><!-- END Pages -->of {Total_Pages}
<!-- BEGIN Next_On --><a href="{Next_URL}">Next</a> <!-- END Next_On -->
<!-- BEGIN Next_Off -->Next <!-- END Next_Off -->
<!-- BEGIN Last_On --><a href="{Last_URL}">Last</a> <!-- END Last_On -->
<!-- BEGIN Last_Off -->Last <!-- END Last_Off --><!-- END Navigator Navigator --> </td>
</tr>
<!-- BEGIN Panel PageBreak -->
</table>
</td>
</tr>
</table>
</p>
<p style="PAGE-BREAK-AFTER: always"> </p>
<p>
<table cellspacing="0" cellpadding="0" border="0">
<tr>
<td valign="top">
<table class="Grid" cellspacing="0" cellpadding="0">
<!-- END Panel PageBreak --><!-- END Section Page_Footer -->
<!-- BEGIN Section EmployeeID_Header -->
<tr class="Row">
<td style="TEXT-ALIGN: right" scope="col">{EmployeeID} </td>
<!-- END Section EmployeeID_Header -->
<!-- BEGIN Section Date_Header -->
<td scope="col">{Date}
<!-- END Section Date_Header -->
<!-- BEGIN Section Detail -->
<table class="Grid" cellspacing="0" cellpadding="0">
<tr class="Row">
<td style="TEXT-ALIGN: right">{RegHours} </td>
<td style="TEXT-ALIGN: right">{OThours} </td>
</tr>
</table>
<!-- END Section Detail -->
<!-- BEGIN Section Date_Footer --></td><!-- END Section Date_Footer -->
<!-- BEGIN Section EmployeeID_Footer -->
</tr>
<!-- END Section EmployeeID_Footer -->
<!-- BEGIN Section Report_Footer -->
<!-- BEGIN Panel NoRecords -->
<tr class="NoRecords">
<td colspan="99">No records</td>
</tr>
<!-- END Panel NoRecords --><!-- END Section Report_Footer -->
</table>
</td>
</tr>
</table>
<!-- END Report chris_t --><br>
Done like:
Starting with the generated layout:
Page Header:
we dont want the sorter columns, just employeeID (you might want first/Lastname) and Date.
So remove the sorter blocks. now our table formatting will be off, so we give date a colspan of 99. 32 would be good for 16 dates * 2 columns but 99 will do the trick nicely.
Page Footer:
we only need to adjust the colspan of the pagenumbering here, colspan="99".
Now for the nice part, we are to combine the groupings into the details and give the detail an extra row.
This will all be done without any programming, just HTML.
Study the altered HTML, if not clear report back.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/22/2008, 6:23 AM |
|
edit HTML:
Find <!-- BEGIN Section Page Header
Change: <th scope="col">Date </th>
to: <th scope="col" colspan="99">Date </th>
we need the Date header to span the whole report width.
Find: <-- BEGIN Section Page Footer
Change : <td style="TEXT-ALIGN: right">Page {Report_CurrentPage} of {Report_TotalPages}
To : <td style="TEXT-ALIGN: right" colspan="99">Page {Report_CurrentPage} of {Report_TotalPages}
same reason colspan="99"
Find:<!-- BEGIN Section EmployeeID_Header -->
Change:
<tr class="GroupCaption">
<th style="TEXT-ALIGN: right" scope="col">{EmployeeID} </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
<th style="TEXT-ALIGN: right" scope="col"> </th>
</tr>
To:
<!-- BEGIN Section EmployeeID_Header -->
<tr class="Row">
<td style="TEXT-ALIGN: right" scope="col">{EmployeeID} </td>
We change the class to "Row" for layout purposes
We change the <th> to <td> and </th> to </td> we do not want headers we want columns.
the other 3 headers we remove as well as the close row </tr>
We want the row to continue, will be the date header as well as the detailsrows.
We will close the row </tr> in the EmployeeID Group Footer
Find:<!-- BEGIN Section Date_Header -->
Change:
<tr class="GroupCaption">
<th scope="col"> </th>
<th scope="col">{Date} </th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
To:
<!-- BEGIN Section Date_Header -->
<td scope="col">{Date}
<table class="Grid" cellspacing="0" cellpadding="0">
We do not want the <tr> not the empty rows, we just want the Date in a column <td>
and add a <table.....> statement for the Datarows.
We will close the table and the column </table></td> in the Date footer later
Find: <!-- BEGIN Section Detail -->
Change:<tr class="Row">
<td style="TEXT-ALIGN: right"> </td>
<td> </td>
<td style="TEXT-ALIGN: right">{RegHours} </td>
<td style="TEXT-ALIGN: right">{OThours} </td>
</tr>
To:
<tr class="Row">
<td style="TEXT-ALIGN: right">{RegHours} </td>
<td style="TEXT-ALIGN: right">{OThours} </td>
</tr>
We do not need the empty colums, just the data, here you can add extra columns if you need to.
extra <td>...</td>'s within the <tr></tr> bit will add columns id you need extra rows you can add them here too.
Now all we need to do is close the extra table and rows like this, all added :
<!-- BEGIN Section Date_Footer -->
</table>
</td>
<!-- END Section Date_Footer -->
<!-- BEGIN Section EmployeeID_Footer -->
</tr>
<!-- END Section EmployeeID_Footer -->
That's it, beware at several places there are & n b s p ; in the code, these will not print in this forum.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/23/2008, 6:45 AM |
|
Too much information to see on a Monday morning! 
Thanks Walter! Let me work through your helpful instructions to figure out what you are doing. I'll report back.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/23/2008, 7:36 AM |
|
Wow this is awesome! I'm having a few formatting issues but this is definitely what I was looking for! Thanks! Thanks! Thanks, Walter!
The issues are that every page you scroll thought has different formatting, though this must be some kind of html error I have done. Some of the columns span big for no reason. Employee and Date header columns go goofy, as well as the "page x of 13" footer. And it only shows 3 employees per page, making the whole report span 13 pages instead of maybe 3-4. Sounds like I've bungled some html code. Let me see if I can find it.
But thanks again Walter. 
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/23/2008, 1:35 PM |
|
make sure :
all employees have all dates
all date:hours have zero when empty formatting
calculate the exact colspan if you want to
and most important have some sort of selection for the period
date_from date_to.
If still 'moving' pm me the data and your report, will have a look at it.
But the fun part was (for me) to avoid any (PhP' coding! )´cause you´re ASP.....
Happy your happy, it was fun to puzzle.
Change topic title to [SOLVED} or [HOWTO]
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/23/2008, 2:07 PM |
|
Ok, I'll go back and check all of those. I think some of the records are missing "0" if the person didn't work that day.
Haha, you'll be an ASPer before you know it 
Thanks again, and I'll report back when I've checked out what you've told me to take care of.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/23/2008, 3:28 PM |
|
Cheers.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/23/2008, 5:31 PM |
|
I have some help for you, considering the page overflows.......
Each report section has a height parameter, to be set in absolute number of lines.
You can see these as you click in each of the white area's in Design mode.
Now consider this, each section has a height of 1 (lines)
The report adds/counts these lines as each section is printed, when the total approaches your set pagelength (web/print) a page-break is forced after footer, header etc.
Origibally your report was:
Employee
---- Date
---------Hours
etc, so we have 3 lines at the minimum.
Now we are going horizontal, we only have
Employee Date Date Date Date
Hour Hour Hour
etc
So we need to alter the height of the sections
Employee height =1
Date Height = 0 as it is on the same line as Employee
RegHours OTHours, height =1
OR
Employee height =2
Date Height = 0 as it is on the same line as Employee
RegHours OTHours, height =0
Now the report is able to accurately count the number of lines printed.
If you set the lines per WebPage property to 7 for instance, mine will print 2 employee lines per page.
(2*2)+header+Caption+Footer
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/24/2008, 6:46 AM |
|
Ah, yes! That was it. I've never really understood how the "Lines Per Web Page" and have had trouble before getting reports and pages to format right. Thanks for the mini-tutorial. I just set the heights as you suggested, then increased my Lines Per Web Page really high, and it worked. Now I can fiddle with it and add a few more fields.
This has made my week!
Thanks Walter!
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/24/2008, 12:10 PM |
|
To be honest....
<supersmalltype> This actually was the first time I ever started the recordbuilder or use reports at all........
The second is here : http://forums.codecharge.com/posts.php?post_id=97798
</supersmalltype>
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 06/25/2008, 8:32 AM |
|
I'm having a hard time reading that last post without my glasses......
|
|
|
|
|
