Linking an excel worksheet to an oracle table

[Former Member]

When i link an excel worksheet to an oracle table, i do not have the ability to "select distinct records", the option is grayed out. Hence my report returns double/triple...... rows.

Anyway around this ? there is only 1 common link between the 2 tables, a string field for "name"

i'm using cr11

[abhilash_kumar]

Hi John,

The Select Distinct Records is not available when:

• There are multiple datasources in the report (E.g. Excel + Oracle or Oracle + SQL Server etc)
• The datasource is a command object, stored procedure
• Or even when the database driver does not support this

Why don't you simply create a group on the 'name' field and suppress the details section? This way you'll see only one record for the particular name. Otherwise, write a sql query with the Distinct keyword in it and use it as a Command Object.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Abhilash, thanks. 

Ok,  this is the "show sql query"

C:\Crystal Reports\Input Files\Cluster\new cluster.xls
SELECT `'Phoenix_VMAX_'`.`Tier +Shared List`, `'Phoenix_VMAX_'`.`Dev Size GB`, `'Phoenix_VMAX_'`.`ARRAYTYPE`, `'Phoenix_VMAX_'`.`ARRAYSERIALNUMBER`, `'Phoenix_VMAX_'`.`Site Name`, `'Phoenix_VMAX_'`.`Sorted Share List`, `'Phoenix_VMAX_'`.`Tier Level`, `'Phoenix_VMAX_'`.`Host`
FROM   `'Phoenix Non-VMAX$'` `'Phoenix_VMAX_'`
EXTERNAL JOIN 'Phoenix_VMAX_'.Host={?StorageScope-  Phoenix: SRMHOST.HOSTALIAS}

StorageScope-  Phoenix
SELECT "SRMHOST"."HOSTOS", "SRMHOST"."HOSTALIAS"
FROM   "EMCSTS"."SRMHOST" "SRMHOST"
WHERE  "SRMHOST"."HOSTALIAS"={?C:\Crystal Reports\Input Files\Cluster\new cluster.xls: 'Phoenix_VMAX_'.Host}

How would i create a SQL (command from this) ?

[abhilash_kumar]

Hi John,

I've never handled Distincts in an External Join (which is apparently a CR specific join). I doubt if we can use Distinct with External Joins in the first place.

The best thing of course would be to create groups in the report.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Abhilash,

I am trying to get a count for the group, so i have a summary(count) which i add to the group header.
It is always two or three times larger than the unique(distinct) count, due to the duplicate(same names) in the linked table.

The report is a bunch of groups added to a single line(group header 1) with details section suppressed. i created a summary(count) and added it to group header 1.

when i look at the data(details) section the nb rows is duplicated 2 or 3 times, the summary count based upon this data is correct. So i just need to determine how to make the data unique(disctinct).

As you can see the bold is the group header 1 line. the 14 is the summary(count) i added to group header 1.   the red are the duplicate Host names(gained becuase there are duplicate in the other linked table)

[Former Member]

Abilash, if i add :-

{'Phoenix_VMAX_'.Array + Lun} = Previous({'Phoenix_VMAX_'.Array + Lun});

to the details section suppress formaula my data looks like :

which is now correct(at least the nb rows is correct) how do i summary(count) this new selection ? so it is 7 and not 14    the end column is a running total, and shows the missing(duplicate rows)

Thanks

[abhilash_kumar]

Hi John,

What is the nb rows?

Could you attach the report with saved data please?

- Abhilash

[Former Member]

sure, but how do i attach the .rpt ? to this forum i cannot see an attach option

[abhilash_kumar]

Hi John,

Change the extension of the report to .txt. Then click on the 'Use advanced editor' option in the reply window and you should get an option to attach the file.

Also, if you could put in a little summary of what you wish to see on the report would also be great.

- Abhilash

[Former Member]

Abhilash, thank you. But i cannot attach files as .txt i get the error "The content type of this attachment is not allowed" even though it is a .txt file.

Can i email them to you ?  thanks

Data to look like this, is the original .rpt with the doubled or invalid "lun count)

test.....  is the one with the check in details section that does print the correct nb rows but need the proper "return/valid" count.

Thanks John

[abhilash_kumar]

Hi John,

Can I have your e-mail address? I'll send a PM and you can attach the reports.

- Abhilash

[Former Member]

johne.fleming@emc.com   thank you very much

[abhilash_kumar]

Hi John,

Here's what you need to do:

1) Move all the fields from the Group Header 1 to Group Footer 1

2) Right-click the Running Total field (Unique Count) > Edit Running total > Select Distinct Count as the Type of Summary

3) Place the Running total field on the Group Footer 1 section at the end

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Thank you very much,  i placed all metrics in group footer 1

that change does make the "unique" count = 7 and not 14.

But the metric "assigned Gb" reflects double(i.e. sum of the 14 rows, not 7) this is a "summary" defined as "sum" for the assigned gb field.

How do i also make this reflect the correct total ?

can you explain the behavior of the placement from header to footer.

[Former Member]

Abhilash, Don't worry about the "assigned gb" metric.  i created a running total and specified a formula :

NOT({'Phoenix_VMAX_'.Array + Lun} = next({'Phoenix_VMAX_'.Array + Lun}))

so that its only added to as above, and reevaluated on change of group.

then i place this formula in group footer instead of the original "assigned GB" metric.

so just the placement explanation for group header vs group footer.

Thanks John