Crosstab Comparing Current Year and Prior Year

[jaz237]

Using Crystal Reports XI rel 2 with SQL Database through ODBC

This report is a PACE report comparing where we are this year based on exactly where we were at this point last year. So the table structure is such that one set of tables pulls current information and a table named Snapshot tracks changes to significant data such as room nights.

I am attempting to migrate this report into Crystal in order to eliminate having to enter data in an Excel spreadsheet. Currently the spreadsheet houses the data from the previous year as it was entered and uses formulas to calculate the difference.

Here is the Crosstab representing the PACE report:

2008 RM Produced Total Rooms Prior Yr Variance
this month Total Rooms # and %
01/2008 0 41,420 54,498
02/2008 0 84,966 30,481
03/2008 0 65,356 25,896
04/2008 0 32,036 10,001
05/2008 2714 25,842 5,770
06/2008 245 29,741 9,560
07/2008 20 17,863 11,938
08/2008 0 11,037 13,558
09/2008 45 7,518 722
10/2008 0 27,227 9,415
11/2008 713 20,223 10,424
12/2008 0 20,400 12,655

The first column represents the room nights produced this month in the corresponding arrival dates (row is command.arrival1.)

Here is the formula (@current):

 if ({Command.updatedate} = (lastfullmonth)) and ({Command.Arrival1}>= date ('01-01-2008')) and ({Command.statusid}=(105)) then {Command.BlockRooms}

To deal with multiple returns from the snapshot table I have this formula in the crosstab (@maximumcurrent):

if {Command.snapid} = Maximum ({Command.snapid}, {Command.oppid}) then {@RoomNightsBkCurrentMth}

The second column uses the following formula (@total):

if ({Command.statusid} in ([105,115])) and ({Command.Arrival1} >= date('01-01-2008')) and ({Command.Arrival1} <= date('12-31-2014')) then  {Command.BlockRooms}

with the following formula in the crosstab (@maximumtotal)

 if {Command.snapid} = Maximum ({Command.snapid}, {Command.oppid}) then {@TotalRoomNights}

The formula for the third column (@PreviousYR)

 if {Command.snapdate} <= date('04-30-2007') and {Command.Arrival1} >= date('01-01-2007') and {Command.statusid} in [105,115] then {Command.rooms}

does not give me the data I need and it moves the crosstab back to 2007.

I need a way to compare the total room nights for this year to the total room for the last year (with each lead represented only once) in the same crosstab. I worked with the grouping (arrival1 by year and arrival1 by month). But I was unable to write the appropiate formula to group the crosstab.

Here is the command written to pull the data from the database:

SELECT     o.oppid, a.Company, o.LeadName, o.Arrival1, o.Departure1, o.RoomAttend, o.showattend, o.PeakRoom, o.BlockRooms, o.DecisionDate, o.statusid as currentstatus, vw.snapdate, vw.rooms, vw.arrival, vw.statusid as snapstatus, vw.snapid, ot.updatedate, ot.statusid
FROM   Opps_Meeting o INNER JOIN
             vwSnapshot_Meeting vw ON o.oppid = vw.oppid INNER JOIN
             Accounts_Meeting a ON o.AcctID = a.AcctID INNER JOIN
             OppStatus os ON o.StatusID = os.StatusID INNER JOIN
             OppTrace ot ON o.oppid = ot.oppid inner join
             OppStatus ost ON ot.statusid = ost.statusid LEFT OUTER JOIN                               (UDFLists udf_66 INNER JOIN UDFListValues ul_66 ON udf_66.listID = ul_66.listID) ON (udf_66.fieldID = 66 AND ul_66.recID = o.oppid) LEFT OUTER JOIN (UDFLists udf_68 INNER JOIN UDFListValues ul_68 ON udf_68.listID = ul_68.listID) ON (udf_68.fieldID = 68 AND ul_68.recID = o.Oppid) inner join udflists ul_67 INNER JOIN
UDFListValues udf_67 ON ul_67.listid = udf_67.listid ON o.oppid = udf_67.recid AND ul_67.fieldid = 67

WHERE     o.del=0 AND udf_67.listid=154

When I am able to pull the data for both years accurately then I need to give the variance in the years as a number and a percentage.

Thank you so much in advance for any response. I have been working on this report for almost a month and look forward to it being correct! I attempted to be a thorough as possible but please feel free to ask questions if any information is unclear.

Julie

 

[transtead]

Julie,

First I have an SQL optimization suggestion. You have a few joins that I'm not sure you need, necessarily.

All of your UDFList joins look like they could be consolidated into one join as they seem redundant an not used.

This might be a better way...

INNER JOIN udflists udf
INNER JOIN udfListValues udfv
ON udf.listid = udf.listid
ON udf.recID = o.oppid

...then...

WHERE
o.del=0 AND udf.listid=154 AND udf.fieldid = 67

This <i>could</i> be the reason why you're having trouble with multiple returns (I'd need to know more about your database to make an absolute determination).

As far as your report goes...
Is your above example the only information shown on the report? Or is the pivot table part of a larger report that utilizes all of the other fields?

-Dave

 

[jaz237]

Dave

Thank you for the SQL suggestion. Unfortunately it did not speed up the report. I believe the multiple returns are caused by the inclusion of the snapshot table.

This table tracks any changes based on the o.oppid so there can be several o.oppid for one vw.snapid. But I must have the snapshot to evaluate where that lead stood this time last year.

The crosstab shown above is the only thing necessary for this report. Not all fields are utilized in the report.

Julie

 

[transtead]

Julie,

Does your OppsMeeting table contain data that is in the snapshot view?

It might be possible to simply return the data from the OppsMeeting table as of a specific point in time by joining two "sub queries" of the OppsMeeting on the Arrival1 date field.

For example (you'd need to tweak this a bit to meet your needs -- also, I'm using SQL Server syntax, so you may need to figure out the syntax appropriate for your db):

select
a.oppid,
a.arrival_month_char,
b.[2007_blockRooms],
a.[2008_blockRooms],
a.[2008_blockRooms]/b.[2007_blockRooms] as BlockRoom_Delta

from
(
-- query #1 returns the oppid, month, and 2008 (or "current") total

-- you would put all of your descriptive fields that you
-- want to include in the report for grouping or otherwise.

o.oppid,
datename(mm,arrival1) as arrival_month_char, -- SQL Server syntax
month(arrival1) as arrival_month_int,
sum(blockRooms) as [2008_BlockRooms]

from
OppMeeting

where
year(arrival1) = 2008

group by
o.oppid,
datename(mm,arrival1), -- SQL Server Syntax
month(arrival1)
) as a

inner join ( -- or left join if you think 2007 was missing months
-- query #2 returns oppid, month, and 2007 total
-- summarize any 2007 totals you want to evaluate against 2008
o.oppid,
month(arrival1) as arrival_month_int,
sum(blockRooms) as [2007_BlockRooms]

from
OppMeeting

where
year(arrival1) = 2007

group by
o.oppid,
month(arrival1)
) as b on a.oppid = b.oppid
and a.arrival_month_int = b.arrival_month_int

order by
a.arrival_month_int

The result of this query will be something like:

oppid arrival_month_char 2007 2008 Delta
1 January 15 25 1.666
1 February 18 25 1.388
1 March 15 20 1.333
etc...

In your report, then you really don't need a pivot table, because the output itself is already grouped by month.

I'd group (in the report) on oppid or any other descriptive field you include in the first "sub-query".

If there are other figures you need to compare across the years, include those in both sub-queries and the select of the "master" query.

-Dave