deBronkart
asked on
Crosstab show all months (CR-XI)
Hi y'all - me again, in Stunned mode -
I need to do the same thing described in this MS Access thread from Jan 2004 - show a column for every month even if no records fall into that group:
https://www.experts-exchange.com/questions/20842822/On-report-how-to-show-columns-for-12-months-when-only-have-data-for-some-months.html
This newer thread (Aug 2005) says there's no direct way to do the same thing in CR, which would astound me:
https://www.experts-exchange.com/questions/21538250/Can-CrossTab-Report-display-fields-with-no-data.html
I've found some fairly hairy approaches, like https://www.experts-exchange.com/questions/21356262/Crosstab-showing-all-dates-between-a-start-and-end-date.html ... is this really the only way to do it, or am I missing something?
Frodoman also posted this https://www.experts-exchange.com/questions/21176880/Handling-Dates.html.
It just seems amazing that something as common as "show all dates" wouldn't be a standard part of something as powerful as CR.
I need to do the same thing described in this MS Access thread from Jan 2004 - show a column for every month even if no records fall into that group:
https://www.experts-exchange.com/questions/20842822/On-report-how-to-show-columns-for-12-months-when-only-have-data-for-some-months.html
This newer thread (Aug 2005) says there's no direct way to do the same thing in CR, which would astound me:
https://www.experts-exchange.com/questions/21538250/Can-CrossTab-Report-display-fields-with-no-data.html
I've found some fairly hairy approaches, like https://www.experts-exchange.com/questions/21356262/Crosstab-showing-all-dates-between-a-start-and-end-date.html ... is this really the only way to do it, or am I missing something?
Frodoman also posted this https://www.experts-exchange.com/questions/21176880/Handling-Dates.html.
It just seems amazing that something as common as "show all dates" wouldn't be a standard part of something as powerful as CR.
ASKER
Okay, thanks Frodoman ... in that earlier thread you offered to walk the guy through it. I'm game.
After posting this question, I manually created a table called Months (columns MonthNumber, ShortName, FullName) and tried to link it to my table of orders on the DateEntered field. Of course there's nothing in the Months table that matches the type of DateEntered.
So should I instead create a table of all dates and group by month? How exhaustive does "all dates" need to be? One entry for every date as far back as my data goes?
I'd be inclined to create that in Excel, just for simplicity, and import it. Is that a reasonable approach?
And, for a bit of spice... going forward I'll want this report to always show the last three completed months. (Today that would be Nov-Dec-Jan.) If I have date entries that go back years, and I've successfully made this report "show all months even if there's no data," how do I restrict it to only the requested months?
Dave
After posting this question, I manually created a table called Months (columns MonthNumber, ShortName, FullName) and tried to link it to my table of orders on the DateEntered field. Of course there's nothing in the Months table that matches the type of DateEntered.
So should I instead create a table of all dates and group by month? How exhaustive does "all dates" need to be? One entry for every date as far back as my data goes?
I'd be inclined to create that in Excel, just for simplicity, and import it. Is that a reasonable approach?
And, for a bit of spice... going forward I'll want this report to always show the last three completed months. (Today that would be Nov-Dec-Jan.) If I have date entries that go back years, and I've successfully made this report "show all months even if there's no data," how do I restrict it to only the requested months?
Dave
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Unfortunately it is true - there is no direct way to do this in Crystal because Crystal only renders that data that is provided to it. As you've already read in the referenced threads you'll need to find a way to get the 'missing' data into Crystal.
The common approach is a left outer join to a control table that has the date range you want to see. An alternate approach is a stored procedure that manually inserts the missing records into the resulting data set. The left join is generally easier to implement but can cause some confusion whenever averages or record counts are involved because of the control table entries - this is when a stored procedure can be a useful alternative.
I suspect you posted this question hoping that things had changed (improved) but unfortunately you're forced to remain in Stunned mode.
frodoman