Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

How change the display order of the columns in Crystal Enterprise (SAP BI)

Hi Experts,

I Have a requirement i need to change the display order of the columns based on the parameters.

For example: i have to display State,City,Revenue these are three columns and two prompts one is on state and one is on city. In case if the user selects City.
 Display has to be City,State,Revenue.

In case if the user selects both then State,City,Revenue.

Am  new to Crystal Report. Please help me.

Thank you.
0
Maruthi Rao Yarapathineni
Asked:
Maruthi Rao Yarapathineni
  • 16
  • 9
  • 4
2 Solutions
 
James0628Commented:
If you're creating the report in CR, the simplest thing would probably be to create two detail sections, with the fields in a different order in each section.  Then you can suppress one section or the other, depending on your parameter(s).  If you want headings for the fields, you could also have two page header sections, one for each detail section, and you'd also suppress one of those sections, depending on the parameter(s).

  To suppress a section, right-click on the section label and select Section Expert, then click the formula (X+2) button to the right of Suppress and enter a formula.  Something like:
{?parameter} = "City"

 Just remember that you're entering the condition that decides when the section is suppressed (not seen), so, for example, if you want to _see_ a section when {?parameter} <> "City", you would enter {?parameter} = "City", so that the section is suppressed when the parameter is "City", and visible when it's not.

 James
0
 
mlmccCommented:
That is a good method.

Another that might be more flexible and would be easier to extend would be to use formulas for the columns

Column1
If {?parameter} = 'City' then
    {CityField}
Else
    {StateField}

Column 2
If {?parameter} = 'City' then
    {StateField}
Else
    {CityField}

Open in new window


I flater someone wanted to add County you could easily modify the formulas to include a third one that sets the correct order.  WIth James solutiion you would have to add one or more detail sections.

As James says, for just the 2 coulmns his method is probably easier to implement and understand.

mlmcc
0
 
Maruthi Rao YarapathineniAuthor Commented:
Hi James/mlmcc,
 
  Thanks for your time and Valuable suggestion.

James,

  When we try to use two sub reports then it will be having 2+2 parameters which is not acceptable. In case if it is accepted am not sure how to suppress the sub report. i haven't found anything on that.

Mlmcc,
  I have tried almost similar to your suggestion but didn't work. You have given in case if they select city or state either one.  But in my scenario they might select both and they might not neither one. in that case it has to display State,City,Revenue only.


I have tried using the below  formula in each column (Format Result Object Element->General->Advanced)

City Column.

