Crystal Reports: Pass Group from the subreport to Group in the Main report

BelleBottoms
BelleBottoms used Ask the Experts™
on
I need to pass a Group (GH4 @name) from the subreport Named: EventDissemSub to a Group (GH1 @Name) in the Main report Named: DATATeam.  

The grouping needs to be done in the main report because I use this report to monitor their progress and count their activities.

Here is what I think you may need...
//Subreport link to main report
{EventStaff.VF_VE_ID} = {?Pm-Event.VE_ID}

//subreport table link (inner joint).  I do not have EventStaff and  Staff tables in my main report
EventStaff.VF_SF_ID --> Staff.SF_ID

//formula {Staff.SF_First}+" "+{Staff.SF_Last} is Group Header 4 in Subreport.  I do not have GH1 @Name in my main report
@Name
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
So there is only one staff record in the sub-report? Why, then, is it a group header at all.

Even so, you cannot pass data back without using shared variables, and you can;t sort on any formula using shared variables, because they are only filled in while printing, and the sorting is done before that.

If you want to sort by event staff first name, you are going to have to link in event staff to the main report.

Why is it a sub-report in the first place? Can you give us any idea of the tables and relationships.

I generally find that with careful crafting of the data fetched form the server, you can avoid sub-reports and their attendant headaches altogether.

Author

Commented:
OK I learned somthing new...Crystals counts then sorts
1. Are you saying I will need to add the two staff tables to my main report then ask you how I should add variables?  
2. I create a sub because
it's easier to link it to other reports ALSO
I am having problems with pulling alll events associated to staff if the Staff is NOT attached to the record.  It tends to pull only the events with a staff instead of everything

I have used if (isnull) ___ then .... but sometimed that does not do what ot's suppose to do.

The other challenge I face when writing reports is that I connect to two different ODBC data source  AND download 12 seperate reports from the state database which I save as access databases.  All this information is plugged in my reports.  Most of my reports look at date ranges.

I know one thing...this site is my salvation
Top Expert 2009

Commented:
How is an event associated with a staff member if the staff id is not in the event record?

I think you need to set out the tables and their relationships, and the type of results you are looking for.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
I have 6000+ staff name and 500,000+ events.  One staff will have 2000+ events in 5 months
Top Expert 2009

Commented:
Yes, but how are the tables joined together?

How can you have a staff member associated with an event if the event doesn't have a staff id in it?

Do you mean you want to show the staff member even if there are no events for that staff member in the date range?

Author

Commented:
Well good morning

I do it using the Select expert
{EventStaff.VF_VE_ID} = {?Pm-Event.VE_ID}"
Top Expert 2009

Commented:
I cannot work out why you are having a problem with joins between your tables, without knowing what fields there are in the tables, and how the tables relate to each other.

If you want to sort by staff in the main report, you're going to have to have staff data IN the main report. You say that when you try this you encounter problems, so I am trying to work out why.

I cannot do this without more information on how the tables relate to each other, and what problem you have encountered exactly while trying to include staff in the main report.

Author

Commented:
Every event should have a staff assigned.  Sometimes the end user forgets to attach a staff to the event OR forgets to add their name and/or the end date to the completed event.  The application allows the end user to make these mistakes.

I need the event because it's associated to some documentation we processed or received.  The staff assignment helps me pinpoint the person responsible for the document as well as the person responsible for inputting the information in the database.  I also use the events to manage everyones case load.
Top Expert 2009

Commented:
So how do you want to sort the event if there is no staff member assigned?

Author

Commented:
I sent you an attachment
EventReportTables.doc

Author

Commented:
Event with No Staff will fall under "Missing Staff Name".  That's one of the formulas I having trouble with
Top Expert 2009
Commented:
So, is there more than one EventStaff record for any Event record?

What selections are in the subreport at the moment?

Author

Commented:
No anymore:  I  made certain that each event in the last year only had one staff assignment.  I ran a report once a week to find those records with two staff assigned...retrained the end users  and fixed the problem.

<><><>Selection Expert:
{Event.VE_tstamp} = {?StampDateRange} and
{Event.VE_lastuser} <> "nassimar" and
{Event.VE_Type} startswith ["Added-Documentation to Database", "ADDED-Documentation to Database(s)"] and
{Event.VE_Name} startswith ["Added Add", "Added Closure Reeval Declined-Parent", "Added Closure Unable", "Added Diss"]

<><><>Grouping:
Event. VE_tstamp-D
Event. VE_EndDate-D
Event. VE_StartDate-D
Event. VE_Name-S
@Name_ID-A
Event. VE_VE_Type-A

Author

Commented:
Whoops I made a mistake, I gave you the Main report

<><><>The Selection Expert reads like this:
{EventStaff.VF_VE_ID} = {?Pm-Event.VE_ID}

<><><>Grouping:
EventStaff.VF_SF_ID-A
EventStaff.VF_ID-A
EventStaff.VF_VE_ID-A
@Name-A

Author

Commented:
OK...points are going up for the second time!
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you ttrying to group in the main report based on a value from the subreport?
It can't be done.  Subreports are evaluated during the final pass through the report which is after the grouping is done.

