(RS) Can't figure out how to author this report

[Arsenal]

Hi All,

I have a requirement as below

Amount                    Credit Card                      Paper                Combined Credit Card and Paper
                         Master   Visa   Amex          Wire  Check        Credit Card   Wire   Check  Paper+Card

Amt >=0 <=100      0           0     0               100     0               0               100       0       100     
Amt >=101 <=200
Amt >=201 <=300
Amt >=301 <=400


and so on

Basically, the amount under paper+card (the last column) must fall correctly within the amt range on the left

I have figured out how to do the rest of the report (am using list within tables). I just can't figure out how to align the amount ranges on the left. Because, if I do a calculation, then depending on the amount, some of the text range might not show up. The entire range has to show up (between 0 and 1000) that is, the left most column must have 10 rows and amount under the paper+check must line up correctly within the text range

i know this is confusing

Can you guys think of some way I can get the left most column? Text within a table doesn't seem like a good idea, because like the claculation, alignment will be a problem

[almeids]

Is there some reason you didn't develop this as a crosstab?  That would address the alignment issues.  It will also allow you to deal with the missing values (fewer than 10 rows) issue in one place rather than having to repeat the logic for each list.
As far as forcing the 10 rows, your best bet would be a database table with 10 rows in it to guarantee the size of your result set.  If you add something specifically for this report it could contain a column for the amount range text and columns (numeric start and end of range, for example) supporting the report logic.  You could also use a generic table containing a record number (filtered if necessary to return 10 rows), calculate your amount range text from the record number, and use those results in a union with your "real" data to ensure all ranges are represented.  In a real pinch, you could use a sql statement selecting from your database's pseudotable (e.g. DUAL in Oracle) to return a single row for each amount range and assemble these in a 10-part union (11 including the real data).

[Arsenal]

Thanks for your reply.

I had to do a list (or rather lists within table) because of the way the labels (on top of the report) are designed..for example credit card broken down into mastercard, visa etc.

now, I'm pondering the database option as you suggested. Meanwhile, I'm also thinking of adding a text column and the number range that you talked about.

But, again, if I use a simple case statement with the amounts, then the entire range might not show up, depending upon the amounts. Or, am I missing something, and there is a way to force the entire range to show up?

as for tyhis numeric range thing...where do you define it and how do you use it? Sorry, never used it before.

Thanks

[almeids]

I'll try to clarify - serves me right for just throwing stuff out off the top of my head...but first, regarding the lists, I still think this is better suited for a crosstab, albeit one with 2 levels for the columns dimension.  If you can formulate your query to include a data item containing the summary categories (Credit card, paper, both) and one for the detail (Master, Visa, Amex etc) you'll be OK.  Because of the overlap/duplication you may have to union multiple versions of the same data.  I like to think of a crosstab as a template and then work back to figure out how to stuff what I want into the right dimensional data items.

How you come up with the text for the amount ranges isn't your problem, so a case statement is fine.  It's how you supplement the "real" data to guarantee that all 10 ranges are represented in your records that I was addressing, and one approach is to use a union to merge your real result set with another that always returns 10 rows like so:

Real results:
"0-100"   "Paper"           "Wire"           100
"0-100"   "Combined..."   "Wire"           100
"0-100"   "Combined..."   "Paper+Card"  100

Supplemented with:
"0-100"    "Paper" "Wire" 0
"101-200" "Paper" "Wire" 0
"201-300" "Paper" "Wire" 0
...
"901-1000" "Paper" "Wire" 0

You just want to make sure the measure in the supplement is zero so where it coincides with real data it can sum without affecting your values, and that your column data item values concide with at least one record in your real data so you don't get an extra column of zeroes in the crosstab (my choice of Paper & Wire was arbitrary).

Note that I am implying that a union has already taken place to generate the "real" results, assuming that in your database table there is only a single record containing the value 100.

Finally, the numeric range thing: that was really just an alternative to your case statement.  You could conceivably join your transactional data to a table containing a column for the descriptive range text and one each for the numeric start and end of range, but you'd have to hand code the join in FM (...where facts.measure between reference.start_range and reference.end_range).  This sort of a join would generally yield poor performance except for tiny data sets, the only advantage to this approach would be that if you used the lookup table for both the real data and the supplement you'd have a single point of maintenance for the range descriptions and would not have to worry about keeping them in synch in multiple queries in your report.  This is a moot point if your case statement is at the [C8 equivalent of] query level rather than in your [C8 equivalent of] tabular model.