Using command function in Crystal Reports (Visual Studio 2010)

[dax1]

Hello

I am quite new to SQL and CR but managing (to date) but have come across an issue caused by the structure of a table in which I need to generate a report. I am using CR through Visual Studio 2010. I am building a report that calls on a Table that has 4 columns (for ease I have named them C1 to C4)

So:
C1 = Identfies the unique object (Identifies the object within the group - See C4)
C2 = is the ID (3 digit code from 001 to 999) of the the field
C3 = Is the value related to the field (C2)
C4 = Is the document ID (5 digits) to which all the information is assigned

The problem I have is that the table is structred vertically so that for the same document (C4) you can have the same C2, C1 (and C3) so for example

C1 C2 C3 C4
10000 138 Good 23456
10000 159 New 23456
10000 222 22/06/2011 23456
10022 138 BAD 23456
10022 159 USED 23456
10022 222 29/08/2011 23456

So my issue is how can I convert the vertical format of the data and make it horizontal so it appears in the Crystal Report as


C1 C2_VALUE(138) C2_VALUE(159) C3_VALUE(222) C4
10000 Good NEW 22/06/2011 23456
10022 Bad USED 29/08/2011 23456

I know I have to use the Command function in CR but I really am having an issue getting the code to work so I can extract and layout the data as shown

Any suggestions would be greatly appreciated.


Thank you

Dan

 

[dax1]

Hello again

Just some additional information. I am running the CR off of a local SDF file (UDL link) as the report will have to work for users in the field running offline.

Also I have tried using the Cross-tab functionality with no success. From what I have read it seems that a Command (Add a command) function would be the best solution.

I hope this additional information is useful.

Thank you again

Dan

 

[hilfy]

A command is just a SQL Select statement. So, you're going to have to figure out how to "pivot" your data in the query. Is there a max number of rows that a given object can have? Will the ID's (C2 values) be the same for all documents? You will have to have a column for each possible C2 value.

If you have a limited number of values for C2, it might be easier to pull the table into the report multiple times with aliases and use filters to get the data.

1. Aliases - when you add a table to a report that already exists in the report, Crystal tell you it's already in the report and will ask you if you want to alias it. It will then add the table with "_1", "_2" etc. You can change these aliases and I would recommend using the table name with the individual C2 number for that copy of the table.

2. Linking - is there a single C2 value that exists for all objects? If so, this will be your "master" table. If not, you'll need one more copy of the table to be your master. You could also use a different table that has one record for each of the object ID's as your master. Left Outer join from the master table to each of the other aliased tables.

3. Filters - using the C2 values from your example above and my aliasing suggestion from step 1, here's what the filter would look like:

(IsNull({Table_138.C1}) or {Table_138.C2} = 138) and
(IsNull({Table_159.C1}) or {Table_159.C2} = 159) and
(IsNull({Table_222.C1}) or {Table_222.C2} = 222)

You have to do the IsNull check first just in case there is no record for that particular C2 value.

4. Report - Group by the object ID from the master table. Put your data in the details - one column for each aliased table.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

If you don't mind having the C4 column in a different position, you could handle this by inserting a crosstab in the report header or footer that uses C1 and C4 as rows, C2 as a column, and C3 as the summary field (use a maximum). In the customize style tab you can suppress the subtotals and the row and column totals.

-LB

 

[dax1]

Thank you both for the help, because it is a CE database I am running off it was harder for me to figure it out, but your recommendations got things to finally work

Dax