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
Quote
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..
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
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
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])