Add Command question

[Former Member]

To forum participants,

I need to take a data file and construct a table. The data file is written out in one long record format, see example below.

Property ID Year Batch Base01 Penalty01 Interest01 Base02 Penalty02 Interest02 Base03 Penalty03 Interest03

1234 2009 abcd 500.00 50.00 25.00 300.00 30.00 15.00 600.00 60.00 30.00

Property ID Year Batch # Base Tax Penalty Interest

1234 2009 abcd 500.00 50.00 25.00

1234 2009 abcd 300.00 30.00 15.00

1234 2009 abcd 600.00 60.00 30.00

I need to create a table like the one above. I was hoping that this would be possible through the Add Command feature. Any help would be greatly appreciated.

Bdecker

[Former Member · Accepted solution]

To forum participants

OK, I didn't make myself clear. I don't really want to make a table. It's my understanding that the Add Command creates a subset of data that can be used in a report, just like a table. This is what I'm needing. I will try to provide a more simple example.

The lines immediatly below is an example of the data file I'm dealing with.

Customer Name Order # 1 Order # 2 Order # 3

Date Product Price Date Product Price Date Product Price

ABC COMPANY 04/01/11 Bike Helmets 100.00 03/01/11 Bike Seats 200.00 02/01/11 Bike Chains 100.00

Below is what I hoped I could accomplish with the Add Command.

Customer Name Date Product Price

ABC COMPANY 04/01/11 Bike Helmets 100.00

ABC COMPANY 03/01/11 Bike Seats 200.00

ABC COMPANY 02/01/11 Bike Chains 100.00

So I don't need to make a new file, I just need to rearrange the data.

B Decker

[former_member11696 · Accepted solution]

Hi B,

Ah... that makes more sense now. Command objects gives you the ability to simply paste in or write a SQL statement that CR then passes to the database. The fields you declare in the SELECT portion is what you see in the database options on the right or left side of the designer.

It's the same thing as adding a database and table using the Database connection Wizard. Only you have complete control of the SQL when using a Command, it does have to be valid SQL to work of course.

So you have 2 options, use a Command and write the SQL yourself or connect to your database and Add the tables with the required fields in the report then simply drop the field onto the detail section in that order.

What you may want to do is download these sample reports to see what you can do with Groups and other parts of reprot designing:

https://smpdl.sap-ag.de/~sapidp/012002523100005852352008E/cr_xi_xtreme_rep_smpl_en.zip

Thank you

Don

[Former Member]

Bdecker,

It's not really clear what you are wanting to do here.

Are you wanting to add the data to a table in a database? If so, CR is NOT the tool for the job. Check the appropriate database vendor forum to find out how to do a bulk import from a text file.

Or, are you wanting to use the flat file as the data source for a report? If so, try using Access/Excel (DAO) connection type and choose "Text" as the Database Type.

Either way, the "Add Command" feature is going to be of no benefit to you. If your idea is to use a Command to write the flat file data back to a database... Think of that as building a boat out of bricks... It might be possible. The laws of physics may allow it. But... It's certainly not supported or recommended and there are far better ways to insert data into a database. Basically, unless you're trying to win a bar bet, don't bother with it.

HTH,

Jason

[former_member11696]

Hi Bdecker,

I suggest not using CR to create tables. Use either ODBC to Excel or a database tool that created these flat files... It can be done but not recommended....

What is the output database going to be the destination for the SQL?

Thank you

Don