If (Not IsNull ({?pmCity}) And  IsNull ({?pmState}) Then
 {City}
Else
 {State}

State:

If (Not IsNull ({?pmState}) And  IsNull ({?pmCity}) Then
 {State}
Else
 {City}

But still didn't work.

Regards,
Maruthi
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
mlmccCommented:
What version of Crystal?

I believe you have to use HasValue for parameters

City Column.

 If (Not HasValue ({?pmCity}) And  HasValue ({?pmState}) Then 
 {City} 
Else 
 {State}

 State:

 If (Not HasValue ({?pmState}) And  HasValue ({?pmCity}) Then 
 {State} 
Else 
 {City}

Open in new window


mlmcc
0
 
Maruthi Rao YarapathineniAuthor Commented:
Hi Mlmcc,
 
Thanks for your response. It didn't work.  (I think we can not use "HasValue" Because i couldn't find any where in user guide too)
Am using SAP Crystal Reports For Enterprise Version 14.1.2.1171 and Build 2013 sp2 Patch 1.

Thank you.
Regards,
Maruthi
0
 
Maruthi Rao YarapathineniAuthor Commented:
I have created two sub reports in each section. Now I need to suppress the one based on the parameters. where as  surprising am seeing total 4 parameters instead of 2.  For City Sub Report is showing 2 parameters and State Sub Report it is showing 2 Parameters.

 Normally in Business Objects webIntelligence in case of same prompts with text it will show only 2 instead of 4 and even though it is having 2+2 parameters and input values it pass it correctly.

Thank you.
0
 
James0628Commented:
You didn't mention subreports in your first post.  My thought was to just use the main report (no subreports), with 2 detail sections.  You'd have City-State-Revenue in one detail section, and State-City-Revenue in the other, then suppress one of the detail sections, based on your parameter(s).


 Re: Your subreport parameter issue

 CR doesn't automatically link parameters with the same name.  If you have the same parameters in both subreports, what I would do is add the parameters in the main report, and then you can right-click on a subreport, select "Change subreport links", and link a main report parameter to each subreport parameter.  Then CR will ask for the parameters once, for the main report, and just pass those values to the subreport parameters.

 If the "Change subreport links" dialogue is the same, the subreport parameters will be in the dropdown list in the bottom left corner.  Select the main report parameter in the top section, and then the corresponding subreport parameter from that list.  You do _not_ want to use the "Select data in subreport ..." option on the bottom right.

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
When I was posted first time, I have used only main report. Since it was not possible. I thought of trying out with alternative method. Then I have created sub Reports.

Here I have implemented the same logic what you have suggested and its working partially. In the sense now I am unable to suppress the sub reports based on the requirement.

As per my requirement I have to keep enable State,City,Revenue (when I don't select any prompts or  when I select State  only or when I select state and city both).  and other sub report is City,State,Revenue should display only when the user selects City only.
 
For this when I have tried using the  below logic am getting Action Required Message:

Not Is Null{?pm State} or IsNull{?pm City}
this I have written in city subreport Hide section.  in part of format subsection .

Thank you so much James.

Regards,
Maruthi
0
 
James0628Commented:
Did you try the HasValue function that mlmcc mentioned?  It seems like you're using a pretty recent version of CR, and HasValue was added years ago (CR 2008?).

 But for HasValue to work, you may also have to set an option for the parameter, defining it as an "optional" parameter.  I'm not sure how that fits in with what you're doing now.

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
Hi James,

 Has value didn't work. Yeah  my parameters are optional. In case if the user skips parameter option then it has to display the State,City,Revenue. Am using the latest version of CR.

Maruthi
0
 
James0628Commented:
When you say that HasValue didn't work, do you mean that you didn't get the results that you want, or that you actually got an error when you tried to use it, like CR didn't recognize it?

 If CR accepted it, but the results weren't what you wanted, what, exactly, did you try?

 Also, are you running the report directly from CR, or using some other software?  If something else is running the report, maybe it's providing default values to the parameters.

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
James,
   when i used HasValue CR didn't recognized it.
    Thank you.

Maruthi
0
 
Maruthi Rao YarapathineniAuthor Commented:
James,
 Am trying to run it from CR  and  Business Objects Launch Pad.

Maruthi
0
 
mlmccCommented:
Where did you add it?

It generally is used in the SELECT EXPERT but it could be used in any formula.

mlmcc
0
 
James0628Commented:
Can you post the formula where you used HasValue, so we can see exactly how you used it?

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
Hi james,

  Here am attaching two screen shots. One is when I used Not Is Null and one screen shot for the Has Value.  you can observe that formula was not accepted when I used Has Value.

But when we use Not Is Null am getting the exception error.

Maruthi
NotIsNull.png
HasValue.png
0
 
mlmccCommented:
Parameter values in Crystal cannot be tested for NULL only database fields can be tested with IsNull.  

What is wrong with the other one?
Does the section show when no value is entered for both parameters?  That is what you are testing for.

mlmcc
0
 
James0628Commented:
In both screenshots, there are problems with the ().

 In the HasValue formula, you need to either remove the "(" at the beginning, or add a ")" at the end.

 In the IsNull formula, IsNull is a function, like HasValue, so the parameter names need to be in ().  And you have a "(" after And that you don't need.

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
I have tried using just HasValue. But still didn't work.  Please find the screenshot.

mlmcc, you are right . we can not use IsNull with parameter fields.  is there any other way we can check weather parameter has value or not?
0
 
Maruthi Rao YarapathineniAuthor Commented:
Here is the screenshot.
HasValue.png
0
 
James0628Commented:
I have tried using just HasValue. But still didn't work.  Please find the screenshot.
Once again, your () are off.  This time you don't have the parameter name in ().  You had them when you used HasValue in the earlier screenshot, but you also had an extra "(" at the beginning of the formula, with no matching ")" at the end, which would give you an error.

 Your latest screenshot has
if HasValue{?pmUser Name} then true

 That should be
if HasValue ({?pmUser Name}) then true

 or just
HasValue ({?pmUser Name})

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
Excellent  James :) .   It is working.
Thank you so much. am just wondering in case if the user selects both the parameters then it has to display only state,City,Revenue.

What condition might work over there?
0
 
Maruthi Rao YarapathineniAuthor Commented:
Its working James.
Thank you So much for your time :)

You are really Great.

i Have written this condition and its working .

City

if (Not HasValue({?pmCity}) and Not HasValue({?pmState}))
or
  (HasValue({?pmCity}) and HasValue({?pmState}) or Not HasValue({?pmCity}) and HasValue({?pmState}))
 then
  True

State

if  (HasValue({?pmCity}) and Not HasValue({?pmState}))
 then
  True
0
 
Maruthi Rao YarapathineniAuthor Commented:
Excellent James. You are awesome.
0
 
James0628Commented:
I believe mlmcc deserves most of the points.  He was the one that suggested using HasValue, back in post 40621764.  Your problem seems to have been that you didn't have () in the right places.  FWIW, I just noticed that his examples in that post have an extra "(", or are missing a ")", and I guess that might have contributed to your problem.

 Anyway, you can ask to have the question re-opened, and then split the points between us.

 James
0
 
Maruthi Rao YarapathineniAuthor Commented:
Please split the points to James 0628 and mlmcc.

Thank you.
0
 
Maruthi Rao YarapathineniAuthor Commented:
Thanks for your Suggestions. Excellent.
0
 
Maruthi Rao YarapathineniAuthor Commented:
Great Help.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 16
  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now