Print Page | Close Window

Crystal reports Layout- Unable to group,sort

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5902
Printed Date: 03 May 2024 at 4:27am


Topic: Crystal reports Layout- Unable to group,sort
Posted By: namitanamburi
Subject: Crystal reports Layout- Unable to group,sort
Date Posted: 26 Mar 2009 at 7:44am

I want to create a report in Crystal Reports 10.

 

I have already created this is Oracle reports 10g using the Group left layout and I want to recreate same layout in Crystal.

 

Issue 1: Scenario is one employee John is both a Chef and Waiter , his User Id is Unique and his status can be different as per his role.In the sense he can have multiple roles, he can be a chef and a waiter and he both either active as chef and inactive as Waiter or versa or can be active as both chef or waiter or Inactive as both chef and waiter. No restriction on this.

 

But when I create this in Crystal , the User id  and name email address are always repeating. Is there any way you can tell me how to group this in Crystal like I did in Oracle reports 10g.In Oracle 10g I used wizard and chose Group left report style which gave me desired output as I grouped them with Role User Id then name, email address and then role.

 

I scanned through all built-ins in Crystal like the Group Expert but nothing worked out. The group expert gives me an output 3 shown below.

 

Question 1)

 

Can anyone tell me is there any way to group or do something which will get me my Required layout.

 

 

Question 2)

 

Can we write a sql query and generate report in Crystal like we do in Oracle Reports? Or is it like we have to design everything and then only we can see query but cannot write query before designing it.

 

 

 

Required :

 

 

 

UserId  Name   Email address   Role       Status

 

A         John     http://us.mc5.mail.yahoo.com/mc/compose?to=John@al.com - Obtained in Crystal Reports :

 

 

                                                

User Id Name   Email address   Role     Status

 

A         John     http://us.mc5.mail.yahoo.com/mc/compose?to=John@al.com - John@al.com    Chef     Active

 

A         John     http://us.mc5.mail.yahoo.com/mc/compose?to=John@al.com - John@al.com    Waiter  Inactive

 

B          Sam     http://us.mc5.mail.yahoo.com/mc/compose?to=Sam@al.com - Sam@al.com     Chef     Inactive

 

C         Jim       http://us.mc5.mail.yahoo.com/mc/compose?to=Jim@al.com - Jim@al.com      Waiter Inactive

 

C         Jim       http://us.mc5.mail.yahoo.com/mc/compose?to=Jim@al.com - Jim@al.com      Bartender Active

 

D         Ted      http://us.mc5.mail.yahoo.com/mc/compose?to=Ted@al.com - Ted@al.com     Waiter  Inactive

 

D         Ted      http://us.mc5.mail.yahoo.com/mc/compose?to=Ted@al.com - Ted@al.com     Supervisor Active

 

 

 

Obtained with Group Expert in Crystal and chose Role as grouping field :

 

 

                                                

User Id Name   Email address   Role     Status

 

Chef

                                                

A         John     http://us.mc5.mail.yahoo.com/mc/compose?to=John@al.com - John@al.com    Chef     Active

B          Sam     http://us.mc5.mail.yahoo.com/mc/compose?to=Sam@al.com - Sam@al.com     Chef     Inactive

 

Waiter 

                                    

A         John     http://us.mc5.mail.yahoo.com/mc/compose?to=John@al.com - John@al.com    Waiter  Inactive

C         Jim       http://us.mc5.mail.yahoo.com/mc/compose?to=Jim@al.com - Jim@al.com      Waiter Inactive

 


Replies:
Posted By: DBlank
Date Posted: 26 Mar 2009 at 8:55am
Group1=USer ID (Suppressed)
Details: User ID, Name, Email address, Role Status
 
In UserID, Name and Email Address go into Field Format and Common Tab and select the Suppress if Duplicated.
This will suppress the field if the previous rows record field is the same as the current rows record field.
If you run into problems where the name from one group is the same as the name from the next group and it is suppressing it just create a formula that is the ID and the Name together in the same field and display /suppress duplicate on that formula field since it should be unique.

Posted By: namitanamburi
Date Posted: 26 Mar 2009 at 12:26pm

Thanks for reply,can you help me find the option SUPPRESS WHEN DUPLICATED

