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 Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Formatting Tables so that repetition of rows is avoided
- Thread starter shehul
- Start date
It will be better if you provide more details/info for your requirements. This repetition can be taken in many senses. Like if you have values repeating for a column and want to show only one value of that you can insert a break. Are you looking at that??? Or if you have same dimensions and some measures then Measures will be aggregated automatically to the function you've set for aggregation. Do you mean that??
Sri
Sri
- Thread starter
- #3
I have both dimensions and measures. These are the foll fields in four tables all related one to many,
First Table
Airline,ticket no, class, sector from, sector to
Second table
invoice no, invoice date, invoice amount, basic, taxes, Domestic flag
Third table
Credit Note no, Date, Credit note amount
Fourth table
Cancelled sector from and to
There are some other tables which are used for linking...
I need to show all the above data in one line with the duplicate data suppressed. I tried breaks but it puts one more additional line in the table. Also should I put a break for all dimenstions which are repeating.
For the measures, it didnt aggregate to the function I had selected...
Any ideas....
First Table
Airline,ticket no, class, sector from, sector to
Second table
invoice no, invoice date, invoice amount, basic, taxes, Domestic flag
Third table
Credit Note no, Date, Credit note amount
Fourth table
Cancelled sector from and to
There are some other tables which are used for linking...
I need to show all the above data in one line with the duplicate data suppressed. I tried breaks but it puts one more additional line in the table. Also should I put a break for all dimenstions which are repeating.
For the measures, it didnt aggregate to the function I had selected...
Any ideas....
Still I don't understand your requirement completely. Regarding Measures not aggregating make sure they are defined properly in the Designer and also the projection function set for Aggregation. If break adds a row you can delete that row. But those table are they separate tables in the report or the table at the DB level.
One more thing is it will help to provide precise answers if you provide some sample data and also how it looks presently and how you want it !!
Sri
One more thing is it will help to provide precise answers if you provide some sample data and also how it looks presently and how you want it !!
Sri
You mention duplicate data:
1. Is you're SQL bringing in more data than you expect, possibly by a partial cartesian product? Add a distinct clause to the SQL by going to the options in data-manager, or adjust structure of universe to get proper output.
2. BO will give by default a table representation of data with only distinct rows shown, which makes you're problem a bit of a puzzle (as Sri points out)
3. If you are bringing in data with 1:n relationship you will BY RULE fetch multiple values from the 1 side. That's the whole point in having a 1:n relationship anyway. Either use section or a break to get the information to display only once (delete footer from break as Sri suggests)
4. Show us an example how the report looks like now and we'll clear this up.
T. Blom
Information analyst
tbl@shimano-eu.com
1. Is you're SQL bringing in more data than you expect, possibly by a partial cartesian product? Add a distinct clause to the SQL by going to the options in data-manager, or adjust structure of universe to get proper output.
2. BO will give by default a table representation of data with only distinct rows shown, which makes you're problem a bit of a puzzle (as Sri points out)
3. If you are bringing in data with 1:n relationship you will BY RULE fetch multiple values from the 1 side. That's the whole point in having a 1:n relationship anyway. Either use section or a break to get the information to display only once (delete footer from break as Sri suggests)
4. Show us an example how the report looks like now and we'll clear this up.
T. Blom
Information analyst
tbl@shimano-eu.com
Since there is 1:n relationship between the table the values in the 1 side table is sure to be repeated time the row in the many side table. the way to remove repitation is to apply break on the dimensions and use max or min on the measure to get single value on the summary footer.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question