Baggers2003
Programmer
Hi,
My situation is this ... I have a report that is grouped on customer type. You can drill down on these groups and get sales by customer. I have budget figures for the groups (customer types) only. To get the budgets I have created subforms with the budget data by customer type. I have linked the subreports to the main report using these customer types.
I need to only show non blank rows, and in order to achieve this I have created a formula which adds up all the sales figures and only shows those that are non-zero. However, this means that any customer types that have budgets, but no sales are not adding to the budget total.
Then I tried linking (Visual Linking Expert...) the budget file to the other files in the main report. Still using the subreports to display the data, but relying on the budget file multiple-matching (each customer will inherit the budget of the customer type it belongs to). Then I added all the sales figures up AND the budget figures, and used this formula field to calculate whether or not the row was non-zero. This solved the problem with some of the budget values not adding to the total in the main (not drilled-down) report.
But now when you drill down, all customers have a value for budget, and therefore, even if there are no sales figures for that customer, the row still shows up because there is a budget figure (only because the file is multiple-matching). Because on the drill down I have hidden the budget figures from showing through (as there is no budget data by customer), the row appears as a blank row.
I cannot use global variables to get data between the main and subreports as I will need to know the budget value for each row and the number of rows is unknown. I suppose I could reset the variable and assign it the value of the budget in each row, but this sounds slow to me and I'm sure there must be a cleaner solution.
Can I get data from a subreport into a main report, without that link representing a relational link and hence selecting out records? Or has someone got a better way of doing this?
I am using CR8.5 and I can post the SQL if anyone needs it.
TiA,
Steve.
My situation is this ... I have a report that is grouped on customer type. You can drill down on these groups and get sales by customer. I have budget figures for the groups (customer types) only. To get the budgets I have created subforms with the budget data by customer type. I have linked the subreports to the main report using these customer types.
I need to only show non blank rows, and in order to achieve this I have created a formula which adds up all the sales figures and only shows those that are non-zero. However, this means that any customer types that have budgets, but no sales are not adding to the budget total.
Then I tried linking (Visual Linking Expert...) the budget file to the other files in the main report. Still using the subreports to display the data, but relying on the budget file multiple-matching (each customer will inherit the budget of the customer type it belongs to). Then I added all the sales figures up AND the budget figures, and used this formula field to calculate whether or not the row was non-zero. This solved the problem with some of the budget values not adding to the total in the main (not drilled-down) report.
But now when you drill down, all customers have a value for budget, and therefore, even if there are no sales figures for that customer, the row still shows up because there is a budget figure (only because the file is multiple-matching). Because on the drill down I have hidden the budget figures from showing through (as there is no budget data by customer), the row appears as a blank row.
I cannot use global variables to get data between the main and subreports as I will need to know the budget value for each row and the number of rows is unknown. I suppose I could reset the variable and assign it the value of the budget in each row, but this sounds slow to me and I'm sure there must be a cleaner solution.
Can I get data from a subreport into a main report, without that link representing a relational link and hence selecting out records? Or has someone got a better way of doing this?
I am using CR8.5 and I can post the SQL if anyone needs it.
TiA,
Steve.