I've got a subreport which works fine by itself. I display the parameters in the report header and then show the detail. My MainReport passes parameters to the subreport, and based upon the parameters showing up correctly in the header information I believe that they are being passed without problem. The problem is that in the MainReport, the SubReport detail data fields do not show up (detail title fields do show up). Any suggestions on what to look for to fix my problem?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Subreport not showing detail 1
- Thread starter skuhlman
- Start date
synapsevampire
Programmer
Check the record selection and the SQL in the subreport.
It may be that it isn't passing what you think it is, or it's comparing differently than you intend, thus resulting in no records.
-k kai@informeddatadecisions.com
It may be that it isn't passing what you think it is, or it's comparing differently than you intend, thus resulting in no records.
-k kai@informeddatadecisions.com
- Thread starter
- #3
Through Query Analyzer, the subreport stored procedure is pulling what it is supposed to. Through the subreport.rpt it is working correctly. I'm not sure how to check the stored procedure result sets when running the main report. Any suggestions?
Select the subreport and use the menu option of
Format,
Format Subreport...
Is the "Can Grow" option checked?
Cheers,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:
Format,
Format Subreport...
Is the "Can Grow" option checked?
Cheers,
- Ido
CUT (Crystal UTilities): e-mailing, exporting, electronic bursting & distribution of Crystal Reports:
synapsevampire
Programmer
What do you have in your record selection criteria in the subreport?
I still suspect that what's being passed isn't correct.
-k kai@informeddatadecisions.com
I still suspect that what's being passed isn't correct.
-k kai@informeddatadecisions.com
- Thread starter
- #6
CAN GROW is already set
I pass an ID, start date, and end date to the subreport as parameters. The subreport runs a stored procedure to collect its data. I put some additional steps in the subreport to log the results while it is called. The results being logged ARE correct. Exactly the subset of records which I want to appear on the report are being inserted into my log table.
ARGH!
I pass an ID, start date, and end date to the subreport as parameters. The subreport runs a stored procedure to collect its data. I put some additional steps in the subreport to log the results while it is called. The results being logged ARE correct. Exactly the subset of records which I want to appear on the report are being inserted into my log table.
ARGH!
synapsevampire
Programmer
I didn't mean that you were passing the wrong thing, but it may be a type issue, or a difference within the record selection criterias (main and sub).
I had hoped to see the record selection criteria for the subreport, but you seem convinced that it can't be that.
A log from within the subreport...how did you do this, using a UFL?
Another option is to turn on ODBC logging on your ODBC driver ( if you're using odbc and it allows), execute the report, turn off the logging (it'll get large quickly) and have a look at what's being passed, much simpler and it will show you what the database is getting.
And I would simplify a bit for testing:
Create a copy of the main and the subreport, with just a single parm being passed to the sub and see if it displays, if so, then add in the other parms one at a time.
-k kai@informeddatadecisions.com
I had hoped to see the record selection criteria for the subreport, but you seem convinced that it can't be that.
A log from within the subreport...how did you do this, using a UFL?
Another option is to turn on ODBC logging on your ODBC driver ( if you're using odbc and it allows), execute the report, turn off the logging (it'll get large quickly) and have a look at what's being passed, much simpler and it will show you what the database is getting.
And I would simplify a bit for testing:
Create a copy of the main and the subreport, with just a single parm being passed to the sub and see if it displays, if so, then add in the other parms one at a time.
-k kai@informeddatadecisions.com
- Thread starter
- #8
Sorry if I'm not giving you what you need to help, 'cause I DO want the help, it's just miscommunication...
RECORD SELECTION for the subreport is done through a stored procedure which runs using the parameters passed by the main report. The code in the stored procedure is:
select contact.actiondate, isnull(respcode.descrip,' ') as rdescrip, contact.event1,
(case when contact.complete=1 then 'COMPLETE' when contact.complete=1 then 'INCOMPLETE' else ' ' end) as status_text,
(case when typecode.type is not null and typecode.descrip is not null then ltrim(rtrim(typecode.type))+' - '+ltrim(typecode.descrip)
when typecode.type is not null then typecode.type
when typecode.descrip is not null then typecode.descrip
else ' ' end) as typecodedescrip
from contact
join ReportJoinKeys on ReportJoinKeys.ReportID = @ReportID and ReportJoinKeys.JoinKey = contact.contact_key and contact.stafftyp_key = @StaffTyp_Key
left outer join respcode on contact.resp_key = respcode.resp_key
left outer join typecode on contact.type_key = typecode.type_key
where contact.names_id = @Contact_Names_ID
and contact.actiondate>=@StartDate
and contact.actiondate<=@EndDate
THE "LOG" I CREATED was not through a loging function. Within the stored procedure I added code to write the selected records to another table along with execution information (time, parameters...). The data that was written to the other table indicates that the correct records are being selected on each pass through the stored procedure
SIMPLIFYING PARAMETERS... I simplified down all the way to having NO parameters and hard coding values into the stored procedure (which would give me the same results at every occurance of the subreport). Even when I do that, the subreport by itself shows the detail the way I expect, but when I go to the main report, the subreport detail is still not showing up there. (I've changed bakground color of the subreport each time so I KNOW that I'm always getting the latest version of my subreport when checking it in the main report).
RECORD SELECTION for the subreport is done through a stored procedure which runs using the parameters passed by the main report. The code in the stored procedure is:
select contact.actiondate, isnull(respcode.descrip,' ') as rdescrip, contact.event1,
(case when contact.complete=1 then 'COMPLETE' when contact.complete=1 then 'INCOMPLETE' else ' ' end) as status_text,
(case when typecode.type is not null and typecode.descrip is not null then ltrim(rtrim(typecode.type))+' - '+ltrim(typecode.descrip)
when typecode.type is not null then typecode.type
when typecode.descrip is not null then typecode.descrip
else ' ' end) as typecodedescrip
from contact
join ReportJoinKeys on ReportJoinKeys.ReportID = @ReportID and ReportJoinKeys.JoinKey = contact.contact_key and contact.stafftyp_key = @StaffTyp_Key
left outer join respcode on contact.resp_key = respcode.resp_key
left outer join typecode on contact.type_key = typecode.type_key
where contact.names_id = @Contact_Names_ID
and contact.actiondate>=@StartDate
and contact.actiondate<=@EndDate
THE "LOG" I CREATED was not through a loging function. Within the stored procedure I added code to write the selected records to another table along with execution information (time, parameters...). The data that was written to the other table indicates that the correct records are being selected on each pass through the stored procedure
SIMPLIFYING PARAMETERS... I simplified down all the way to having NO parameters and hard coding values into the stored procedure (which would give me the same results at every occurance of the subreport). Even when I do that, the subreport by itself shows the detail the way I expect, but when I go to the main report, the subreport detail is still not showing up there. (I've changed bakground color of the subreport each time so I KNOW that I'm always getting the latest version of my subreport when checking it in the main report).
synapsevampire
Programmer
Ahhh, interesting... Sounds like you did your homework with the SP logging. The important part of that test was to learn that you are getting parameters, and you are expecting data (not that it's proven that it's getting back to the sub when in the main).
You sound plenty savvy, but just to make sure you haven't overlooked the obvious:
-Make sure that the save data with report is turned off.
-There should not be any suppression being used in the main report section which houses the sub.
-The sub should not have any suppression formula in the details section.
-The sub WILL have a record selection criteria (I doubt that this is a problem, but thus far it's been a well kept secret
)
This is starting to sound a bit bug-like, some tests seem appropriate.
Save the subreport to another name, create a new blank report, and place this subreport into the report footer.
Hardcode the parameter values in the record selection criteria.
Do you see the Subreport?
If so, you either have corruption somewhere, or some formula which is preventing the subreport from showing details.
Let's hear the results.
-k kai@informeddatadecisions.com
You sound plenty savvy, but just to make sure you haven't overlooked the obvious:
-Make sure that the save data with report is turned off.
-There should not be any suppression being used in the main report section which houses the sub.
-The sub should not have any suppression formula in the details section.
-The sub WILL have a record selection criteria (I doubt that this is a problem, but thus far it's been a well kept secret
)This is starting to sound a bit bug-like, some tests seem appropriate.
Save the subreport to another name, create a new blank report, and place this subreport into the report footer.
Hardcode the parameter values in the record selection criteria.
Do you see the Subreport?
If so, you either have corruption somewhere, or some formula which is preventing the subreport from showing details.
Let's hear the results.
-k kai@informeddatadecisions.com
- Thread starter
- #10
SAVE DATA WITH REPORT.... is turned off
SUPRESSION... all supressions including "supress blank" have been turned off in the entire subreport and that section of the main report
RECORD SELECTION ON SUBREPORT... The subreport accepts 5 parameters from the main report, all of which are used by the subreport's stored procedure to collect the correct data
RECREATE SUBREPORT... Nice Try... I did have a similar problem on a different subreport and recreating it fixed the problem. This particular one I've recreated at least 4 or 5 times and no such luck.
NEW REPORT LINKED TO COPY OF SUBREPORT... I created a new report linked to that subreport and it works fine in any section (detail, footer, header...)
ARGH!!!!
I think that my next step will be to totally recreate the first MAINREPORT. I didn't want to do that because it's got a lot of sections and a lot of formatting, but I don't know what else could be causing the problems.
SUPRESSION... all supressions including "supress blank" have been turned off in the entire subreport and that section of the main report
RECORD SELECTION ON SUBREPORT... The subreport accepts 5 parameters from the main report, all of which are used by the subreport's stored procedure to collect the correct data
RECREATE SUBREPORT... Nice Try... I did have a similar problem on a different subreport and recreating it fixed the problem. This particular one I've recreated at least 4 or 5 times and no such luck.
NEW REPORT LINKED TO COPY OF SUBREPORT... I created a new report linked to that subreport and it works fine in any section (detail, footer, header...)
ARGH!!!!
I think that my next step will be to totally recreate the first MAINREPORT. I didn't want to do that because it's got a lot of sections and a lot of formatting, but I don't know what else could be causing the problems.
-
1
- #11
I would give this a try before rebuilding the main report:
1) delete the subreport (keeping the copy that you have already saved)
2) "save as" the main report as something new
3) close Crystal
4) reopen crystal.. open the new "saved as" report, import the subreport..
It seems silly but has done the trick for me before on flaky reports.
As for rebuilding the main report (especially with lots of formulas and parameters etc.)
Start a "blank" report with all the tables set up that you need.
Place ALL the paramters, formulas, SQL expressiongs etc that you use (even if they never show up on the report) in a report header section of the old report..
Select them all and do a copy and paste them into the new report (make certain the report header section that you are pasting into is large enough... crystal just drops stuff that doesn't fit). That way you get all the formulas etc recreated for you (woohoo that is the part that always seems to take forever).
Then delete all those uneeded fields (the formulas stay) and then recreate the sections you use (I try to add my guidelines as well in as similar of area as possible).. then one section at a time... copy paste as above. I probably would skip the section that the subreport is in a rebuild that by hand.
Using the above, I can rebuild a pretty complex report in less than an hour, including reanchoring the fields to guidelines. The copy/paste brings most field formatting with it.
Lisa
1) delete the subreport (keeping the copy that you have already saved)
2) "save as" the main report as something new
3) close Crystal
4) reopen crystal.. open the new "saved as" report, import the subreport..
It seems silly but has done the trick for me before on flaky reports.
As for rebuilding the main report (especially with lots of formulas and parameters etc.)
Start a "blank" report with all the tables set up that you need.
Place ALL the paramters, formulas, SQL expressiongs etc that you use (even if they never show up on the report) in a report header section of the old report..
Select them all and do a copy and paste them into the new report (make certain the report header section that you are pasting into is large enough... crystal just drops stuff that doesn't fit). That way you get all the formulas etc recreated for you (woohoo that is the part that always seems to take forever).
Then delete all those uneeded fields (the formulas stay) and then recreate the sections you use (I try to add my guidelines as well in as similar of area as possible).. then one section at a time... copy paste as above. I probably would skip the section that the subreport is in a rebuild that by hand.
Using the above, I can rebuild a pretty complex report in less than an hour, including reanchoring the fields to guidelines. The copy/paste brings most field formatting with it.
Lisa
synapsevampire
Programmer
I've also used this trick, delete the subreport from the main report, save the main report as a version 7 (providing you don't require later functionality), open the 7, save as 8.5. Put the subreport back in. THis worked for one of mine...
Lisa: Good points. That's how I *recreate* a report too, and how I move reusable formulas to other reports, saves LOTS of time.
-k kai@informeddatadecisions.com
Lisa: Good points. That's how I *recreate* a report too, and how I move reusable formulas to other reports, saves LOTS of time.
-k kai@informeddatadecisions.com
- Thread starter
- #13
RESOLVED...?!?!?! if you can call it that.
Back from a holiday weekend, I TOTALLY rebuilt my main report. I used the tricks mentioned above, but it still took some time. I got the report working and then decided to add one graphic line to a subreport and then POOF, they ALL stopped working. I've played with it enough now to see that if I touch ANY of the .rpt files in Crystal, then I must delete all of the subreports from within the main report and then re-insert them. Just re-importing them without deleting them doesn't work. Anyway, I've got them going for the moment. I hope this is helpful to someone and I DO appreciate the suggestions so far.
Back from a holiday weekend, I TOTALLY rebuilt my main report. I used the tricks mentioned above, but it still took some time. I got the report working and then decided to add one graphic line to a subreport and then POOF, they ALL stopped working. I've played with it enough now to see that if I touch ANY of the .rpt files in Crystal, then I must delete all of the subreports from within the main report and then re-insert them. Just re-importing them without deleting them doesn't work. Anyway, I've got them going for the moment. I hope this is helpful to someone and I DO appreciate the suggestions so far.
Do you have "Reimport subreport on opening" checked? Reimporting can mess up subreport data retrieval, especially if the subreport links are not to the default subreport parameters. When that happens, the only cure I've found is to delete the subreport from the main report and then manually import and link it again.
- Thread starter
- #15
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question