Date grouping issue

[Bastel]

Hello everybody,
I've got a start_date and end_date
now i would like to make a report with a group for every day between these dates.
I tried this example, but when i try to make that as group field the OK button is greyd out and the result of that field shows up as true. What's wrong ??
numberVar maxd := {tbl_schulung.ende} - {tbl_schulung.beginn} +1;
numberVar i;
i:=0;
while i < maxd do
(
{tbl_schulung.beginn} = {tbl_schulung.beginn} +i;
i := i + 1;
);
I'am using Postgres 7.0 and CR 8.0

Bastel

 

[dgillz]

Anytime you group on a date field in CR, you get prompted for how often the grouping should take place - monthly, weekly, daily, etc. Just choose daily and you should be fine.

If there is no data for a certain day, you will not get a result of zero, that date will just be skipped in the grouping.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Bastel]

Thank you for your reply,
but i only have the starting and ending date, so i have to calculate the days during runtime,
for e.g. datediff(&quot;x&quot;,end, beginn)
then calculate the field by adding one day to the beginning until we are at the end date.
Is there a way to make a group on calculateted field ??
Bastel

 

[dgillz]

Bastel,

you DO NOT have to calculate all the days in between to group by those dates.

In the design window, rt click on a field you want subtotaled by date. Select insert, summary, and group by the date field in the database (Not the parameter field). Crystal will ask you how often to group and select daily. You are done.

Use the parameter fields in your record selection formula to select only those records with a date in the range you desire.

Please let me know if you have any questions. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Bastel]

Hi,
looks like i don't get what you mean.
where do i use my starting and where do use my end date ?
Because this gives me the total amount of days where i wan't to have my gruping on.

Bastel

 

[rosemaryl]

Dear Bastel;

Here is my assumption. You have data and within that data is a field that stores your start date and another field that stores your enddate.

You want to select records where the StartDateField or EndDatefield fall within the ?StartDate to the ?EndDate parameters.

Create a Startdate Parameter and an EndDate Parameter.

(Insert/Field Object/Parameter/New)

In the Select Expert write a formula as follows:

(Report/Select Expert/Click Field to Select {StartDateField}, click ok, Click Show Formula, Click Formula Editor, enter your formula in the formula editor and then clic Save and Exit button.)

StartDateField in ?StartDate to ?EndDate
or
EndDateField in ?StartDate to ?EndDate

This will select all records where either the startdate or enddate fall within a particular date range. This will provide the report with Flexibility.

Now insert a group for your records based upon the StartDateField.

When you insert a group for a DateField a choice on grouping will be available in the Insert Group Dialog Box:
daily, weekly, monthly, and so on.

Once you have your group you can insert detail records for your data:

This report will look as follows if grouped on date/Daily and the range selected was 03/25/2002 to 03/26/2002

March 25, 2002

Rec 1 StartDate EndDate DataField DataField DataField
Rec 2 StartDate EndDate DataField DataField DataField
Rec 3 StartDate EndDate DataField DataField DataField

Total for Group: 3

March 25, 2002

Rec 5 StartDate EndDate DataField DataField DataField
Rec 8 StartDate EndDate DataField DataField DataField
Rec 9 StartDate EndDate DataField DataField DataField
Rec 11 StartDate EndDate DataField DataField DataField
Rec 12 StartDate EndDate DataField DataField DataField
Rec 13 StartDate EndDate DataField DataField DataField

Total for Group: 6

Total for Report: 9

I hope that was helpful. If this is not what you are trying to do, post a more detailed explanation with an example like the report format above of what you are trying to accomplish.
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear Bastel,

Oops, in copying I forgot to change second grouping's date to March 26, 2002, but hopefully you got the idea.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Bastel]

Hi Rosemary,
thanks a lot for your answer,
but where i got the problem is:
first: my crystal version is 8 and German so i am not quite sure where the Menu Report/ Select Expert is.
second: is to make it a lot easyer i only need of every recordset a group
of every day in the range of begin to end date.
I got to tables
one with Nr Start End Data1 Data1
second Id Data2 Data2 -> Nr and Id is relation.
so what i need now is a report where eg the selection is Nr = 20
and the report should look
Nr Start Data1
Data2 Data2
Nr Start + 1 day Data1
Data2 Data2
Nr Start until End day Data1
Data2 Data2
So i don't need the Parameter Fields.
excuse my english native is german
Bastel

 

[rosemaryl]

Dear Bastel,

I am also using Crystal 8.0, regardless of the language version there should a menu option that has Select Expert. I do not know what it would be called in German but in the same menu are the following options:

Report Expert
Select Expert
Change Group Expert and so on.

In the English version it is the 7th menu item on the menubar from Left.

You say that you do not need parameters, I assume then that you will hardcode the date range?

I think the problem is that I am assuming you need one thing, when you are actually looking for another.

Questions:

What is your startday you intend to start for the report?

Does data exist for both tables for each StartDate +1?

Do all records for ID in table one have a match in table 2?

I am confused because you have to provide a Date Range where the NR_Start and NR_End fall within that range.

You cannot use the formula as you originally showed to select or group records!

