Report Design: Crosstab column date grouping

Print Page | Close Window

Crosstab column date grouping

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17384
Printed Date: 04 May 2024 at 11:48am

Topic: Crosstab column date grouping

Posted By: Craigbob
Subject: Crosstab column date grouping
Date 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

Replies:

Posted By: hilfy
Date 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

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date 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.

-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?

Posted By: Craigbob
Date 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

Posted By: DBlank
Date Posted: 22 Aug 2012 at 9:23am

try using an AND on the join instead of a WHERE

Posted By: Craigbob
Date 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?

Posted By: DBlank
Date 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.

Posted By: Craigbob
Date 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

Posted By: DBlank
Date Posted: 22 Aug 2012 at 11:16am

what is dbo.RJDateLookup.DateFull field youa re using in your join?

Posted By: Craigbob
Date 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

Posted By: DBlank
Date Posted: 22 Aug 2012 at 12:09pm

so your RJDateLookup table is just a "full calendar table" (one row per day) that has a lot of meta data for each day. You actual data is in the Quote table but you need to limit that by only items in the next 6 months (including missing days/months) and only where the Type='C'...correct?

try this for the join and where clause

FROM dbo.RJDateLookup LEFT JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate and (dbo.CST_MS_Quote.QuoteType = 'C')
WHERE (dbo.RJDateLookup.DateFull BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2013-01-01 00:00:00', 102))

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 12:25pm

You have the concept correct. The code you gave only brought back data for July and August. nothing past 8/31/2012. I think the problem lies in the join being DateFull = QuoteDate.

If I'm understanding it correct it will only pull data for the CST_MS_Quote table for those days where the two tables each have a date in common. But it should return all the dates in the RJdatelookup table.

Since I want to group this by month/yr would it work if we changed the join to that?

Posted By: DBlank
Date Posted: 22 Aug 2012 at 2:26pm

The grouping can be handled in your crystal design.
If you have one day for every day in the RJlookup table then you should have gotten all of the records from Quotes table with a type of c between the listed dates plus all of the blank dates in that range as well (from the RJlookup table).
If your lookup table is missing any days then it they will be missing those days from the quote table also.
Does that help?

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 2:37pm

Originally posted by DBlank

The grouping can be handled in your crystal design.
If you have one day for every day in the RJlookup table then you should have gotten all of the records from Quotes table with a type of c between the listed dates plus all of the blank dates in that range as well (from the RJlookup table).
If your lookup table is missing any days then it they will be missing those days from the quote table also.
Does that help?

The RJDatelookup table has every day from 1/1/1900 thru 12/31/2099 so they are all there.

I did get the QuoteDates, but not any date after 8/31/12.

Posted By: DBlank
Date Posted: 22 Aug 2012 at 2:49pm

Run a query on the quote table to verify that you have items past that date that also have the c type.
Basically just use the same query, just remove the date tableland make sure to read the c type to the where clause becuase it will disappear when you get rid of the join.
Do you get all of the dates you were expecting?

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 3:01pm

Originally posted by DBlank

Run a query on the quote table to verify that you have items past that date that also have the c type.
Basically just use the same query, just remove the date tableland make sure to read the c type to the where clause becuase it will disappear when you get rid of the join.
Do you get all of the dates you were expecting?

Ahah! I see the miscommunication, let me clarify. At this point, there are no records that have a ReqDate past 8/31 in the Quote Table. Hence why I created the date table to fill in the rest of the dates. At this point the table is in a test/transition phase and while we have live data its not being used in full production.

That being said how can I get the data I need?

Posted By: DBlank
Date Posted: 22 Aug 2012 at 3:10pm

Oops.
Try and change it to a right join

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 3:14pm

Originally posted by DBlank

Oops.
Try and change it to a right join

Nope, No change from the left Join.

Posted By: DBlank
Date Posted: 22 Aug 2012 at 3:23pm

Also this will not fill in the quote date field. That will be null in the data set for items past 8/31.
Instead use the date.fulldate field as it matches the records plus fills in the 'blanks' that way.
Does this help?

Posted By: DBlank
Date Posted: 22 Aug 2012 at 3:28pm

Also is y our WHERE clause using the rjdatelookup table only, not the quote table, correct?

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 3:55pm

Originally posted by DBlank

Also this will not fill in the quote date field. That will be null in the data set for items past 8/31.
Instead use the date.fulldate field as it matches the records plus fills in the 'blanks' that way.
Does this help?

I'm not looking to fill in the quotedate field. That will be pulled from the record.

I want to fill in the missing ReqDates (a child field from a sub table) with the Datefull Date from the Datelookup table.

Posted By: Craigbob
Date Posted: 22 Aug 2012 at 3:57pm

Originally posted by DBlank

Also is y our WHERE clause using the rjdatelookup table only, not the quote table, correct?

Correct. Though I will be replacing the 1st date with the quotedate and the last with a date of the last day of the month 6 months in the future (from the Quotedate).

Posted By: hilfy
Date Posted: 23 Aug 2012 at 3:28am

Try this for your query:

LEFT JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate

and dbo.CST_MS_Quote.QuoteType = 'C'
and dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
WHERE dbo.RJDateLookup.DateFull >= '07/01/2012'

This way you will be filtering the dates based on the date table, not on the quote table.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 23 Aug 2012 at 7:28am

Thank You. That is better. But it is still returning dates past 6 months in the future, so it is not stopping when expected.

Posted By: hilfy
Date Posted: 23 Aug 2012 at 7:51am

That's because of your filter - you're filtering for end date on 6 months past the Quote date where the quote date can be any date after 7/1/12. So, if the quote date is 8/1/12, you'll get an end month for that data of 2/1/13. If you want just 6 months, take the date part out of the join an add something like the following to your where clause:

and dbo.RJDateLookup.DateFull < "2/1/2013"

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 23 Aug 2012 at 2:25pm

Originally posted by hilfy

and dbo.RJDateLookup.DateFull < "2/1/2013"

-Dell

I still think I'm muddling what I'm trying for.

The month and year of the QuoteDate will be used a parameter. I don't need the quote date through the 6 months in the future.

The date field I need for 6 months in the future is the ReqDate. This is the field that has missing data that needs to be filled in by the Datelookup table.

so in essence I want the report to look like this: (Showing Fields rather than data)

Location2

QuotedBy

Material ReqDate (Current Month) ReqDate (Next Month).... (6 months out)
Sum(BidAmt) (0 if ReqDate is missing)

Hopefully that makes more sense and clarifies things a bit.

Again thanks for the assistance.

Posted By: Craigbob
Date Posted: 29 Aug 2012 at 8:53am

Bump, Is there any hope for me here?

Posted By: hilfy
Date Posted: 29 Aug 2012 at 9:22am

If your report is pulling multiple quotes, each with a different Quote Date, then you're going to see more than 6 months in your cross-tab, because you have more than 6 months worth of dates in your data.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 29 Aug 2012 at 9:44am

Originally posted by hilfy

If your report is pulling multiple quotes, each with a different Quote Date, then you're going to see more than 6 months in your cross-tab, because you have more than 6 months worth of dates in your data.

-Dell

Doh'

I realized that after I sent the message.

But I'm still not getting what I expect.

I ran a query for a single quote dated 8/1/2012 and I was hoping to get dates for Aug 2012 thru Feb 2013 and did not. I just got 1 record for that quote. Which had no required dates.

What am I missing and how can I get force/fake the 6 months for the forecast to display?

Posted By: hilfy
Date Posted: 30 Aug 2012 at 3:19am

Please post the exact join and where clauses from your query so that we can see what's going on.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 30 Aug 2012 at 10:42am

Originally posted by hilfy

Please post the exact join and where clauses from your query so that we can see what's going on.

-Dell

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
and dbo.CST_MS_Quote.QuoteType = 'C'
and dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
WHERE
dbo.RJDateLookup.DateFull >= '08/01/2012'
AND
Quote = 'AC1586'

All I did was add the quote clause so I could see if it works in one instance.

All it did was return the one record and no dates from the datelookup past August.

I was expecting it to return the quote record and the next 6 months from the datelookup table for a total of 7 records.

Posted By: Craigbob
Date Posted: 30 Aug 2012 at 11:40am

Okay some more fuel for the fire. When I try and select dates from the datelookup table alone and filter between 8/1/2012 and 3/1/2013, it returns only the data for August (31 records).

SELECT
*
FROM
RJDateLookup
WHERE
DateFull >= '08/01/2012 00:00:00' AND DateFull < '03/01/2013 00:00:00'

I've also tried DateFull Between '08/01/2012' and '03/01/2013' with the same results.

I did a select * on the table and it came back with all the dates.

What am I missing? Am I insane?

Posted By: Craigbob
Date Posted: 30 Aug 2012 at 12:24pm

Well it looks like the Datelookup table is missing all dates for Sept. Oct. Nov, Dec, Jan and Feb for each year. Makes no sense. Here is the SQL used to populate the table.

DECLARE @Date DATETIME
SET @Date = '1/1/1900'

WHILE @Date < '1/1/2100'
BEGIN
     INSERT INTO RJDateLookup
     (
        DateKey, DateFull, FullYear,
        QuarterNumber, WeekNumber, WeekDayName,
        MonthDay, MonthName, YearDay,
        DateDefinition,
               CharacterDate,
               WeekDay,
               MonthNumber
     )
     SELECT
        CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
        DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
        DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
               DATENAME(mm, @Date) + ' ' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',
           ' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
           CONVERT(VARCHAR(10), @Date, 101),
           DATEPART(dw, @Date),
           DATEPART(mm, @Date)

     SET @Date = DATEADD(dd, 1, @Date)
END

Posted By: Craigbob
Date Posted: 30 Aug 2012 at 4:23pm

Okay rebuilt and repopulated the date table and all the dates are in there now. But still no luck in getting all dates to show when linked to the quotes table. GRRRRR.

Posted By: hilfy
Date Posted: 31 Aug 2012 at 3:15am

Add the quote number to the join instead of the where.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 24 Sep 2012 at 8:58am

Originally posted by hilfy

Add the quote number to the join instead of the where.

-Dell

Thanks. just getting back to this now that I've finished the higher priority report that came in.

Here is the code as it it now stands. I've taken out some unnecessary fields and fixed the date filter.

SELECT distinct

dbo.CST_MS_Quote.MSCo, 

dbo.CST_MS_Quote.QuotedBy, 

dbo.RJDateLookup.MonthNumber, 

dbo.RJDateLookup.MonthName, 

--dbo.RJDateLookup.DateFull, 

Month(dbo.CST_MS_Quote.QuoteDate) QuoteDate, 

Month(dbo.CST_MS_Quote.ReqDate) ReqDate, 

dbo.CST_MS_Quote.Location2 AS Location, 

dbo.CST_MS_Quote.MaterialCode, 

dbo.CST_MS_Quote.MaterialDescription, 

isnull(sum(dbo.CST_MS_Quote.QuoteUnits),0) QuoteUnits





FROM dbo.RJDateLookup 

Left JOIN dbo.CST_MS_Quote 

ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate 

and dbo.CST_MS_Quote.QuoteType = 'C' 

and dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))

