SSRS Report Design Question - drill down / sub report
Hi, i am relatively new to SSRS, and an dusting off my SQL skills, so i may be missing an obvious solution here. But right now i am stuck in finding the best way to display some data in a tablix.
I have two main tables, contracts and subcontracts. This is a report by salesperson which shows what they have sold over a given timeframe.
From my contract table, i have the main info, i have the subcontract id, salesperson id, invoice, id. Subcontracts shows the details of exactly what was sold, and when. there can be multiple subcontracts. my issue is when i join to the subcontracts table, i get multiple rows in my result per contract, which creates a problem for the contract amount. the contract amount is repeated in each row, and when SSRS subtotals it the numbers are way high.
I was considering using a subreport, when the user clicks a contract, then i go into the subcontracts, which makes sense because the displayed fields are different for subcontracts. but i prefer the drill-down capability and keeping the user on the same page.
Its possible my SQL is wrong, but is there a way to show this data with a different data set, without having to move to a new report?
because of confidentiality agreements i cant post any code, but i will add a screenshot of what i get now, and dont like. if you notice by the screenshot, i have a single contract, with multiple subcontracts showing the items that are sold as part of that. I want to show the items but get the dollar amount correct.
also as a subtopic, why dont my parameters go across the whole top bar? is there a way to contorl the order and location of the parameter selection screens?
ssrs-Capture-redacted.jpg
I have two main tables, contracts and subcontracts. This is a report by salesperson which shows what they have sold over a given timeframe.
From my contract table, i have the main info, i have the subcontract id, salesperson id, invoice, id. Subcontracts shows the details of exactly what was sold, and when. there can be multiple subcontracts. my issue is when i join to the subcontracts table, i get multiple rows in my result per contract, which creates a problem for the contract amount. the contract amount is repeated in each row, and when SSRS subtotals it the numbers are way high.
I was considering using a subreport, when the user clicks a contract, then i go into the subcontracts, which makes sense because the displayed fields are different for subcontracts. but i prefer the drill-down capability and keeping the user on the same page.
Its possible my SQL is wrong, but is there a way to show this data with a different data set, without having to move to a new report?
because of confidentiality agreements i cant post any code, but i will add a screenshot of what i get now, and dont like. if you notice by the screenshot, i have a single contract, with multiple subcontracts showing the items that are sold as part of that. I want to show the items but get the dollar amount correct.
also as a subtopic, why dont my parameters go across the whole top bar? is there a way to contorl the order and location of the parameter selection screens?
ssrs-Capture-redacted.jpg
also as a subtopic, why dont my parameters go across the whole top bar? is there a way to contorl the order and location of the parameter selection screens?
The only thing that can be controlled is the order. To change order using BIDS (Visual Studio), open the Report Data window (CTRL + ALT + D), select a parameter and use the blue up/down arrow icons to move it.
The only thing that can be controlled is the order. To change order using BIDS (Visual Studio), open the Report Data window (CTRL + ALT + D), select a parameter and use the blue up/down arrow icons to move it.
ASKER
Thanks for the comments. The First would work, i think. I just need a way to suppress the values in the detail, which i think i can do with a formula. but if i suppress the value in the cell, will it still give me the first? I'm going to give this a shot now..
ASKER
So i did this and it works when i sum by contract, but when I want to sum up all contracts it looks at every line. This is a grouping at the salesperson level, and not the contract level. I need a way to just sum the "Firsts" for each subgroup to get this correct. Is this possible?
see the new image. the 13mm is incorrect, but the subtotals are corrct now. The 13mm is a Sum(AmountGross). Using First(AmountGross) just gives one of the sub group sum values.
SSRS2.PNG
see the new image. the 13mm is incorrect, but the subtotals are corrct now. The 13mm is a Sum(AmountGross). Using First(AmountGross) just gives one of the sub group sum values.
SSRS2.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Valentino! Sorry for the delay in marking it accepted.
By default SSRS uses Sum(Fields!YourField.Value
More info: First Function (Report Builder and SSRS)