Suppress already read records

[manishsawant]

Hi,

I want to take sum of a field for a group and if the sum is less than 100 then i have to suppress all the records for the group. Now my problem is how can i suppress records which the report has already read.

Please suggest, i have to do this without subreport.

Thanks,
Manish

 

[kchaudhry]

Manish,

Suppressing is only hiding the fields the report will still read the records. Here is what you can do.

I am assuming that you already have the summary field in your report. Right click on the field you would like to suppress > Format Field > Common > x+2 next to suppress and enter something like this:

sum ({table.field}) < 100.00

Save this and refresh your report. Hopefully this will do what you are looking for.


Kchaudhry

 

[lbass]

To suppress all groups with sums less than 100, go to the section expert (format->section)->Group Header->suppress->x+2 and enter:

sum({table.amt},{table.yourgrpfield}) < 100

Then repeat for the details and group footer section.

Alternatively, you could go to report->edit selection formula->GROUP and enter:

sum({table.amt},{table.yourgrpfield}) >= 100

This will select only those groups which meet your criterion. In either case you would need to use running totals for calculations based on displayed data, since suppressed records and non-group-selected records contribute to the more usual summaries. In the case of suppressed records, you would need to add the opposite of the suppression formula into the "evaluate based on a formula" section of the running total (assuming you are using the running total editor).

-LB

 

[manishsawant]

Hi,

Thanks guys that worked perfectly, but i have one more defect in the report which is the query is giving me duplicate records and i want to suppress the duplicate records. The order of duplication is inconsistent. There is a stateId and a performanceDesc fields which are repeating. I have grouped by stateId then too its duplicating. I need to suppress this some how. This duplication is not happening for all the records.

I would be greatful if u can help me in this.

Thanks,
Manish

 

[kchaudhry]

Do you have the "select distinct records" option checked? This should remove the true duplicates from your report.

Kchaudhry

 

[lbass]

If you are still getting duplicate records, please provide a sample of your data that shows how the records are duplicating, and provide a little more information about your report structure.

-LB

 

[manishsawant]

Hi guys,

I have just found out that the records are not duplicate in the sence they look like this.

StateId StateDesc CoreId
AZMA-1.1 XYZ CSMAOK-1.1
AZMA-1.2 XYZ1 CSMAOK-1.2
AZMA-1.1 XYZ CSMAOK-1.3
AZMA-1.4 XYZ2 CSMAOK-1.4

In this case the coreId is changing and there is a duplicate StateId and StateDesc. I want to display something like.


StateId StateDesc CoreId
AZMA-1.1 XYZ CSMAOK-1.1
CSMAOK-1.3
AZMA-1.2 XYZ1 CSMAOK-1.2
AZMA-1.4 XYZ2 CSMAOK-1.4

Let me know if this is possible. I have tried all kinds of grouping but didnt happen for me.

Thanks again,
Manish

 

[lbass]

To get this display, sort on {table.stateID} (report->sort records) and then right click on {table.stateID} and {table.statedesc}->format field->common->check "Suppress if duplicated." I think that should do it, but let us know if you continue to have problems. Again, more sample data would be helpful if that is the case.

-LB

 

[manishsawant]

Hi,

I am really very sorry for the late reply. That problem was fixed by modifying the query. But it has lead to a different problem. I am just tired of working on it. The problem is like.

Skills Status %
AP 3.25 Mastered 100%
AP 3.25 Mastered 100%
PR 0.90 In progress 10%
NC 2.60 Not Mastered 20%
DC 1.90 Mastered 100%

In this case i have records for AP repeating. I can do suppress if duplicate and it will suppress the duplicate skills. Similarly i can do it for Status and % as well. But the problem with Status and % is that it will suppress the status and % for DC 1.90 as well which is not duplicate.

I am really confused about this and i hope that you guys can give me a little hint about this.

Thanks,
Manish

 

[lbass]

In your example, applying "suppress if duplicated" would result in:

Skills Status %
AP 3.25 Mastered 100%
PR 0.90 In progress 10%
NC 2.60 Not Mastered 20%
DC 1.90 Mastered 100%

If you sorted your example first by the first column and you used "suppress if duplicated", you would get:

