Crosstabs in the detail section

[ABerkowicz]

I am having a problem with making my crosstab aligned and visually appealing on my report when I move it from the report footer to the detail section. Can someone offer some formulas or advice as to how to get the results mentioned below?<br><br>I have a parameter asking what month peple want to look at and based on that, the timesheets for each week in that month will appear.<br><br>In the footer, it looks like this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date1&nbsp;&nbsp;&nbsp;&nbsp;Date2&nbsp;&nbsp;&nbsp;Date3&nbsp;&nbsp;&nbsp;Date4<br>Name1&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<br>Name2&nbsp;&nbsp;&nbsp;40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17<br><br>When I bring this crosstab up to the detail section, the dates with 0 don't appear. (People might not have worked that week and therefore don't have a timesheet entered and there is nothing in the table for that week.) Only the dates that have numbers appear. <br><br>Therefore, in the detail section, it looks like this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date1&nbsp;&nbsp;&nbsp;&nbsp;Date2&nbsp;&nbsp;&nbsp;&nbsp;Date3&nbsp;&nbsp;&nbsp;Date4<br>Name1&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date1&nbsp;&nbsp;&nbsp;&nbsp;Date4<br>Name2&nbsp;&nbsp;&nbsp;40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17<br><br>I would like the dates to appear regardless if the person has a timesheet entered or just to have the dates align under one another. <br><br>Can this be done?

 

[LindaC]

How about making the date field a formula like:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if isnull(datefld) then 0 else datefld<br>&nbsp; <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[ABerkowicz]

Thanks but when I try creating the formula I get this error message:<br><br>A number is required here.

 

[LindaC]

I see what you mean.&nbsp;&nbsp;I went into the File¦Report Options and changed the Convert Date-Time Field To String.&nbsp;&nbsp;Then the formula:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if isnull(datefld) then '0' else datefld <br><br>I didn't get the 'A number is required here' error. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[ABerkowicz]

That does make sense but the more I look at my report, I don't know if it really would know a null value where I need it. Let me explain:<br><br>I have a parameter that prompts the user to enter the date range requested. Based on that date range, I give them whatever timesheets have been entered for an employee.<br><br>I am thinking that I may first have to look at an individual that has the max timesheets entered based on the users selection and use that as sort of a required amount for the rest of the employees. Then incorporate your formula above when someone doesn't have one for a given week.<br><br>Does this make any sense? Thanks for your help.

 

[LindaC]

If you are using two tables check out the Left Outer join.&nbsp;&nbsp;This brings in all records in the primary table with matches in the lookup table and it also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table. There is a full explanation of Join types in CR help file. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>

 

[ABerkowicz]

Thanks again for spending the time answering..I really appreciate your help - I think that by providing this example, it may help clarify things...<br><br>This is a much simpler example:<br><br>Table A (only table used lets assume) contains the following records and fields:<br><br>Name&nbsp;&nbsp;&nbsp;Timesheet date&nbsp;&nbsp;&nbsp;Hours<br><br>Andy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20 <br>Andy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/16/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/9/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/16/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;22<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/23/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;16<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/30/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18<br>Jack&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/7/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20<br><br>The user wants to see all June timesheets:<br><br>My crosstab looks like this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Timesheet Date<br>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hours<br><br><br>In the footer it looks like this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2&nbsp;&nbsp;6/9&nbsp;&nbsp;&nbsp;6/16&nbsp;&nbsp;&nbsp;6/23&nbsp;&nbsp;6/30<br>Andy&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;16&nbsp;&nbsp;&nbsp;&nbsp;18<br><br>As soon as this is moved up to the detail or group section, I get this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2&nbsp;&nbsp;&nbsp;6/16<br>Andy&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2&nbsp;&nbsp;&nbsp;6/9&nbsp;&nbsp;&nbsp;&nbsp;6/16&nbsp;&nbsp;&nbsp;6/23&nbsp;&nbsp;&nbsp;6/30<br>Bill&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;22&nbsp;&nbsp;&nbsp;&nbsp;16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18<br><br><br>I either want it to align up or put 0 in for Andy when he has no timesheets for a given week so that it looks like this:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2&nbsp;&nbsp;&nbsp;6/9&nbsp;&nbsp;&nbsp;&nbsp;6/16&nbsp;&nbsp;&nbsp;&nbsp;6/23&nbsp;&nbsp;&nbsp;6/30<br>Andy&nbsp;&nbsp;&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6/2&nbsp;&nbsp;&nbsp;6/9&nbsp;&nbsp;&nbsp;&nbsp;6/16&nbsp;&nbsp;&nbsp;&nbsp;6/23&nbsp;&nbsp;&nbsp;6/30<br>Bill&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18<br><br>I hope this helps clarify things. Thanks once again.<br>

 

[LindaC]

Maybe there is a way to calculate each payday from the first date in your range and not go beyond the end date entered.&nbsp;&nbsp;I'm not sure if this will work and don't have the time to research it but if you could add 7 days to your start date you would get the next payday and assign it to a variable.&nbsp;&nbsp;repeat until you reach the end date.&nbsp;&nbsp;Set up as many variables as you need then check each employee to see if they have a record on the table.&nbsp;&nbsp;if not move 0 to the field.&nbsp;&nbsp;Not sure if this can be done??&nbsp;&nbsp;Let me know how you make out. <p>LindaC<br><a href=mailto:lcastner@co.orange.ny.us>lcastner@co.orange.ny.us</a><br><a href= > </a><br>