Posted By: DBlank
Date Posted: 26 Mar 2009 at 12:50pm
I believe this option is in v10.
Right click on the field and select the Format Field option.
The Format Editor window will open.
Click on the Common Tab.
There is a Suppress if Duplicated option in the upper portion of the window.
That is what I was referring to.

Posted By: namitanamburi
Date Posted: 30 Mar 2009 at 10:37pm




Hello,


Thanks for the reply and the prediction. Iam really sorry for posing such silly qns but being a novice in Crystal reports its reallY taking a while to crack out petty issues.

with your help I finally suppressed the duplicating values of User Id and Name and Email address.

Now I want a report like the one below.


UserId    Name     Email address   Role        Speaciality

A         John     John@al.com      Chef        Indian
                                                Chinese                 
                                    
                                    Waiter      Serving
                                                Taking Orders

B          Sam     Sam@al.com       Chef        Mexican
 
C         Jim      Jim@al.com       Waiter      Taking orders 
                                     
 
D         Ted      Ted@al.com       Chef        Mexican
                                                Thai
                                   
                                    Waiter      Serving


But Iam able to get something like this.

 
UserId    Name     Email address   Role      Speaciality

A         John     John@al.com      Chef        Indian
                                    Chef        Chinese                
                                    Chef        Thai
                                   
                                    Waiter      Serving
                                    Waiter      Taking Orders

B          Sam     Sam@al.com       Chef        Mexican
 
C         Jim      Jim@al.com       Waiter      Taking orders 
                                     
 
D         Ted      Ted@al.com       Chef        Mexican
                                    Chef        Thai
                                   
                                    Waiter      Serving



I cannot supress duplicate values for Role field AS i DID FOR uSERID,NAME,email address because then I shall get somethink like this.


UserId    Name     Email address   Role        Speaciality

A         John     John@al.com      Chef        Indian
                                                Chinese                
                                   
                                    Waiter      Serving
                                                Taking Orders

B          Sam     Sam@al.com                   Mexican
 
C         Jim      Jim@al.com                   Taking orders 
                                     
 
D         Ted      Ted@al.com                   Mexican
                                                Thai
                                   
                                                Serving


As you specualted I landed at this issue.

can you help me in writing a formula for this.I am not familiar wth formula syntax of Crystal,I guess this is different than SQL code. Also please let me know where do I need to write the formula.

Is it at Report - Selection formulas - Record?

         Report - Selection Formulas - Group?


Or should I write that formula at X-2 of selection expert?





Looking forward for your support.


Thanks in advance

Namita
 

 

Posted By: DBlank
Date Posted: 31 Mar 2009 at 6:42am

Instead of a suppress when duplicated use the suppress as a formula.

Right click on the field and select the Format Field option.
The Format Editor window will open.
Click on the Common Tab.
There is a Suppress option with an X-2 button on the right hand side.
Click on it and put a formual for suppression in there.
I believe you will want something like this for the role field:
previous (table.UserIDfield) =table.UserIDfield and previous (table.Rolefield)=table.Rolefield
and something like this for the Specialty field:
previous (table.UserIDfield) =table.UserIDfield and previous (table.Specialtyfield)=table.Specialtyfield
 
Replace the table with the actual table name and the field with the actual field names.
Note: Do not check the box for either suppress or suppress when duplicated. The formula you put in activates the suppression when the coniditions are met. You do not want it activated all the time.

Posted By: namitanamburi
Date Posted: 31 Mar 2009 at 7:26am
Hey,

Thanks for the reply.

I did try using the formula in the x-2 beside supress , but when I try to check that formula, (Alt+C) there is an error.


It says

The ) is missing

I checked that I have closed and opened all parenthesis properly, did try couple of trial and error methods by keying in few more paranthesis. But did not paid off.


Thanks in advance
Namita

Posted By: DBlank
Date Posted: 31 Mar 2009 at 8:51am
Can you post the formula that you put in and got the error on?
Also make sure you use the brackets around the table objects (which I did not put in the original post ) like so:
 
previous({TableName.UserIDfield})={TableName.UserIDfield} and previous({TableName.Rolefield})={TableName.Rolefield}

Posted By: namitanamburi
Date Posted: 31 Mar 2009 at 1:10pm
Please see below

Posted By: namitanamburi
Date Posted: 31 Mar 2009 at 1:11pm
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 10">< name="Originator" content="Microsoft Word 10"> file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - <>
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Hi,

