Report Builder Filters

Back from Perspectives today. Didn't see a response to this yet so I will jump in.

Yes, you definitely CAN use the Report-Filter values. But you must first pass it through an aggregate function (like Min or Max) and then use the result of that. Technically it is cheating but it works.

Below are some notes from a text file I keep handy for cut/paste in reports where I need a filter date range in the header. The principle is the same for any situation where filter info is needed as well as setting bogus true/false flags for turning on/off things like detail or group bands.

Alas, this will soon be obsolete knowledge with 8.0.

-Todd C.
------------------------------------------------------------------------------------------------------------------------
PrtFilter = Report-Filter() ! (system function)

ParseFilter = Minimum(PrtFilter) ! must pass thru aggregate due to indirect use of function

! In the following adjust the starting point (60) as needed depending on the filters
! and the position of the first date test

Start-From: INDEX(ParseFilter,'equal to',60) + 9
Start-To: INDEX(ParseFilter,'equal to',Start-From) + 9 SEE NOTE BELOW!!!!!
FromDate: SUBSTRING(ParseFilter,Start-From,10)
ToDate: SUBSTRING(ParseFilter,Start-to,10)

FromDate and ToDate are the dates you can use in the heading of the report. Or if you have only one date you can skip the other.

Please note that the starting "off-set" (in this case 60) may be different for your set of filters. Also the above example assumes the end date filter is the next one following the start date filter. Report filters, as returned by the REPORT-FILTER function are phrased written out in words not using symbols (like + < >) so all comparisons end in "equal to" (such as "less than of EQUAL TO") So this makes "equal to" a good marker to search on for dates.

NOTE!!!!
As of 2/10/04 there is a BETTER WAY to determine the starting location for each date that does not "break" if you add new filters ahead of the dates. So you may either find the starting position for dates by "Equal To" locaiton or.......

Start-From: INDEX(ParseFilter,'/') - 2
Start-To: INDEX-NTH(ParseFilter,'/',3) - 2

What these do is to find the first "slash" and backup two characters to the start of the date. Then the next one finds the third "slash" using the INDEX-NTH function and does the same. If you had several date ranges just set the NTH part accordingly. The only reason this method might not work is if you have filters that contain slashes. Otherwise this method works really well if you happen to need to add another filter ahead of the dates (usually for performance reasons). The starting point for looking for the first date's "Equal To" will not change on you.
------------------------------------------------------------------------------------



-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf Of Richard Bailey
Sent: Thursday, October 20, 2005 6:21 AM
To: UserGroup
Subject: [Vantage] Report Builder Filters


Does anybody know if there is a way of setting a variable in a report
builder report based on a filter value?
The REPORT-FILTER value cannot be interrogated via "substring" directly or
indirectly. Is there any other way of getting at the filter values?
All advice gratefully received

Regards,
Richard

Richard Bailey
Dudley Industries
Mailto: rbailey@...
Tel: +44(0)1253 738311
Mob: +44(0)7836 550360
Fax: +44(0)870 7628173



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links



_____

YAHOO! GROUPS LINKS



* Visit your group " vantage <http://groups.yahoo.com/group/vantage> " on the web.


* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .


_____




[Non-text portions of this message have been removed]
My report builder experience is very limited, but I have an existing report
in which I would like to filter by either one of the aggregate fields or a
calculated field I created in the report to use for the filter. When I go
to add one of them to my filters, they don't come up as one of the available
choices. What am I missing?
Nora Story
Spectra Technologies, Inc.
817-540-4475, ext. 105
Nora_Story@...



[Non-text portions of this message have been removed]
Make sure if the field is aggregate that you have it set to Pre-pass. If
the calculated field uses an aggregate in it, make sure the aggregate is set
to prepass.

-----Original Message-----
From: Nora Story [mailto:Nora_Story@...]
Sent: Thursday, August 30, 2001 3:59 PM
To: vantage@yahoogroups. Com
Subject: [Vantage] Report Builder Filters

My report builder experience is very limited, but I have an existing report
in which I would like to filter by either one of the aggregate fields or a
calculated field I created in the report to use for the filter. When I go
to add one of them to my filters, they don't come up as one of the available
choices. What am I missing?
Nora Story
Spectra Technologies, Inc.
817-540-4475, ext. 105
Nora_Story@...



[Non-text portions of this message have been removed]



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
If you want to use an aggregate field in a filter, you have to make it
"pre-pass."
-----Original Message-----
From:
sentto-20369-23389-999201586-jhellebuyck=stremel.com@...
[mailto:sentto-20369-23389-999201586-jhellebuyck=stremel.com@...
.com]On Behalf Of Nora Story
Sent: Thursday, August 30, 2001 2:59 PM
To: vantage@yahoogroups. Com
Subject: [Vantage] Report Builder Filters


