WEBI: Less records received in WEBI Report than actual no. of record.

[Former Member]

Hi,

I have a WEBI report with SQL SERVER 2005 Backend.

The query responsible for fetching the data, selects more than 5000 records, if executed in Database client directly.

However, if the same query is executed from WEBI report, it fetches only 520 records.

I have chaged the record limit from 5000 to 50000, knowing that the resultant record count can be higher than 5000. However the problem persists.

Can somebody help me nail down the issue and the probalbe resolution, please.

thanks,

-Vishi

[Former Member]

Hi Vishkha,

Some time there is some problem with the Input controles try to input controle and recreate it.

Regards,

Shivu.

[Former Member]

Hi,

I have changed the 'Custom SQL', by making it a derived table. It has resolved the issue.

Thanks to all of your support.

Regards,

-Vishi

[former_member142702]

Please note that WebI will additionally aggregate the key figures internally before displaying according to the aggregation option set in the universe for your measure objects.

Assuming your WebI query contains 2 dimension objects (D1,D2) and 1 measure (KF1) and the SQL query returns the following rows:

D1 D2 KF

val1- val2 - keyval1

val1- val3 - keyval2

val1- val2 - keyval3

Assuming you using SUM as aggregation operator for KF

If you display only D1 and KF in a WebI table you will receive 1 row

D1 KF

val1 keyval1keyval2keyval3

If you display D1,D2 and KF in a WebI table you will receive 2 rows

D1 D2 KF

val1 val2 keyval1+keyval3

val1 val3 keyval2

Regards,

Stratos

[Former Member]

Please Open your Universe

Then

FILE--PARAMETERS---CONTROLS TAB

Here Uncheck query limits all option..

i think your problem will be solved.

Thanks

Masud Iqubal

[Former Member]

-Vishi,

In general BO will retrieve DISTINCT records.

Use Distinct in SQL Server query and count the records.

Are you using any filters or Prompts??

I'm Back

[Former Member]

Well, the records are distinct anyways. Also there are no aggrigate functions used, as such.

Yes, I have a prompt in my report, one for allowing to pick a value from list.

Also, my SQL is custom SQL as there are few joins, that do not reflect, if the query is formed automatically.

Can this be a issue?