| Author |
Message |
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
   Topic: Filter main report based on subreport result
Posted: 26 Sep 2016 at 8:53pm |
|
I have a subreport (based on a stored procedure) that is linked to the main report via the field ArtNr.
I would like the main report to only show the records for which a certain criterion in the subreport is met. Is that even possible?
In this case, the subreport has a formula field AvailableStock that calculates the sum of the difference between two Running Total Fields. If AvailableStock is negative, the main report should show that record including the subreport.
Also, if the subreport has no records, the main reprt should not show that record.
If this is possible - please try to describe exactly what I need to do. Assume that I'm an idiot...
More information:
The main report is based on one table that contains basic article information.
The records in the subreport are planned transactions that can be purchasing, sales or manufacturing.
The purpose of the report is to list all articles that have a higher demand than the current stock on the day when the report is run.
Edited by pbengtss - 26 Sep 2016 at 9:03pm
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Sep 2016 at 4:05am |
|
No, but you might 'switch' the main report and the sub-report. That way you can pass the appropriate filter to the sub-report. If you are not displaying anything from the present sub-report, it is pretty easy to 'hide' it.
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 27 Sep 2016 at 6:44am |
|
I'm afraid that will be difficult. The subreport consists of a stored procedure that looks like this:
ALTER PROCEDURE "ownername"."ProcName"(
IN as_artnr NVARCHAR(16),
IN al_art_id INTEGER,
IN ai_location INTEGER
)
RESULT(
period NVARCHAR(10) ,
date DATETIME,
...
And it continues for another 1300 rows of code...
So the subreport needs ONE article number, its corresponding ID and another parameter. So it's not a table and thus it is not possible to use it as the main report. As far as I understand.
Perhaps there is a smarter way to do this? I have to use the stored procedure.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Sep 2016 at 7:25am |
|
So I a little confused. You need the results from a stored procedure to filter another report? But you also supply a value to the stored procedure to filter the results? A round about way is to have two sub-reports, where the result of the first sub-report is used as a filter for the second sub-report.
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 27 Sep 2016 at 7:46am |
|
The article registry (table in main report) contains about 11000 articles. For each one I want to know if there are any orders placed, and if so - do the orders exceed the stock? I get that information from the subreport by linking the article number and ID in the registry to the input parameters that the stored procedure in the subreport needs in order to list the planned transactions.
Then, if the stock is enough, everything is fine and I don't want to show that article at all.
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 27 Sep 2016 at 8:06am |
|
My idea is:
1. Put the article in the Group header of the main report
2. Put subreport in the Details section
2a. Filter the subreport to show only demands up until today's date
2b. Calculate "AvailableStock" as CurrentStock - Sum of demands
3. Return "AvailableStock" to main report
4. Suppress Group header and Details if "AvailableStock"<0
But I can't return "AvailableStock" to the Group header, only to the Group footer. And therefore my method doesn't work out.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Sep 2016 at 11:26am |
|
True, but does the sub-report really need to be in the details section vs. the GH section (you could always put in a second GH and 'hide' the first GH).
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 27 Sep 2016 at 8:10pm |
|
No, it doesn't have to be in the details section.
I tried:
1. Subreport in Group header 1a
2. Articles in Group header 1b
3. Formula with shared variable that shows correct result in Group header 1b
But then I realised that you can't hide only one Group header - both get hidden...
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 27 Sep 2016 at 8:18pm |
|
I also tried subreport in Group header and Articles in Group footer. When I hide the Group header, the formula field that holds the shared variable is not calculated.
|
|
IP Logged |
|
pbengtss
Newbie
Joined: 18 May 2016
Online Status: Offline
Posts: 22
|
Posted: 28 Sep 2016 at 1:46am |
|
OK, solved it. So if anyone else has the same need:
1. Open the subreport from the Open dialogue (not by right clicking it and choosing "Edit Subreport").
Suppress all sections in the subreport.
Save and close.
2. Group Header of the main report - Insert subreport and place a formula field that sets the shared variable to 0.
3. Right click subreport -> Format Subreport -> Tab Subreport -> Check "Suppress Blank Subreport"
4. Group footer - Place the main fields AND the formula field with the shared variable that was calculated in the subreport.
5. In my case, I want the subreport to be shown when my condition is fulfilled.
To do so, insert the subreport again but this time in the Group footer.
6. Right click the second subreport and choose "Edit Subreport". Open Section expert and add contition for the Details' section's Suppress option (the shared variable "AvailableStock">=0, I only want to show the articles with AvailableStock<0).
Save and close the Edit Subreport.
7. In Main report's Section expert:
a) Group header - Check "Suppress Blank Section"
b) Details - Check "Suppress"
c) Group footer - Check "Suppress" and add condition "AvailableStock">=0
Now it should work.
The problem I'm facing now is that it takes forever to generate the report for my 11000+ articles, that will be my next challange...
|
|
IP Logged |
|
|
|
