?
Solved

Crosstab show all months (CR-XI), continued

Posted on 2006-06-08
8
Medium Priority
?
436 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:deBronkart
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 16863066
GO into the Chart Expert, and under the Customize Style tab, make sure you don't have the Suppress Empty Columns property set.
0
 

Author Comment

by:deBronkart
ID: 16863400
> 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
 
LVL 28

Expert Comment

by:bdreed35
ID: 16863464
Yeah, I meant the Cross-Tab Expert.

Are you using a SQL Command or using the tool tables in Crystal directly?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 28

Accepted Solution

by:
bdreed35 earned 1050 total points
ID: 16865586
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
 

Author Comment

by:deBronkart
ID: 16869827
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 450 total points
ID: 16874502
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
 

Author Comment

by:deBronkart
ID: 17037358
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 17055406
Glad i could help

mlmcc
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question