show group headers

[kimchavis]

I need to display a report based on product codes, whether there is a listing in the database for this code or not. If the code isnt in the database, i would like for the code to display and a zero be next to it.

I have tried everything and cant figure out how to do this in Crystal. Please help.

Im using v 8.5.

Thanks,
KIM

 

[kskid]

If the product code is not in the database, then where does it reside? Do you have a product code table in another database or spreadsheet like Excel.

If so, create a report using that database or spreadsheet and group by product code. Then you can create a subreports to link your database to your spread sheet.

Will need more detail information from you

Crystal Version
Database and connectivity
Sample data
Expected output by Report section

-LW

 

[kimchavis]

I have the product codes in an excel spreadsheet. They are not currently in the database, but they will be. How would i link up my crystal report to an excel spreasheet?

 

[kimchavis]

Does anyone know how to do this? Is there any more information I can provide to figure this out, just let me know.

Thanks,
KIM

 

[kskid]

As I said earlier, need more detail information so we won't make the wrong assumptions

Crystal Version
Database and connectivity
Sample data
Expected output by Report section

What tables are involved, how are they linked, what columns are on the spreadsheet, what groups do you want, what summary totals, etc

-LW

 

[kimchavis]

Hey LW-


here ya go:

Crystal Version 8.5
Database and connectivity: oracle ODBC
Sample data
Expected output by Report section

Im looking for it to look like this:

GH product name - amcloc
D <if there are any loans under that product code they would be listed here.. loan number,, loan amt etc
GF Total loans - (I want to capture the loans that would be 0)



example

GH AMCLOC
D <blank>
GF Total loans - 0

GH PRELOC
D 12345 250,000
D 45678 150,000
GF Total loans - 2

etc.

I have a list of codes on an excel spreadsheet.
All listed from A1 to A35.

Please assist.

 

[kimchavis]

Any help with this would be appreciated. If it is not possible, please let me know.

Thanks,
KIM

 

[Naith]

Kim,

You must have a reference table somewhere in your database listing all possible product codes.

In order to achieve what you want here, you'll need to place this reference table in your report, and use a left-outer join from the reference table to your current driving table.

Group on the product from the reference table, and take a look at the results.

Naith

 

[Naith]

Sorry, I haven't paid attention to the entire thread - I know how annoying that can be.

You can use your excel spreadsheet as your reference table, if you don't have one in the db. You'll get a warning after you link it regarding different datasources, but just ignore it, and proceed as above.

Naith

 

[kskid]

Have you created any reports at all? If not go ahead and create a report against the Oracle DB and grouping by Product. It should be close to what you have

GH PRELOC
D 12345 250,000
D 45678 150,000
GF Total loans - 2

Place the following code in the group footer (GF) or report footer (RF).

shared numbervar loantot := Count(loanid,productcode);\\Your actual loan count goes here. 

Save and close the Report using the Oracle DB

Create a new report using the Excel Spreadsheet using Report Expert. On the data Explorer window, it is under ODBC->Excel Files.  Click on the browse, find and select the Excel spreadsheet.

On the detail tab, add prooduct code
On the group tab, create a group by product code.
Click finish your report preview should look like the following

GH - AMCLOC
D AMCLOC
GF 

GH - PRELOC
D PRELOC
GF 

Suppress the detail section

In the group header place the following formula and suppress it so it doesn't show on the report.
[code]shared numbervar loantot := 0;

Insert another section under the group header, resulting in
following in the report design tab

GH1A
GH1B
D - (Suppressed)
GF

Click on Insert->Subreport
Choose Insert a subreport and insert the Oracle report you created above.
Click on the link tab and link the product code from the spreadsheet to the product code on the oracle report and click OK.

Place the subreport in GH1B section and make it as thin as possible

GH1A
GH1B (subreport)
D - (Suppressed)
GF

In the group footer, place the following formula

shared numbervar loantot;

"Total Loans - " + totext(loantot)

That should do it.

-LW

 

[kskid]

Did I mess that one up. My post should have looked like this

Have you created any reports at all? If not go ahead and create a report against the Oracle DB and grouping by Product. It should be close to what you have

GH PRELOC
D 12345 250,000
D 45678 150,000
GF Total loans - 2

Place the following code in the group footer (GF) or report footer (RF).

shared numbervar loantot := Count(loanid,productcode);\\Your actual loan count goes here.

Save and close the Report using the Oracle DB

Create a new report using the Excel Spreadsheet using Report Expert. On the data Explorer window, it is under ODBC->Excel Files. Click on the browse, find and select the Excel spreadsheet.

On the detail tab, add prooduct code
On the group tab, create a group by product code.
Click finish your report preview should look like the following

GH - AMCLOC
D AMCLOC
GF

GH - PRELOC
D PRELOC
GF

Suppress the detail section

In the group header place the following formula and suppress it so it doesn't show on the report.

shared numbervar loantot := 0;

Insert another section under the group header, resulting in
following in the report design tab

GH1A
GH1B
D - (Suppressed)
GF

Click on Insert->Subreport
Choose Insert a subreport and insert the Oracle report you created above.
Click on the link tab and link the product code from the spreadsheet to the product code on the oracle report and click OK.

Place the subreport in GH1B section and make it as thin as possible

GH1A
GH1B (subreport)
D - (Suppressed)
GF

In the group footer, place the following formula

shared numbervar loantot;

"Total Loans - " + totext(loantot)

That should do it.