subreport linked on groups problem removing blank rows 1

[Baggers2003]

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.

 

[synapsevampire]

Consider posting technical information, such as:

Crystal version
Database used
Example Data
Expected output

What you're describing is how you might like to see something done, but not sharing anything technical about the underlying data and what should be done with it.

Knowing the requirements are far more important than what you've tried and why you thought it might work.

-k

 

[MrBillSC]

Would it work to use your budget report as the main report and link by customer type to get customer data in subreport?

MrBill

 

[Baggers2003]

SV,

Thanks for your reply, the information you require follows:

Crystal Version is 8.5
Database is DB2

Example data:

file CUSOWN - Holds customer details, fields follow ...
CUSCAT - Customer category ("VOLUME ACCOUNT")
CUSACC - Customer account code ("ACC001")

file SALES_TM, SALES_LM - Holds sales details (this month, last month etc.). Fields ...
CUSACC - Customer account No. ("ACC001")
SALES - Sales (10000)

file BUDGET - Holds budget details, fields ...
CUSCAT - Customer category ("VOLUME ACCOUNT")
CUSVAL - Budget value (9000)

CUSOWN holds all possible customers. Whereas sales this month and budgets etc. don't necessarily share the same customers. For instance the budget customer category could correspond to a category to which none of the customers in the sales file belong for the same period.

Simplified SQL:

SELECT
...
FROM
(
(
CUSOWN LEFT OUTER JOIN SALES_TM ON CUSOWN.CUSACC = SALES_TM.CUSACC
) LEFT OUTER JOIN SALES_LM ON CUSOWN.CUSACC
) LEFT OUTER JOIN BUDGET ON CUSOWN.CUSCAT = BUDGET.CUSCAT
WHERE
...

I would like the main report to show sales and budget by customer category. the main thing is, all blank rows must not be visible. Then I would like the user to be able to drill down and see the sales values only by customer (there are no budget values by customer). Again there can be no blank rows. By blank rows I mean where all of the values visible in the row (i.e. both sales and the budget value in the top-level report and both sales values in the drill-down report) are zero.

Any ideas?
Thanks again,
Steve.

 

[Baggers2003]

Mr Bill,

Thanks for your response. Unless you're thinking of a different way than I am I don't think it will, because if I had sales for a customer in a category for which there were no budget values for that period, then those sales would have nothing to link to, and so wouldn't show in the report. Let me know if that's way off track.

Cheers,
Steve.

 

[Baggers2003]

p.s

Just noticed a typo in the SQL - but you get the idea: sales files linked to CUSOWN on account number, budget file on account category.

Steve

 

[Baggers2003]

p.p.s

Currently selecting out blank rows using the following parameter formula {@ALL_SUM}

SALES_TM.SALES + SALES_LM.SALES

Set as follows in the Select Records section

{@ALL_SUM} <> 0

Cheers,
Steve.

 

[lbass]

If you remove your formula {@ALL_SUM} <> 0 from the record selection formula and instead add it to the GROUP selection formula, then the non-group selected budget figures should still be available for calculation using the usual summaries.

I'm a little unclear about the {@ALL_SUM} formula, which seems to be a detail level calculation. If you mean to remove category types that sum to zero, then the group formula should be:

sum(({SALES_TM.SALES} + {SALES_LM.SALES}),{CUSOWN.CUSCAT}) <> 0

-LB

 

[Baggers2003]

LB,

Thanks for this - something for me to think about. The {@ALL_SUM} formula selects on detail because they also want to see no blank rows when they drill-down to the detail level. I can get it to work one way or the other, it's just when I try and get both the main and drill-down reports to show no blank rows where I struggle.

Cheers,
Steve.

 

[lbass]

If you use my group selection suggestion, instead of record selection, you can remove blank rows by formatting the detail section with:

{@ALL_SUM} <> 0

Suppression would still allow the budget figures for suppressed details to contribute to the total.

-LB

 

[Baggers2003]

Thanks LB - simple as it seems now - that's the solution I was missing!

 

[Baggers2003]

LB,

Sorry, that doesn't quite work. If I select onlyt groups that have sales value not equal to zero, then groups where the sales value is equal to zero, but there is a budget value also do not show. I cannot use the budget figures in the group select equation as they exist only in sub-reports.

Any ideas?

Cheers,
Steve

 

[lbass]

Sorry, I guess I didn't read your post carefully enough. The following should work:

In your subreport, create a shared variable for your budget figure like the following and place it on the subreport, using it instead of your budget figure:

whileprintingrecords;
shared numbervar budget := {table.budget} //or if it is a sum, put the sum here, like: sum({table.lineitems},{table.category})

I'm assuming that the subreport and group summaries are in the group header of the main report. In the main report, create a formula to reset the shared variable:

//{@reset} to be placed in the group (category) footer:
whileprintingrecords;
shared numbervar budget := 0;

Instead of using a group select, go to format->section->group header AND group footer->x+2 and enter:

sum({SALES_TM.SALES},{CUSOWN.CUSCAT}) +
sum({SALES_LM.SALES},{CUSOWN.CUSCAT}) +
shared numbervar budget = 0

Also go to the details section->suppress->x+2 and format that with:

{@ALL_SUM} = 0

Let me know if this still doesn't quite meet your needs.

-LB

 

[Baggers2003]

LB,

This sort of works, but the value of the shared numvar budget is assigned in the section where the sub-report and data are. However it doesn't show up until at least the next section below - if you see what I mean. So say the budget value for a row was 10, the value of the formula field in the main report is only 10 in the section "below" that, meaning I can't use it to hide the section I want.

Hope this is clear.

Thanks,
Steve.

 

[lbass]

Did you add the reset formula for the shared variable in the group footer of the main report? I tested this, and adding the reset resulted in the shared variable appearing in the correct section (the same group instance as the subreport that produced the value).

-LB

 

[Baggers2003]

Actually my data is in the footer, but I created a section below that and put it in there - that resulted in the budget field returning all zero's. I've had a look at other posts on this forum about this problem, and it seems that in version 8.5 the value is calculated in one section and is only available after that. The work-around is to create two subreports, one invisible one in a section above where you require the data. If you have version 9 this could explain why you get it to work as this "feature" has been fixed in 9 apparently - if not then I think I am missing something. I hope I am in a way because the workaround sounds like it will slow my already slow report down even further!

Let me know what you think.

Cheers,
Steve.

 

[lbass]

No, I have 8.0. You could place the subreport in the group header, and then display the budget figure via the shared variable in the group footer. Then if you placed the reset in the group footer, this will work. You need the subreport in the group header anyway so that the shared variable will be available to suppress the group header as well as the group footer. You can make the subreport "invisible" by removing borders, and within the subreport, dragging the budget field to the right so that it doesn't appear within the visible subreport margins. Also resize the field and the section that it is in so that it is tiny.

-LB

 

[Baggers2003]

Yes! Very hard work - but worth it - thanks!