Skills Status %
AP 3.25 Mastered 100%
DC 1.90
NC 2.60 Not Mastered 20%
PR 0.90 In progress 10%

To get the correct display, select all fields you want to suppress if duplicated, and then in the x+2 box next to "suppress if duplicated", add:

{table.firstfield} = previous({table.firstfield})//plug in the name of your first field

This should give you:

Skills Status %
AP 3.25 Mastered 100%
DC 1.90 Mastered 100%
NC 2.60 Not Mastered 20%
PR 0.90 In progress 10%

-LB

 

[manishsawant]

Hi LB,

Thanks for the reply again and it was really helpful. But it can also happen that i have something like

Skills Status %
AP 3.25 Mastered 100%
AP 3.25 Mastered 100%
AP 3.25 Mastered 85%
PR 0.90 In progress 10%
NC 2.60 Not Mastered 20%
DC 1.90 Mastered 100%

Now in this case the third record is not duplicate so it shouldnt be suppressed.

Thanks,
Manish

 

[lbass]

You could concatenate the fields that you want suppressed if they repeat:

//{@concat}:
{table.skills}+{table.status}+{@percent}

...and then use a suppression formula like:

{@concat} = previous({@concat})

-LB

 

[manishsawant]

Hi,

Thanks again. You mean instead of 3 seperate formulas i have to use a single formula concatinating the fields. There are headings about it and so i need to use proper proportionate space between the fields. Ok let me try this and i will get back in case of any issues.

Regards,
Manish

 

[lbass]

No, I meant for you to create a formula like this ONLY to use for the purpose of suppression, not for display.

-LB

 

[manishsawant]

Hi LB,

Thanks for that suggession but there is another addition to the suppression list. I am sorry that i reply late as i have to look into some other functionalities as well. Now the scenario has become like

I have 3 sections

Group1
Group2
Detail

In detail i get what you already know.

Skills Status %
AP 3.25 Mastered 100%
AP 3.25 Mastered 100%
AP 3.25 Mastered 85%
PR 0.90 In progress 10%
NC 2.60 Not Mastered 20%
DC 1.90 Mastered 100%

It can be like

Skills Status %
AP 3.25 Off 100%
AP 3.25 Off 100%
AP 3.25 Off 85%
PR 0.90 Off 10%
NC 2.60 Off 20%
DC 1.90 Mastered 100%

We can turn off the skills. I have written a big formula for displaying the status checking various conditions. Status formula is calling Off formula for displaying 'Off'. Now What i want is when all are 'Off' then i have to suppress all the 3 sections else if there is something like the about example then i dont have to suppress. I dont know how to count the 'Off' in the group sections for making it suppressed.

Please help.

Thanks
Manish

 

[lbass]

You haven't told us what fields you are grouping on, so I'll respond in a general way. Create a formula {@off} and place it in the details section:

if {@yourformula} = "Off" then 0 else 1

Then go to the section expert->highlight the Group Header->suppress->x+2 and enter:

sum({@off},{table.yourgroupfield}) = 0

Repeat for the Group Footer and Details section.

-LB

 

[manishsawant]

Hi,

I tried your logic now and when i put sum({@off},{table.yourgroupfield}) = 0 in my section expert--> suppress, I get a error "Summary/running total cannot be created". Has it to do anything about the version. I am using crystal 8.0.

Can i use a formula field inside a sum()?

Thanks,
Manish

 

[lbass]

Yes, you can, but I think the problem must be with your formula that gives you the result "Off", so you need to show us the contents of that formula.

-LB

 

[manishsawant]

Hi LB,

I was referencing a wrong formula so it gave me the error. Its working fine now. I have 2 formulas one is status and one is statusOff. From status i am accessing statusOff. In the sum i used status instead of statusOff and so may be it might have created a circular reference.

It was a great help from you.

I have one more issue. I want to use a relative path for the logo in my report. Now is just a static image on the report. The problem is we have around 100 reports and when we have a new version of the application we need to change the logo. In that case i have to go in each and every report and change the logo.

Do i need to post a new thread for this.

Regards,
Manish

 

[lbass]

Yes, if after doing a keyword search you cannot find the answer to your question in earlier threads, please start a new thread.

-LB