Getting Data From FreeHandSQL to Business Objects WebIntelligence

[carla_rogers]

NOTE: It might be easier to see the details in the images if clicked upon, which some modern browsers can take as a signal to expand the image.

MY GOAL - To Keep My Job By Discovering A Solution to the Problem Displaying Data Not Correctly Prepared for Display

The first image below shows the sql that is incompatible with default behavior or WebIntelligence.

The 2nd image shows the horrendously complicated data that is so convoluted not even one of the world's greatest report writer platforms (Web Intelligence) could cope with it.

The 3rd image shows the agony and destruction that resulted in my report.

My hope is that a solution can be suggested.

The only background.

Image 1: The diseased SQL.

Starting with diseased SQL can create only one result: hideous data that no professional would ever produce. Here is my hideous data.

Image 2 - Hideous Data

Image 3 - Agony That Would have been avoided with best practice sql and clean data

Is there a way to tell WebIntelligence the order_id is a string not a number?

[nscheaffer · Accepted solution]

I created some sample code to drop into a free-hand SQL query...

select 1 as "ID", to_char(431660025) as "OrderID" from dual
union
select 2, to_char(431660029) from dual
union
select 3, null from dual
union
select 4, '' from dual
union
select 5, to_char(431660030) from dual
union
select 5, to_char(431660032) from dual

That yields this...

I fully understand this interface looks very different than yours. That is because your version is something prior to 4.3 and we are on 4.3. However, the concepts are the same.

There are two factors...

• The Qualification of ORDER_ID is "Measure".
• The Data Type of ORDER_ID is "Numeric".

Causing two issues...

• ORDER_ID is being displayed with commas.
• #MULTIVALUE is being displayed for ORDER_ID when all other column values are the same.

I do not know why Web Intelligence is interpreting ORDER_ID as Numeric rather than String. That did not happen for me. I changed the Data Type to Numeric to mirror you situation. I will explain how to do that below. If I was sitting next to you I am confident we could figure out why. In the absence of that let's try this. There are a few different ways I can think to address this.

Within the Query Panel you can update the Qualification (e.g. Dimension, Detail, or Measure) and Data Type (e.g. Date, Numeric, String, etc.) or objects returned by a free-hand SQL query. In my version (4.3), that is within the Object Properties in Design interface.

Option #1

• Update the Qualification to "Dimension" to get rid of the "#MULTIVALUE" in this situation.
• Format the ORDER_ID column to something that does not include commas.

Option #2

• Update the Data Type to "String" to eliminate the commas.
• Check "Avoid duplicate rows aggregation" within the properties of your table to get rid of the "#MULTIVALUE" in this situation.

Option #3

• Update the Qualification to "Dimension" to get rid of the "#MULTIVALUE" in this situation.
• Update the Data Type to "String" to eliminate the commas.

I understand it can be frustrating when faced with a roadblock that seems either should not even occur or at the very least should be simple to solve. Web Intelligence is a powerful tool that takes some experience to learn. This community is glad to help out. In the future, I would advise a less caustic tone for a higher likelihood of assistance and engagement.

Feel free to reply with screenshots of what you are seeing if you cannot make sense of what I have provided.

Hope this helps,

Noel

[carla_rogers]

Both answers good, thank you.

[ayman_salem]

In addition to what @Noel Scheaffer said:

You can convert the Order_ID to a string with this simple trick:

Option #4:

in the Select Statement

select 1 as "ID", to_char(431660025) || '' as "OrderID" from dual

Option #5:

in WebI

Assign the new variable as follows and use it instead of the original one

v_Order_ID: =[ORDER_ID]+""