| Author |
Message |
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
   Topic: Crosstab column date grouping
Posted: 21 Aug 2012 at 3:37pm |
|
Hi All,
I'm trying to build a cross tab report in Crystal XI that has dates, grouped by the month as column heading, and have 2 questions I'm trying to find answers to.
1: Is there any way to display a month even if there are no records to be summarized in that month? i.e. no orders for March, but want to display Jan, Feb, Mar.? I'm pretty sure I can create a dates table and use that spoof it, but don't want to go that route as the database is part of a Vendor's package and I don't want to get to crazy in there.
2: Is there a way of specifying or limiting how many columns go across? For example in a forecast cross tab I only owant current month and the next six months to show up. I suppose I can do a date range on the selection criteria, but that seems a bit kludgy to me.
Thanks for any answers
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 22 Aug 2012 at 4:06am |
1. There is no way I know of to do this without a Dates table. 2. The only way is to filter your data. This is not "kludgy" - it makes sense because it means you're only processing the data that you actually need for the report instead of pulling in ALL of the data and having Crystal do the filtering in memory. -Dell
|
|
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 7:28am |
Originally posted by hilfy
1. There is no way I know of to do this without a Dates table. 2. The only way is to filter your data. This is not "kludgy" - it makes sense because it means you're only processing the data that you actually need for the report instead of pulling in ALL of the data and having Crystal do the filtering in memory. -Dell
Thanks for the answer, As I really can't create a new table in the SQL server database, do you think it's possible to create a temp dates table in view I've built? and would that work?
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 8:59am |
|
One last question, I'm trying to do a left Join from the Dates lookup table (I was able to create one after all) to the view that has the data I need to pull.
Even though I'm specifying the monthname be used I'm still not seeing the full 6 months forward.
Here is the SQL for query.
SELECT
dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.JCCo,
dbo.CST_MS_Quote.Quote,
dbo.CST_MS_Quote.MSHQStatus,
dbo.CST_MS_Quote.QuoteType,
dbo.CST_MS_Quote.Job,
dbo.CST_MS_Quote.Status,
dbo.CST_MS_Quote.Description,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
dbo.RJDateLookup.DateFull,
dbo.CST_MS_Quote.QuoteDate,
dbo.CST_MS_Quote.ReqDate,
dbo.CST_MS_Quote.ExpDate,
dbo.CST_MS_Quote.FromLoc,
dbo.CST_MS_Quote.Location2 AS Location,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription,
dbo.CST_MS_Quote.UM,
dbo.CST_MS_Quote.QuoteUnits
FROM dbo.RJDateLookup LEFT JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate
WHERE
(dbo.CST_MS_Quote.QuoteType = 'C')
and
QuoteDate >= '07/01/2012'
and
dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 9:23am |
|
try using an AND on the join instead of a WHERE
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 9:26am |
Originally posted by DBlank
try using an AND on the join instead of a WHERE
I'm not sure I understand your solution. Can you elaborate?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 9:40am |
adding a a where clause in your SQL can effective change an outer join into an inner join because the where is applied after the join.You can alter your join statement in SQL to use multiple conditions to apply befere the outer join occurs.
Try to change the WHERE to AND and see if it works in SQL to get your expected data set.
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 10:18am |
Well that expanded the data set all right, but it includes everything from the Datelookup table. NOt quite what I'm looking for. 
I just want everything from the month of the quote to the month 6 months in the future.
I.E. QuoteMonth is July 2012, I need July, Aug, Sep, Nov, Oct, Dec and Jan 2013
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2012 at 11:16am |
|
what is dbo.RJDateLookup.DateFull field youa re using in your join?
|
|
IP Logged |
|
Craigbob
Newbie
Joined: 21 Aug 2012
Location: United States
Online Status: Offline
Posts: 27
|
Posted: 22 Aug 2012 at 11:54am |
|
Here is the table structure of RJDateLookup:
DateKey INT PRIMARY KEY,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MonthName VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WeekDay TINYINT,
MonthNumber TINYINT
I've tried using Datefull ans CharacterDate both of them give me dates in the past as well.
I need to use QuoteDate as the start and show month of ReqDate for the next 6 months from QuoteDate. If the record does not have a reqdate I need to still show the month with 0 in the sum (that I know how to do).
Craig
|
|
IP Logged |
|
|
|
