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

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

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}

Select allOpen 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

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

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

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.

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

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

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

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

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

James,
   when i used HasValue CR didn't recognized it.
    Thank you.

Maruthi

James,
 Am trying to run it from CR  and  Business Objects Launch Pad.

Maruthi

Where did you add it?

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

mlmcc

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

 James

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

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

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

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?

Here is the screenshot.
HasValue.png

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?

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

Excellent James. You are awesome.

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

Please split the points to James 0628 and mlmcc.

Thank you.

Thanks for your Suggestions. Excellent.

Great Help.

You're welcome.  Glad I could help.

 James