My report builder experience is very limited, but I have an existing
report
in which I would like to filter by either one of the aggregate fields or a
calculated field I created in the report to use for the filter. When I go
to add one of them to my filters, they don't come up as one of the
available
choices. What am I missing?
Nora Story
Spectra Technologies, Inc.
817-540-4475, ext. 105
Nora_Story@...



[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
Tried that and they are still not one of the filter choices. Any more
ideas?

> -----Original Message-----
> From: sarah.vareschi@... [mailto:sarah.vareschi@...]
>
> Make sure if the field is aggregate that you have it set to Pre-pass. If
> the calculated field uses an aggregate in it, make sure the
> aggregate is set
> to prepass.
>
>
Make sure the filed that you are comparing is of the same 'type' as the
calculated or aggregate field.


-----Original Message-----
From: Nora Story [mailto:Nora_Story@...]
Sent: Thursday, August 30, 2001 4:23 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filters

Tried that and they are still not one of the filter choices. Any more
ideas?

> -----Original Message-----
> From: sarah.vareschi@... [mailto:sarah.vareschi@...]
>
> Make sure if the field is aggregate that you have it set to Pre-pass. If
> the calculated field uses an aggregate in it, make sure the
> aggregate is set
> to prepass.
>
>



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Make sure "User-Defined" is selected instead of "Vantage" on the field
selection screen, you should have the two user-defined folders underneath it
if you have created both Calculated and Aggregate fields.

Ed Giallombardo
Computer Technician
Major Industries, Inc.
7120 Stewart Ave.
Wausau, WI 54402
phone: 715-842-4616 ext. 322
email: egiallombardo@...

-----Original Message-----
From: Nora Story [mailto:Nora_Story@...]
Sent: Thursday, August 30, 2001 3:23 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Report Builder Filters


Tried that and they are still not one of the filter choices. Any more
ideas?

> -----Original Message-----
> From: sarah.vareschi@... [mailto:sarah.vareschi@...]
>
> Make sure if the field is aggregate that you have it set to Pre-pass. If
> the calculated field uses an aggregate in it, make sure the
> aggregate is set
> to prepass.
>
>



Yahoo! Groups Sponsor

ADVERTISEMENT



<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/4.gif>
<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/1.jpg>
<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/orgline.gif> You
can, too! Start here...
<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/clear.gif>
Height:
3 4 5 6 7 8 ft 0 1 2 3 4 5 6 7 8 9 10 11in
<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/clear.gif>
Weight:
lbs. kg.

<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/clear.gif>
<http://us.a1.yimg.com/us.yimg.com/a/di/dietsmart/3.gif>


<http://us.adserver.yahoo.com/l?M=210544.1579977.3132570.1261774/D=egroupmai
l/S=1705007183:HM/A=776683/rand=488168080>

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<http://groups.yahoo.com/group/vantage/messages>
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
<http://groups.yahoo.com/group/vantage/links>

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]
Does anybody know if there is a way of setting a variable in a report
builder report based on a filter value?
The REPORT-FILTER value cannot be interrogated via "substring" directly or
indirectly. Is there any other way of getting at the filter values?
All advice gratefully received

Regards,
Richard

Richard Bailey
Dudley Industries
Mailto: rbailey@...
Tel: +44(0)1253 738311
Mob: +44(0)7836 550360
Fax: +44(0)870 7628173
To any interested parties,
My requirement was to report sales values by customer/part which would
report the revenue figures for a user defined specific fiscal period but
which would also detail YTD sales values as at the end of that period. The
problem was that whilst I could easily get the YTD by filtering invoice
values for periods <= the specified period, how could I then determine if an
invoice value should accumulate into the required period as well?

With great thanks to Calvin Dekker I can now interpret the REPORT-FILTER
variable to extract the period number entered in the filter and then check
if an invoice value should be accumulated to the period value as well as to
the YTD value.

Calvin's clever trick allows you to get at the REPORT-FILTER variable (which
is not usually available for calculations) the following way,
1. Create calculated variable "A" using REPORT-FILTER()
2. Create aggregate value "B" as a MAX prepass of "A".
3. Create calculated variable "C" as "B" with NUMBER and SUBSTRING
statements to extract the character(s) which report the entered period and
convert it to a numeric value

You can then use "C" to test against an Invoice period number to determine
whether to accumulate revenue for the given period in addition to
accumulating it for YTD.

Thanks again, Calvin