Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filtering main report records based on sub-report criteria

Status
Not open for further replies.

JRR123

Technical User
Apr 18, 2013
4
CA
Good day,

I am at a loss and as such turn to the forums for help.

I am generating a report where I am providing details on incidents called into a helpdesk where a specific product was used. These incidents may have a task that was assigned to a support group for resolution, there may have been more than 1 task, or there may be no task at all. The incident and tasks reside on separate tables within the database.

I need to provide all incidents that match this product criteria except where a task was sent to a group named "Business Solutions". This means that all tasks that went elsewhere, or were addressed solely by the helpdesk need to detailed in the report.

I have built the report on the incident table, and set the necessary filter on the specific product and time period etc. I have built a subreport to detail the task information and declared a shared string variable on the subreport @BSTask that provides a Yes or No output on if the Business Solutions group received a task (Yes being a task that is sent to Business Solutions).

I am able to use this shared string in the main report and it does report correctly, however I am unable to filter these incidents out based on the variable @BSTask as I receive the error "this field name is not known". I attempted to use a formula using the variable to assign a value of 1 to each task that sent to business solutions and a 0 for all other tasks and filter based on this, but this did not work either. I also tried a group selection on this but was unsuccessful.

Any suggestions are greatly appreciated.

Thanks,

JRR
 
Hello JRR,

As far as I'm aware you can't filter main report data based on a shared variable.

What you can do is supress/hide the main report data based on the shared variable.Hope this helps.
 
Thank you for the response raja2008, I will look into that and report back
 
Unfortunately I am not having much luck. When I enter conditional statements to suppress detail sections it is causing my variable to change its value, i think this is because of the placement of the variable in the main report. Here is a breakdown of the report:

details a: incident data (submit date, submitter etc.)
details b: incident summary (field can grow)
details c: incident details (field can grow)
details d: task subreport
details e: incident resolution (field can grow)
details f: shared variable
 
Just remember that you cannot suppress the subreport (or it will not execute). Otherwise a shared variable should keep it's value once it is set.

I hope this helps.
 
Not sure I understand what you want to remain in the report. If you mean to exclude all incidents that contain a task sent to Business Solutions, then do this:

First insert a group on {table.incident}. Then create a formula {@BusSoln}:

if {table.dept} = "Business Solutions" then 1

Then go to report->selection formula->GROUP and enter:

sum({@BusSoln},{table.incident})=0

This would show all incident groups where there is no referral to Business Solutions.

-LB
 
lbass, thanks for your post but when I try to summarize the shared numbervar I receive the error "This field cannot be summarized
 
I'm suggesting that you eliminate the subreport and then try my suggestion.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top