Solved

Group name field display data horizontally.

Posted on 2004-04-28
7
636 Views
Last Modified: 2008-03-10
Hi,

I have a group name field (GenericMiscPrompt.GroupPrompt) in Crystal 9 that generates characteristics for trucks and displays as

Characteristics
Drivetrain     FT Axle     10,000
                   RR Axle     19,800
                   Brakes       Hyd

Is there a way to have this field display the data horizontally?
Such as:
Drivetrain     FT Axle     10,000     RR Axle     19,8000     Brakes     Hyd

I believe in access this was done with cross tabbing.
Any help would be greatly appreciated. Thanks.

lburg55
0
Comment
Question by:lburg55
  • 3
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 10941047
Are the details separate records?

You may be able to do this with a multiple column report.
Open the report
Click FORMAT --> FORMAT SECTION
Select the Detail section
Check - FORMAT WITH MULTIPLE COLUMNS
Click the LAYOUT tab
Setup the columns.
Ensure you select ACROSS then DOWN

Crystal does have cross tab reports and a wizard.  You might also try that.

mlmcc
0
 

Author Comment

by:lburg55
ID: 10942002
Yes, the details are separate records.
0
 
LVL 1

Expert Comment

by:sortascend
ID: 10962737
Are you wanting to report all Characteristics that are found for that Drivetrain, or specific ones that you know the characteristic type/id for (ftaxel, rtaxel, emptyweight, gross weight)?

If you want specific characteristics as columns, I've had best luck for performance and reusability by using SQL Expression. I created a oracle function that i pass the ID for the Truck and the Characteristic that i want to lookup, and i get back a single answer that can be dropped anywhere on the report, just like you are trying to do. You can do the same thing without the oracle function, by typing the sql in the SQL Expression.

If this will help you, i'll post the code samples .

SortAscend


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:lburg55
ID: 10964902
Yes, please post a sample of the code. That would be a great help.

Thanks!
0
 
LVL 1

Expert Comment

by:sortascend
ID: 10966527
I'm going to make 2 post. The first is a simple way doing the entire thing in Crystal Report. The second way is leveraging the power and reusability of oracle functions.

Overview
----------------
I have a Batch table which is a many table of Batches created by the business.

BatchID      
CreatedDate      
....

I have a Batch Characteristic setup table that defines 1000+ characteristics that can be used to record additional information about Batches

BatchCharacteristicID      
CharName
UnitofMeasure
...

I have Batch Characteristic Detail which is records that actual Batch Characteristic results for a Batch

BatchCharacteristicDetailID
BatchID
BatchCharacteristicID
ResultValue
...

----------------

In Crystal Reports create 2 SQL Expression. One for each column that i want.


%Size
Select max(characteristicvalue)
from batchcharacteristicdetail
where batchid = "BATCH"."BATCHID" and
Batchcharacteristicid= 44001

The Max() is required because you will get error if more than 1 row is returned.

%Volume
Select max(characteristicvalue)
from batchcharacteristicdetail
where batchid = "BATCH"."BATCHID" and
Batchcharacteristicid in (44004, 44354)

This one for Volume allows you to look up 1 or more Characteristic ID.
0
 
LVL 1

Accepted Solution

by:
sortascend earned 125 total points
ID: 10966658
1. But what if you want to do something like return the most recently updated Characteristic for the Batch from a list of possible Characteristic IDs. This feature is crucial since sometimes the information is recorded using different ID depending on the situation. The report needs to find the info no matter which ID is use and determine which result to show if more than one is found. (You could modify code below to concatenate all results found into a single varchar variable to return from Oracle to Crystal).
The TableofNumbers solves issue of being able to pass only 1 number as a parameter to function. The fnBatchChar() function adds more complex logic than the SQL Expression from the Crystal example i just posted.

2. And you want to reuse the code, since this is task is required frequently.

This solution and methodology came about as result of over 2 week of trying to come up with a reusable solution taking into account Crystal Reports limitations. I talked to a number of expert Oracle Developers but they were not use to thinking around the constraints generated by Crystal. I tried methods of using outerjoins and the (+)= oracle operator. But this required hardcoded views and i think it had problems if looking for more than 1 Char ID. I took some of the things i learned from them to develop this unique design.

I hope this works for you and that some other people might benefit from this. I should probably type up a simple explanation of the problem and design.
--------------------------------

SQL Expression
--------------
Note: when you add a Crystal Report field to a SQL Expression it puts "" around the table and field like this "table"."field".

%Size2
fnBatchChar("BATCH"."BATCHID", 44001)

This is more robust version of %Size from my last post.

%Volume2
fnBatchChar("BATCH"."BATCHID", '44008, 44354')

This is more robust version of %Volume from my last post.


Oracle Code
---------------
--Declare type TableofNumbers
create or replace type TableofNumbers as table of number;

--Convert a list of numbers receieved as a varchar array
--to a table of numbers. This is used by fnBatchChar below
--since you can't check for string of numbers using the IN clause
--without going through TableofNumbers function.
--Example: str2tbl('1,2,3')
--          Return
--               1
--               2
--               3
create or replace function str2tbl( p_str in varchar2 )
         return TableofNumbers
is
      l_str   long default p_str || ',';
      l_n        number;
      l_data    TableofNumbers := TableofNumbers();
      begin
            loop
                  l_n := instr( l_str, ',' );
                  exit when (nvl(l_n,0) = 0);
                  l_data.extend;
                  l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
                  l_str := substr( l_str, l_n+1 );
            end loop;
            return l_data;
      end;
/


--Find the Result of the most recently updated Batch Characteristic
--for a Batch from a list of Characteristic ID (number) to look for.
--Input list of Characteristic IDs as a string of numbers.
--Example: fnBatchChar(56845, 11) or fnBatchChar(56845, '11, 14, 54')
--The String of CharId is used because related information may go by
--different CharID depending on the type of Batch. This method will still
--allow for creating a single column for all batches included in the
--primary query.

CREATE OR REPLACE function fnBatchChar
      (fBatchID in number, fBatchCharIDs in varchar)
return varchar2
is
vCharValue varchar2 (60);

begin
      --Use Max() to return the most recently updated Characteristic from the list
      --of Characteristics types that are being searched for.
      select max(characteristicvalue)
      into vCharValue
      from (select characteristicvalue from BatchcharacteristicDetail
            where batchid = fbatchid and batchcharacteristicid in
                  ( select * from THE ( select cast(str2tbl(fBatchCharIDs) as TableofNumbers )
                              from dual )  )
            order by changeddate desc
            )
      where rownum <= 1;

      return vCharValue;
end;
/
create public  SYNONYM fnBatchChar FOR fnBatchChar;
/
GRANT execute ON fnBatchChar  TO HPREAD, HPAPP;
/
0
 

Author Comment

by:lburg55
ID: 10981086
Thanks! This has been a big help.

Lynne
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now