Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Limit to # of Queries in a Report
- Thread starter ainkca
- Start date
First, for all us to understand each other, don't mess Queries with Data Providers. A Data Provider is made of one or more queries combined with union, intersection or difference.
In the case of data providers from universes, you must distinguish between BO data providers, BO queries and SQL queries. A data provider is composed of one or several BO queries, up to 8, combined with union, intersection or difference. Each BO query usually corresponds to a SQL query (there are more to say, but let's leave it here).
In the case of free-hand SQL data providers, the number of select statements combined with unions, intersections and diferences is limited by the database engine.
In the other hand, I don't know if there is a limit on the number of data providers. I've made some report with 20 data providers or so.
Trying is easy. Simply make a test report with 35 simple free-hand SQL queries. Pasted the SQL code (let's say "select A from T where 1=0" from the clipboard.
Are you sure you need 35 data providers? Perhaps, if you combine them, you can make, let's say, 5 data providers of 6 queries each. This can reduce the refresh time.
If you have to show several tables/graphs with similar data but different conditions, you can make a data provider for all tables/graphs, and use BO filters to filter the needed data on each.
In the case of data providers from universes, you must distinguish between BO data providers, BO queries and SQL queries. A data provider is composed of one or several BO queries, up to 8, combined with union, intersection or difference. Each BO query usually corresponds to a SQL query (there are more to say, but let's leave it here).
In the case of free-hand SQL data providers, the number of select statements combined with unions, intersections and diferences is limited by the database engine.
In the other hand, I don't know if there is a limit on the number of data providers. I've made some report with 20 data providers or so.
Trying is easy. Simply make a test report with 35 simple free-hand SQL queries. Pasted the SQL code (let's say "select A from T where 1=0" from the clipboard.
Are you sure you need 35 data providers? Perhaps, if you combine them, you can make, let's say, 5 data providers of 6 queries each. This can reduce the refresh time.
If you have to show several tables/graphs with similar data but different conditions, you can make a data provider for all tables/graphs, and use BO filters to filter the needed data on each.
Hello ainkca,
What I want to add to Promero's excellent explanation is a very nice piece of VBA that will give you the opportunity to duplicate dataproviders in a document. This will give you an easy time looking for the max number of queries for a report.
Look at (library section)
and download the VBA script for duplicating dataproviders.
I tested the script, it works when you want to duplicate a dataprovider consisting of a single query statement. I did not work for a dataprovider built as a 5 union query.
Happy testing and please tell us if 35 queries still works T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
What I want to add to Promero's excellent explanation is a very nice piece of VBA that will give you the opportunity to duplicate dataproviders in a document. This will give you an easy time looking for the max number of queries for a report.
Look at (library section)
and download the VBA script for duplicating dataproviders.
I tested the script, it works when you want to duplicate a dataprovider consisting of a single query statement. I did not work for a dataprovider built as a 5 union query.
Happy testing and please tell us if 35 queries still works T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
- Thread starter
- #4
Still working on trying both of those solutions.
Sorry for getting the terminology wrong... Our Software is 3rd party through another vendor, and I had no formal training. (IE, Business Objects has a partnership with the company we bought from.) The documentation I have calls them queries. I suppose I should have known better though.
Since most of the users won't have much computer experience, I suppose they didn't want to confuse us with actual terminology? Anyway, this sounds like a complaint, it's supposed to be an apology...
Thanks for the suggestions. I get an error with the add-in, asking me to select a repository. I imagine I can define it in the code somewhere, but I havne't got that far yet. If I can get it to work it will very helpful.
I don't get it about making 6 queries on 1 data provider?
Please forgive my stupidity.
I did try using a single data provider and then making tables with filters, but it didn't work out because the tables need to be able to grow in length, but they were overlapping.
Anyway, I've been 'playing' and I have 27 data providers so far. It's a little slow, but it works. (Takes about 1 minute to run). I'm going to play and see if I can figure out what promero meant.
Thanks so much for your help.
ainkca
Sorry for getting the terminology wrong... Our Software is 3rd party through another vendor, and I had no formal training. (IE, Business Objects has a partnership with the company we bought from.) The documentation I have calls them queries. I suppose I should have known better though.
Since most of the users won't have much computer experience, I suppose they didn't want to confuse us with actual terminology? Anyway, this sounds like a complaint, it's supposed to be an apology...
Thanks for the suggestions. I get an error with the add-in, asking me to select a repository. I imagine I can define it in the code somewhere, but I havne't got that far yet. If I can get it to work it will very helpful.
I don't get it about making 6 queries on 1 data provider?
Please forgive my stupidity.
I did try using a single data provider and then making tables with filters, but it didn't work out because the tables need to be able to grow in length, but they were overlapping.
Anyway, I've been 'playing' and I have 27 data providers so far. It's a little slow, but it works. (Takes about 1 minute to run). I'm going to play and see if I can figure out what promero meant.
Thanks so much for your help.
ainkca
>I don't get it about making 6 queries on 1 data provider?
The queries must be related. All queries must have the same number of objects, with coincident types and qualifications (dimension, measure, etc.).
It's equivalent to the SQL code:
[tt]
(select ...)
union
(select ...)
...
[/tt]
It's useful, for example, if the same concept is sparsed thorugh several tables (a live table and an historical table, for example).
To do this in a universe data provider: In the query panel, clik on the button with two crossed circles (on top). This will add a new query to the data provider (look at bottom tabs). Set objects and conditions you want. Right-click on the tab name to select union, intersection or difference.
>I did try using a single data provider and then making
>tables with filters, but it didn't work out because the
>tables need to be able to grow in length, but they were
>overlapping.
Select the second table, go to "Format" menu and select "Table properties". On the "Appearance" tab (I'm not sure about the translation, but it's the last tab), select the first table in the "Vertical position relative to" combo box, and put the distance between them in the text field next. Click "Accept" to exit the dialog. Repeat the operation to relate the third table with the second, and so forth.
The queries must be related. All queries must have the same number of objects, with coincident types and qualifications (dimension, measure, etc.).
It's equivalent to the SQL code:
[tt]
(select ...)
union
(select ...)
...
[/tt]
It's useful, for example, if the same concept is sparsed thorugh several tables (a live table and an historical table, for example).
To do this in a universe data provider: In the query panel, clik on the button with two crossed circles (on top). This will add a new query to the data provider (look at bottom tabs). Set objects and conditions you want. Right-click on the tab name to select union, intersection or difference.
>I did try using a single data provider and then making
>tables with filters, but it didn't work out because the
>tables need to be able to grow in length, but they were
>overlapping.
Select the second table, go to "Format" menu and select "Table properties". On the "Appearance" tab (I'm not sure about the translation, but it's the last tab), select the first table in the "Vertical position relative to" combo box, and put the distance between them in the text field next. Click "Accept" to exit the dialog. Repeat the operation to relate the third table with the second, and so forth.
MedInfoMan
IS-IT--Management
Ainka,
Just sort of curious, but if you are going to many different types of tables, you may well need the number of dataproviders you are constructing, or if you are going to one table and are trying to get under a limit you have on the number of rows of data you can return per data provider.
However, if you are going to just one table, and the 35 or so data providers you are construcing each just deal with a separate piece of data (say, 1 of 35 different sources of supply, set up as a dimension), then filters may still be the best approach.
Also, if you go to just one (or a few) data tables, you might want to try constructing a graph, but sectioning the report based on the'source of supply'. All the graphs should appear in the same report, each lilmited to just the data pieces that are relevant to that "source of supply"
Quite understandable about the confusion over what a query is. Each data provider is typically set up with a default name beginning with 'Query # from <univers>', yet each of those are actually composed of 1 to several separate queries (or subqueries)).
Hope this helps, and good luck.
Just sort of curious, but if you are going to many different types of tables, you may well need the number of dataproviders you are constructing, or if you are going to one table and are trying to get under a limit you have on the number of rows of data you can return per data provider.
However, if you are going to just one table, and the 35 or so data providers you are construcing each just deal with a separate piece of data (say, 1 of 35 different sources of supply, set up as a dimension), then filters may still be the best approach.
Also, if you go to just one (or a few) data tables, you might want to try constructing a graph, but sectioning the report based on the'source of supply'. All the graphs should appear in the same report, each lilmited to just the data pieces that are relevant to that "source of supply"
Quite understandable about the confusion over what a query is. Each data provider is typically set up with a default name beginning with 'Query # from <univers>', yet each of those are actually composed of 1 to several separate queries (or subqueries)).
Hope this helps, and good luck.
- Thread starter
- #7
- Thread starter
- #8
Thanks to everyone who has responded... I have learned quite a bit from you.
MedInfoMan.... I think I'm trying to do what you described.
I'm looking for Medical Results. 2 Result classes, 20 specific result names in one, 15 in the other.
The filter thing is the way I went first, but I didn't know how to stop the tables from overlapping. Promero answered that for me, (Can't believe I didn't find that on my own.... wearing the big L on my forehead).
Again, thanks everyone.
MedInfoMan.... I think I'm trying to do what you described.
I'm looking for Medical Results. 2 Result classes, 20 specific result names in one, 15 in the other.
The filter thing is the way I went first, but I didn't know how to stop the tables from overlapping. Promero answered that for me, (Can't believe I didn't find that on my own.... wearing the big L on my forehead).
Again, thanks everyone.
Hello Ainkca,
I have of course no idea how your data looks like, but give some time to examine the effect of using sections in your report. This is probably the single most powerful formatting tool within BO to distribute data into nice lumps of data at report level. (Rightclick at a column and then set as master) If using one or more sections gives useful division of data in a report this is THE way to go , much more manageble than creating seperate tables you have to format against each other !!!
My second piece of advice is to make a habit of renaming the dataproviders into something meaningful (Data --> View Data ) and changing the cumbursome statement of "Query2 with Universe XXXXXXXXX" to a meaningful description. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
I have of course no idea how your data looks like, but give some time to examine the effect of using sections in your report. This is probably the single most powerful formatting tool within BO to distribute data into nice lumps of data at report level. (Rightclick at a column and then set as master) If using one or more sections gives useful division of data in a report this is THE way to go , much more manageble than creating seperate tables you have to format against each other !!!
My second piece of advice is to make a habit of renaming the dataproviders into something meaningful (Data --> View Data ) and changing the cumbursome statement of "Query2 with Universe XXXXXXXXX" to a meaningful description. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com
CindyClayton
Technical User
I see that you've gotten some good info on queries versus data providers and may be well on your way to creating a report with sections. I thought I'd throw out a bit of trivia. I once created a document with over 120 data providers. It wasn't the most efficient report. Had I been given more flexibility I would have probably done it differently but it did function. Perhaps the most irritating thing was waiting for the data manager to pop up. It took an extended period of time that increased with each additional provider. Good luck!
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question