Pass a text field value from subreport to main report

Posted on 2014-02-18
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 200 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 100

Accepted Solution

mlmcc earned 300 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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