Question about subreport connection to main report

[former_member557357]

Hi,

I am very new to this field. I have created three different reports (one main and 2 sub reports). I linked my sub report #1 to 'USERID'.

I do get results only if they have user id matches from main report and sub report. I want if there is no match, still I need results from sub reports. I am not sure how to go about it.

I tried stringvar ... my formula did work but did not get me results what I was looking for.

if anybody would guide me step by step, would be very appreciated.

Thanks

Mona

[DellSC · Accepted solution]

You need to have some way - usually through an outer join - to get the user IDs of all students, regardless of whether they have overdue books. That is the only way you're going to get the fines subreport to run.

-Dell

[former_member557357 · Accepted solution]

Yeah. I tried already that option....

no luck. I think I am not getting result as it is interfered by the policy.

Thanks

mk

[DellSC · Accepted solution]

Another thought might be a re-design of the report.

Can you run a query that would get you a list of all of the users who have either overdue fines or overdue books? If you can do that, I would make that the query for the main report and then make each of your other three reports subreports of that. That way you would have a list of everyone you need and the subreports would pull the detail data for each situation.

If you can write SQL against your database, you could use a Command in the main report to get this information more easily by either being creative with your joins or using "exists" with sub-queries in the where clause. I could help you write this if you need assistance. To do this, I would need to know what type of database you're connecting to, what the table names are for the tables that have the the student info, the overdue books, and the overdue fines, and what the where clauses are for each of your sub-reports. You could also go to the Database Menu and then to "Show SQL Query" and put the query for your existing main report and each of your subreports (from the Show SQL Query in the subreports) into a text file and attach it here.

-Dell

[former_member557357 · Accepted solution]

I would give a try. Looks it might work.

I will get back to you soon.

Thanks

[former_member557357]

ok. Great. But again is there any way I can suppress all unmatched record from main report if there is no match in any of three sub reports.

Please help

I do get results but lot of unmatch records I see in main report which I need to fix.

If I use shared var ho can I put so all unmatched records in main report will be suppressed?

Please help

I am struggling so badly

Thanks

Mona

[DellSC]

There is a way do to this, but you're going to have to use a command in the main report. Basically, the command will look something like this:

Select
<All the fields for the main report>
From <student table>
left join <fines table> on <student id>
and <filter for fines>
left join <overdue books table> on <student id>
and <filter for overdue>
left join <misc charges table> on <student id>
and <filter for misc charges>
Where fines.studentid is not null or
overdue.studentid is not null or
misc.studentid is not null

By putting the subreport filter information in the joins here you'll get just the students who have a record in at least one of the subreports. If you're not used to working with commands, see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/. If you need help with the query, let me know.

-Dell

[former_member557357]

Hi Dell

My supervisor wants this report made in pure crystal. SQL takes longer time to pull data and we get complains from teachers as they do not have patience.

Is there any ways I can be able to suppress all student records who are not owing fines or overdue books .

I get very nice results as you suggested to expand my search and really it worked . I see all matched records with sub reports but do not know how to suppress records of user id in mail report

Thanks

[former_member557357]

Hello Christy,

I tried to work around my report as you suggested. As I mentioned to you earlier, I have two subreports which I linked as userID. After receiving your suggestion, I created another dynamic report which carries all userID. This is now my main report.

I linked all my subreport to "userID'. My all subreports are dynamic. Earlier I made my main report as overdue report, which now I am making as subreport placed right under detail section of my useriD as main report and other subreposrt one after another.

My problem is I do get results from all three reports, but I do not understand how should I suppress all userid which are not a match from any of my subreports?

If my main report has a school location selection as a parameter and when I connect my subreports, will they all be filtered by school location as well ? If yes, then I can easily remove my school location parameters from all my subrepost as all subreports have school location parameters. Is it correct?. If no, how can I by pass 'school selection parameter' from all subreports?

Please advise.

Thanks

Mona

[Former Member]

You can keep School parameters in Subreport and then link Main report Parameter to SubReport Param

In linking dialog box add main report Param to main linking window. In bottom left use drop down to link to corresponding SR param

Ian

[DellSC]

Assuming that each student only has one location, if you're linking the subreports based on student, then the location filter in the main report will limit it to just that location's students and you won't need the location in the subreports.

-Dell

[former_member557357]

Great.

Thanks Dell very kind of you

Mona

[former_member557357]

Hi Dell

Thank you for your valuable suggestion and I took it religiously. I am happy to tell you finally I was able to finish my report the way I wanted. This was my biggest challeng but I made it very well.

I have one small issue and I am sure you will have a brilliant solution to it.

Here is my issue:

I linked my three sub reports to my main report and it pulls all matching record and I suppressed all unmatched records. Now I sorted this list by home room teacher followed by user id.

I want my report header and page header repeated on every page when home room changes. The idea is this report will be printed and given to home room teacher so they can give to their students.

I went to group header and check off the last option which says print every page. I did and it does its job but I wanted report header and page header should be printed for every home room teacher not on every page.

This is the only thing I am not able to do. I tried in section report and click every new page but I get lot of blank pages which I suppressed earlier. These are the ones which are unmatched records from sub reports to main report.

Any great idea which helps me achieve my result?

Please do let me know.

Thanks

Mona

[former_member557357]

Hello Ian,

The report I created goes like this. main report contains records of students who have either outstanding, lost, damage books listed. This come from a designated policy called location.

second report, which is my sub report, I created for the fines they owe. meaning books returned, but fines not paid...this comes from another policy called BRSN

I created a third sub report which also come from BRSN, but these charges are implied but not related to books, its called 'MISC'. The table I connected has item table, which has barcode no and MISC report does not contain barcodes, so it does not work along with BRSN sub report as a result I had to create another report.

Now , all students who owe books may not have fines from previous returned books. When I linked my main report to sub report #1, I got result for those only who have outstanding book and also fines. It knocks out all other records which has just fines. Same case with sub report #2. I need all students from main report and sub reports if they have no match or one or more matches.

I also tried to link with home room where every students have an assigned home room teacher. But my sub report attaches all home room students under one user ID. I do not know what I am doing wrong.

Please help

Thanks

Mona

[Former Member]

If there is not a corresponding USERID in subreport then it will always be blank if that is your link.

If you want a report for all user IDs in subreport then either link at a higher level eg Customer or Company. Or do not impose a link, and all data will be returned irrespective of data in Main report.

If you are looking to achieve something else then you will need to describe problem in more detail.

Ian

[former_member557357]

Hello Ian,

The report I created goes like this. main report contains records of students who have either outstanding, lost, damage books listed. This come from a designated policy called location.

second report, which is my sub report, I created for the fines they owe. meaning books returned, but fines not paid...this comes from another policy called BRSN

I created a third sub report which also come from BRSN, but these charges are implied but not related to books, its called 'MISC'. The table I connected has item table, which has barcode no and MISC report does not contain barcodes, so it does not work along with BRSN sub report as a result I had to create another report.

Now , all students who owe books may not have fines from previous returned books. When I linked my main report to sub report #1, I got result for those only who have outstanding book and also fines. It knocks out all other records which has just fines. Same case with sub report #2. I need all students from main report and sub reports if they have no match or one or more matches.

I also tried to link with home room where every students have an assigned home room teacher. But my sub report attaches all home room students under one user ID. I do not know what I am doing wrong.

Please help

Thanks