Aggregation on character type data column in Report Studio C10

[ktangirala]

Hi All,

I have two tables A and B in which the table B (B.A_id) holds foreign key relationship (1:N) with table A (A.id). I am trying to aggregate a row in table A and the report contains some columns that correspond to multiple rows in B (for the primary key of A's row).

When I apply the Summary filter, I am able to aggregate on Count and Total functions. However I also have a column of Character type. In my aggregated row, I need to show that character column value provided it exists in table B. However since the column is not of aggregation type, I am unable to get the value into the aggregated row.

Any ideas on how I can achieve this? Please feel free to let me know if you have any questions to understand my requirement.

Cheers,
KT

[blom0344]

When I apply the Summary filter, I am able to aggregate on Count and Total functions.

???

I read your post 3 times, but it still makes little sense. Please provide an example with data from tables A and B..

[ktangirala]

Oh I am sorry, let me articulate it with data.

Summary Filter applied: A.emp_id = B.emp_id

Table A
======
emp_id (PK)
name
// other fields

Table B
=====
id (PK)
emp_id (FK to Table A)
code (can be one of 'a','b','c','d')
amount

Report Columns (that I am expected to create)
===========
Emp ID (A.emp_id)
Emp Name (A.name)
CodeA Present ('Y' if B.code = 'a' else 'N')
CodeB Amount (B.amount if B.code = 'b' else 0.00)
CodeC Amount (B.amount if B.code = 'c' else 0.00)
CodeD Amount (B.amount if B.code = 'd' else 0.00)

Sample Data:
==========
A
==
001 Sam
002 David
003 Matt

B
==
1 001 'a' null
2 001 'b' 100.00
3 002 'b' 150.00
4 002 'd' 200.00
5 003 'a' null
6 003 'c' 300.00
7 003 'd' 250.00

Expected Report
============
001 Sam      'Y'   100.00       0.00       0.00
002 David    'N'   150.00       0.00   200.00
003 Matt      'Y'       0.00   300.00   250.00

Actual Report
=============
001 Sam      'Y'   
001 Sam      'N'   100.00      0.00       0.00
002 David           'N'   150.00      0.00   200.00
003 Matt      'Y'   
003 Matt      'N'       0.00   300.00   250.00

[pricter]

In order to achieve that try to create a calcution using numbers like
CodeA Present ('1' if B.code = 'a' else '0')
and then another data item for getting yes or no by finding the total for the employe if it is different to zero then Y else N

[blom0344]

I would expect that you will get the desired result if you define the Y/N column as follows:

maximum([Y_N_column] for [Employee_Id])