mlmcc

Author

Commented:
Alright then can you tell me how to write a formula that will allow the event to appear on the report even it the staff is not attached?

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Not sure what you are getting at.

mlmcc
Top Expert 2009

Commented:
if(isnull({EventStaff.VF_VE_ID}) ) then
 "No staff assigned"
else
  {Staff.SF_First}+" "+{Staff.SF_Last}

Author

Commented:
Thank You...I was so close I did not put the parenthese after the if and before then

Author

Commented:
This formula is now named @NoneStaffName

if(isnull({EventStaff.VF_VE_ID}) ) then
 "No staff assigned"
else
  {Staff.SF_First}+" "+{Staff.SF_Last} // I created a formula for this line @StaffName

PROBLEM
I used the formula ABOVE in the Subreport and it worked very well.  
I realize now that I can't group in the main report based on a value from the subreport so I moved eberything to the Main Report
<><><>I added/linked EventStaff>Staff tables to the Main report.  
<><><>Linked EventStaff to Event table using {EventStaff.VF_VE_ID}) .
<><><>Created two formulas (StaffName and NoneStaffName) in the Main report
<><><>I did not delete my StaffName Subreport because I wanted to use it as reference

When I placed the formula (NoneStaffName) in the Main report, it did not pull the Event with the "missing staff name".  It excluded this event

When I removed the formula (NoneStaffName) from the Main report it pulled the Event with the missing staff and it displayed "No Staff Assigned" because VF_VE_ID isnull.  

I need the Event to pull in the report listing the Staff as "No Staff Assigned" when StaffName isnull

Author

Commented:
without having to keep the subreport.
Top Expert 2009

Commented:
That formula will not affect the data returned by the report, unless you have not linked the eventstaff and staff tables as left outer join.

In the database expert, click on the link, right click on the link and select link properties.
Make sure it says 'Select all the records from XXX and only those which match from EventStaff

Repeat with staff name.

Author

Commented:
I did Step One
linked the eventstaff and staff tables as left outer join.
Joint Type: Left Outer Joint
Enforced Join: Not Enforced
Link Type: =

I could not complete Step two On Staff and StaffName tables because I could not find the words "link properties"

"'Select all the records from XXX and only those which match from EventStaff"


Top Expert 2009

Commented:
Okay,

You want 'link options'

On link options, the first table in the link is the left and the second the right.
The left table for the link from event to eventstaff should be Event. So it should say in the box at the top

Event.VE_ID --> EventStaff.VF_VE_ID

If it's the other way around, you need to close this dialog, right click the link again and select 'Reverse Link'. Then set it to left outer join.

Do the same with the link from EventStaff to Staff

Your link from event to eventstaff and your link from eventstaff to staff both need to be left outer...

Author

Commented:
Table link corrected to

Staff.SF_ID --> EventStaff.VF_SF_ID
Event.VE_ID --> EventStaff.VF_VE_ID
Both Left Outer Joint

No Change in the report.  When I remove the StaffName from the Event, I do not see the event in the report
Top Expert 2009

Commented:
No.


Event.VE_ID --> EventStaff.VF_VE_ID
EventStaff.VF_SF_ID --> Staff.SF_ID

"I want all events, even where there are eventstaff records, then I want all eventstaff records even when there are no Staff records (which would, hopefully never be the case)"

Author

Commented:
Here' how the tables look
JBTables.doc
Top Expert 2009

Commented:
Reverse the link between Eventstaff and Staff. The arrow should point at Staff not eventstaff, and the link shoul dbe left outer join and showing
EventStaff.VF_SF_ID --> Staff.SF_ID
in the block at the top of the link options.
Top Expert 2009

Commented:
If you hit autoarrange on the database page it will show Staff to the left of Eventstaff, it needs to be to the right, in that the join runs FROM eventstaff TO staff.

Author

Commented:
I checked and rechecked...Still Nothing

Event.VE_ID --> EventStaff.VF_VE_ID
EventStaff.VF_SF_ID --> Staff.SF_ID
Both Left outer joint

I'm still using this formula to pull the StaffName
if(isnull({EventStaff.VF_VE_ID}) ) then
 "No staff assigned"
else
 {@StaffName}

I have this formula in the Format Editor>>>Display String <propercase ({@NoneStaffName})>

Could it have something to do with report Options?
Top Expert 2009

Commented:
You are saying that when you paint the formula on the report, the event which has no staff assigned is removed from the report entirely?

Can you use Database ->Show SQL Query for the version that doesn't work and post the results here.

Author

Commented:
Yes..."when I paint" the event is removed entirely

