Michael Katz
asked on
Create simplified data range in Crystal Reports
Hi Experts,
I need to create 2 month / Year range parameters..Where the results are based on
{GlPeriod to GlPeriod } in {GlYear to GlYear}
to
{GlPeriod to GlPeriod} in {GlYear to GlYear}
I was hoping to have 1 Paramter for the first range of GlPeriod / GlYear to GlPeriod / GlYear
and another for GlPeriod2 / GlYear2 to GlPeriod2 / GlYear2
my available fields are {GlPeriod} and {GlYear}
I hope this make sense
I need to create 2 month / Year range parameters..Where the results are based on
{GlPeriod to GlPeriod } in {GlYear to GlYear}
to
{GlPeriod to GlPeriod} in {GlYear to GlYear}
I was hoping to have 1 Paramter for the first range of GlPeriod / GlYear to GlPeriod / GlYear
and another for GlPeriod2 / GlYear2 to GlPeriod2 / GlYear2
my available fields are {GlPeriod} and {GlYear}
I hope this make sense
You can use the ranges directly
(
{table.GLPeriod}&{table.GL Year} in {?Param1}
)
OR
(
{table.GLPeriod}&{table.GL Year} in {?Param2}
)
mlmcc
(
{table.GLPeriod}&{table.GL
)
OR
(
{table.GLPeriod}&{table.GL
)
mlmcc
ASKER
i Have created a Record Selection formula ({@StartRange1} in {?StartRange1} to {?EndRange1})
@StartRange1 = ToText({ArTrnDetail.GlPeri od},0,"") + ToText({ArTrnDetail.GlYear },0,"")
My @Sales formula = if {@StartRange1} in {?StartRange1} to {?EndRange1} then {ArTrnDetail.NetSalesValue }
else 0
If i enter 32013 at {?StartRange1}and 52013 at {?EndRange1} i get all Sales Data from the beginning of time... but when i enter 32013 at both of these prompts i get only 32013 Sales Data.. How can i build this so i get only the Sales information between these
@StartRange1 = ToText({ArTrnDetail.GlPeri
My @Sales formula = if {@StartRange1} in {?StartRange1} to {?EndRange1} then {ArTrnDetail.NetSalesValue
else 0
If i enter 32013 at {?StartRange1}and 52013 at {?EndRange1} i get all Sales Data from the beginning of time... but when i enter 32013 at both of these prompts i get only 32013 Sales Data.. How can i build this so i get only the Sales information between these
I wouldn't expect 32013 to 52013 to give you "all Sales Data from the beginning of time", but it would include period 4 in any year, and period 5 in any year up to 2013 (and period 3 in any year after 2013). You need to have the year first.
And assuming that the period numbers go above 9, you also need to include a leading 0 on the period numbers below 10. Ignoring the year for the moment, a range like period 5 to 11 won't work, because "5" is greater than "11". It needs to be "05", or " 5" (with a leading space).
Putting the year first and assuming a 2 digit period with a leading 0, if necessary, you could change @StartRange1 to:
ToText({ArTrnDetail.GlYear },0,"") + ToText({ArTrnDetail.GlPeri od},"00")
And, of course, you need to rearrange your parameter values to be year then period. Assuming that they will always be pyyyy (for the periods below 10) or ppyyyy, you could use something like:
Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2)
James
And assuming that the period numbers go above 9, you also need to include a leading 0 on the period numbers below 10. Ignoring the year for the moment, a range like period 5 to 11 won't work, because "5" is greater than "11". It needs to be "05", or " 5" (with a leading space).
Putting the year first and assuming a 2 digit period with a leading 0, if necessary, you could change @StartRange1 to:
ToText({ArTrnDetail.GlYear
And, of course, you need to rearrange your parameter values to be year then period. Assuming that they will always be pyyyy (for the periods below 10) or ppyyyy, you could use something like:
Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2)
James
ASKER
Hi James
How do I work this piece of the formula into my report??
Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2) not sure where to put this
How do I work this piece of the formula into my report??
Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2) not sure where to put this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use between in Crystal record selection.
Even if your GL periods are stored as text, you should still be able to do this.
Create a RANGE parameter that is a text. "Range" is in the lower part of the parameter set up. Instruct your users to enter the range is PPYYYY format (periodyear format). Repeat this for your second parameter range so that you have two parameters.
In your report selection your formula would look something like
//concatenates your two single fields into one to compare against parameter.
(
{table.GLPeriod}&{table.GL
)
OR
(
{table.GLPeriod}&{table.GL
)