Can’t see all the rows in your drillthrough report in report builder?

December 13, 2009

no comments

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

NA was what I chose to put in case I was using referential integrity. I was looking at the sales table and the employees table and if I found a sale which didn’t have an employee, then I created a fictitious employee for that sale.So, I wanted to see all the 865 sales that had a fictitious employee for them. I clicked on amount of sales and only got 20 records :(… where did it all go?
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!

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.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*