Web intelligence not retrieving all the data

[Former Member]

We created a new Web intelligence document base on a universe we created using designer and the SQL generated from the web intelligence document retrieves much more data than the report. Are there limitations on how many rows are retrieved? I have the "limit size for result set" unchecked in designer.

[Former Member]

Graham,

Could it be that the query is returning 200,000 rows and your Webi report is aggregating or grouping the measure by the dimensional objects in the report? Making the number of lines in the report 1000.

Mike

[amrsalem1983]

i've a small idea after all those posts,

if you have a measure like sales, or budget or watever,

get the total, using SUM in the webi report

and get the total using some aggregate function in the SQL, and see if there is any difference.

good luck

Amr

[Former Member]

Is there a limit on the number of rows returned on the query level?

[Former Member]

Max rows and max time are both enchecked at the query level

[Former Member]

How many rows are retuned via Webi?

=NumberOfRows([Query 1])

[Former Member]

Where do I see the number of rows?

[Former Member]

Create a variable in the Report.

For example,

x=NumberOfRows([Query 1])

Where

[Query 1]

is the name of the data provider. (The square brackets are required.)

x contains the number of rows.

[Former Member]

Is the data provider the Universe name that we are using to build the Webi doc?

[Former Member]

No. It's the query name.

[Former Member]

I get a WIS 10006 error when I try and insert a free standing formula cell with this:

=NumberOfRows([Drug Utilization-Test])

[Former Member]

You can't use more than one query (name) with this function. Try creating one variable for each query.

[Former Member]

create the variable in designer?

[Former Member]

The NumberOfRows() is a report level function.

If you have 2 queries called Drug Utilization and Test, create 2 variables which return the number of rows in each query.

i.e.

     x=NumberOfRows([Drug Utilization]) and
     y=NumberOfRows([Test]) 

That said, I believe Webi (appears to be) returning fewer rows because because of the way Webi aggregates data.

How many rows does an export to Excel give?

[former_member212749]

Hi Graham ,

Drag and Drop a Column in the Report which is not a Measure should be a dimension and Hide that perticular Object from the report

This should repeat the number of Rows.

Regards

Prashant

[Former Member]

That didn't seem to make a difference

[Former Member]

It looks like we are only getting around 1000 lines, the SQL returns over 200,000 records.