Negative values not showing in BO

[nsandee]

Hello All,

I am working with one of the WebI report. Database team had inserted data and there are some -ve values for some Entity ID's but those -ve values are not coming to the report level.

I checked those records in the database by running the BO query against database. Records are there in the database and not able to view those records in the report level.

We are currently in BOXI 3.1 SP4. Any idea?


Best Regards
Sandeep

[Henry_Banks · Accepted solution]

Hi,

if you copy/paste the 'preview sql' statement from the query panel, then execute it against the database directly, what results do you get?

you should also ensure that the object in the universe is set to Number not String, and that the measure formatting/mask permits a negative value.

also, try creating a new document/new query, for test purposes.

regards,

H

[former_member197846]

Hi Sandeep,

As you have mentioned the Entity Id field has negative values. I believe this Entity Id must have been joined with several other tables in your Universe. If any of those tables are missing these negative values your joins in the SQL will fail and those records will get filtered.

Hence As suggested by Henry, please copy the SQL from "Preview SQL" pane and run it against database to see if it returns those records, which I believe it won't. So you may have to get in touch with your ETL Team to understand whether this is a valid scenario. If it is then you may have figure out the failing joins and convert them to Outer Joins.

Thanks,

Abhijit

[nsandee]

Hello All,

I had followed below procedure but nothing worked out.

1. I had copied SQL from "Preview SQL" pane and ran against database and records with negative values are there in the database but not showing in the report level.

2. I had created a new report for testing purpose but didnt work. There are no filters, alerters in the report.

Best Regards

Sandeep

[Former Member]

Hi Sandeep,

few more doubts i have....

1)what are all the objects did you pulled along with entity? if possible could you show the result sample...

and

2) did you check the corresponding object select query , where conditions and any self restriction joins? ( since these are the few possibles to restrict the data...)

Regards

Subbarao

[Former Member]

What is the number format for Entity ID in the universe? It may be that the negative values are formatted differently (you can specify custom formats for negative, zero and null values)

[Former Member]

Hi Sandeep,

As you have mentioned that you have ran the BO query in the database and there you are seeing the negative values but you are not getting the values in report.

Few things you can check:

1. Check for report level filters if applied any. Probably a report level filter is causing this.

2. Aggregation at report level.(Your Query is aggregating the data at database level, but with the combination of objects you are displaying the data, check if it is projected correctly or not.) In the data properties tab in report. Click on the check box "Avoid duplicate row aggregation" and compare the results.

Thanks
Gaurav

[Former Member]

Hi Sandeep,

Test the report with filter on entity id = existing/loaded negative no and check weather the data is coming or not...

if not check the alerts,filters  applied on the reports...

Regards

Subbarao

[Former Member]

Hi

Go to Universe --> Refresh Structure.-Data with latest update will come.

Regards

NK