Report Grouping not working

I have created a calendar formula which includes
if ({@NextDate00}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate01}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate02}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate03}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate04}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate05}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate06}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate07}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate08}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate09}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate10}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate11}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate12}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate13}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate14}) in Date (2014,08,01 ) to  Date (2014,08,31 )
  or ({@NextDate15}) in Date (2014,08,01 ) to  Date (2014,08,31 )
    then "August 2014"

and
if ({@NextDate00}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate01}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate02}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate03}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate04}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate05}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate06}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate07}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate08}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate09}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate10}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate11}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate12}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate13}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate14}) in Date (2015,08,01 ) to  Date (2015,08,31 )
  or ({@NextDate15}) in Date (2015,08,01 ) to  Date (2015,08,31 )
    then "August 2015"

On the report I have created a group based on the @Calendar

But when I preview the report it shows (example)
5/8/2014 as August 2014 (correct)
but the 5/8/2015 as August 2014 (not correct)

Any suggestions
Gordon
Gordon HughesDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
You're checking a lot of different formulas (@NextDate00, @NextDate01, etc.).  If any of them are in August of 2014, you'll get "August 2014", because that's what you look for first.  So, the obvious guess would be that while one or more of the dates may be in 2015, at least one is in 2014.

 James
0
Gordon HughesDirectorAuthor Commented:
OK Any idea how to fix this
Gordon
0
mlmccCommented:
What are you trying to accomplish?

How do you want the records grouped?

mlmcc
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Gordon HughesDirectorAuthor Commented:
Hi mimcc
Long time no communicate
I want to group the records by the Calendar, which I have created
The tasks I am trying to show repeat themselves, so there may be one or more due at different times
ie one in August 2014, one in August 2015 etc
So in the grouping August 2014 only want to see any due within that month
I have had to create several formulae to show the repeat dates , ie NextDate00, NextDate 01, NextDate02 (up to 15)
The formula for NextDate00 is CDate ({TASKEQ.NEXTDUEDATE})
The formula for NextDate01 is ({@NextDate00}) +({TASKEQ.NUMOFDATE}*{@DateUnitQty})
The formula for NextDate02 is {@NextDate01} + ({@DateUnitQty}*{TASKEQ.NUMOFDATE} )
etc
Maybe there is another way to do this
Gordon
0
mlmccCommented:
Are you trying to show a single record in several groups?

TO do that you need to "replicate" the records so there is one record for each required date or use a subreport for each month.  The subreport would be essentially your current report with some minor modifications.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Ok will put my thoughts into gear with some sub reports
Will let you know how I get on
Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Well not sure how to approach this, don't know how to get a record for each month?
Any suggestions
Gordon
0
mlmccCommented:
WHat are you trying to show in the report?

Have you considered using a cross tab?

TASKEQ.NEXTDUEDATE - Next date the task is due.

What is TASKEQ.NUMOFDATE?

What is the formula for DateUnitQty?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Am trying to show which tasks are due within a month and what resource is required

