Need Help with a complicated (to me) query 1

[Amapuche]

Hello,

I need help with a query that I cant get to work or rather
dont know where to start anymore.

I have a data that looks somthing like this if I do a
simple select statement eg. SELECT animal, colour FROM tbl_animals

Animal Colour
------ ------
Cat Brown
Cat Red
Cat Orange
Cat Black
Cat White
Dog Black
Dog Red
Dog Orange
Dog White

but what I want the data to look like is this when I do a
query to output to a report. (crystal reports 8.5)

Animal Colours
------ --------------------------------
Cat Brown Red Orange Black White
Dog Black Red Orange White

Is this possible? and if so, how would I go about doing
this.

Thanks for listening

Mapuche

 

[Ngolem]

you don't need a special query for this

I do assume you are working in Crystal reports

you have a single table with no record select criteria I can see of...unless you have oversimplified your question.

you just form Group 1 on {Table.animal} and Group 2 on {Table.color}

In the page header you put the text labels for the columns

Animals Color
------- --------------------------------------

you suppress the Groups 1 & Groups 2 headers ,Detail section, and Group2 footer

in Group 1 header place an initialize formula

@initialize (Suppressed placed in Group 1 header)

WhilePrintingRecords;
stringVar AnimalColor := "";

In the detail section you put the formula

@StoreColors (suppressed)

WhilePrintingRecords;
stringVar AnimalColor := "";

//based on 10 chars per color you can have 25 colors/animal
//which should be fine.
AnimalColor := AnimalColor + {table.color} + " ";

In the Group 1 footer you display the results

@displayColors

WhilePrintingRecords;
stringVar AnimalColor ;

AnimalColor ;


so the fields would be placed like this in Group 1 footer

{Table.animal} {@displayColors}

that is all there is to that Jim Broadbent

 

[Amapuche]

Hi Jim,

Thank you very much for the reply, I must be doing something extremely wrong because I can not get this to work for me.I am using Crystal Reports 8.5

At the moment it is a very simple select statement, in reality I have a number of styles that come in multiple colours very similar to the example I gave except for 2500 records.

Its quite difficult to explain here what I did but I will try

1. I chose create File>New Report>As blank report
2. I selected the database, and database table.
3. I selected the fields I wanted to report on e.g. Style & Colour
4. I selected finish.
5. the report designer was presented to me
6. I selected Insert>Group and selected table.style and pressed ok (that is all, I didn't tick any of the checkboxes). This created Group 1
7. I selected Insert>Group and selected table.colour and pressed okay. This created Group 2
8. I added the text labels style colour in the page header.
9. I suppressed Group Header #1 Group Header #2, Details, Group Footer #2
10. I then created the formulas for @initialize, @StoreColour, @DisplayColour
11. I placed @initialise in the Group Header #1, @StoreColour in Details, style_colour_master.style and @DisplayColour in Group Footer #1
12. When I ran the report I got the following result. It only prints the last colour value of each style.
e.g
Style Colours
----- -------
AB935 T5
AB962 C3
AC325 S3

The grouping of the styles is exactly what I want, but the colours are not printing horizontally across.

formulas are pasted below for your reference.

I have exported a pdf, and created a gif of the report designer a total of 50 KB if you would like to see what I have done, if I have not made sense here let me know.

Thanks again for your help.

Regards,

Mapuche.

@initilize
WhilePrintingRecords;
stringVar StyleColour := "";

@StoreColour
WhilePrintingRecords;
stringVar StyleColour := "";

StyleColour := StyleColour + {style_colour_master.colour} + " ";

@DisplayColour
WhilePrintingRecords;
stringVar StyleColour ;

StyleColour ;

 

[Ngolem]

whoops...sorry...I made a mistake...(one of the problems of cloning code ^-^)

Everything you did is fine...just modify this formula

@StoreColour
WhilePrintingRecords;
stringVar StyleColour;

StyleColour := StyleColour + {style_colour_master.colour} + " ";


We were resetting the Style colour back to Null everytime we entered the formula....not what we wanted to do....sorry about that...it should work now.
Jim Broadbent

 

[Amapuche]

Hi Jim,

It works perfectly! Thank you very much

Regards,

Mapuche

 

[kenhamady]

faq149-243 also describes this technique. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com