Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Group using value from multi-select parameter

Posted on 2012-09-21
20
Medium Priority
?
547 Views
Last Modified: 2012-09-27
Please help with urgent work problem with grouping in Crystal reports 2008.

I am trying to group with one or more values from a multi-select parameter of string values.
I have attached a png of the parameter window and an attachment of the formula that I am attempting to use.
What I am getting is when more that one parameter is selected only one of the values from the list of parameters works in the group.
This formula evolved from a previous report that Did Not have the parameter that i am using now, so I may have to scrap it and start over.
This formula does work correctly for the one (only one) of the values from the parameter, but not with more than one.
I have not been able to figure out a pattern for which value is going to show up. Like what order the value is in the parameter, it seems not to matter on that.
I do use another formula to display the parameter values in the header and it works right showing the strings in a comma delimited list.
thanks,
Roger
Parameter.PNG
Performance.txt
0
Comment
Question by:rogerk_fsww
  • 9
  • 5
  • 5
  • +1
20 Comments
 
LVL 1

Expert Comment

by:nicde
ID: 38423415
What this function supposed to do ?
As it is now you are looping through the selected values prepare some Boolean calculation inside the case , but finally the function returns the last of the parameter values.
If you want to use the last value the CASE is not necessary
0
 

Author Comment

by:rogerk_fsww
ID: 38423500
I want to change the grouping to show the date ranges according to a selection(s) from the parameter. So if there are two parameter values, there will be a group for each one that is based on the date range that corresponds to the parameter from the formula.
For instance, when the parameter values are 'On Time' and 'Early' then there will be two groups in the report with each being in the date range that the values from the formula represent.

Where 'On Time' range would be like:
           Case 'On Time':
                ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                ({@Actual_Receive_Date} < {@Des_Received_Date})
            .... up to four possible groups here.

The group works good with one parameter value, but if I select more than one parameter value I only get one group.
thanks.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 400 total points
ID: 38423988
You want to select some number of fields (better set a limit or only handle the first n fields)
and then create a ggrouping hierarchy based on the selection

Field listed first - group 1
Field listed second - group 2
etc

You will need to create formulas for each group and create groups in the report.  They can't be created on the fly.

Formula Group1
    strPerf := {?Perf_Types_Shown}[1];
        Select strPerf
            Case 'Pending':
                (isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ')
            Case 'On Time':
                ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                ({@Actual_Receive_Date} < {@Des_Received_Date})
            Case 'Late':
                ({@Actual_Receive_Date} > {@Des_Received_Date});
);

strPerf;

