Crystal Reports Column Count and summary total

[999990]

I have access to an ODBC table that has multiple Columns with the same data. I have tried to create a Crosstab, rolling totals and not this site for further assistance.

As seen below Col1, Col2, and Col3 have the same data -- I would like creat a rollup of this data by creating a field that shows the single item and the number of times it appears.

Like a summary see below

--------------------
ID COL1 COL2 COL3
001 10 7777 7777
002 9999 7777 9999
003 7777 20 20
004 9999 30 30
005 40 9999
--------------------

( What I would like to see)

COLX total
7777 4
20 2
9999 3

 

[Turkbear]

Hi,
What database is the ODBC connection to?
Depending on that, you may be able to use a Commmand object to create the info you need.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

More specifically, if you able to use a command, then you would write a union all query like this:

select table.ID, table.col1 as col
from table
Union all
select table.ID, table.col2
from table
union all
select table.ID, table.col13
from table

Then you could insert a group on {command.col} and insert a count on {command.ID} at the group level.

-LB

 

[999990]

I am using SQL 2005 (tables) for the ODBC, however, I was hoping that there was some way to get the data counts correct within Crystal.

The Database design is really bad having 3 seperate cols for the same items or mixture. However, I would like to put this to rest; I am not sure how to write a command/query for Crystal. Please explain. Overall, I would like to count the number of times the item appears across the columns (or fields)

ID COL1 COL2 COL3
001 10 7777 7777
002 9999 7777 9999
003 7777 20 20
004 9999 30 30
005 40 9999
--------------------

( What I would like to see)

COLX total
7777 4
20 2
9999 3

 

[999990]

I am trying to do this in Crystal 2008 or Crystal Xi

 

[lbass]

My solution should work for you. Open a new report->blank report->your datasource->add command (above the table list) and enter the query there. If you are unsure of the syntax/punctuation to use, take a look at database->show SQL query for a report that uses the same datasource and fields. If you want to, you can paste a sample query (the actual "show SQL query") using the fields in question into this thread and I'll help you with the design of the command.

-LB

 

[999990]

I figured out how to get the "add command" This is really Cool.. It worked --

Thanks