Solved

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

Posted on 2015-02-19
29
193 Views
Last Modified: 2015-03-06
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
Comment
  • 16
  • 9
  • 4
29 Comments
 
LVL 34

Expert Comment

by:James0628
ID: 40620877
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40621142
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40621649
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40621764
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40621780
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40621974
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
 
LVL 34

Expert Comment

by:James0628
ID: 40622274
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40622356
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
 
LVL 34

Expert Comment

by:James0628
ID: 40622570
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40625924
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
 
LVL 34

Expert Comment

by:James0628
ID: 40626166
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40626213
James,
   when i used HasValue CR didn't recognized it.
    Thank you.

Maruthi
0
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40626224
James,
 Am trying to run it from CR  and  Business Objects Launch Pad.

Maruthi
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40626236
Where did you add it?

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

mlmcc
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:James0628
ID: 40626270
Can you post the formula where you used HasValue, so we can see exactly how you used it?

 James
0
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40629654
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40629706
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
 
LVL 34

Expert Comment

by:James0628
ID: 40629744
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40631184
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40631187
Here is the screenshot.
HasValue.png
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40631563
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40631877
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40631919
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40631922
Excellent James. You are awesome.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40632282
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
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40648361
Please split the points to James 0628 and mlmcc.

Thank you.
0
 

Author Closing Comment

by:Maruthi Rao Yarapathineni
ID: 40650373
Thanks for your Suggestions. Excellent.
0
 

Author Comment

by:Maruthi Rao Yarapathineni
ID: 40650375
Great Help.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40650396
You're welcome.  Glad I could help.

 James
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Salesforce.com’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

15 Experts available now in Live!

Get 1:1 Help Now