Formula Group2
If UBound({?Perf_Types_Shown}) >= 2 then
(
    strPerf := {?Perf_Types_Shown}[2];
        Select strPerf
            Case 'Pending':
                (isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ')
            Case 'On Time':
                ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                ({@Actual_Receive_Date} < {@Des_Received_Date})
            Case 'Late':
                ({@Actual_Receive_Date} > {@Des_Received_Date});
)
Else
    TRUE;

strPerf;


Formula Group3
If UBound({?Perf_Types_Shown}) >= 3 then
(
    strPerf := {?Perf_Types_Shown}[3];
        Select strPerf
            Case 'Pending':
                (isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ')
            Case 'On Time':
                ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                ({@Actual_Receive_Date} < {@Des_Received_Date})
            Case 'Late':
                ({@Actual_Receive_Date} > {@Des_Received_Date});
)
Else
    TRUE;

strPerf;


Formula Group4
If UBound({?Perf_Types_Shown}) >= 4 then
(
    strPerf := {?Perf_Types_Shown}[4];
        Select strPerf
            Case 'Pending':
                (isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ')
            Case 'On Time':
                ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                ({@Actual_Receive_Date} < {@Des_Received_Date})
            Case 'Late':
                ({@Actual_Receive_Date} > {@Des_Received_Date});
)
Else
    TRUE;

strPerf;

Open in new window


You will need 1 formula for each possible group and a group created in the report using the formula.

mlmcc
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:rogerk_fsww
ID: 38424038
Ok. Thanks. yep that makes sense.
Now you say create a group for each different parameter? Oh and there will only ever be 4 or 5 parameter values max.
But will I have to suppress the groups that are empty (have no matching rows)?
Another thing, should/could I add an 'ALL' parameter or do that by allowing users to select all the possible values? I think the latter is the answer to that, just verifying.
I have been trying to use this parameter group in the record select formula too, but I don't think I need to??  
There is already a date range param in the report that sets the overall date range values in the record select formula.

Thanks much,
Roger
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38424112
No need to add to the selection unless you want to select based on that criteria.

You could suppress the group header/footer when the number of values is less than the group number

If you will allow 5 values in the parameter selectionthen you will need 5 groups.

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1600 total points
ID: 38424304
I may very well be wrong, but I think you're coming at this from the wrong direction.

 Instead of trying to create your group(s) in a formula(s) based on the parameter values, I think you just need a formula that creates the groups based on the dates, and then use the record selection to decide which dates are included, based on the parameter.

 Create a formula like the following (call it whatever you like) and create a group on this formula:

if isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ') then
  'Pending'
else
  if {@Actual_Receive_Date} = {@Des_Received_Date} then
    'On Time'
  else
    if {@Actual_Receive_Date} < {@Des_Received_Date} then
      'Early'
    else
      if {@Actual_Receive_Date} > {@Des_Received_Date} then
        'Late'
      else
        'Unknown'


 That will group your records.  If you need them sorted in a non-alphabetic way, you could use "specified order", but I would probably just add some spaces to the front of the group name (no spaces in front of the last value, 1 space in front of the next to last, and so on).  If you put that formula on the report, it would include those leading spaces, but if you use a proportional font, they'll probably barely be noticeable.  And, if necessary, you could always use another formula to do a TrimLeft on that formula to remove the spaces, and put that other formula on the report instead.

 Then your record selection formula would need to check each parameter value, to determine which records should be included.  Using the formula that you posted as a starting point, it might be something like this:

local stringvar strPerf;
local numbervar idx:=1;

Local BooleanVar include;

For idx:=1 to ubound({?Perf_Types_Shown}) Do
(
    strPerf := {?Perf_Types_Shown}[idx];
        Select strPerf
            Case 'Pending':
                include := include or
                 (isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ');
            Case 'On Time':
                include := include or ({@Actual_Receive_Date} = {@Des_Received_Date})
            Case 'Early':
                include := include or ({@Actual_Receive_Date} < {@Des_Received_Date})
            Case 'Late':
                include := include or ({@Actual_Receive_Date} > {@Des_Received_Date});
);

include


 That loops through the parameter values, does the appropriate test on the dates for each value, OR's the results together and produces the result from that.  That formula is untested, but I think it would work (the basic idea will definitely work).

 FWIW, you could make this easier, and possibly a lot more efficient, if you just created a separate parameter for each test.  Instead of one Perf_Types_Shown parameter with multiple values, you'd have separate Pending, "On Time", etc. parameters with Yes/No values, or something like that.

 I'm concerned that the record selection with the loop through the parameter values won't be passed to the server, in which case the server will have to send every record to the report and then CR will filter them.  That could be very inefficient.  Maybe that test will be passed.  CR does seem to be pretty good about doing that kind of thing.  But if not, an alternative would be to have a separate Yes/No parameter for each type.  Then the record selection tests should be passed to the server.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38425077
Good point James.  I thought the formulas looked strange but it never occurred to me he had it backwards

mlmcc
0
 

Author Comment

by:rogerk_fsww
ID: 38425081
James,

I think your idea is sorta like the original report that I based this report from.
It just didn't have a parameter selection for choosing the groups.
I'm not too worried about run time, when i took over the report update I created a view that only returns the values that the report uses instead of like 4 or 5 tables that it had been using, it runs pretty quick with the new parameters. I will take a look at that before I release it though.

Great work you and mlmcc,
I will try to get this going soon and report back.

Thanks you guys - a lot!
0
 

Author Comment

by:rogerk_fsww
ID: 38431216
James,
I used your idea and it is working great. I had a few issues, but mostly minor.
I had to tweak some of the group settings, but after that I used your example almost verbatim.
I did notice that it takes a good bit longer to run than it did before I added the parameter.
I can live with it, I mean it only takes like 3 1/2 minutes to go thru over a million records.
I do wonder if it would be more efficient if I used SQL expressions for the parameter date ranges? I have just started using them so don't know if that would be better. What do you think?

I wanted to thank you and mlmcc too since he got the ball rolling,
I hope it's proper the way I divided up the points.
You guys are the best!
Roger K
0
 
LVL 35

Expert Comment

by:James0628
ID: 38431560
The report could be taking longer because CR is reading more records and then filtering out the groups that weren't selected.  Or not.  It had just occurred to me that that could happen.  Now that you've got the report running, you could go to Database > "Show SQL Query" and see if the date tests (to select the specified groups) are included in the WHERE.  If not, then CR is reading all of the records and doing the filtering in the report.  If the date tests are included, then something else is presumably making the report take longer.  Maybe you've added a new table to the report or something and that's slowing it down.

 While "Show SQL Query" really should answer the question, you could also try a simple test.  Select one or more groups and run the report a few times and see how long it takes.  Then go into the record selection and change it to look for the dates for those specific groups, as opposed to trying to check the parameter values, and then run the report a few more times.  If it's faster, then checking the parameter values is presumably slowing it down.  If it's not faster, then the parameter presumably isn't the issue and something else is slowing it down.


 I can't really help you with the SQL Expression question.  I've really only played around with them a little.  Almost all of my reports used stored procedures and you can't use SQL Expressions with SP's.  FWIW, it seems counter-intuitive to use a SQL Expression instead of just putting the test in the record selection formula, but it is an unusual situation.  OTOH, I don't know if you could even use a SQL Expression for something like this.


 As for the point split, FWIW, I don't have any objection to it.  mlmcc's post may not have been the eventual solution (although I imagine that it would have worked), but I know you're just trying to thank him for the effort.  Since he's also a Zone Advisor, I'll take the easy way out :-) and defer to him as to whether or not the split was "proper".

 James
0
 

Author Comment

by:rogerk_fsww
ID: 38432727
Thanks James,

I've looked at the Sql that it's running and it (the parameters) are not included.
I am just getting back into crystal after being out of work for about 2 years.
I did use a lot of stored procedures at my last job, but where I'm working now they are not using them much. Would it be worth while for me to start using them here? I think I can convince them to let me use them. I have pretty much free rein in our Oracle database.
I'm just looking for anything that improves what we are currently using.

Roger
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38432920
Stored procdures (properly written) will generally run fater than using table or views.

It sounds like the selection filter for the report is not being passed to the databae for execution.  SO you are returning all records and filtering on the client.  That will generally be very slow and will not take advantage of any indexes the database may have.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 38432982
I've never used Oracle, so I can't comment from that POV.  In MS SQL, SP's are theoretically more efficient for more complicated queries, since the db can try to optimize them.  However, your multi-value parameter would probably be a problem, unless Oracle has multi-value parameters and CR supports them.

 You could try handling the parameter tests in a different way and see if that gets passed to the server.  My example formula used a for loop, which is flexible (it doesn't matter how many values the parameter has), but if you assume that the parameter will never have more than 5 values, for example, you could use something like a series of if-then tests instead and see if that gets passed to the server.

 Another option might be a CR Command, which is a query that you create in CR and it's stored in the report.  I haven't used CR 2008, but I think I read that it supports using multi-value parameters in a Command (although that might be db-dependent).  But, of course, then you'd have to include something in your query to interpret the parameter values.

 Before you go too far down any of these paths, it might be worthwhile to try the test I mentioned.  Pick some parameter values and run the report a few times, then go into the record selection formula and replace the loop with the tests for those specific values, and see if the report is significantly faster.  If not, maybe the loop isn't the problem.  If the report isn't any faster, I would check "Show SQL Query" and make sure that the new tests are being passed to the server.  If not, then maybe there's something other than the loop that's preventing that, and maybe you can "fix" whatever it is, so the tests from the loop will be passed.

 James
0
 

Author Comment

by:rogerk_fsww
ID: 38433015
I will test your theory. The report is definitely not passing the selections to the database.
I've checked that several times.
I have used multi-value parameters in a command object before, but I think I will have to push this report out soon, so I'm not going to spend too much more time with it. If I get complaints about the speed i'll revisit it then.

THANKS ALL
Roger
0
 
LVL 35

Expert Comment

by:James0628
ID: 38435636
> The report is definitely not passing the selections to the database.

 Just to be clear, when I mentioned checking "Show SQL Query" at the end of my last post, I meant after you remove the loop from the record selection formula (if the report isn't faster).

 For example, you run the report a few times with "Pending" and "On Time" selected for the parameter and see how long it takes.  Then go into the record selection and remove the loop and just use the "Pending" and "On Time" tests:

(isNull({@Actual_Receive_Date}) or ToText({@Actual_Receive_Date}) = ' ') or
({@Actual_Receive_Date} = {@Des_Received_Date})

 Run the report like that a few times and see if it's faster.

 If it's not faster, check "Show SQL Query" and confirm that the date tests are being passed to the server.  If they're not, maybe there's something else preventing that (not the loop).

 If it is faster, then presumably the tests are being passed, but you might take a look at "Show SQL Query" anyway, just to make sure, and see if you see anything interesting there.

 And if you already understood all of that ...  Nevermind.  :-)

 James
0
 

Author Comment

by:rogerk_fsww
ID: 38437181
Hey James,

I understand your testing and am ok with writing sql and code in general.
Is it the case that if a formula or formulas are used in the record selection and not actual DB fields that Crystal won't pass that to the database? I am thinking I read that somewhere.
I am experimenting with the selection formulas like you suggested.

Thanks,
Roger
0
 

Author Comment

by:rogerk_fsww
ID: 38438291
Ok. I got the report to pass the record selection formulas to the database.

I just created two sql expression fields with the database fields that I'm using for the date filters.
They each contain the table date fields I use in the record selection formula.
like this : ("TGC_VENDOR_PERFORMANCE"."ACTUAL_RECV_DATE")
and this (TGC_VENDOR_PERFORMANCE.DES_RECEIVED_DATE).
Nothing else is in the expression fields. Now when I replace the formulas in the rec selection with these two sql expressions they get passed along with other fields that were not getting passed to the database.
Now the report runs in maybe 25- 30 seconds instead of 3 - 4 minutes. Wow!!
I still need to learn how to use the Sql Expression fields for more complex filters, but for now this is great.

Later Roger
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38439515
Formulas can be passed to the database so long s they don't use Crystal specific functions or constructs that Crystal can't convert to the syntax the database can use.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 38439984
What mlmcc said.  :-)

 Basically, using formulas in and of itself isn't necessarily a problem.  The question is, can (or will) CR translate the formula into something that can be passed to the db?  From the little bit of experimenting that I've done, it actually seems to be pretty good about doing that.  I had a test report that had a formula named include_record that tested a number of fields, and the record selection formula was just {@include_record}.  I had just assumed that those tests weren't going to be passed to the server, and was surprised when I realized one day that they were.

 FWIW, in the case of your record selection formula, I was concerned that the loop might make the formula too complicated, but it sounds like that wasn't the issue after all.  Congratulations on finding that fix.  I always love it when I find something like that.  :-)

 James
0
 

Author Comment

by:rogerk_fsww
ID: 38440827
Thanks you guys. I'm signing out and moving on to bigger and better challenges.
I know that I have a good source of information close at hand when I get stuck again.
See You.
Roger
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

810 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