The system only show the first TASK.NEXTDUEDATE so have to calculate the following dates
The following dates are based on the taskduedate plus the number of days in the system (ie TASKEQ.NUMOFDATE
so I have craeted the formula
DateUnitQty
if {TASKEQ.DATEUNIT} = 'D' then 1 else if {TASKEQ.DATEUNIT} = 'W' then 7 else if {TASKEQ.DATEUNIT} = 'M' then 30 else if {TASKEQ.DATEUNIT} = 'Y' then 365
The TASKEQ.NUMOFDATE is a numeric value within the database

I have then created seveval formulae to calculate up to 15 additional dates
ie
NextDate00 = CDate ({TASKEQ.NEXTDUEDATE})
NextDate01 = ({@NextDate00}) +({TASKEQ.NUMOFDATE}*{@DateUnitQty})
up to :-
NextDate15 = {@NextDate14} + ({@DateUnitQty}*{TASKEQ.NUMOFDATE} )

Gordon
0
mlmccCommented:
So your record is something like

Task - Wash Windows
Due Next - 15 Aug 2014
DateUnit - W - weekly
NumofDate - 2 - every 2 weeks
Resource - Window washer

NextDate00 = 15 Aug 2014
NextDate01 = 29 Aug 2014
NextDate02 = 12 Sep 2014
NextDate03 = 26 Sep 2014
etc

You want to see in the report
Aug 2014
Wash Windows   15 Aug 2014  Window Washer
Wash Windows   29 Aug 2014  Window Washer


Sep 2014
Wash Windows   12 Sep 2014  Window Washer
Wash Windows   26 Sep 2014  Window Washer

Etc

mlmcc
0
Gordon HughesDirectorAuthor Commented:
mimcc

Spot on, you got it as normal

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi

Any update?
Gordon
0
mlmccCommented:
Here you go.

I created a main report that is basically a shell.  It uses a table of dates that are the month start and end dates.  
The subreport shows the data and is limited by the dates passed form the main report

mlmcc
Schedule-Main.rpt
Schedule-Sub.rpt
DateList.xls
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Thanks for your support
Am trying to get to grips with your concept which works
Gordon
0
mlmccCommented:
It was a bit tricky to get it working so ask questions if you need to.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
I really do appreciate your support on this
To start I have created the sub report
When I run it with a 3 months date range, it comes up with the following in the @task
a loop was evaluated more than the maximum number of times allowed
Also if I change the the calendar in the paramenter selection (manually) to say Start of Range September 2014 with the end of range to say November 2014 (again manual input) it says the minimum cannot be larger than the maximum

Am I on the right track?
Gordon
0
mlmccCommented:
For the date range you have to use dates.  If you are using strings then September 2014 does come after November 2014.

Can you upload your report?
I don't need the data just need to see the formulas

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
File attached
Doing my best, but not there yet! complicated report I think
I note that yo use a sheet that contains dates, so I have included a table that has dates FNPERIOD but with no links (which it does not like) also there is a mismatch with Date and DateTime type data

I am trying to get to a projection that shows the craft resource and time required for each task and the equipment linked to the task

Gordon
Work-Projection-V2.rpt
0
mlmccCommented:
Are you going to be using the other tables in the main report?  If not I would get rid of them.

mlmcc
0
mlmccCommented:
What dates are in the FNPERIOD table?
It should be just the 1st and last day of each month

mlmcc
0
mlmccCommented:
I finally got it to get your error.  It happens when there are no tasks for the subreport

Change the subreport selection formula to be

{TASKEQ.NEXTDUEDATE}  >= {?StartDate} 
and      
 {TASKEQ.NEXTDUEDATE} < {?MonthEnd} + 1

Open in new window


Also change the TASKS formula in the subreport to check for a NULL Due date

WhilePrintingRecords;
Local StringVar DateUnit;
Local DateVar NextDue := Date({TASKEQ.NEXTDUEDATE});
Global StringVar TaskList := "";
Global StringVar DateList := "";
Global StringVar ResourceList := "";
If Not (IsNull({TASKEQ.NEXTDUEDATE})) then
    (
    If {TASKEQ.DATEUNIT} = 'D' then
        DateUnit := 'd'
    Else If {TASKEQ.DATEUNIT} = 'W' then
        DateUnit := 'ww'
    Else If {TASKEQ.DATEUNIT} = 'M' then
        DateUnit := 'm'
    Else If {TASKEQ.DATEUNIT} = 'Y' then
        DateUnit := 'yyyy'
    Else
        DateUnit := 'd';

    While NextDue < {?MonthStart} do
        NextDue := Date(DateAdd(DateUnit,{TASKEQ.NUMOFDATE}, NextDue));

    While NextDue <= {?MonthEnd} do
    (
        TaskList := TaskList & {TASK.TASKNUM} & chr(13);
        DateList := DateList & CStr(NextDue,"dd-MMM-yy") & chr(13);
        ResourceList := ResourceList & {TSKCRAFT.CRAFT} & chr(13);
        NextDue := Date(DateAdd(DateUnit,{TASKEQ.NUMOFDATE}, NextDue));
    );
);
'';

Open in new window


mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
OK done as you relayed
The FNPERIOD table has a start and an end date for each period
The data format is Date
I tried to use the select as
{FNPERIOD.STARTDATE} >= Date({?Start Date}) and
{FNPERIOD.STARTDATE} <= Date({?End Date})
But when I try to run the report it goes back to this and says a date-time is required

Any ideas
Gordon
0
Gordon HughesDirectorAuthor Commented:
Ok
I can see the FNPERIOD data is DateTime
So what do I need to change?
Gordon
0
mlmccCommented:
Is Start Date and End Date a DATE or DATETIME parameter?

Use
{FNPERIOD.STARTDATE} >= {?Start Date} and
 {FNPERIOD.STARTDATE} <= {?End Date}


mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

What a challenge
I changed the {?start Date} and {?End Date} to Date-Time
It got ride of the mis-match
Now I get an error
OBDC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The column prefix 'FNPERIOD' does not match with a table name or alias used in the query

Gordon
0
mlmccCommented:
Make sure you select fields from the list and not just type them in or in this case copy and paste from my comment.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Still get the ODBC error
Remind me how to convert a date-time field to date
Date ({FNPERIOD.STARTDATE})

So then I tried in the selection
{@FNPeriod Date}>= {?Start Date} and
{@FNPeriod Date} <= {?End Date}

But it now still asks for a Date-time
Gordon
0
mlmccCommented:
Try a VERIFY DATABASE to make sure the database has not changed

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Ok done that, no change
is my formula right to change the date-time field to date
Date ({FNPERIOD.STARTDATE})

gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Well have changed all the dates to date-time
It runs but no data from the sub report!!

Gordon
0
mlmccCommented:
Did you update the subreport selection formula?

If you link it and choose to select based on the links they become = tests.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

The sub report selection is
{TASKEQ.NEXTDUEDATE}  >= {?StartDate} and
{TASKEQ.NEXTDUEDATE} < {?MonthEnd} + 1
Have attached latest file

Gordon
Work-Projection-V2.rpt
0
mlmccCommented:
You still need to link the subreport

Try this version

mlmcc
Work-Projection-V2.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

You must be feed up wih this report by now, I know I am!!
Ok
I have various operating systems, the one with the MP2 database has Crystal reports 8.5
Another system with MP2Live has Crystal Reports XI
I have to use MP2Live to open your report
Have changed the database location to MP2Live and can get the sub report to bring back data, but the main report is blank

Have attached the report

Gordon
Work-Projection-V2.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
is it possible to get the report to open in Crystal Reports 8.5 then I can get it to connect to the correct MP2 database
Gordon
0
Gordon HughesDirectorAuthor Commented:
Mimcc

Or is it best to create a MP2 database in the system with Crystal Reports XI in it?
Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

OK have installed MP2 on the system with the CP XI and tried it as you sent me
I can run the sub report and get data but the main report does not show any data from the sub report

Gordon
0
James0628Commented:
You said that the subreport shows data, but the main report doesn't.  But normally the subreport data would show on the main report.  Are you running the subreport as a stand-alone report, and that's when it shows data?

 If so, that would imply a problem with the subreport links, so the subreport parameters don't get the correct values (which prevents it from finding data).

 I noticed that you don't have anything linked to the ?MonthStart parameter in the subreport, which means that you will get prompted to enter something for that parameter every time the subreport is run, which would be for every detail record in the main report.  You presumably want to link something in the main report to that subreport parameter.  Either add a ?MonthStart parameter to the main report, or link something else to the subreport parameter.

 I don't know if this is significant, but I noticed that, in the subreport, you have WhilePrintingRecords in the @Tasks formula, but not in the @Display* formulas that are supposed to be evaluated after @Tasks and show the values in the variables.

 Worst case, the @Tasks and @Display* formulas might be evaluated during different passes through the subreport, and the variables might not be set properly when the @Display* formulas are evaluated.  But, even if that's not happening, I would add WhilePrintingRecords to the @Display* formulas, or remove it from @Tasks, for consistency.

 James
0
mlmccCommented:
You can't have 2 evaluation time functions.

By using the EvaluateAfter, Crystal knows it is a print time formula since the one it is after has WhilePrintingRecords.

You should link FNPERIOD.STARTDATE to MonthStart

The main report you sent has no data other than the group headers.  If you are using a copy that has other fields then I don't know why it wouldn't get data.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Still not getting any data on the main report, if i run the sub report, get data!!!!
Attached report as is
Gordon
Work-Projection-V2.rpt
0
mlmccCommented:
WHy do you include all the tables in the main report.  It only needs the FNPERIOD table.

What data is in the FNPERIOD table?

What are you entering for the start and end date parameters?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Ok when we have sorted the report I want to have the report
Euipment
Equipment Descrition
Location
Task Number
Task Description
Task Craft
Hours
Due Date

To capture all these fields I need to include the following tables
EQUIP
TASK
TASKEQ
TSCRAFT

I included the FNPERIOD because it is the only table with start and end period dates in the system

Now, should these tables only be i the sub report and the FNPERIOD in the main report?
The FNPERIOD data columns are:
PERIODID
STARTDATE
ENDDATE
GRACEDAYS
DESCRIPTION
UPDATESTAMP
UPDATEUSER
CONVNUM

The date fields are Date-Time

The start and end dates I am currently using are
01/08/2014 00:00:00
31/08/2014 00:00:00
Set as Date Time

What do you think I should try?
Gordon
0
James0628Commented:
Are you going to add the fields that you listed, like Equipment and "Equipment Description", to the main report, or are they only going to be in the subreport?  If you're not actually going to use anything in those other tables in the main report, then you don't need to include the tables.  You are using some fields in formulas, but the formulas don't seem to actually be used anywhere.  If you aren't really using them, you could delete those formulas, and then the tables.

 James
0
Gordon HughesDirectorAuthor Commented:
Ok
Have removed all the tables from the main report except the FNPERIOD
Still no data when I run the main report!!

Gordon

Attached uptodate rpt file
Work-Projection-V3.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Have found reason no data in main report
The FNPERIOD table did not contain the dates we selected
So sorted that
Now:
The the group name appears on a page then there is a no data until the next page, have looked at the Section Expert but cannot see the issue
Also the data shows information without any task number or dates, example
CALIBRATION BY EUROTHERM CONTROLS LTD (task)
QS/E/CAL/52W/03 (task Number)
QS/E/CAL/52W/03 (equipment)

The schedule says duse 5/9/214 with next schedule due 52 weeks later

The report shows it for 5/9/2014 (correct)
Then shows it in October with no task number or date
Have attached latest report with data (i hope)
Gordon
Work-Projection-V3.rpt
0
mlmccCommented:
Try this to solve the blank page

Right click the subreport
Click FORMAT SUBREPORT
On the first tab Uncheck KEEP OBJECT TOGETHER

Looking into the other issue.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

That has solved the page issue, thanks
Gordon
0
mlmccCommented:
The problem with the "extra" tasks is that you are putting the fields on the report rather than using the formulas to display the information.  It appears you have additional columns that need to be accounted for in the TASKS formula.

You need to add variables and code to gather the information.  You also then need formulas to display the data.

If you wish, I can build the formulas for you

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Yes please it is a bit beyond my capability
I think you will need lots of points after this 5000 not 500

Gordon
0
mlmccCommented:
Try this version

mlmcc
Work-Projection-V3.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I think it is looking good, need to check it against the database
Am keen to get the hours total, by craft and by month
How do I get these?

Gordon
0
mlmccCommented:
I don't think you can get them in the group header without another subreport to calculate it.

You could add the calculation to the TASKS formula and display the value in the group footers

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Can you update the @Task formula to show the group footer totals?
Am I able to add a parameter to be able to select a SITEID, is this as standard or does this mean a change to the @Task?
Also I am missing lots of data, trying to go through it now
I know one thing is that not all the tasks would have a craft linked to it, is this a case of going to the database expert and changing the link between the TASK and TSKCRAFT tables from = to > or something else

Gordon
0
mlmccCommented:
You would change the join to LEFT OUTER

Adding a parameter for selecting the SiteId shouldn't affect the report other than the data selected.

mlmcc
0
mlmccCommented:
I think this has it

mlmcc
Work-Projection-V3.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
I bet you will be glad when this report is OK!!

I have changed the link in the subreport between the TASK and TSKCRAFT tables to be LEFT OUTER
but still not getting all the data, should get some saying No Craft as per the @Craft
There are tasks without any crafts
Also can we surpress the group header and footer in the sub report if there is no data?

Gordon
Work-Projection-V3-1-.rpt
0
mlmccCommented:
I was afraid you would ask for that.  The footer is easy but the header is difficult since you don't know if there is data until you run through the tasks.

I can't think of a mechanism to do it since the information is already in a subreport

mlmcc
0
mlmccCommented:
Try this report.  I changed the TSKCRAFT to CRAFTS join to left outer.

mlmcc
Work-Projection-V3-1-.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

It is begining to take shape
For some reason if there is no craft, it does not show any equipment details (this is the group that says No Craft
Have attached latest report
Any ideas
Gordon
Work-Projection-V3-2.rpt
0
James0628Commented:
I'm guessing that the "No Craft" records will not have any matching records in TASKEQ?

 If that's true, I see a couple of possible problems.

 You have a Left Outer Join to TASKEQ, but you're checking TASKEQ.NEXTDUEDATE in the record selection formula.  That test could be causing CR to change the Join to Inner, which might cause problems.  You could check that by going to Database > Show SQL Query, while in the subreport tab (to check the subreport query), and checking the query that CR will send to the server.

 Even if the Join is OK, if NEXTDUEDATE could be null (as implied by your IsNull test in the @Tasks formula), then you need to check for that in the record selection formula too.

(
IsNull ({TASKEQ.NEXTDUEDATE}) or
 (
  {TASKEQ.NEXTDUEDATE}  >= {?StartDate} and
  {TASKEQ.NEXTDUEDATE} < {?MonthEnd} + 1
  )
) and
{TASK.SITEID} = {?Site}


 Also, if NEXTDUEDATE could really be null, you need to check for that in the @Tasks formula, before you use the field for anything else.  Specifically, when you set NextDue.  The simplest thing would be to just move the line that sets NextDue into the If statement right below it, so that that line is only evaluated when NEXTDUEDATE is not null.

 James
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc and James
The key table is TASK
the TASK should always have a linkage to the TASKEQ which contains the nextduedate
The TASK may contain a CRAFT (the CRAFT is not linked to the TASKEQ

In terms of the @task formula, I am affriad it is beyond me as mimcc is the expert and creator

The SQL Statements are currently?
Main Report
 SELECT "FNPERIOD"."STARTDATE", "FNPERIOD"."ENDDATE"
 FROM   "MP2"."dbo"."FNPERIOD" "FNPERIOD"

SubReport (using the parameters)
 SELECT "TASKEQ"."NEXTDUEDATE", "TASKEQ"."NUMOFDATE", "TASKEQ"."DATEUNIT", "TASK"."TASKNUM", "TSKCRAFT"."CRAFT", "TASKEQ"."EQNUM", "TSKCRAFT"."ESTLABORHOURS", "TASK"."DESCRIPTION", "EQUIP"."DESCRIPTION", "TASKEQ"."LOCATION", "TASKEQ"."SUBLOCATION1", "TASKEQ"."SUBLOCATION2", "TASKEQ"."SUBLOCATION3", "CRAFTS"."DESCRIPTION", "TSKCRAFT"."NUMOFPEOPLE", "TASK"."SITEID"
 FROM   ((("MP2"."dbo"."TASK" "TASK" LEFT OUTER JOIN "MP2"."dbo"."TASKEQ" "TASKEQ" ON ("TASK"."TASKNUM"="TASKEQ"."TASKNUM") AND ("TASK"."SITEID"="TASKEQ"."SITEID")) LEFT OUTER JOIN "MP2"."dbo"."TSKCRAFT" "TSKCRAFT" ON ("TASK"."TASKNUM"="TSKCRAFT"."TASKNUM") AND ("TASK"."SITEID"="TSKCRAFT"."SITEID")) INNER JOIN "MP2"."dbo"."EQUIP" "EQUIP" ON "TASKEQ"."EQNUM"="EQUIP"."EQNUM") LEFT OUTER JOIN "MP2"."dbo"."CRAFTS" "CRAFTS" ON "TSKCRAFT"."CRAFT"="CRAFTS"."CRAFT"
 WHERE  ("TASKEQ"."NEXTDUEDATE">={ts '2014-08-01 00:00:00'} AND "TASKEQ"."NEXTDUEDATE"<{ts '2014-10-01 00:00:00'}) AND "TASK"."SITEID"='Southall'


I feel we are so close to sorting this challenging report!!
Gordon
0
James0628Commented:
OK, I think it may be an issue with a different null field(s).

 Does your version of CR include an option in the formula editor for how nulls should be handled?

 If so, open the @Tasks formula in the editor and set the option to use default values.

 If not, then with the subreport tab selected, go to File > Report Options and check the "Convert Database NULL Values to Default" option.  There are separate report options for the main report and the subreport, so you need to do that with the subreport tab selected, to change the setting in the subreport.  If there are any formulas in the subreport that actually need to see null fields, that setting could cause problems for those formulas.  But you can give it a try and see what happens.


 What I think is happening is that the @Tasks formula starts setting the variables and sets the first 3, but then it gets to ResourceList, and TSKCRAFT.CRAFT is null on the "No Craft" record, so CR stops evaluating the formula there, and doesn't set the other variables.  You could use IsNull tests to check TSKCRAFT.CRAFT, and any other fields that could be null, but it would be much simpler if you could use the options described above to replace any nulls with "default" values (depending on the type of field).


 FWIW, here's a quote from the CR 10 Help about how CR handles (or doesn't handle) nulls in formulas:

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.
0
Gordon HughesDirectorAuthor Commented:
Hi James

I used the check the "Convert Database NULL Values to Default" option.
And I can now see the equipment
But have lost the group heading saying No Craft, is is blank
Don't know what if anything I should do with the@Tasks

Is this rectifiable?

Gordon
0
James0628Commented:
Oh, yeah.  Duh.  I didn't think of that.

 Change the @Craft formula to:

if {TSKCRAFT.CRAFT} = '' then 'No Craft' else {TSKCRAFT.CRAFT}


 That should work.  The only problem would be if TSKCRAFT.CRAFT could actually be blank sometimes (not null), and you need to display something else when it's just blank.

 James
0
mlmccCommented:
Without looking at the Tasks formula, I don't recall anything tat would need to change.  I don't have Crystal installed on this machine

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi
Not sure if the report is picking up the data correctly
Have attached latest report
Look at Mech group
This task should be done every week, it shows the correct number of records, but
Does not show the dates
Does not repeat the Equipmen
The Equipment Descrion is out of alignment
Although the total seems to be correct

Am trying to get my head around the scheduled dates
If for some reason the first scheduled date is not in the selection criteria it does not show
for example
Shedule due every 2 weeks
Next due date is shown as 10/8/2014
The selection criterial starts with 1/9/2014
Data is not shown

Does this make sense?

Gordon
Work-Projection-V3-2.rpt
0
mlmccCommented:
All the fields need to be set to GROW.  This creates another issue but before I solve it I need some information

Look at this version I set the fields to gow.

What are the database sizes of the firlds being used?

mlmcc
Work-Projection-V3-2.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
OK, have had a look at your latest
Can see it shows the Equipment etc
Why is the records not aligned? See MECH

Is there an easy way to get all the attributes of the tables being used from SQL Server?

Gordon
Work-Projection-V3-3.rpt
0
mlmccCommented:
They aren't properly aligned because they are of different lengths and some are using multile lines.  I can account for them in the formulas.

The lengths are in the report.

mlmcc
0
mlmccCommented:
I used the one I had not the latest.  You can probably just copy the tasks formula and replace it in your latest.

mlmcc
Work-Projection-V3-2.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Thanks for all your efforts

OK can see the lines are aligned now
Now we can see the @tasks at the end and it is crating some gaps (see pages 4 and 5)

Are you able to look at
If for some reason the first scheduled date is not in the selection criteria it does not show
for example
Shedule due every 2 weeks
Next due date is shown as 10/8/2014
The selection criterial starts with 1/9/2014
Data is not shown

Do you need anything from me to help?

Gordon
0
mlmccCommented:
What do you mean the " first scheduled date is not in the selection criteria "

When you out in a start and end date you are wanting to see all the tasks that will occur in the time period.  If a task's next scheduled date isn't in the time period it won't be read so it can't be shown.

Are you wanting to see all tasks that don't have a next scheduled date in the time period?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

When I look at the data there seems to be lots of tasks with the next due date prior to the selection criteria
Say we select dates 1st Sept to 30th Nov
Say there is a task with the next due date of 1st Aug which is done every week

Because this next due date is not within the selection criteria, the report will not calcultae the following dates, which in reality would be done ech week

Hope this makes sense

Am trying to understand why there would be back dates, not got to the botton of this

Gordon

In terms of the report showing figures for the @tasks and creating extra lines, I have suppressed the @tasks and I think it seems to be ok
What do you think?
0
mlmccCommented:
I had modified the formula to show the value fr troubleshooting and forgot to set it back to show nothing.

Unsuppress the formula
Edit the formula and uncomment the last line which shoud be ''

If you change the selection formula to select any task due date <= EndDate it should ick up the missing data.
You need to find out why there would be tasks that aren't getting updated.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Ok have changed the @tasks formula and that is not showing the data

The selection forula in the main report is already
{@FNPeriod Date}>= {?Start Date} and
{@FNPeriod Date} <= {?End Date}

Gordon
0
mlmccCommented:
It would be the subreport since that is where the data is selected.
IN the main report you should be just getting the dates for the groups.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Thought I would try and run the report for a much longer date range
Then got the following error messge in the@tasks formula
"A loop was evaluated more then the maximum number of times allowed"

Any solution?
Gordon
0
mlmccCommented:
I don't think there is.

Any idea which loop got the error or which formula?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I think it is going back to the data
If I select just Jan/Feb this year it gives me the error with the about the string cannot exceed 65534 when I try to go to the last page
If I select March onwaards it seem to be OK

Will now look at the data in the system
Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

For some reason the data does not always align, see attached report page 14 for example

Am still concerned about showing all the data for a month
Example say the next due date is shown as 1 sept and the task is carried out every week but you only want to see what is due in december
So you select 1st to 31st December
The report does not show the data
Whereas it should show the task due in December

Does this make snse?
Gordon
Work-Projection-V3-2.rpt
0
mlmccCommented:
The alignment is going to be an ongoing issue.  Would it be ok to split words or do you want the line breaks to occur at blanks and perhaps other appropriate characters like / or -

SO you would like to run the report today to show what tasks would be due in December say for the purpose of planning holiday downtime.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc
Ideally to have the break after all the data for that record is shown, but don't know what charater could be used

You got it about the December, the idea is to know what is due in the period (all of it)and the resources required (craft)

Gordon
0
mlmccCommented:
The alignment issue is caused by the fields that can take more than 1 line.  Crystal will only wrap at a space not in the middle of words. In some cases even though the field can handle say 30 characters on a line your text may leave a fair space on the right.  In the sample data you included with the report you had something like FILTERS/STOCKINGS, if that is at the end of a line it may get wrapped to the second line and you lose 17 characters on that line thus my calculation of lines required is wrong.

If you change the width of a field you also have to adjust the calculation.
Task Description is now 2.4" wide - formula was using 28 characters per line.  Changed to 34 and the alignment issued disappears.  

Code to determine number of lines required. The divisors (34,25, 22) are the estimated characters per lines based on 15 characters per inch (8 point font) (120 points per inch)

   LinesRequired := Truncate(Length(Trim({TASK.DESCRIPTION})) / 34 + 1);
   If Truncate(1 + Length(Trim({EQUIP.DESCRIPTION})) / 25) > LinesRequired then
      LinesRequired := Truncate(1 + Length(Trim({EQUIP.DESCRIPTION}))  / 25);
   If Truncate(1 + Length(Trim({TASKEQ.LOCATION})) / 22) > LinesRequired  then
      LinesRequired := Truncate(1 + Length(Trim({TASKEQ.LOCATION})) / 22);
   For Index := 1 to LinesRequired do
      strCR := strCR & chr(13);

Open in new window


I can change the formula to account for long words and split them at the appropriate spot and then count the lines if you wish.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Not sure what to do with your last code?

Gordon
0
mlmccCommented:
Those are the lines in the TASKS formula that determine how many blank lines are needed.
You should be able to replace them in tasks or just change the divisors in your TASKS formula to match.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Does this help?

The key fields on the report
Task.TaskNum (varchar 20, not null)
Task.Description (Varchar 72, null)
Equip.Eqnum (Varchar 30, not Null
Equip.Description (Varchar 50, Null)
Crafts.Craft (Varchar 10, not Null)

The Location should be made up of:
{TASKEQ.LOCATION} & " " & {TASKEQ.SUBLOCATION1} & " " & {TASKEQ.SUBLOCATION2} & " " & {TASKEQ.SUBLOCATION3}
Taskeq.Location (Varchar 30, Not Null)
Taskeq.Sublocation1 (Varchar 30, Not Null)
Taskeq.Sublocation2 (Varchar 30, Not Null)
Taskeq.Sublocation3 (Varchar 30, Not Null)

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Do you think there is a solution to:

Am still concerned about showing all the data for a month
Example say the next due date is shown as 1 sept and the task is carried out every week but you only want to see what is due in december
So you select 1st to 31st December
The report does not show the data
Whereas it should show the task due in December

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Any luck with my last post?

Gordon
0
James0628Commented:
As far as the dates go, I am guessing that if the next due date is after the ending date that you entered, you don't want to see that task.  Using your example, if you ask for 12/01 to 12/31, and the next due date is 01/05 of the next year, then you don't want to see that task.  Correct?

 If so, the first thing may be to remove {TASKEQ.NEXTDUEDATE} >= {?StartDate} from the record selection formula in the subreport.  Then the subreport should include any task with a next due date that's before your ending date.

 I don't know if you'll need to make further changes.  It looks like the Tasks formula is already skipping any calculated due dates that are before your starting date, so you may only need to change the record selection formula.

 FWIW, I think that may have been what mlmcc meant in post 40300191, when he said

If you change the selection formula to select any task due date <= EndDate it should [p]ick up the missing data.

 He may have meant that you should compare the next due date with the end date, but not the start date.

 James
0
Gordon HughesDirectorAuthor Commented:
Hi James

Thanks for your post
Your first statement is correct

The issue is that if the next due date is before the selected start date, the task does not calculate what may be due within the period selected
Say the nextt due date is 1st September done every 4 weeks, if the report selection is 1st December to 31st December , the task will not be shown, but it would be due within December


I removed the {TASKEQ.NEXTDUEDATE} >= {?StartDate} from the sub report
and got the following error messge in the@tasks formula
"A loop was evaluated more then the maximum number of times allowed"
This was highlighted as the issue area within the formula:-
While NextDue < {?MonthStart} do
     NextDue := Date(DateAdd(DateUnit,{TASKEQ.NUMOFDATE}, NextDue));

The sub report cureently does not contain ?End Date (should it?
The main repport contains the selection
{@FNPeriod Date}>= {?Start Date} and
{@FNPeriod Date} <= {?End Date}

The FNPERIOD.ENDDATE is linked to the ?MonthEnd in the sub report

What do I do?
Gordon
0
James0628Commented:
I don't think the subreport needs the {?End Date} parameter.  The month end seems to be the important thing.

 As for the error, I'm not sure.  Is there any chance that TASKEQ.NUMOFDATE could ever be 0?  That would give you an endless loop.  It could be 0 in an old task record, which was being excluded until you removed the {?StartDate} test from the record selection formula.

 If you think TASKEQ.NUMOFDATE might be 0, you could add the line below to the record selection formula in the subreport, to exclude those records (it seems reasonable to exclude them, since you can't calculate "next due" dates without knowing how many units to add).

and {TASKEQ.NUMOFDATE} <> 0


 James
0
mlmccCommented:
Good thought.  I think the loop may be because a really old one is selected with a 1 for Numofdate and daily as the frequency.  

Is the issue happening frequently or just when you enter some parameter values?

mlmcc
0
James0628Commented:
I think the loop may be because a really old one is selected with a 1 for Numofdate and daily as the frequency.

 I thought of that, but I did a quick test in CR 10 and it executes a For loop 100,000 times before it gets that error.  At 1 day per loop, that's over 273 years.  I'm guessing his data doesn't go back that far.  :-)
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc and James

There could be tasks with the Numofdate not filled in and any number from 1
The error occurs when I try to view all the pages

Gordon
0
mlmccCommented:
What do you want done if the NumofDate is NULL?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Exclude it as it means that it does not have a schedule

Gordon
0
James0628Commented:
Add the following to the end of the record selection formula in the subreport:

and not IsNull ({TASKEQ.NUMOFDATE})
and {TASKEQ.NUMOFDATE} > 0

 James
0
Gordon HughesDirectorAuthor Commented:
Hi James

OK that seems to have fixed the loop issue

Still need to try and fix:-
The issue is that if the next due date is before the selected start date, the task does not calculate what may be due within the period selected
Say the nextt due date is 1st September done every 4 weeks, if the report selection is 1st December to 31st December , the task will not be shown, but it would be due within December

Gordon
0
James0628Commented:
Did you remove {TASKEQ.NEXTDUEDATE} >= {?StartDate} from the record selection formula in the subreport, like I suggested earlier?

 What does the subreport record selection formula look like now?

 James
0
Gordon HughesDirectorAuthor Commented:
H James

I did
it now reads
{TASK.SITEID} = {?Site} and
{TASKEQ.NEXTDUEDATE} < {?MonthEnd} + 1 and
not IsNull ({TASKEQ.NUMOFDATE}) and
{TASKEQ.NUMOFDATE} > 0 and
{TASK.INSERVTASK} <> "N"

Gordon
0
mlmccCommented:
Can you upload the latest report?

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

File attached

Gordon
Work-Projection-V3-2.rpt
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Sorry, I think it may be working
I created a task with a weekly schedule with the next date during September
I then ran the report for December only and it appears for each week
So we are probably there!!!!

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I think it is just the record alignment to sort if possible

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

A thought could the report be run with a critera to suppress the details as an option using the parameters
Ie so that there is an option just to see the workload for each craft within the month?

Sorry if I am a pain!!

Gordon
0
mlmccCommented:
Not sure I understand the request.

mlmcc
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

I would like, if possible, to have the option of selecting whether to show the details or not
I woud normally
Create a parameter ?Show Details with a Yes or No
Then use the selection expert to surpress the section if No was selected

Not sure if this would supptess all the data
And if that was Ok do I use the Supreport links to action this i the sub report

Gordon
0
Gordon HughesDirectorAuthor Commented:
Hi mimcc

Got the show details working ok as per my last post

I think it is just the alignment left to do

Gordon
0
mlmccCommented:
Try this version

mlmcc
Work-Projection-V3-3.rpt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gordon HughesDirectorAuthor Commented:
Mimmc

Looking good

Gordon
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.