Calendar Style Report 4

[Cydamac]

Has anyone ever created a report that has th elook and feel of a calendar? I am a report designer for a company that develops meeting planning/room reservation software. I have had many requests from external cleints for a report that looks like a calendar. Does anyone know of any way to do this in Crystal 8.5?

Thanks,

cydamac

 

[synapsevampire]

CR 9 has this functionality, you have to cheat to do it in CR 8.5:

http://support.crystaldecisions.com/library/kbase/articles/c2011476.asp

-k

 

[Mike Lewis]

Cydamac,

Has anyone ever created a report that has th elook and feel of a calendar?

I recently finished a report that resembled a year planner. It had a range of dates across the top of the page, and coloured bars underneath them to represent various situations (e.g. black = unavailable, red = in service, etc.).

Is that the sort of thing that would be helpful to you?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Cydamac]

synapsevampire,

I was able to use the formula to creat a calendar, but it is not quite what I am looking for. I need something that I can use to evaulate my actual data. Thanks for the help.

MikeLewis,

I think that you are on the right track with what I am looking for. I need something that will give me a block for each day and show me what meetings I have scheduled for each day. Because some days are more busy than others, each day block needs to be able to expand and contract accordingly. Any ideas?

Thanks,

Cydamac

 

[lbass]

Do you have a table that contains all calendar dates from which you can do a left join to a table with your data? I just created an accurate calendar (month by month), with day numbers in each square, using a crosstab with an Excel table which contained all dates. However, I couldn't do a left join with the Extreme database table in order to add summary data to the calendar. That is probably just a limitation of my particular connection, so I'd be glad to outline this to you if you have the capacity to do a left join like this or if all dates are already represented in your table. Let me know.

-LB

 

[Cydamac]

The problem is that our reports are disconnected. We use ttx files and ql statements in the db to push the recordset to the Crystal Report for formatting. My only other plan was to make a subreport for each day of the week and add grouping in each SR to handle the dates and times of my activities. Thoughts???

 

[Mike Lewis]

Cydamac,

I need something that will give me a block for each day and show me what meetings I have scheduled for each day.

That's very roughly what I did. Basically, I had a field in my table for each day, with a value to tell me the activity for that day. Within the report, each field in the detail band (that is, each day) was represented by a formula which just returned a string of a certain character. The character appeared as a solid block when formatted in the WingDings font. (In your case, the formula might fix the length of the string according to the 'busy-ness' of the date in question).

I also used a conditional formatting formula that changed the colour of the Wingdings string according to the activity flag.

(All this is harder to explain than to actually do. I hope I'm not making it sound too difficult.)

My biggest problem was to put the actual dates above the columns across the top of the page -- given that the dates were variable, and were picked up from the same records in the table as those used in the detail band. I solved it my putting the dates in a sub-report and placing that in the header band.

I don't suppose any of this will help you much, but it might give you something to think about. Let me know if you have any specific questions.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[lbass]

Are you still interested in a solution for this? I'm not sure how this would translate when using ttx files (since I don't have experience working with them), but this might give you some ideas.

I used an Excel table which contained a field with all dates for an entire year (1997) to create the main report, and the Extreme database to create the subreports for the content of each calendar day. This required 5 subreports for Monday through Friday (or 7 if you are doing this for the entire week).

To create the empty calendar in the main report, do the following:

1-Insert a group on {xl.date}, and choose monthly. Check "use customized group name based on a formula" and enter:

monthname(month({xl.date}))

Drag the groupname to the page header and go to format->section->group #1 (monthdate) footer and check new page after and in the formula area enter: Not OnLastRecord

2-Create a formula {@weeknumber}:

DatePart("ww",{xl.date},1,crFirstJan1)

3-Insert a group #2 on {@weeknumber}.

4-Create a formula for each day of the week, e.g., {@Sun}:

if weekdayname(dayofweek({xl.date},1)) = "Sunday" then day({xl.date})

