Difficult Report Requirement

[pookeybear]

I have two SQL tables with most of the report fields on table one. These comprise the rows. The client wants the test name field from table two to display as column headers and the appropriate test results from table two, matched up by datetime stamps found on both tables, to appear under the appropriate column in the appropriate row. The cross tab report almost gets me there, but I have no field to summarize and don't want all the total fields and their layouts at the bottom of the report. Any ideas? I can send a spreadsheet with a better description of the fields/tables if needed.

 

[Guest_imported]

One method might be to build the report around a SQL statement that uses a GROUP BY clause so that the summary is accomplished on the server. To do this you would need to use an ADO datasource, a Crystal Query or a SQL view.
This would give you the cross tab summary you are looking for. If you still need more info you can join the primary table back to the summarized result set.

SELECT
Col1,
Col2,
Col3

FROM T1 JOIN T2 ON T1.col1 = T2.col1

WHERE ....

GROUP BY
Col1,
Col2,
Col3

ORDER BY
Col1,
Col2,
Col3

Another method would be to skip Crystal in this case and use MS Excel with a Pivot table.

If neither of these are appropriate, please add the additional details/requirements of your report

 

[synapsevampire]

You can turn off totals in a cross tab if that's the only problem.

Right click the cross tab and select Format Crosstab.

Select customize style.

Select the boxes for Suppress Column and Row Grand Totals.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[pookeybear]

Thanks! I'll try those two solutions and get back to you if I still don't get the results I need.

 

[pookeybear]

Okay; tried the cross-tab solution. It shuts off the report totals and groups totals, but not the summarizing of the test values. I want to print the actual values, not a count of the occurances of those values.
Column Names:
Program Part StartDate TestNameA** TestNameB Etc.
Row Values:
Progname PartNo Datetime1 Testresult TestResult

**(Here is where a field becomes a column header each time the name of the test run is changed)

Any other ideas?

 

[synapsevampire]

Perhaps you should send along the table descriptions, some sample data, and a mock up of what the output would be.

Another thought on a cross tab is that if you only have one data element per row and column, change the summary to the Nth most frequent and use 1.

I avoid cross-tabs whenever possible because I have less control over them, but it sounded like you *almost* had what you needed, hence my initial response.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[pookeybear]

I've sent you the tables and a sample pivot report via email to the address at the bottom of your post. Let me know if you have trouble reading/receiving it. Thanks.