Evaluate all records to create a value for each record

[allyson1]

Hi, I am fairly new to creating sql commands. I am a user of Crystal Reports and have hit a 'brick-wall' with a value I am trying to generate and export, and have found that CR is not able to do what I need. I was wondering if a SQL statement could be created for what I need, then added to my Crystal Report.

I have a number of tables that when joined, generate the following recordset:

Record Type Number
1 B 100
2 B 106
3 R 102
4 S 105
5 R 104
6 S 105
7 R 108
8 B 106
9 S 107
10 B 106

What I need is a fourth column to generate the following:

Record Type Number Group
1 B 100 1.1
2 B 106 1.2
3 R 102 2.1
4 S 105 3.1
5 R 104 2.2
6 S 105 3.1
7 R 108 2.3
8 B 106 1.2
9 S 107 3.2
10 B 106 1.2

The column is based on a combination of type and number.

Can SQL do this?
Many thanks for any help.

 

[MichaelRed]

so, why is the new column needed? it 'looks like' you gain no ne information, just apending a new character to existing information?



MichaelRed

 

[allyson1]

The new column will be used in a crosstab in Crystal Reports. The data needs to be grouped by the combination of the two fields (Type and Number), but displayed as Group field (my new column). In Crystal Reports, I can actually generate this value by using Variables in the display, but I need to be able to export the crosstab to csv, and the Variable in the display does not export to csv (only the original value does).
So I thought if I can either create a sql view, or create a sql Command in CR then I may be able to generate the new column that can be used directly into the crosstab. Then my export to csv will work.

 

[RiverGuy]

How do you know B = 1, R = 2 and S = 3?

 

[allyson1]

The GROUP is based on TYPE and NUMBER combination. The first combination is B100 so it becomes 1.1, so all B's will start with 1. The second combination is B106 so it gets 1.2 because the NUMBER is unique. The next combination is R102. The TYPE is new so it gets a new first number, so now all R's will start with 2.

The recordset in this table will change each time the report is run as there are a number of parameters.

I really dont know what path to go down, whether it be sql view, sql command in CR, or even a stored procedure, but CR can connect to all these to run the report.

Many thanks.

 

[RyanEK]

Hi... try this solution?

declare @temp table (Record int, Type varchar(1), Number int)
insert into @temp values (1,'B',100)
insert into @temp values (2,'B',106)
insert into @temp values (3,'R',102)
insert into @temp values (4,'S',105)
insert into @temp values (5,'R',104)
insert into @temp values (6,'S',105)
insert into @temp values (7,'R',108)
insert into @temp values (8,'B',106)
insert into @temp values (9,'S',107)
insert into @temp values (10,'B',106)

select t1.*, 
t3.idx + cast(row_number() over (partition by t3.type order by t3.type) as float)/10 as Value
from @temp t1
join (
 select row_number() over (order by type) as idx, type
 from ( 
   select distinct (type) 
   from @temp 
 ) as t2
) as t3 on t3.type = t1.type

Ryan

 

[allyson1]

I'm sorry, I'm not sure if I may have led you astray a little. My example is just that, an example. My actual recordset is about 200 records, and the records will change depending on a parameter value.

 

[RyanEK]

Ahh... umm i'm not sure what you mean. My solution should work with a given recordset. Just replace the @temp table in my query with your results table.

 

[MichaelRed]

so, why does a (double) grouping on type then number work?






MichaelRed

 

[allyson1]

Sorry, I don't really understand your question. The new GROUP field is based on the combination of the two fields TYPE and NUMBER. It has to be that way, because the NUMBER value could be repeated but to a different TYPE. So I need to create this new GROUP field based on the two fields concatenated.

 

[allyson1]

Hi RyanEK, I'm not sure where to test out your code? I have a view that contains my recordset, do I just try your code from the select statement?

 

[MichaelRed]

most database 'reporting' software (all that I use anyway) have multiple levels of sorting and grouping available. These override any sorting / ordering of the recordsets on which the repor is based (making it not just unnecessary but actually wasteful to use sorting in the recordset itself). Sorting with out any header or footer JUST sorts the recordset, while grouping (with at least a header or footer or both also GROUPS the recordset within the report. While you may leave as header &/or footer empty, you may also use it to display other information. Typically the header identifies the group while the footer includes aggregate iformation regarding the group.

from what you have posted, i do not understand why the (simple?) use of the header(s) footer(s) would not provide the functionality described w/o the generation of additional information. Particularly since the information is transient.

so. what did/do you not undestand?



MichaelRed

 

[allyson1]

Thanksi MichaelRed for your reply. You're correct with your 'grouping' thoughts. Maybe I'll give you a more overall picture. I have a recordset created from a number of tables, then narrowed based on a parameter (project) selection. This record set is then reported on using Crystal Reports Version 10. I am using a crosstab to display columns (elements), rows (sample Id's) and summarized values (amount. The report is then exported to CSV and this file is sent back to the customer for importing into their system. I must use a crosstab and I must export to csv so that limits some options in crystal reports. What I need to change is the row descriptions. At the moment, using my original sample, I will have the following rows in the crosstab:

B100
B106
R102
S105
R104
R108
S107

I have previously tried using variables in my crystal report (see thread767-1544950: Group numbering shown in original order)but despite getting very close to exactly what I needed, crystal reports does not export the formula on a 'Display String' when exporting to csv; only the original row value exported.

So what I need is to generate the values for this example so that CR can display the new field in the row. My rows would then look like:

1.1
1.2
2.1
3.1
2.2
2.3
3.2

I hope you are able to understand my dilemma. Many thanks in advance.

 

[MichaelRed]

so, you could make a table recordset from the query which produces the generated values ...

MichaelRed

 

[allyson1]

yes, that would be great, but I dont know how to.

 

[MichaelRed]

Create Table is a standard SQL Server function. Since you are using a crosstab query (therefore the column count/designations for each instantation), one approach would be to drop the table after each use. If you were doing this, then you need to get the column names and data types from the soruce recordset, create the table, and populate it.

An update query could then be used aginst the soure to populate the newly created table?




MichaelRed

 

[ergen]

or maybe something like that?

declare @temp table (Record int, Type varchar(1), Number int)
insert into @temp values (1,'B',100)
insert into @temp values (2,'B',106)
insert into @temp values (3,'R',102)
insert into @temp values (4,'S',105)
insert into @temp values (5,'R',104)
insert into @temp values (6,'S',105)
insert into @temp values (7,'R',108)
insert into @temp values (8,'B',106)
insert into @temp values (9,'S',107)
insert into @temp values (10,'B',106);


select t1.*, cast(DENSE_RANK () OVER ( order by Type) as varchar(max))+'.'+
cast(DENSE_RANK() OVER (partition by type order by Number,Record) as varchar(max))
from @temp t1
order by Type

Jacek Szarapa