{@Mon} would look like:
if weekdayname(dayofweek({xl.date},1)) = "Monday" then day({xl.date}) //etc.

5-Add each of the 7 formulas to the details section, insert a maximum on each formula, and drag the group #2 summaries to the group #2 header, positioning them equidistant across the page and then in design mode dragging the lower boundary of the header down to allow the desired space per day or "square". Then suppress the details. To eliminate "0" results, right click on each formula->format field->common->suppress and enter:

sum({@Mon}, {@weeknumber}) = 0 //changing the day formula name for each of the remaining field suppressions

6-Next, to create the grid, add a box which includes the page headers (daynames) and extends down to the bottom of the group #2 header. Then add vertical lines extending from the top of the box to the bottom to divide the days. For both the box and each line, format them to "Extend to bottom of section when printing" by right clicking on the box or line and choosing "format." Next left justify and reposition the maximums so they are in the upper left corner of each day box, as you would normally see in a calendar.

Now you should see an empty calendar with one month per page.

7-To create the subreport "Mon", insert a subreport and select tables (I used the Extreme database tables Orders, Order Details, Product). I added the {Product.Product Name} field to the subreport (this is where you would add your meetings and times data) and suppressed all other sections but the details section.

8-Next you would also insert a formula in the subreport called {@weeknumber}:

datepart("ww",{Orders.Order Date},1,crFirstJan1)

9-In the record selection formula for the subreport, add:

year({Orders.Order Date}) = 1997 and
dayofweek({Orders.Order Date}) = 2 //for Monday

10-Then link the subreport to the main report using the {@weeknumber} fields from each, and then place the subreport in the Monday square of the group #2 header, resizing it to fit.

11-Next, right click on the subreport->format subreport->common->suppress and enter:

maximum({@Mon}, {@weeknumber}) = 0 //again, change to correspond to the correct weekday

Because weeknumbers are repeated for the end of one month and the beginning of the next, since month ends/starts are typically mid-week, this last suppression prevents duplicate entries in "blank" days.

12-Repeat steps 7 to 11 for the remaining subreports.

The subreports now fill the dates throughout the year with your data.

-LB

 

[lbass]

Forgot to mention the (obvious) need for a horizontal line running across the top of Group #2 header to divide the weeks.

-LB

 

[aud9]

LB, this is exactly what I was looking for. I work all October on various calendar solutions. Is it possible to email me a copy of this crystal report.

Thanks

aud9
rwhetsell@sc.rr.com

 

[lbass]

Sure, I'll send this on to you. It uses the Xtreme database and so is a 1997 calendar, and instead of events, I listed product names per day. I only added the subreports for Monday and Tuesday for test purposes, so subreports for other days would need to be added to finish the calendar. The main report was based on an Excel spreadsheet, and I'm not sure how portable the report is without it, so I'll attach the spreadsheet as well, just in case it is helpful.

-LB

 

[javaRocks]

Hi lbass-do you think you could email me a copy too? -I am looking to do something similar-and I am not a crystal programmer, just a plain ol' java programmer who has only done server stuff before.
Thanks a million.
VH.
vhariths@hotmail.com

 

[lbass]

I'll e-mail you tonight--I don't have the report at this location.

-LB

 

[ghergster]

lbass...could you email me a copy of this report as well? I have been searching for a calendar solution for a while and this looks like it might just solve my problem. Thanks!

GC
ghergster@yahoo.com

 

[lbass]

Sure, tonight.

-LB

 

[bbduc]

lbass...could you email me a copy of this report as well? I am looking for that kind of calendar
Thanks!

BD
bbduc@yahoo.com

 

[lbass]

Done.

-LB

 

[dckidd]

me too also, can I get a copy of your template?

 

[lbass]

Yes, tonight, if you provide your e-mail.

-LB

 

[dckidd]

dave.howard@linium.com