Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
I have a requirement to drill down from the measure/expression in a straight table to see the list of values that make up the expression.
Example:
Below is a sample data of the actual table.
Category | Product | Mother Account | Balance |
Assets | Auto Loans | 100000 | 500000 |
Assets | Auto Loans | 100001 | 600000 |
Assets | Leases | 200000 | 100000 |
Assets | Leases | 200001 | 150000 |
Assets | Credit Cards | 300000 | 60000 |
Assets | Credit Cards | 300001 | 50000 |
Assets | Mortgages | 400000 | 10000000 |
Liability | Deposits | 500000 | 9000000 |
Liability | Mortgages | 600000 | 5000000 |
OFB | OFB | 700000 | 80000 |
MISC | MISC | 800000 | 85000 |
Below is how my Straight Table will look like
Category is the dimension and Balance is the expression with sum as the aggregation function
Category | Balance |
Assets | 11460000 |
Liability | 14000000 |
OFB | 80000 |
MISC | 85000 |
Requirement: When user clicks on a Balance in the straight table, let's say that user clicks on 11460000, then another sheet should be opened with data displaying the records that make up 11460000. I would need this to do on Balance column only as I may add more dimensions to the straight table.
Category | Product | Mother Account | Balance |
Assets | Auto Loans | 100000 | 500000 |
Assets | Auto Loans | 100001 | 600000 |
Assets | Leases | 200000 | 100000 |
Assets | Leases | 200001 | 150000 |
Assets | Credit Cards | 300000 | 60000 |
Assets | Credit Cards | 300001 | 50000 |
Assets | Mortgages | 400000 | 10000000 |
Is this doable? Please assist me in this regard. Let me know if you need more information on this regard
PSA.
Edit: put the Records st table in another sheet and use some triggers to show/hide when user clicks on the Balance's sum.
Thanks
What about the table box? You can create straight table and Table Box. If you click the Balance (dimension will select), the details will show in the table box..
I don't think that's a very good idea, but should be possible. Clicking on the balance amount will select the dimension values that are used to calculate that amount. In your example clicking on 11460000 will select the value 'Assets'. You can use a field trigger to perform an action to move to another sheet. In your case you can add a Activate Sheet action to the OnSelect field trigger of the Category field.
Personally I'd add a table box with the detail records on the same sheet with an expression for the Show Condition (on the Layout tab of the properties window of the table box). With the expression =If(GetSelectedCount(Category)=1,1,0) the table box will only show if only one value is selected in the Category field. Clicking on an amount in the Balance column of your straight table should do that.
PSA.
Edit: put the Records st table in another sheet and use some triggers to show/hide when user clicks on the Balance's sum.
Thanks
Thank you Krishna, Gysbert and Settu for the suggestions. I was able to implement my requirement based on the feedback you guys provided.
Hi Ram,
Can you please guide me how to achieve this . I have similar requirement .
Regards,
Chirag