Data Mapping Database Report

I am working on a data mapping project (i.e. mapping tables from a SQL server database to VSAM files on a mainframe). I know that's bass-ackwards .. but that's what "the man" wants. And what "the man" wants he gets :)

What i am trying to do is to produce one report for each table (in the SQL server database). Each report would contain one column per field-name. Along with the field-name, i would like to also include the 10 most popular values of that field, a count for each, and one key per value (to refer-back).

If anyone is following along, do you have any suggestions ?? I'm assuming Crystal MUST have this functionality, but i'm banging my head against a wall trying to figure out even where to begin.

Thanks in advance !!The first part is easy - I presume you can use the report expert to get the raw data from the SQL table and show that as columns in the report. Am I right?

If you can do that, then you want to produce an individual count for each field - do you mean a count of unique elements for each field, and then the topN for each field.

Here's some sample data, by way of clarification:
ID Name City Salary
1 John A 20000
2 Fred A 20000
3 Mary A 50000
4 Kim B 35000
5 **** C 33000

A = 3 90000
B = 1 35000
C = 1 33000

If that is a very simple example, then you need to create a group for each field that you want to summarise, in this case City, and then create Count field in groop footer for City, and Sum field for Salary.

There's a start. TopN side of things should be able to be achieved using the TopN/Group Sort expert.

If you havew lots of fields, you'll have lots of groups.......my example groups on only City, so is simple, but your could end up looking rather ugly, I think!

Dave|||Hey Dave,

You're on the right track, but what i wanna do is "flip" the report around a little. Let's say i have a Customer table ... with SSN, Name, Addr.

I want my report to contain ONE ROW for each field-value ... not one column. My columns would be the Field-Name, Most Popular Occurence, Refer-Back key, and Count.

I would want my report to look like ...

Field Most popular value 1 Key (to refer back) Count (for that value)

SSN 111111111 111111111 1
222222222 222222222 1
333333333 333333333 1
...
123459699 123459699 1

Name John Smith 243050630 24
Dave Smith 294848372 22
Bob Jones 249858382 21
...
Tom Brown 385838375 11

Addr 123 Main St 948473859 6
123 Main Dr 938584894 6
222 Main St 983583959 4
...
123 Broadway 958347583 2

Thanks for your input !

I know that's ugly. My fields are lined up in my message text, but all of the spacing is ignored when the message is saved.

I want one row per field-name, and then 10 Values, refer-backs, and counts for each field name.

Data Load Query

Hi,

I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.

The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.

Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

i.e.

12344,Mr Agent,$29.95,2,BX123,BX124
12345,Mr Jones,$14.95,3,XX123,XX124,XX125
12345,Mr Jones,$14.00,1,XY123
12345,Mr Jones,$15.95,2,XZ124,XZ125
12346,Mr Smith,$19.95,3,AX123,AX124,AX125
12346,Mr Smith,$19.00,1,BY123
12347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129

There may be up to 3 records created for each Account Number with
different values in the array fields.

Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?

i.e.

File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated

File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

File 1
========================
12344,Mr Agent,$29.95,2
12345,Mr Jones,$14.95,3
12345,Mr Jones,$14.00,1
12345,Mr Jones,$15.95,2
12346,Mr Smith,$19.95,3
12346,Mr Smith,$19.00,1
12347,Mr Acant,$99.95,7

File 2
========================
12344,BX123
12344,BX124
12345,XX123
12345,XX124
12345,XX125
12345,XY123
12345,XZ124
12345,XZ125
12346,AX123
12346,AX124
12346,AX125
12346,BY123
12347,CX123
12347,CX124
12347,CX125
12347,CX126
12347,CX127
12347,CX128
12347,CX129

At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.

I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.

Any help that could be suggested would be greatly appreciated.For a varying number of fields per record, you might consider XML, as
it's a good format for that kind of data. But I have to say that I have
very limited experience of importing XML data into MSSQL myself - check
out OPENXML in Books Online, the SQLXML tools from Microsoft (which
include an XML bulk load COM component), and you could also post in
microsoft.public.sqlserver.xml to get some feedback on that approach.

Simon