--AND 

--Quote = 'AC1586'

WHERE 

dbo.RJDateLookup.DateFull >= '09/01/2012' 



Group by

dbo.CST_MS_Quote.MSCo, 

dbo.CST_MS_Quote.QuotedBy, 

dbo.RJDateLookup.MonthNumber, 

dbo.RJDateLookup.MonthName, 

--dbo.RJDateLookup.DateFull, 

month(dbo.CST_MS_Quote.QuoteDate), 

month(dbo.CST_MS_Quote.ReqDate), 

dbo.CST_MS_Quote.Location2, 

dbo.CST_MS_Quote.MaterialCode, 

dbo.CST_MS_Quote.MaterialDescription



order by QuotedBy,Location,MaterialCode

However I'm not sure how to get the extra months as required dates.

Posted By: hilfy
Date Posted: 01 Oct 2012 at 3:20am

You have two mutually exclusive conditions in your join:

dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate

and

dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))

both work on the same two fields. So, you need to decide which one you need.

Also, based on whether a join is a left or a right join, they're sensitive to the order of fields in the condition. Whichever of these two conditions you decide to use, I would change them to this:

dbo.CST_MS_Quote.QuoteDate = dbo.RJDateLookup.DateFull

Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))>= dbo.RJDateLookup.DateFull

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Craigbob
Date Posted: 03 Oct 2012 at 8:37am

Originally posted by hilfy

You have two mutually exclusive conditions in your join:

dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate

and

dbo.RJDateLookup.DateFull <= Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))

both work on the same two fields. So, you need to decide which one you need.

Also, based on whether a join is a left or a right join, they're sensitive to the order of fields in the condition. Whichever of these two conditions you decide to use, I would change them to this:

dbo.CST_MS_Quote.QuoteDate = dbo.RJDateLookup.DateFull

Dateadd(m,6,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,QuoteDate))-1),DATEADD(mm,1,QuoteDate)),101))>= dbo.RJDateLookup.DateFull

-Dell

Actually they are not mutually exclusive. one sets the start date and the other sets the end date.

But a new wrinkle has been thrown into the mix last night. They want to be able to group all records that are earlier than the quote date have a status of Active = 'Y' and expired date < current date in the 1st column.

I think I know how to do that using Nested case statements. When I get back to my desk later I'll post the SQL and see whaty happens.

Posted By: hilfy
Date Posted: 03 Oct 2012 at 9:21am

But this

dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate

means you'll only get records where they're exactly equal - regardless of the end dat you've set.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Print Page | Close Window