PDA

View Full Version : Report Filter


ABC123

Anis
04-08-2008, 03:15 PM
Can you filter a report based on a calculated field setup in that report?

I have a calculated field that show the percentage of the difference between 2 amounts, the amounts are grouped by total.

YESQT = total(Yespos->Si_Qty,grp->Group 1,grp->Detail)
TODQT = total(Todpos->Si_Qty,grp->Group 1,grp->Detail)
DIFF = calc->TODQT-calc->YESQT
percnt = abs(calc->Diff/calc->Yesqt)

I tried filtering the report based on PERCNT > 10
it doesn't generate any records, once I remove the filter it shows all records

Selwyn Rabins
04-08-2008, 03:26 PM
Can you filter a report based on a calculated field setup in that report?

I have a calculated field that show the percentage of the difference between 2 amounts, the amounts are grouped by total.

YESQT = total(Yespos->Si_Qty,grp->Group 1,grp->Detail)
TODQT = total(Todpos->Si_Qty,grp->Group 1,grp->Detail)
DIFF = calc->TODQT-calc->YESQT
percnt = abs(calc->Diff/calc->Yesqt)

I tried filtering the report based on PERCNT > 10
it doesn't generate any records, once I remove the filter it shows all records

alpha five does not support the use of calculated fields in report filters.

the easiest solution is to add a temp field to your table and then run xbasic code to set the value in the temp field. then use the temp field in your report filter.

Anis
04-08-2008, 03:39 PM
This report is based on a set of 3 files, I import 3 files daily from another system into new tables.... so that will make it complicated.

MoGrace
04-08-2008, 03:59 PM
Can you filter a report based on a calculated field setup in that report?

I have a calculated field that show the percentage of the difference between 2 amounts, the amounts are grouped by total.

YESQT = total(Yespos->Si_Qty,grp->Group 1,grp->Detail)
TODQT = total(Todpos->Si_Qty,grp->Group 1,grp->Detail)
DIFF = calc->TODQT-calc->YESQT
percnt = abs(calc->Diff/calc->Yesqt)

I tried filtering the report based on PERCNT > 10
it doesn't generate any records, once I remove the filter it shows all records
Hi Anis,
You could use your calculated field to set a conditional object in the detail section. use percnt > 10 to display the page with your fields and the default page to show no fields. Then shrink the band for the detail section and for the conditional object to close up the blank space.

Anis
04-08-2008, 04:12 PM
In the report I'm not using a detailed section, should this be in the group footer ? that's what I'm actually printing !

MoGrace
04-08-2008, 04:34 PM
In the report I'm not using a detailed section, should this be in the group footer ? that's what I'm actually printing !Gee it sounded so simple! So you are doing a summary report? I don't know if it could be done in the group footer or not, having never tried a conditional object there. Its worth a try... Are you also using a group header or just the footer?

Ok I just tried it on a copy of a report I have that uses 2 groups with headers and footers and it worked. For each conditional object that you put in each section, use the same calculation PERCNT > 10 to show the fields and leave the default page blank. I would try it with a copy of the report first to make sure you don't ruin the original!

DaveM
04-08-2008, 04:41 PM
Anis,

Just a thought, but could you do this in a script(the calculations) that creates the variables needed, then use the variables in the filter for the report?

if you are printing this from a form, you may be able to do this in the action scripting(xbasic) and have the variables available for the report filter?

Anis
04-08-2008, 04:43 PM
group header and group footer

Anis
04-08-2008, 04:46 PM
Dave;

That is too complex for me, to store every totaled group in a variable and filter them out.

MoGrace
04-08-2008, 05:13 PM
group header and group footer
You could move the Group header fields into the footer and eliminate the header altogether. Otherwise you would have to precalc the report to test for PERCNT > 10 and that could be time consuming. Since you are not displaying any detail and each footer provides your record line - why not put all the fields on the same line? And then your conditional object in the footer will work like you want.

Anis
04-08-2008, 05:36 PM
That's a good idea. I will try that .

Anis
04-08-2008, 06:07 PM
Robin;

The expression on the conditional object doesn't show me the calculated fields, so it didn't accept that.
How did you enter the calculated field on the condition ?

Anis
04-08-2008, 07:02 PM
Robin;

I got this to work, except it still shows empty lines.
How can I supress the blank lines ?

MoGrace
04-08-2008, 11:54 PM
Robin;

I got this to work, except it still shows empty lines.
How can I supress the blank lines ?
If you right click in the footer region, see if there is an option to shrink to band. Do the same for the conditional object.

Anis
04-09-2008, 01:11 AM
I did, and I also changed the properties of all objects inside the condition to allow shrinking.

It worked fine.

Thanks again for all the help.

MoGrace
04-09-2008, 05:33 PM
Robin;

The expression on the conditional object doesn't show me the calculated fields, so it didn't accept that.
How did you enter the calculated field on the condition ?For some reason the F5 key is not listed to show you how to select a calculated field for the expression window but it does give the list if you use it.

Anis
04-09-2008, 05:42 PM
Yes I actually noticed that, and I noticed some wiered things in defining calculated fields, sometimes it doesn't allow you to enter a group paramater, it tells you that it doesn't exist.
you have to define it thru a button, not directly from the menu.