[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

656 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