file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - I was trying to review if I have phrased everything clearly.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - I guess I made few errors, so let me recreate the whole scenario once again.(I told one person can be active in 2 job titles which is not true, so lets rephrase everything)
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - These are the tables Iam working with
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -            
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Table1              Table2             Table3          Table12          Table23
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                                
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Tnum                     Jnum               Snum         tnumber      Snumber             
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Login_Id             Job_title       Speciality       jobnumber     jobnumber
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - First_name                                          
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Last_name                                           
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Email_address                                     
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Status                                      
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                                
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - This is the query I wrote in  Add command line.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Select t1.First_Name || ' ' || t1.Last_Name as Name,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                 t1.Login_Id as User_Id,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                 t1.Email_address,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                 t1.Status,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                 t2.Job_title,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                                 t3.Speciality
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                        from table1 t1,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                             table2 t2,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                             table3 t3,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                             table12 t12,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -                             table23 t23
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - where  t12.tnumber = t1.Tnum
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -    and t12.jobnumber =t2.Jnum
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -    and t3.Snum = t23.Snumber
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -    and t2.Jnum=t23.jobnumber
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - order by t1.Login_id,t2.job_title,t3.speciality
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  My target report layout :
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - I want to create a report which has to look like this (ignore cells, I copied from excel so it looks like that).Please focus on Layout
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
UserId
Name
Email address
Status
Job Title
Speciality
 
 
 
 
 
 
 
 
A
John Lee
      John@al.com
Active
Chef
Thai
 
 
 
 
 
 
Indian
 
 
 
 
 
Waiter
Cleaning
 
 
 
 
 
 
 
 
B
Sam Watson
Sam@al.com
  Inactive
Waiter
Taking Orders
 
 
 
 
 
 
Cleaning
 
 
 
 
 
  Bartender
Cocktails
 
 
 
 
 
 
 
 
C
Tim Lopez
Tim@al.com
  Inactive
Chef
Indian
 
 
 
 
 
Waiter
Cleaning
 
 
 
 
 
 
Taking Orders
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Note : Status is status of User. Please donot confuse yourself by seeing Sam as Inactive yet his roles as waiter and Bartender. He might have been assigned these roles earlier and probably he might have been laid off.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - So to attain the report above expanded my command in the command of fields in field explorer and then created a report like below by dragging the required fields to the design tab.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - I achieved the table below by suppressing user id, name, email address
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
UserId
Name
Email address
Status
Job Title
Speciality
 
 
 
 
 
 
 
 
A
John Lee
      John@al.com
Active
Chef
Thai
 
 
 
 
Active
Chef
Indian
 
 
 
 
Active
Waiter
Cleaning
 
 
 
 
 
 
 
 
B
Sam Watson
Sam@al.com
  Inactive
Waiter
Taking Orders
 
 
 
 
  Inactive
Waiter
Cleaning
 
 
 
 
  Inactive
  Bartender
Cocktails
 
 
 
 
 
 
 
 
C
Tim Lopez
Tim@al.com
  Inactive
Chef
Indian
 
 
 
 
  Inactive
Waiter
Cleaning
 
 
 
 
  Inactive
Waiter
Taking Orders
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Now I have two issues.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Issue 1)Status Suppression
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Issue 2 ) Job title suppression
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  I donot want to suppress duplicate values in Status, Job_title, the reason is it will not display inactive for Tim . Same issue with Job title.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - So I tried to write a formula like this at X-2 beside the suppress of command tab in the field editor of Status and Job_title field format like below respectively.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({table1.login_id})={table1.login_id}and Previous({Table1.status})={Table2.status}
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - It gave following errors.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  It highlights
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({table1.login_id})={table1.login_id}and Previous({Table1.status})={Table2.status}
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - And says
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - The field name is not known.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - So I replaced syntax with
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({table1.User_Id})={table1.User_Id}and Previous({Table1.status})={Table1.status}
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  But no luck,
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - I tried similar formula for role but same error repeats.

file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({table1.login_id})={table1.login_id} and previous({Table2.job_title})={Table2.job_title}
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  

Also I did uncheck both suppress and suppress duplicate values before I wrote this formula.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Hope this info helps.
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Thanks in advance
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Namita
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -  
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml -

