September 15, 2014 at 6:03 am
I have a Tablix on an SSRS 2008 report that is populated by the following dataset.
SELECT 642 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 17 AS [TicketSubProjectTypeID], 'SubProject 1' AS [TicketSubProjectTypeDescription], cast('2014-08-08' as date) AS [TicketCreationDate], 1 AS [TrainingRequired], 1 AS [TicketCount] UNION ALL
SELECT 714 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 17 AS [TicketSubProjectTypeID], 'SubProject 1' AS [TicketSubProjectTypeDescription], cast('2014-08-12' as date) AS [TicketCreationDate], 0 AS [TrainingRequired], 1 AS [TicketCount] UNION ALL
SELECT 748 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 17 AS [TicketSubProjectTypeID], 'SubProject 1' AS [TicketSubProjectTypeDescription], cast('2014-08-14' as date) AS [TicketCreationDate], 1 AS [TrainingRequired], 1 AS [TicketCount] UNION ALL
SELECT 826 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 17 AS [TicketSubProjectTypeID], 'SubProject 1' AS [TicketSubProjectTypeDescription], cast('2014-08-15' as date) AS [TicketCreationDate], 1 AS [TrainingRequired], 1 AS [TicketCount] UNION ALL
SELECT 1169 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 17 AS [TicketSubProjectTypeID], 'SubProject 1' AS [TicketSubProjectTypeDescription], cast('2014-08-26' as date) AS [TicketCreationDate], 0 AS [TrainingRequired], 1 AS [TicketCount] UNION ALL
SELECT 1315 AS [TicketID], NULL AS [TrainingNotes], 3 AS [TicketProjectTypeID], 'Project A' AS [TicketProjectTypeDescription], 19 AS [TicketSubProjectTypeID], 'SubProject 2' AS [TicketSubProjectTypeDescription], cast('2014-08-30' as date) AS [TicketCreationDate], 0 AS [TrainingRequired], 1 AS [TicketCount]
There are filters on the tablix for TicketCreationDate (between 8/1/2014 and 8/31/2014), TicketProjectTypeID (3), and TicketSubProjectTypeID (17,19). The data above is the result of the filtering. The Tablix is a simple table, with a group and heading for Project and SubProject, with sums of TicketCount and TrainingRequired. The detail group consists of the TicketID, the TicketCreationDate, and the TrainingNotes. I only want to display the Detail group if TrainingRequired =1. I put a filter on the Detail Row Group with this criteria, but now the SubProject group for SubProject 2 disappears (where there is only one record in the dataset and TrainingRequired = 0). I do not knw why the parent group of SubProject is affected by the filtering of the Details group. If I recall, I have done something similar to this before and did not experience anything unusual.
Any ideas?
Thanks,
Paul
September 15, 2014 at 11:39 am
I just tried a test report using the query you provided and I can't duplicate your issue. It sounds to me like you either have a filter incorrect on the tablix or you have didn't put the TrainingRequired filter where you think you did. I'm attaching the RDL for what I did.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2014 at 12:11 pm
Interesting. See attached RDL for a similar layout to what i am working on. I did not apply any parameter filters in this test. The only filter is on the Details Row Group. If i add another record to the test data for Project A, SubProject 2 where TrainingRequired = 1, then the SubProject group shows up (with the correct counts)
I was able to get it to work with the help of a user in another forum, but i had to add a grouping for TrainingRequired and hide the table row. See this post: http://www.bidn.com/forums/microsoft-business-intelligence/reporting-services/2713/detail-group-filtering-ssrs-2008/1#8828"> http://www.bidn.com/forums/microsoft-business-intelligence/reporting-services/2713/detail-group-filtering-ssrs-2008/1#8828
I would not think that any "trickery" would be necessary to get this to work. As far as i have found, a filter should only affect the group on which it is applied.
PK
September 15, 2014 at 1:02 pm
So have you tried deploying the report and running it on the report server or have you just been running it in BIDS/SSDT-BI?
You have to remember that BIDS caches data and it looks like if you run the report the first time in BIDS without the filter and then add the filter you get the results you are expecting, so I think the issue might be with the caching that BIDS does.
Of course now I can't get the newly attached report to NOT work as you would like and the one you have attached I can get to "Fail". I modified it to group exactly as yours groups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2014 at 1:16 pm
Yep, i get the same results in BIDS and the report server.
Very frustrating.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply