subreport data not pulling on main report

[ginaburg]

I am using crystal 8.5 with sql tables.
My main report has 2 tables a project table and timesheet table. The subreport has the same project table and expense table.

I am linking the reports by the contract number within the project table.

I have to use a select statement on the main report from the timesheet table that status = 1 to pull only current data and a select statement on the subreport from the expense table that status = 1.

If the porject does not have any current timesheets with status = 1 from the main report, I am not getting the subreport data.

What am I doing wrong?
I hope I explained the good enough.

Thanks
Gina

 

[IdoMillet]

What is the record selection formula in the subreport?

How is the subreport linked to the main report?

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

You're not doing anything wrong, that's how it works.

The Main Report calls the subreport with the contract number, and if the Main Report doesn't have any data for that contract number, then tyhe subreport won't be passed that number.

Perhaps the subreport should be the main report?

Perhaps you should just join the expense table to the project table in the main report and drop the subreport?

Better yet, you might get your dba's to build out Views or SP's to support the data requirements.

-k

 

[obiwanfan]

Let me see if I understand what is happening.

In the Main report,

Select data from the Project and Timesheet table where the status in the Timesheet table is 1.

In the Subreport,

Select data from the Project and expense Table where the status in the expense table is 1.

The two reports are connected with the Project Table.

When no data is selected in the Main report, you are not getting data out of the Sub-report, is the problem

The reason is because subreports are designed to pull data based on information retrieved from the Main report. If the main report doesn't pull data then there is nothing to match to in the subreport so no data is retrieved.

Example: The Main Report selects project code 111 thus all data for project code 111 is pulled from the subreport. If the Main Report didn't select any data the project code is null and nothing will match that.

I think what you want to do is create one report. Connect the Project Table to the Timesheet Table and Expense Table using a left outer on both. This way if there is a Timesheet but no Expense Table or vise versa you will get the data.

Add a select criteria on both tables for a status of 1 to get the most current.

Hope this novel helps.

Bill

 

[ginaburg]

That's what I tried to do originally but how do I get it to lay out like this:

Project Number

timesheet detail
total Timesheets

expense detail
total expesnes

Total Project amount

Gina

 

[obiwanfan]

Ok I see what you are trying to do, I had the same problem the fun part is explaining what I did.

Your original posting had a master report with the project and timesheet tables and a subreport of the project and expense tables. make these two separate reports. one report of the project and timesheet tables and one with the project and expense tables.

Create a new main report of just the project codes. Link this new main report to the two sub-reports above using the project code. you should be able to get the format you outlined above.

Basically what you are doing is using your Project Codes master table like a left outer join but you are able to group the data by table type.

does this make sense?

 

[ginaburg]

I will try that.
Where do I put the subreports? In detail sections or groups?

Gina

 

[obiwanfan]

I think that because you want detial information about each Timesheet record and Expense Record that you should put it in the detail section.

even though you should only have one project code you might want to create a group based on the Project code so that the detail for each is clearly blocked together.

Good Luck!

 

[lbass]

I think Obiwan has it right, except that I think the subreports should go in the group header sections a and b or group footer sections a and b, where the group is on project code in the main report. If you are then going to total costs from each subreport in the main report, you will need to create shared variables of the subtotals within each subreport, so that you can work with them in group footer_c in the main report.

-LB

 

[ginaburg]

Thanks a lot of all you help. I put them in the group header and it worked.
One other question.
I can supress data in each subreport if there is no activity for that project but how do I supress it at the main report level if there is no activity for timesheets or expenses. I'm getting every project on the main.

 

[obiwanfan]

One thing you could do is in the main report add the Timesheet and Expense tables, do left outers on both and use the status field in each of the tables to only select where the status is 1.

That should then only select project codes where the Timesheet or Expense table shows an active status. You don't need to select any data from either table because you are just using them to select the project codes that are active.

 

[ginaburg]

If I use a select statement timesheet.status = 1 or expense.status = 1 (left outer join on tables)
I get the projects that have timesheets and expense data and the projects that have only timehseet data but not projects that have only expense date.
If I reverse the select to read expense.status = 1 or timehseet.status = 1
I get the projects that have timesheets and expense data and the projects that have only expense data but not projects that have only timesheet date.
Am I doing the select statement wrong?

 

[obiwanfan]

Is the left outer setup like

Project to Timesheet
Project to Expense

 

[ginaburg]

yes

 

[obiwanfan]

I played a little bit and re-thought about it and I don't think the left outer will isolate your data.

If you don't mind gaps in the report could you set the report up to suppress a project code if the timesheet data and the expense data both return nulls?

 

[ginaburg]

After looking at this some more, I realize that I have a page break after each project so I am getting blank pages for the projects with no data. Sorry for the confusion.
I tried the suppress statement like you suggested but still getting all projects. Where all would I have to put the suppress if null to get rid of the blank pages. Maybe I'm not understanding where to put the suppress statement

 

[lbass]

Gina,

Another method would be to use the subreports in GH#1_c and GH#1_d. Save each of them using "Save subreport as" (and identify them differently, e.g., T01 and E01) and then import them into the report again, linking them as before, and placing them side by side or on top of each other in GH#1_a. Resize them to make them as narrow as possible, and remove the borders. Your regular group fields should appear in GH#_1b. Format GH#1_a to "Underlay following sections". Then create a shared variable in each subreport T01 and E01:

//{@Tshared}:
whileprintingrecords;
shared numbervar T01 := sum({timesheet.field});

//{@Eshared}:whileprintingrecords;
shared numbervar E01:= sum({expense.field});

These must be placed on the T01 (or E01) report canvas. Suppress all sections in T01 and E01. Then go to the main report section expert->GH#1_b->suppress->x+2 and enter:

{@Tshared} = 0 and
{@Eshared} = 0

Format the other two sections to be suppressed when the value of the subreport in it = 0.

There will still be a narrow space for each group where the subreports appear in GH#1a though.

-LB