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


Pass a text field value from subreport to main report

Posted on 2014-02-18
Medium Priority
Last Modified: 2014-02-18
None of the examples on the web are what I want.  I need to pass a value back to the main report from the subreport.  Just the value.  Not add it, subtract it, nothing, just pass the value up so it is on the same row as the rest of the data.  This is so I can eventually export the whole thing to Excel for further manipulation.

Please help!
Question by:Becky Edwards
  • 2
  • 2
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39868207
You don't need to do anything to pass the value, because your subreport is returning a single field at a detail level.  If there's data for the ICD9 field, then a value will be displayed.

That being said, I a few problems, such as:

1)  You seem to have a fundamental misunderstanding of how to use joins in Crystal Reports, specifically when to use left vs. inner joins.  You should only use LEFT outer joins when there may or may not be data in the joined table AND you're not filtering against the joined table.  You're basically LEFT joining every table, most of which are guaranteed to have data in them or are filtering against them.  That being said, most examples you download directly from EPIC have everything Left Joined and I know EPIC teaches everybody to just left join all tables.  That's wrong.

2)  Your subreport has way too much going on.   The only tables you actually need in it are HSP_ACCT_DX_LIST and CLARITY_EDG.  You're doing the HAR filtering in the main report, so just pass in the HAR from HSP_ACCOUNT to the HSP_ACCT_DX_LIST in the sub and filter against the specific ICD9s that meet the criteria for the subreport.

Author Comment

by:Becky Edwards
ID: 39868232
" You're doing the HAR filtering in the main report, so just pass in the HAR from HSP_ACCOUNT to the HSP_ACCT_DX_LIST in the sub and filter against the specific ICD9s that meet the criteria for the subreport. "

Ok, I can take out the tables I am not using.  And I am already filtering against the specific ICD9s, correct?

I am not sure how to "pass in the HAR from the HSP_ACCOUNT to the HSP_ACCT_DX_LIST" which is actually like my initial request, how to pass the value into the main report.

Yes, my subreport is returning a single field at the detail level, but it will not export to the same line in Excel, which it needs to do.

Perhaps this is also an EXCEL question?
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 800 total points
ID: 39868281
"Pass" is technically not the right word, I guess.  You would link the subreport to the main report on HSP_ACCOUNT.HSP_ACCOUNT_ID to HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID in the sub (just like you would do in a main report).

Nothing is passed, the value in the subreport is displayed on the main report page.  If you see it in Crystal Reports when you preview the report, but don't see it in Excel when exported, that's really a different issue.

Also, technically, your subreport could return multiple values, because there can be multiple DXs in the list for that specific HAR.

The exporting may just be a formatting issue based on the height of the detail section and the height/position of the field in the subreport.
LVL 101

Accepted Solution

mlmcc earned 1200 total points
ID: 39868284
The problem with the export is how Crystal exports to Excel.

Because a subreport can grow, crystal exports subreports to Excel on their own line.

The solution to that issue is to put the subreport in a section above where you need it.
Use a shared variable to pass the data back tp the main report for display.

In your case you simply need to add a second detail section and move it to be above the existing one.
You then move the subreport to the new section and change it to pass a shared variable back to the main report.

AS for the database and tables issue, I don't know the EPIC schema so I would take rhinok at his word for the tables you need.  Since you are filtering against some of the other tables does that change the value you will return from the subreport?

You can pass values from the main report to the subreport in 2 ways.
1.  Subreport links if you are filtering on the field
2.  Shared variables where you set the value in the main report then use it in the subreport.

I changed the subreport so it should be exporting as you want it to.


Author Closing Comment

by:Becky Edwards
ID: 39868879
Thank you both for your excellent comments.  I really appreciated mlmcc fixing my report, so I gave him(her?) a few extra!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

867 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