SQL Query..........................................
oriondb1
 SELECT "Event"."VE_StartDate", "Event"."VE_Name", "Student"."ST_RoleId", "Student"."ST_Last", "Student"."ST_First", "Event"."VE_Type", "Event"."VE_ST_ID", "Event"."VE_lastuser", "Event"."VE_tstamp", "Student"."ST_ID", "Event"."VE_EndDate", "Event"."VE_ID", "Student"."ST_Active", "EventStaff"."VF_VE_ID", "EventStaff"."VF_SF_ID"
 FROM   ("IEPplus"."SPIPublic"."Event" "Event" INNER JOIN "IEPplus"."SPIPublic"."Student" "Student" ON "Event"."VE_ST_ID"="Student"."ST_ID") LEFT OUTER JOIN "IEPplus"."SPIPublic"."EventStaff" "EventStaff" ON "Event"."VE_ID"="EventStaff"."VF_VE_ID"
 WHERE  ("Event"."VE_tstamp">={ts '2010-02-01 10:52:16'} AND "Event"."VE_tstamp"<{ts '2010-02-05 10:52:27'}) AND "Event"."VE_lastuser"<>'nassimar' AND ("Event"."VE_Type" LIKE 'Added-Documentation to Database%' OR "Event"."VE_Type" LIKE 'ADDED-Documentation to Database(s)%') AND "Event"."VE_Name" LIKE 'Added Diss%'
 EXTERNAL JOIN Student.ST_RoleId={?ebr_live: REG.STUDENT_ID} AND EventStaff.VF_SF_ID={?oriondb1: Staff.SF_ID}


ebr_live
 SELECT "REG"."CURRENT_STATUS", "REG_PERSONAL"."SSN", "REG"."BUILDING", "REG"."GRADE", "REG_PERSONAL"."CLASSIFICATION", "REG_PERSONAL"."IEP_STATUS", "REG"."STUDENT_ID"
 FROM   "EBR_Live"."dbo"."REG" "REG" INNER JOIN "EBR_Live"."dbo"."REG_PERSONAL" "REG_PERSONAL" ON "REG"."STUDENT_ID"="REG_PERSONAL"."STUDENT_ID"
 WHERE  "REG"."STUDENT_ID"={?oriondb1: Student.ST_RoleId} AND "REG"."CURRENT_STATUS"='A'


oriondb1
 SELECT "Staff"."SF_First", "Staff"."SF_Last", "Staff"."SF_ID"
 FROM   "IEPplus"."SPIPublic"."Staff" "Staff"
 WHERE  "Staff"."SF_ID"={?oriondb1: EventStaff.VF_SF_ID}


Top Expert 2009

Commented:
Okay, are you deliberately selecting from two databases?
If not, use set database location to change the datasource for REG to the oriondb database.
If you are (and I think you are)

In the links tab, click on "Order Links". Tick, link ordering is enforced, and select each of the links to the ebr_live database and move them to the bottom.

You want the "VIew SQL" to show one block for each database, it's currently showing two blocks for the oriondb1 database.

Author

Commented:
OK...I did it...and I learned something!!!!
 
I re-ran the report ...it's still the same
 
Now what's next oh great one!

oriondb1
SELECT "Event"."VE_StartDate", "Event"."VE_Name", "Student"."ST_RoleId", "Student"."ST_Last", "Student"."ST_First", "Event"."VE_Type", "Event"."VE_ST_ID", "Event"."VE_lastuser", "Event"."VE_tstamp", "Student"."ST_ID", "Event"."VE_EndDate", "Event"."VE_ID", "Student"."ST_Active", "EventStaff"."VF_VE_ID", "Staff"."SF_First", "Staff"."SF_Last"

FROM (("IEPplus"."SPIPublic"."Event" "Event" LEFT OUTER JOIN "IEPplus"."SPIPublic"."EventStaff" "EventStaff" ON "Event"."VE_ID"="EventStaff"."VF_VE_ID") INNER JOIN "IEPplus"."SPIPublic"."Student" "Student" ON "Event"."VE_ST_ID"="Student"."ST_ID") LEFT OUTER JOIN "IEPplus"."SPIPublic"."Staff" "Staff" ON "EventStaff"."VF_SF_ID"="Staff"."SF_ID"

WHERE ("Event"."VE_tstamp">={ts '2010-02-01 10:52:16'} AND "Event"."VE_tstamp"<{ts '2010-02-05 10:52:27'}) AND "Event"."VE_lastuser"<>'nassimar' AND ("Event"."VE_Type" LIKE 'Added-Documentation to Database%' OR "Event"."VE_Type" LIKE 'ADDED-Documentation to Database(s)%') AND "Event"."VE_Name" LIKE 'Added Diss%'
EXTERNAL JOIN Student.ST_RoleId={?ebr_live: REG.STUDENT_ID}


ebr_live
SELECT "REG"."CURRENT_STATUS", "REG_PERSONAL"."SSN", "REG"."BUILDING", "REG"."GRADE", "REG_PERSONAL"."CLASSIFICATION", "REG_PERSONAL"."IEP_STATUS", "REG"."STUDENT_ID"

FROM "EBR_Live"."dbo"."REG" "REG" INNER JOIN "EBR_Live"."dbo"."REG_PERSONAL" "REG_PERSONAL" ON "REG"."STUDENT_ID"="REG_PERSONAL"."STUDENT_ID"

WHERE "REG"."STUDENT_ID"={?oriondb1: Student.ST_RoleId} AND "REG"."CURRENT_STATUS"='A'
 




 

Author

Commented:
Ahhhhhhh....you did it.  It worked...it happened

I did not see it right away because the name moved in a different group when I touched it last.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial