CR XI - Can't cosoldate data across columns/fields

[joeg23]

Hi,

This is my second attempt at a post for this problem...i'm new to the forum, but i'll do my best to describe my problem.

I'm using CR XI to access an Excel database. The data is arranged as follows:

Transaction Date[tab]Error Type 1[tab][tab][tab]Error Type 2[tab]ET3
(DATE)[tab][tab][tab][tab][tab](STRING)->
1/1/06[tab][tab][tab][tab][tab]Improper APR[tab][tab]NULL[tab][tab][tab][tab][tab]NULL
1/2/06[tab][tab][tab][tab][tab]NULL[tab][tab][tab][tab][tab]NULL[tab][tab][tab][tab][tab]NULL
1/3/06[tab][tab][tab][tab][tab]NULL[tab][tab][tab][tab][tab]Improper APR[tab][tab]NULL
1/4/06[tab][tab][tab][tab][tab]Improper APR[tab][tab]No Instructions[tab][tab]NULL

I would like to create a running total of all error types, based on the error text. In this example, the output of my desired report would be:

Error Type[tab][tab]# of Occurences[tab][tab]Error %(ErrorSum/TotalRecords)

Improper APR[tab][tab][tab]3[tab][tab][tab][tab][tab][tab]75%
No Instructions[tab][tab][tab]1[tab][tab][tab][tab][tab]25%

I have been unable to find any way to collect occurences of the same error type across multiple columns. Any ideas?

Thanks,
Joe

 

[synapsevampire]

Since you're using CR XI, you can create a more intelligent data source by using a Command Object as the data source with something like:

select 'Type 1' ErrType, transdate, errortype1 from table1
union all
select 'Type 2' ErrType, transdate, errortype2 from table1
union all
select 'Type 3' ErrType, transdate, errortype3 from table1

Now you have it all in one column, which makes aggregates a snap, and you also have an additional column which represents which error type it was which may prove useful.

-k

 

[joeg23]

Awesome...i'll give it a try...thanks so much!

joe