Posted By: DBlank
Date Posted: 31 Mar 2009 at 3:36pm
try:
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({t1.login_id})={t1.login_id} and Previous({t1.status})={t1.status}

Posted By: namitanamburi
Date Posted: 01 Apr 2009 at 8:53am
Hey,

It didnot help,

I have same error again.

The field name is not known.

This is driving me crazy,when I surfed online for errors relating to these, they suggested to check if case of fields is same as that in database. even tried that but nothing worked out.


Thanks
Namita

Posted By: DBlank
Date Posted: 01 Apr 2009 at 9:12am
Sorry,
did not clearly read your statement and see that you renamed not only the table but also the field. Before i repost the updated version i want to explain what we are doing here so you can problem solve.
Basically what you are doin here is checking one detail record against the previous detail record to see if multiple items on that row are matched and then conditionally suppressing them. You are evaulating the same table and field since these are the same on each row. In your design view you should be able to see the table and field names for each of the fields that you want to check against. (I assume you only have a details A and did not add any extra Detail sections).
Your condition is that if the the UserID field is the same on record as the previous row and then if the status or job title or speciality is the same as the previous row
UserId
Name
Email address
Status
Job Title
Speciality
 
T1 
T1 
 T1
 t1
t2 
 t3
 
A
John Lee
      John@al.com
Active
Chef
Thai
 
 
 
 
 
 
Indian
 
 
Given the above you want to make sure each of the fields and tables per row are what we are evaluating against. I think the below is corect but hopefully you can check each of these field in the design view and try it.
For Status suppress  using:
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({t1.User_Id})={t1.User_Id} and Previous({t1.status})={t1.status}
 
For Job title:
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({t1.User_Id})={t1.User_Id} and Previous({t2.Job_Title})={t2.Job_Title}
 
For Job title:
file:///C:%5CDOCUME%7E1%5Crevenue%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml - Previous({t1.User_Id})={t1.User_Id} and Previous({t3.Speciality})={t3.Speciality}

Posted By: namitanamburi
Date Posted: 01 Apr 2009 at 2:45pm
< ="Content-" content="text/; charset=utf-8">< name="ProgId" content="Word.">< name="Generator" content="Microsoft Word 10">< name="Originator" content="Microsoft Word 10"><>

Hello,

 

I finally cracked it down.

 

I am 95% positive that the method I have used can be the right method (hope this method does not land me in another issue :) )

 

There were few reports that were already created by my seniors and I was finally able to grab one report whose layout partially matched to the layout I desperately needed. So I scanned the report meticulously and found a solution.

 

I was able to get a report like this after checking Suppress all duplicates for User_id, name, and email address.

 

I was unable to do the same for Status and Role.

 

So I was struck at this layout.

 

 

 

 

What did I do?

 

1)I checked suppress all duplicates for status and job title fields also.

 

 

2)Then,  I opened the Group Expert and grouped by User_id.

 

 

3) Later I opened record Sort expert and sorted it by Group#1 user_id and then Description. It finally worked.

 

 

Finally my layout looks like this.

 

 

I verified manually against the data and it is displaying records with the right logic.

 

I tried using your formula but for some reason it was repeatingly displaying the same error. I was unable to figure out a way that could crack this error out.

 

“The field name is not known.”

 

 

So here I am unable to realize in what way can I thank you.

 

Issue 2:

 

Can you tell me if we need to lock the size and position of the report (.rpt) file before you upload into source control server/tool. (like PVCS or VSS).

 

 

Can you quickly navigate to all the next steps involved.

 

 

Like if you ask me to elaborate about oracle reports then I would say.

 

 

We initially design a .rdf file in windows development environment then we compile it to .rep file in Unix box which is our production server.

 

Before you explain just a quick note, we have java as user interface, so using JRC they will trigger the reports and make them viewable in a CRviewer which is also a part of Java API for Crystal reports.



Also for some reason though I paste the screen shots of my reports in PASTE FROM WORD ,it doesn't display anyone of them.



thanks

Namita

 

 

Posted By: DBlank
Date Posted: 01 Apr 2009 at 2:50pm
Sorry issue 1 never got resolved.
For issue 2 I do not know the answer.
I would repost that as a new thread to see if anyone else can assist.
Leave out everything else and just ask the second question giving your specs that way you did above.
Good luck.


Print Page | Close Window