[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Report Grouping not working

Posted on 2014-08-06
112
Medium Priority
?
64 Views
Last Modified: 2015-02-08
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
0
Comment
Question by:GiaHughes
  • 58
  • 41
  • 11
110 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 40243308
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
 

Author Comment

by:GiaHughes
ID: 40243526
OK Any idea how to fix this
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40243613
What are you trying to accomplish?

How do you want the records grouped?

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:GiaHughes
ID: 40243645
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40243703
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
 

Author Comment

by:GiaHughes
ID: 40243836
Ok will put my thoughts into gear with some sub reports
Will let you know how I get on
Gordon
0
 

Author Comment

by:GiaHughes
ID: 40245969
Hi mimcc

Well not sure how to approach this, don't know how to get a record for each month?
Any suggestions
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40246003
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
 

Author Comment

by:GiaHughes
ID: 40246192
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40246445
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
 

Author Comment

by:GiaHughes
ID: 40246794
mimcc

Spot on, you got it as normal

Gordon
0
 

Author Comment

by:GiaHughes
ID: 40252934
Hi

Any update?
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40253421
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
 

Author Comment

by:GiaHughes
ID: 40256810
Hi mimcc
Thanks for your support
Am trying to get to grips with your concept which works
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40257069
It was a bit tricky to get it working so ask questions if you need to.

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40270607
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40271060
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
 

Author Comment

by:GiaHughes
ID: 40272035
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40274373
Are you going to be using the other tables in the main report?  If not I would get rid of them.

mlmcc
0
 
LVL 101

Expert Comment

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

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40274425
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
 

Author Comment

by:GiaHughes
ID: 40274875
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
 

Author Comment

by:GiaHughes
ID: 40274891
Ok
I can see the FNPERIOD data is DateTime
So what do I need to change?
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40274893
Is Start Date and End Date a DATE or DATETIME parameter?

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


mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40276321
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40276438
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
 

Author Comment

by:GiaHughes
ID: 40276497
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40276642
Try a VERIFY DATABASE to make sure the database has not changed

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40276787
Hi mimcc
Ok done that, no change
is my formula right to change the date-time field to date
Date ({FNPERIOD.STARTDATE})

gordon
0
 

Author Comment

by:GiaHughes
ID: 40276801
Hi mimcc

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

Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40276891
Did you update the subreport selection formula?

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

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40277044
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40277133
You still need to link the subreport

Try this version

mlmcc
Work-Projection-V2.rpt
0
 

Author Comment

by:GiaHughes
ID: 40278427
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
 

Author Comment

by:GiaHughes
ID: 40278431
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
 

Author Comment

by:GiaHughes
ID: 40278434
Mimcc

Or is it best to create a MP2 database in the system with Crystal Reports XI in it?
Gordon
0
 

Author Comment

by:GiaHughes
ID: 40278628
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
 
LVL 35

Expert Comment

by:James0628
ID: 40278720
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40278801
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
 

Author Comment

by:GiaHughes
ID: 40285623
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40285722
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
 

Author Comment

by:GiaHughes
ID: 40286249
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
 
LVL 35

Expert Comment

by:James0628
ID: 40286480
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
 

Author Comment

by:GiaHughes
ID: 40286765
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
 

Author Comment

by:GiaHughes
ID: 40287362
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40287910
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
 

Author Comment

by:GiaHughes
ID: 40287971
Hi mimcc

That has solved the page issue, thanks
Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40288289
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
 

Author Comment

by:GiaHughes
ID: 40288410
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40290276
Try this version

mlmcc
Work-Projection-V3.rpt
0
 

Author Comment

by:GiaHughes
ID: 40290332
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40290377
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
 

Author Comment

by:GiaHughes
ID: 40290609
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40290638
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40290815
I think this has it

mlmcc
Work-Projection-V3.rpt
0
 

Author Comment

by:GiaHughes
ID: 40291320
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40291336
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40291357
Try this report.  I changed the TSKCRAFT to CRAFTS join to left outer.

mlmcc
Work-Projection-V3-1-.rpt
0
 

Author Comment

by:GiaHughes
ID: 40293122
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
 
LVL 35

Expert Comment

by:James0628
ID: 40293375
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
 

Author Comment

by:GiaHughes
ID: 40293956
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
 
LVL 35

Expert Comment

by:James0628
ID: 40294047
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
 

Author Comment

by:GiaHughes
ID: 40294824
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
 
LVL 35

Expert Comment

by:James0628
ID: 40294885
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40295024
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
 

Author Comment

by:GiaHughes
ID: 40295773
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40295946
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
 

Author Comment

by:GiaHughes
ID: 40296009
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40296033
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40296043
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
 

Author Comment

by:GiaHughes
ID: 40296309
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40296743
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
 

Author Comment

by:GiaHughes
ID: 40299745
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40300191
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
 

Author Comment

by:GiaHughes
ID: 40300637
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40300948
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
 

Author Comment

by:GiaHughes
ID: 40300951
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40300957
I don't think there is.

Any idea which loop got the error or which formula?

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40301192
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
 

Author Comment

by:GiaHughes
ID: 40302122
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40302161
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
 

Author Comment

by:GiaHughes
ID: 40302199
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40303495
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
 

Author Comment

by:GiaHughes
ID: 40308729
Hi mimcc

Not sure what to do with your last code?

Gordon
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40308850
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
 

Author Comment

by:GiaHughes
ID: 40309412
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
 

Author Comment

by:GiaHughes
ID: 40309418
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
 

Author Comment

by:GiaHughes
ID: 40316449
Hi mimcc

Any luck with my last post?

Gordon
0
 
LVL 35

Expert Comment

by:James0628
ID: 40316581
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
 

Author Comment

by:GiaHughes
ID: 40316638
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
 
LVL 35

Expert Comment

by:James0628
ID: 40316843
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40316873
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
 
LVL 35

Expert Comment

by:James0628
ID: 40316883
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
 

Author Comment

by:GiaHughes
ID: 40317008
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40317070
What do you want done if the NumofDate is NULL?

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40317300
Hi mimcc

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

Gordon
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 40317499
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
 

Author Comment

by:GiaHughes
ID: 40317550
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
 
LVL 35

Expert Comment

by:James0628
ID: 40317623
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
 

Author Comment

by:GiaHughes
ID: 40317746
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40317837
Can you upload the latest report?

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40318053
Hi mimcc

File attached

Gordon
Work-Projection-V3-2.rpt
0
 

Author Comment

by:GiaHughes
ID: 40318072
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
 

Author Comment

by:GiaHughes
ID: 40318076
Hi mimcc

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

Gordon
0
 

Author Comment

by:GiaHughes
ID: 40318083
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 40318203
Not sure I understand the request.

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40318761
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
 

Author Comment

by:GiaHughes
ID: 40318769
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 40323694
Try this version

mlmcc
Work-Projection-V3-3.rpt
0
 

Author Comment

by:GiaHughes
ID: 40325646
Mimmc

Looking good

Gordon
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question