Is there a calendar table function?

[Former Member]

Hi,

I've created a few calendar style reports. To do this I created a single column table called calendar_date. There are no keys. It's just a single column table with values that begin with January 1, 2011 and increment daily for several years (e.g. all the way to December 31, 2018). When I link this table to the other tables in my database (on a date field) and then run the report it takes 25 minutes to run each time - because the table is not indexed, I understand.

Instead of using a standalone, non-indexed calendar_date table to achieve the calendar style reporting is there a function (or something) in Crystal that can accomplish the same thing, in effect? That way the report can run quickly.

thx!

Mark

Edited by: mahewitt on Jan 31, 2012 3:54 PM

[Former Member · Accepted solution]

Hi,

As far as I know, you can either use below 2 approach:

1> You can create Crystal report using 2 tables one with data and other with just date and link them in CR designer, i guess you are follwowing this approach.

2> You can create a view at database level on top of these 2 tables and use this view to create a Crystal reports.

I am not sure what do you mean by Calendar tabnle function.

Cheers,

Kiran

[JWiseman · Accepted solution]

hi Mark,

how is your calendar report built? did you use one of the templates from [here|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11344] [original link is broken] [original link is broken];?

if you are using the one that has about 7 groups on top of the date field (Calendar Which is Populated With a Date Lookup Table), and your date lookup table is in the same database as your other tables, then the report shouldn't take that long to generate.

what i would do is the following:

1) go to the Database > Show SQL and then copy this syntax

2) run it in your database sql editor...it should be about the same time

questions....

a) how many records are returned?

b) how many do you actually need on your report?...i.e. if you're showing lots of summaries on your report versus lots of details records, you should write a Command object that aggregates the data or use a view like Kiran suggested.

jamie

[Former Member · Accepted solution]

Thanks!

I'll work on this with the links you provided.

Mark

Edited by: mahewitt on Jan 31, 2012 6:38 PM