BAQ Report Question - Sorting by User Selection

system · October 29, 2008, 2:35pm

Brian,

I always bring my grouping/sorts back to text-type.

This is your current formula:

if left({BAQReportParameter.SortBy},4)="Vend" then
{BAQReportResult.Vendor.VendorID} else
{BAQReportResult.APInvHed.InvoiceDate}

This is what I would make it:

if left({BAQReportParameter.SortBy},4)="Vend"
then {BAQReportResult.Vendor.VendorID}
else ToText({BAQReportResult.APInvHed.InvoiceDate},"yyyyMMdd")

This would make the date field InvoiceDate to a text result.

So: APInvHed.InvoiceDate = 08/10/2008

ToText it with my format above and you'll result in: "20080810"

I find it's necessary to keep leading zeros and put the full year first
in the string.

Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@...
Office: 336.668.4410 x3091
Mobile: 704.530.3092

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian Stenglein
Sent: Wednesday, October 29, 2008 2:05 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ Report Question - Sorting by User Selection

Thanks for the advice Vic. Using the Grouping Options to create the
sort does provide more options than just using the straight sort, good
idea. Looking at the Employee Efficiency report was also useful, I
found I was really trying to do the same thing that they did in creating
the group formula field but I was trying to use it for the sort. I'm
still having one problem with the formula however.

The fields my sort options are choosing from are 2 different data types.
I have a Vendor ID and an Invoice Date.

This is my formula:

if left({BAQReportParameter.SortBy},4)="Vend" then
{BAQReportResult.Vendor.VendorID} else
{BAQReportResult.APInvHed.InvoiceDate}

when I try to save it, I get "A string is required here" where the date
is.

If I change the date to a string using the CSTR() function the error
goes away but of course the dates don't sort properly. Is there a way
to mix data types in the formula field to handle this?

One other question. Does anybody know if using the "Startswith"
operator is better, worse, or the same as using the "left" function as I
did above?

Thanks,

Brian Stenglein

--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...>
wrote:

>
> Sometimes it's easier to learn via reverse-engineering, as I do.
>
> I'd suggest looking at the standard Employee Efficiency Report.

It's a

> true BAQ Report --> Crystal. It uses Sorts for grouping.
>
> Open a copy the EmplEff.rpt found in your Vantage reports folder.

Look

> at the formulas "Group1" and "Group2". Look at the way they're

used in

> the Group Expert (Report-->Group Expert). Instead of grouping

on "real"

> static fields, they group on formulas with dynamic results. It's

one of

> the coolest features of Crystal.
>
> Crystal is amazingly flexible with it's conditional formatting and
> suppressions. Use these literal values passed in the

BAQReportParameter

> table to do some creative, fun reporting.
>
> In fact, even though its primary purpose is "sorting", I've used

these

> fields for many other purposes, too, such a toggling between report
> formats (summary vs. detail).
>
> Run the Employee Efficiency report from Vantage. Get the XML

generated

> and play with it locally with a copy of the EmplEff.rpt.
>
> Let me know if you have more questions. Good luck.
>
>
> Vic Drecchio
> ERP Administrator
>
> TIMCO Aviation Services
> Greensboro, NC
> Email: vic.drecchio@...
> Office: 336.668.4410 x3091
> Mobile: 704.530.3092
>
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On

Behalf

> Of Brian Stenglein
> Sent: Tuesday, October 28, 2008 5:05 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] BAQ Report Question - Sorting by User Selection
>
> I'm just learning all of the new things in 8.X and I took a 4 hour

class

> at Perspectives about BAQ Reports. We learned how to design the

form

> and allow users to choose how they want to sort their data.
> We learned how to connect the data to a Crystal Report. That was

all

> pretty easy. What they failed to teach us is how to get the report

to

> sort using the selections chosen by the user on the form, which

happens

> to be the missing link for using this reporting method.
>
> I see that the data for the choices made by the user are passed to

the

> report in the table BAQReportParameter in the SortBy and SortFields
> fields. The data in the Sort by field is the description of the

Sort

> Option like "Vendor\Date". The data in the SortFields contains the
> direction of the sort A or D with some extra seperator characters

such

> as .`A~.`A~ I don't see how to programmatically change the way

Crystal

> sorts using this kind of data.
>
> My question is, how do I use those to make Crystal do what the user

is

