deBronkart
asked on
Crosstab show all months (CR-XI), continued
This is a continuation of this https://www.experts-exchange.com/questions/21745795/Crosstab-show-all-months-CR-XI.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?
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?
bdreed35
GO into the Chart Expert, and under the Customize Style tab, make sure you don't have the Suppress Empty Columns property set.
ASKER
> 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!
> 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!
Yeah, I meant the Cross-Tab Expert.
Are you using a SQL Command or using the tool tables in Crystal directly?
Are you using a SQL Command or using the tool tables in Crystal directly?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
?!? 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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
Glad i could help
mlmcc
mlmcc