Crosstab show all months (CR-XI), continued

This is a continuation of this http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21745795.html from February. Sure have learned a lot since then... but it's still not working.

The issue was (still is) that I want to summarize my product orders by month, at one level of a complex multi-level report. And, I want to do it with a cross-tab showing all the months in the user-specified date range, even months that have no orders.

Example of a few rows in my order history:
Item#    Kit Product#   Order Date    Order#    Qty
A12       1234567        3/1/06          J1234       10
A12       1234568        3/18/06        J2134        5
A12       1234567        5/10/06        K4312       10

Desired cross-tabs - a column for every month:

      Kit Prod#                March    April     May     Total
      1234567                 1                      1          2
                    qty           10                    10        20

      Kit Prod#                March    April     May     Total
      1234568                 1                                  1
                    qty           5                                  5


What I'm getting - each cross-tab only has months that contain data in this group:

      Kit Prod#                March    May     Total
      1234567                 1           1          2
                    qty           1           10        20

      Kit Prod#                March    Total
      1234568                 1           1
                    qty           5           5

My query is like this:

SELECT       Months.MonthNumber, Months.Abbrev,
      History.ItemNumber as Item,
      History.KitNumber AS Kit,
      History.OrderDate as DateEntered,
      MONTH(History.OrderDate) AS MonthEntered,
      History.Order_Number AS OrderNum,
      History.Quantity AS Qty
FROM Months
LEFT OUTER JOIN History
ON months.MonthNumber = MONTH(History.OrderDate)

where History.OrderDate between {?Beginning Date} and {?Ending Date}

In Query Analyzer the result set seems to contain all the right stuff, so my problem must be within Crystal, yes?

What am I doing wrong?
deBronkartAsked:
Who is Participating?
 
bdreed35Commented:
I was looking at your SQL that you posted.
It looks like you are negating the left join to the history table by applying criteria to the order date field in the History table.
To ge tthe results that you want, you need to apply that to the table before the left join is performed.

Try using this as a SQL Command and see if you get all the blank rows you expect:

SELECT      Months.MonthNumber, Months.Abbrev,
     History.ItemNumber as Item,
     History.KitNumber AS Kit,
     History.OrderDate as DateEntered,
     MONTH(History.OrderDate) AS MonthEntered,
     History.Order_Number AS OrderNum,
     History.Quantity AS Qty
FROM Months
LEFT OUTER JOIN History
ON months.MonthNumber = MONTH(History.OrderDate)
and History.OrderDate >= {?Beginning Date}
and History.OrderDate <= {?Ending Date}
0
 
bdreed35Commented:
GO into the Chart Expert, and under the Customize Style tab, make sure you don't have the Suppress Empty Columns property set.
0
 
deBronkartAuthor Commented:
> GO into the Chart Expert, and under the Customize Style tab,
> make sure you don't have the Suppress Empty Columns property set.

Presuming you meant Cross-Tab Expert :), yeah, Suppress Empty Columns is not set.  Great idea, tho - I hadn't thought of that!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
bdreed35Commented:
Yeah, I meant the Cross-Tab Expert.

Are you using a SQL Command or using the tool tables in Crystal directly?
0
 
deBronkartAuthor Commented:
bdreed,

?!?  I didn't know you can do add a WHERE filter to a JOIN, using ANDs.  Seems odd!  When I get to the office I'll give that a whack.

Still, IIRC, the query that I posted does give the dataset that I want, including at least one row for every month/item/kit combination, even if the order-related fields are NULL. (My complaint was that the cross-tab didn't show the empty columns.)

But I'll check. Thanks!
0
 
mlmccCommented:
WHich columns are in the cross tab.  If they values are null they may get ignored. You might have to us ethe Convert NULL to Default value option and make it 0.

Click FILE --> OPTIONS  or REPORT OPTIONS

mlmcc
0
 
deBronkartAuthor Commented:
As it happens, the solution for me was not to use cross-tabs, but to build my own summary display in the group *footer*, not the header where I'd wanted it.  I can do this because I don't have to cope with an indefinitely long list of values - I can limit it to 3 months.

We're not showing the detail rows, just the summaries.  Here's what works:

At the top of the report, I take the user input for date range and break it into months, and initialize shared counters for order count and order qty for each month.

At the start of each product's group I reset the counters.

In the detail row I have a formula that adds the current order data to the appropriate month's counters.

In the product's footer I display the results in 3 columns (one for each month).

I imagine at some point I'll know enough about cross-tabs to see whether I could do exactly the same thing with a cross-tab.  But, among other things, cross-tabs seem to be substantially limited on how the results are displayed, and the method I used lets me do anything I want with the results.

Bottom line, I didn't solve this problem - I worked around it.  Apologies for not having tried all your generous suggestions!

0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.