> requesting? Is it even possible?
>
> Thanks,
>
> Brian Stenglein
> Clow Stamping Co.
>
>
> ------------------------------------
>
> 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/linksYahoo! Groups Links
>

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

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/linksYahoo! Groups Links

system · October 28, 2008, 5:04pm

I'm just learning all of the new things in 8.X and I took a 4 hour
class at Perspectives about BAQ Reports. We learned how to design
the form and allow users to choose how they want to sort their data.
We learned how to connect the data to a Crystal Report. That was all
pretty easy. What they failed to teach us is how to get the report
to sort using the selections chosen by the user on the form, which
happens to be the missing link for using this reporting method.

I see that the data for the choices made by the user are passed to
the report in the table BAQReportParameter in the SortBy and
SortFields fields. The data in the Sort by field is the description
of the Sort Option like "Vendor\Date". The data in the SortFields
contains the direction of the sort A or D with some extra seperator
characters such as .`A~.`A~ I don't see how to programmatically
change the way Crystal sorts using this kind of data.

My question is, how do I use those to make Crystal do what the user
is requesting? Is it even possible?

Thanks,

Brian Stenglein
Clow Stamping Co.

system · October 28, 2008, 5:18pm

Sometimes it's easier to learn via reverse-engineering, as I do.

I'd suggest looking at the standard Employee Efficiency Report. It's a
true BAQ Report --> Crystal. It uses Sorts for grouping.

Open a copy the EmplEff.rpt found in your Vantage reports folder. Look
at the formulas "Group1" and "Group2". Look at the way they're used in
the Group Expert (Report-->Group Expert). Instead of grouping on "real"
static fields, they group on formulas with dynamic results. It's one of
the coolest features of Crystal.

Crystal is amazingly flexible with it's conditional formatting and
suppressions. Use these literal values passed in the BAQReportParameter
table to do some creative, fun reporting.

In fact, even though its primary purpose is "sorting", I've used these
fields for many other purposes, too, such a toggling between report
formats (summary vs. detail).

Run the Employee Efficiency report from Vantage. Get the XML generated
and play with it locally with a copy of the EmplEff.rpt.

Let me know if you have more questions. Good luck.

Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@...
Office: 336.668.4410 x3091
Mobile: 704.530.3092

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian Stenglein
Sent: Tuesday, October 28, 2008 5:05 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Report Question - Sorting by User Selection

I'm just learning all of the new things in 8.X and I took a 4 hour class
at Perspectives about BAQ Reports. We learned how to design the form
and allow users to choose how they want to sort their data.
We learned how to connect the data to a Crystal Report. That was all
pretty easy. What they failed to teach us is how to get the report to
sort using the selections chosen by the user on the form, which happens
to be the missing link for using this reporting method.

I see that the data for the choices made by the user are passed to the
report in the table BAQReportParameter in the SortBy and SortFields
fields. The data in the Sort by field is the description of the Sort
Option like "Vendor\Date". The data in the SortFields contains the
direction of the sort A or D with some extra seperator characters such
as .`A~.`A~ I don't see how to programmatically change the way Crystal
sorts using this kind of data.

My question is, how do I use those to make Crystal do what the user is
requesting? Is it even possible?

Thanks,

Brian Stenglein
Clow Stamping Co.

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

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/linksYahoo! Groups Links

system · October 29, 2008, 2:04pm

Thanks for the advice Vic. Using the Grouping Options to create the
sort does provide more options than just using the straight sort,
good idea. Looking at the Employee Efficiency report was also
useful, I found I was really trying to do the same thing that they
did in creating the group formula field but I was trying to use it
for the sort. I'm still having one problem with the formula however.

The fields my sort options are choosing from are 2 different data
types. I have a Vendor ID and an Invoice Date.

This is my formula:

if left({BAQReportParameter.SortBy},4)="Vend" then
{BAQReportResult.Vendor.VendorID}
else
{BAQReportResult.APInvHed.InvoiceDate}

when I try to save it, I get "A string is required here" where the
date is.

If I change the date to a string using the CSTR() function the error
goes away but of course the dates don't sort properly. Is there a
way to mix data types in the formula field to handle this?

One other question. Does anybody know if using the "Startswith"
operator is better, worse, or the same as using the "left" function
as I did above?

Thanks,

Brian Stenglein

--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...>
wrote: