Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Occupancy Crosstab Post Reply Post New Topic
Author Message
ideamechanic
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: Canada
Online Status: Offline
Posts: 4
Quote ideamechanic Replybullet Topic: Occupancy Crosstab
     Posted: 27 Feb 2008 at 6:31am
Hello,

I am trying to create (what I think is) a fairly intricate crosstab.  I have two tables: a table containing client information and one containing dates the client stayed at a facility.  I have a checkin date and a checkout date and, among other things I have the age of the client.

Now what I would like to do is run an occupancy report letting me know the number of clients staying at the facility in each age group in each month of a date range.  The problem lies in the fact that some months might not have any data (due to the facility not being open), yet I still want that month to print as a column.  Also, clients may stay over a month but I'm not sure how to represent that in the summary count.  For example the client could check-in in February and not leave until September.  The client should be counted in March, April, May, June, July, August and September.

Can anyone figure out a way to do this that wouldn't require a team of report writers and a couple of weeks to do?

Below is an example of how I would like the report to look.

Thank you so much.

                Jan 07 Feb 07 Mar 07 Apr 07 ...  Dec 07
18-30          0           13       7        14             28
31-45          0            3        0         0                0
46-65          0            1        1         2                1
65+             0            0        0         0                0
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 27 Feb 2008 at 7:00am
Hi,
 
Can you paste the exact sample data with few records....
 
i.e colnames ,values in those cols.......
 
Cheers
Rahul
IP IP Logged
ideamechanic
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: Canada
Online Status: Offline
Posts: 4
Quote ideamechanic Replybullet Posted: 27 Feb 2008 at 12:22pm
Here is an example of the data in the database.  The actual tables hold much more information but this should be enough to provide a solution.  If more is needed please let me know:

Table: Stay





Pk_stay Fk_client Fk_bed Checkin
 Timein Checkout Timeout
1 36 2 10/14/2005 20:34 8/7/2006 13:12
2 34 4 10/14/2005 17:12 10/15/2005 8:56
3 33 4 11/1/2005 13:22 12/31/2005 8:57
4 15 4 4/1/2006 15:17 5/17/2006 10:03







Table: Client





Pk_client FK_Lkgendr Lname Fname Birthday

15 7001 Donovan Jack 10/17/1970

33 7002 Ku Serena 12/18/1987

34 7001 Mallory Sam 6/24/1966

36 7002 McCarthy Siobhan 7/10/1983




Edited by ideamechanic - 27 Feb 2008 at 12:24pm
IP IP Logged
ideamechanic
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: Canada
Online Status: Offline
Posts: 4
Quote ideamechanic Replybullet Posted: 27 Feb 2008 at 12:46pm
This is what the current crosstab looks like and what I would like it to look like:

Current X-Tab Oct-05 Nov-05 Apr-06







18-30 1 1








31-45 1
1







46-65










65+






















Wish X-Tab Oct-05 Nov-05 Dec-05 Jan-06 Feb-06 Mar-06 Apr-06 May-06 Jun-06 Jul-06 Aug-06
18-30 1 2 2 1 1 1 1 1 1 1 1
31-45 1




1 1


46-65










65+











IP IP Logged
ideamechanic
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: Canada
Online Status: Offline
Posts: 4
Quote ideamechanic Replybullet Posted: 28 Feb 2008 at 11:18am
Uh-oh, it's just not good when you're stumping the experts...
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 29 Feb 2008 at 5:38am

Hi

Use the  Sql as below in command object to Create a View ,hope you can do that......

SELECT
year(getdate())-year("Client"."Bthday")'Age',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Oct 05' Then count("Client"."Client") end 'Oct-05',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Nov 05' Then count("Client"."Client") end 'Nov-05',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Dec 05' Then count("Client"."Client") end 'Dec-05',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Jan 06' Then count("Client"."Client") end 'Jan-06',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Feb 06' Then count("Client"."Client") end 'Feb-06',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Mar 06' Then count("Client"."Client") end 'Mar-06',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Apr 06' Then count("Client"."Client") end 'Apr-06',
CASE WHEN left(datename("MM",("Stay"."checkin")),3) + ' '+right(CAST(Year("Stay"."checkin") AS varchar(4)),2) = 'Jul 06' Then count("Client"."Client") end 'Jul-06'
FROM   "Test"."dbo"."Stay" "Stay"
INNER JOIN "Test"."dbo"."Client" "Client"
ON "Stay"."Client"="Client"."Client"
group by "Client"."Bthday","Stay"."checkin","Client"."Client"

 

Then istead of creating cross tab create Group on the Age Column with specified order for Age Column  Which is 18-30  31-45 and so on .......

Once the report is created  go to Group Header, right click select section expert  then check underlay following sections do the same thing for details section.

In relation to second part  about

Also, clients may stay over a month but I'm not sure how to represent that in the summary count,

 
hope you will have some IDEAS ...... Idea before STUMPING AGAIN.......
this is the output .....
 

Wish X-Tab          Oct-05   Nov-05   Dec-05   Jan-06   Feb-06  Mar-06  Apr-06 Jul-06

18-30                     1

1

31-45                     1

 1

 
 

Cheers
Rahul

 



Edited by rahulwalawalkar - 29 Feb 2008 at 5:41am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.