If I were writing the report I would do the following:

Link the two tables via the NR_ID to SecondTable_ID. If all records in NR match to SecondTable records you can use and equal join. If not, you should use a left outer join.

Once the tables are linked. You can enter the selection formula (wherever you entered the NR=20) I am assuming this is the ID field. My select would show as follows:

{NR_ID} = 20 and
{NR_StartDay} in Date1 to Date2
and
{NR_Enday} in Date1 to Date2

Whether you hardcode the dates or use parameters so you can select the days you must provide a date range that tells Crystal what records to return.

Does this help?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Bastel]

Hi Rosemary,
let me try to explain wat's going on:
the data structure is like that:
table 1:
colums: ID Date_beginn Date_end DATA1 DATA2 etc.
data eg: 15 12/03/2002 15/03/2002 whatever1 whatever2
table 2:
colums: ID refrence_id DATAX1 DATAX2 etc.
data eg: 1 15 whateverx1 whateverx21
data eg: 2 15 whateverx2 whateverx22
data eg: 3 15 whateverx3 whateverx23

now i would like CR to build a page for each date :
Date_beginn whatever1 whatever2
15 whateverx1 whateverx21
15 whateverx2 whateverx22
15 whateverx3 whateverx23

Date_beginn + 1 Date_END whatever1 whatever2
15 whateverx1 whateverx21
15 whateverx2 whateverx22
15 whateverx3 whateverx23
etc.
Date_beginn until Date_END whatever1 whatever2
15 whateverx1 whateverx21
15 whateverx2 whateverx22
15 whateverx3 whateverx23

if use dataselection and then use the formular
table1.id = 15 and
table1.date_beginn in table1.date_beginn to table1.date_end
or
table1.date_end in table1.date_beginn to table1.date_end

it only shows up the first day


I figured out the menu: awful translation in german its assitent.
there to different possibilities dataselection or groupselection
Do you know the difference ?

Bastel

P.S. thanks a lot so far, my understanding for CR grows a lot !!

 

[rosemaryl]

Dear Bastel:

The difference between group selection and dataselection (records) is:

If I inserted a group on Report, say Country. I can set Group Selection for that Group as GroupCountry = 'USA'
If i have a summary operation (say SalesTotal) on same group, I can perform:

Group Selection for that Group as GroupCountry = 'USA' and Sum(SalesTotal,CountryFld) >= 1000


Record Selection would be whether or not a group is in report: CountryField = 'USA'

Next, You didn't answer question, Does data exist in table1 for each day until EndDate for record?

In other words,

record 15 StartDate 12/03/2002 EndDate 15/03/2002
13/03/2002 EndDate 15/03/2002
14/03/2002 EndDate 15/03/2002
15/03/2002 EndDate 15/03/2002

If the answer is no, you cannot report on what is not there,
if answer is yes then try below:

Please try this on your report to see results, before deciding it won't work:

In Assitent DataSelection place formula

{table1.id} = 15

Save and close.

Now in Report, chose Insert Menu/Choose Group

Choose Table ID

Click ok and save.

Insert Group

Choose StartDate field,

When choosing date you will be given option to choose group by what, day, month, etc.

Choose Day, click Repeat Group Header

now, place data fields in Detail Section except endDate place it next to StartDate on Group2 line.

Run report. You should see:

15
12/03/2002 15/03/2002

15 whateverx1 whateverx21
15 whateverx1 whateverx21
15 whateverx2 whateverx22
15 whateverx3 whateverx23

13/03/2002 15/03/2002

15 whateverx1 whateverx21
15 whateverx1 whateverx21
15 whateverx2 whateverx22
15 whateverx3 whateverx23

and so on.

Just try it first and see if the results are anywhere near what you are trying to accomplish.

ro





Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Bastel]

Dear Rosemary,
Thanks a lot
the answer is unlukely NO;
So it seems there is no way to build a calculated field and then make o group on it ?
Thanks anyway for your help

Bastel

 

[rosemaryl]

Dear Bastel:

Well not exactly true, but you can't return data in Crystal if doesn't exist in table.

You could try Grouping on the start date and then creating a formula that did something like:

While the date in Start Date to EndDate add 1 to StartDate, convert the date totext and then concatenate each field you want to report on into a string. This should work if the string is not greater than 254 characters.

I'm not sure if this will work and I don't have the time right now to figure it out.

Sorry!

Good luck Bastel



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Ngolem]

Bastel;

I have been reading this thread for a while and trying to get a feel for what you want. I have a couple of comments first.

1. Grouping can be done on a constant value...sometimes you want this to happen to &quot;disable&quot; a group at runtime.

ie.

if {param} = &quot;Y&quot; then
{table.stringfield}
else
&quot;No Grouping&quot;;

2. Grouping can be done on a formula

ie.

{table.String1} + &quot;/&quot; + {table.String2};

3. Grouping CANNOT be done on an array

That seems to be essentially what you are asking for here
in &quot;creating&quot; a range of dates between the Start and End
date in your data base.


Please some me a sample of the recordset that Crystal will return...is it something like

{table.something} {tbl_schulung.beginn} {tbl_schulung.ende}

