Asset Report with related assets as an expandable option RRS feed

  • Question

  • I am trying to create a report that displays asset information for all assets in our database, and then as a hidden toggle I would like to list all assets that are related to that asset.  This would be a one to many relationship.  I have been able to do this as a 1 to 1 relationship.

    The tables would look something like this.  There is an asset table as well as a relationship table.  The realtionship would simply relate one record in the Asset table to another that is a child asset.  An example would be both the Road and curb are within the asset table, but the relationship states that the road is associated to the curb.  I have been able to do this in Visual Studio for SQL reporting services when I write a report for only one asset by using subreports, but this time I would like this to show up in a tabular format with the option to expand each record to see all related assets.  I'm pretty sure this can be done, but I am new to Visual Studio, just not sure how to do it.


    Tuesday, August 19, 2008 1:52 PM

All replies

  • Mike, let me throw an example at you of what I do, and maybe it will help you find a solution. I think I know what your getting at, but as always I could be wrong.

    I have a report that is designed to show the end user a mere record of all the payments they have taken in.   So by default the only thing this report shows is a tabular lists of payments taken within a given period.  But I've added in some toggled visiblity to show a breakdown of how that payment was applied.

    As example, how much went to principal, how much went to interest....whatever.

    The breakdown of these transactions is already stored in a child table of a primary transaction table.   So what I do, is I query the parent table, and join the child table.  Naturally this returns multiple rows per transaction,  which is not the way we want to display it to the user.

    So, in the table, I add a group, and I set it to group on the Transaction ID.  I add only a group header, and in the header I put all the info that should only be displayed once.  Like the payer, the payment date,  payment location...and of course the amount.   Well since we have multiple rows per transaction... i do a sum(Fields!amount.value)  that way if were only looking at this one row....we have the total of the itemized payment.

    Now, the details row, gets the unique information for how the transaction was itemized.    So we just put  in our payment type field, payment amount, and any other unique information to the itemized data.

    Then, we set the details row to default to hidden and it's visibility to be toggled by one of the cells in our group header.

    So in short, you basically could use grouping to do this, and the toggle visiblity feature.
    Living my life at 123mph in 11.15 seconds
    Tuesday, August 19, 2008 3:22 PM
  • Yes,  About five minutes I stumbled across the ability to group and then hide the related asset information.  your description is essentially what I did for an Asset ID.  It worked perfectly.  I simply added the ability to toggle all of the related assets through a control on an external text box.  I initially set this up so that you could toggle related assets on and off individually, but in the end I thought this would be a bit too cumbersome for an end user if they were trying to expand the entire report. 

    Thanks for your help.

    Tuesday, August 19, 2008 7:26 PM
  • Yeah, I had the same issue when the boss asked to be able to hide or show everything at once.  So in that case, I added a checkbox to my main page, and based on if it's checked or not...I passed in a parameter to my report.  I then set the visiblity of my detail rows via expression using an IIF statement on the value of that parameter.
    Living my life at 123mph in 11.15 seconds
    Tuesday, August 19, 2008 7:30 PM