Solved

Create simplified data range in Crystal Reports

Posted on 2013-05-13
6
197 Views
Last Modified: 2013-05-17
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
0
Comment
Question by:Mikeyman_01
6 Comments
 
LVL 14

Expert Comment

by:LinInDenver
ID: 39161579
I think there must be a typo in your post, but I will try to help anyway.

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.GLYear} in minimum({?Param1}) to maximum({?Param1})
)
OR
(
{table.GLPeriod}&{table.GLYear} in minimum({?Param2}) to maximum({?Param2})
)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39161625
You can use the ranges directly

(
{table.GLPeriod}&{table.GLYear} in {?Param1}  
)
OR
(
{table.GLPeriod}&{table.GLYear} in {?Param2}
)

mlmcc
0
 

Author Comment

by:Mikeyman_01
ID: 39163697
i Have created a Record Selection formula  ({@StartRange1} in {?StartRange1} to {?EndRange1})

@StartRange1 = ToText({ArTrnDetail.GlPeriod},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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Expert Comment

by:James0628
ID: 39164329
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.GlPeriod},"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
0
 

Author Comment

by:Mikeyman_01
ID: 39164653
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
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 39167519
Sorry.  I was kind of in a hurry there.  Just replace the parameter in your formula with that code.

if {@StartRange1} in {?StartRange1} to {?EndRange1} then
  {ArTrnDetail.NetSalesValue}
else
  0

  would become

if {@StartRange1} in
 Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2) to
 Mid (Right ("0" + {?EndRange1}, 6), 3, 4) + Left (Right ("0" + {?EndRange1}, 6), 2) then
  {ArTrnDetail.NetSalesValue}
else
  0


 FWIW, if you don't already know, what that code does is add a "0" to the front of the parameter value, so, for example, "52013" would become "052013" and "112013" would become "0112013", and then it takes the rightmost 6 characters, which would give you "052013" and "112013".  IOW, it adds a leading "0" if the period is just one digit, so the parameter will look like ppyyyy.  Then it uses Mid to extract the year (4 characters, starting at the 3rd position) and the two digit period (the leftmost 2 characters) and concatenates them together.  So, "52013" becomes "201305" and "112013" becomes "201311".

 If you're going to be using those parameters in other formulas and need the values "converted" there too, you could create a formula for each parameter that "converts" the value.  For example:

// start_range_ppyyyy
Mid (Right ("0" + {?StartRange1}, 6), 3, 4) + Left (Right ("0" + {?StartRange1}, 6), 2)

// end_range_ppyyyy
Mid (Right ("0" + {?EndRange1}, 6), 3, 4) + Left (Right ("0" + {?EndRange1}, 6), 2)


 Then the earlier formula would be:

if {@StartRange1} in {@start_range_ppyyyy} to {@end_range_ppyyyy} then
  {ArTrnDetail.NetSalesValue}
else
  0


 And, as I mentioned before, the way that I'm rearranging the parameter values is based on the assumption that they will _always_ look like pyyyy (for the periods below 10) or ppyyyy (for periods 10 and up).  If the parameter values could be different (maybe the user types them in and could just make a mistake), then you might want to add some error checking.  In that case, it would probably be easier to use a formula to "convert" each parameter, and include the error checks in those formulas.  You'd have to decide how you wanted the report to handle an invalid value (eg. if the user mis-typed the year and entered 5203 instead of 52013).

 James
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now