Also give us some idea as to how you want your final report to look....showing some sample numbers...

If each record has a beginning and end date...are they different in each record. or is one value constant (for example: {tbl_schulung.beginn} ) with varying values of {tbl_schulung.ende}.

Give us more information as to the nature of what you are trying to do overall and we may be able to help....but grouping on an array...or grouping on a constanly changing formula won't work.

Jim

 

[Bastel]

Hi Jim,
i have to tables with following data
tbl_schulung
id date_beginn date_end title etc.
1 03.05.2002 03.07.2002 Office97
2 03.05.2002 03.05.2002 Access97
3 03.12.2002 03.13.2002 Outlook97
tbl_anmeld
id schul_id user_name
1 2 Jon_Doe
2 1 Jon_Doe1
3 2 Jon_Doe2
4 1 Jon_Doe3
5 3 Jon_Doe4

now i would like to have a report for each dataset in tbl_schulung looking like:

ID 1 03.05.2002 Office97
Jon_Doe1
Jon_Doe3

ID 1 03.06.2002 Office97
Jon_Doe1
Jon_Doe3

ID 1 03.07.2002 Office97
Jon_Doe1
Jon_Doe3

ID 2 03.05.2002 Access97
Jon_Doe
Jon_Doe2

ID 3 03.12.2002 Outlook97
Jon_Doe4

ID 3 03.13.2002 Outlook97
Jon_Doe4

So for each day between start and end day one page with title of tbl_schulung
and each subscriber with the cooresponding tbl_schulung.id <-> tbl_anmeld.schul_id

Bastel

 

[synapsevampire]

I think what you seek, is what Crystal doesn't do...

A standard request of the Crystal folk for years has been the ability to fabricate dates for a range.

For example, you need a report of revenue for the past 2 weeks, but only have 10 days worth of data (no revenue generated on some days), the bosses want to see a data point for EVERY date, even if zero (null).

Or you want to generate a calendar for a couple of weeks to show conference room schedules, and it's only been scheduled for a few hours, you just don't have the data.

The solution is to use a period table, with a structure akin to:

DATE
*Year
*Quarter
*Month
*Workweek


Then you can preload this table with values and link to it in your SQL to generate data for every day (link to it with a between or > and <).

Note that I tossed in a Year, Quarter, Month and Workweek to optimize those sorts of requests, it obviously isn't required here.

Your returned data will now have all of the rows and you won't be reliant upon a report to fabricate data (not a brilliant idea anyway, though it could be useful).

Hope that this helps.

-kai

 

[Ngolem]

I think this might be able to be done with a looping structure.

ID 1 03.05.2002 Office97
Jon_Doe1
Jon_Doe3

ID 1 03.06.2002 Office97
Jon_Doe1
Jon_Doe3

ID 1 03.07.2002 Office97
Jon_Doe1
Jon_Doe3


Can this be on one page? (I hope so)

tbl_schulung and tbl_anmeld tables would be joined by the tbl_schulung.ID and tbl_anmeld.schul_id

I would group on {tbl_schulung.ID}
in the detail section I would have a formula that would collect the tbl_anmeld.usernames that apply for that schedule

In the footer I would have a display formula (make sure the &quot;can grow&quot; is enabled

In it I would determine the number of days between

{tbl_schulung.date_beginn} and {tbl_schulung.date_end}
using DateDiff

then I would compose a string using for-next loops

whilePrintingrecords;
numbervar schedDays;
numbervar i;
numberVar j;
stringVar result := &quot;&quot;;
stringVar array students; //colloected in the detail section

schedDays := datediff(&quot;d&quot;,{tbl_schulung.date_beginn},
{tbl_schulung.date_end} );

for i := 0 to schedDays do
( result := &quot;ID &quot; + {tbl_schulung.ID} + &quot; &quot; +
totext(month({tbl_schulung.date_beginn})) + &quot;.&quot; +
totext(day({tbl_schulung.date_beginn}) + i) + &quot;.&quot; +
totext(Year({tbl_schulung.date_beginn})) + &quot; &quot; +
{tbl_schulung.title} + chr(13) + chr(10);
for j := 1 to count(students) do
(
result := result + students[j] + chr(13) + chr(10);
);
);

result;

I may not have this perfect but you get the idea I hope. Your one problem is the size of the string...it cannot be over 254 chars...and if this is a large listing of people over many days...this could be a problem...but since the names are repeated over and over perhaps listing the names once with the days shown is an alternative presentation

Hope this alternative approach gives you some ideas

Jim

 

[Bastel]

Hi Jim,
thanks a lot, i tried that before but the issue is that i need for every day one page.
I solved it that way that i use VB an calculate the days befor i even start my report an ask the user for the day.
Not the best but usable.
Thanks to all for the great help.

Bastel

 

[Ngolem]

Well...I could do it if you only want a single day...you cannot group on an array unfortunately. But I could give you all shedules/participants on separate pages for a single user entered parameter date.

Think that is the best you can get. To get several days you would have to run the report several times. Perhaps that could be VB driven...I am not sure

this solution I proposed falls apart due to the 254 char restriction...but a single day has no problem.

Jim