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?