I’m developing a report model in Report Builder 1.0 at the moment. I felt like it was finished and started testing the model with some sample reports I knew my client needed. And then I got the oddest thing…I would get a report saying something like: Gender Marital Status Amount of Sales
NA NA 865
Male Single 30
Female Single 36
I asked our trusted DBA to run a trace in the SQL Server Profiler to see what went wrong with the query in Report Builder. He got the query, ran it, and got the 865 rows! All I got was just the first row from all the sales. I couldn’t understand what I did wrong… I looked at the Employee entity I had in the report model as the clickthrough report was being run on that entity’s DefaultAggregateAttributes. The IdentifyingAttributes collection included Employee ID and Sales ID. The DefaultDetailAttributes collection also included Employee ID and Sales ID so I should have seen my fictitious Employee with all his sales. Alas, that wasn’t the case.After a lot of searches on the web (which didn’t amount to anything), I finally got an idea from a colleague (thank you Yaniv!) to change my DSV. In my DSV I had only defined Employee ID as the Logical Primary Key for the Employee table. I thought that in Report Builder what matters is just what you define in the model itself (the .smdl file). I thought that the DSV doesn’t mean much just like it doesn’t in a SSAS project (where what matters is not necessarily the connections between dimension tables and facts in your DSV, but rather the dimension relationships you define in the Dimension Usage tab for the cube).
So I did update the Logical Primary Key for Employee table in the DSV to be Employee ID and Sales ID. I ran Autogenerate on the report model, deployed it and ran the same report. Clicking Amount of Sales this time got me a drillthrough report of 865 records – what a relief!
Gender Marital Status Amount of Sales
So, if you’re wondering why you’re not getting all the records you should be seeing in your clickthrough report, check your DSV – it could prove to be the answer you’re looking for.