vmrfadmin
asked on
Passing value from Hidden subreport in Header to Main report
Hey all, I am having a little trouble passing a value from a subreport to the main report. I have a subreport inserted in the header and trying to pass a value from this subreport to the detail section of the main report. I have the formulas setup and the whole thing works great when I have the header visable. When I change it to dont show....well then it doesnt work. I am NOT suppressing the header section!!!! I am just trying to run this report that passes a few different values to the main report.
I figured I would run this report in the header....(the users dont need to see it) and then pass it over to the main report where I need this information to show.
Any ideas or help would be greatly appreciated.
Thanks,
Bob
I figured I would run this report in the header....(the users dont need to see it) and then pass it over to the main report where I need this information to show.
Any ideas or help would be greatly appreciated.
Thanks,
Bob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok guys...thanks for your feedback.
I used your method mlmcc and it works. I like the fact that it works and am very thankful for your suggestion. It does seem weird to do it that way. I am a little confused why you cant hide a header (NOT Suppress) and get the subreport to run and pass the data to the main detail section of the report. If I ever have to go back and look at my code.....I will have to remember that I have these really small subreports in a header that I have to look at!
Kurt, I like your method but the data from the subreport comes from a different unrelated database than the data in the main report. I have to run the subreport above the detail section of the report and then pass it as a distinct value to the detailed section of the report. I am running cr 9 and I am talking to an SQL database and 2 access databases.
Thanks guys for your comments. I really appreciate them!
bob
I used your method mlmcc and it works. I like the fact that it works and am very thankful for your suggestion. It does seem weird to do it that way. I am a little confused why you cant hide a header (NOT Suppress) and get the subreport to run and pass the data to the main detail section of the report. If I ever have to go back and look at my code.....I will have to remember that I have these really small subreports in a header that I have to look at!
Kurt, I like your method but the data from the subreport comes from a different unrelated database than the data in the main report. I have to run the subreport above the detail section of the report and then pass it as a distinct value to the detailed section of the report. I am running cr 9 and I am talking to an SQL database and 2 access databases.
Thanks guys for your comments. I really appreciate them!
bob
The point of a SQL Expression/subquery is that it can pull in completely unrelated data. It's also much more efficient than a subreport, because it's processed on the database. A subreport at the detail level is very inefficient, because it will run once for every detail record. If there's 50k detail records, the subreport will run 50k times.
Here's a sample of a simple SQL Expression:
//%SampleSQLExpression
(
SELECT DISTINCT
table.field
FROM
database.tableb
WHERE
condition
)
When you review the SQL in the Crystal Report (Database|Show SQL Query), you'll see that the SQL Expression gets added to the SELECT clause as a subquery field:
SELECT
tablea.field,
tablea.field2,
(
SELECT DISTINCT
table.field
FROM
database.tableb
WHERE
condition
) AS "SampleSQLExpression"
FROM
database.tablea
WHERE
conditions...
In this example, most of the fields come from Table A, but the subquery field comes from Table B, which is unrelated. There is no correlation between these two tables, but you are able to bring back a distinct field from Table B in every detail record.
~Kurt
Here's a sample of a simple SQL Expression:
//%SampleSQLExpression
(
SELECT DISTINCT
table.field
FROM
database.tableb
WHERE
condition
)
When you review the SQL in the Crystal Report (Database|Show SQL Query), you'll see that the SQL Expression gets added to the SELECT clause as a subquery field:
SELECT
tablea.field,
tablea.field2,
(
SELECT DISTINCT
table.field
FROM
database.tableb
WHERE
condition
) AS "SampleSQLExpression"
FROM
database.tablea
WHERE
conditions...
In this example, most of the fields come from Table A, but the subquery field comes from Table B, which is unrelated. There is no correlation between these two tables, but you are able to bring back a distinct field from Table B in every detail record.
~Kurt
Glad i could help
mlmcc
mlmcc
~Kurt