| Author |
Message |
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
  
Topic: Crosstab Date Column
Posted: 28 Sep 2016 at 5:37am |
|
I am trying to setup a crosstab report that shows a value by the date of the week.
I want to set the days of the week so that the columns are all the same number for each group.
What formula do I need to use to set the daily columns from 1st of the month to the last day of the month?
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 03 Oct 2016 at 6:01am |
|
First off, you must have all of the dates you want in your report available in the database. If they're not in the database, there's no way to to show them all on the report.
I usually do this with a "Calendar" table, that has all of the dates, an indicator on whether the date is a weekend, and a few other pieces of information. I then make the calendar table the "master" table for the report and left join from it to a date field in the data (or to a SQL expression that will truncate the time off of the date field).
You then use the date field from the calendar table when you want to show the date - because of the left join, it will show all of the dates in the selected time period.
In the Cross-Tab you use the date field from the calendar table as the columns. I think that initially this will show one date for every week. Once the cross-tab is set up, right-click on the date field in it and select "Column Options" and then "Group Options". Set "This column will be printed" to "for each day" to get all of the days.
-Dell
|
|
|
| IP Logged |
|
chris.wolf
Newbie
Joined: 01 Apr 2011
Online Status: Offline
Posts: 9
|
Posted: 03 Oct 2016 at 10:05pm |
|
Hi Thanks for the advise. I have managed to do the week name using this formula in the group options -
'Week ' & Totext(Datepart("ww",{SOPOrderReturn.DocumentDate})+1 - Datepart("ww",{SOPOrderReturn.DocumentDate} - Day({SOPOrderReturn.DocumentDate})+1), 0)
|
| IP Logged |
|
|
|
