SSRS Report Builder Part 5.5 - Filtering Tables and Datasets

[@alxvseti]

00:43 Getting Started 01:52 Creating a Dataset to Return all Rows 03:08 Creating a Basic Table 04:56 Viewing Filters in Tablix Properties 06:10 Applying a Simple Filter 07:07 Duplicating the Table 08:16 Text Filters and Wildcards 10:50 Date Filters 13:18 The Between Operator 15:22 Filtering a Dataset

[@rhys87]

This whole series is so helpful for power bi report builder. thanks for all these!

[@WiseOwlTutorials]

Thanks Rhys, happy to hear that it's helping!

[@63bcollier]

This helped me a ton. Thanks for sharing your wisdom.

[@WiseOwlTutorials]

Happy to hear it! Thank you for watching and taking the time to leave a comment!

[@bendel4370]

Thank you for those very clear informations ! ;-)

[@WiseOwlTutorials]

You're very welcome Ben, thanks for watching!

[@Ragn0rus]

Great information. Thank you.

[@WiseOwlTutorials]

You're welcome, Michael! Thanks for watching!

[@benrandall197]

hey thank you for the video. do you know how these filters can be added to when exporting to excel so a column header can be clicked on and certain values can be filtered?

[@WiseOwlTutorials]

Hi Ben! You can't include filter functionality when you export a report to Excel but you can simply enable the Autofilter feature on the table in Excel after you've exported it. I hope it helps!

[@benrandall197]

@@WiseOwlTutorials thank you for the help!

[@WiseOwlTutorials]

@@benrandall197 My pleasure, Ben!

[@TheLordGonzo]

Thank you so much, this was very useful

[@WiseOwlTutorials]

You're very welcome! Happy to hear you found it useful and thanks for taking the time to leave a comment!

[@flexcodectech9027]

can we filter in the same column? using like and different value

[@hugosantosRN]

Great job

[@WiseOwlTutorials]

Thank you for watching!

[@MrIrrepressible]

is it possible to add a filter control to the report that is visible during runtime so that the end user can filter results?

[@WiseOwlTutorials]

You can, indeed! This video on parameters explains how kzbin.info/www/bejne/mWrFgIJnZZ6Vj5o I hope it helps!

[@MrIrrepressible]

@@WiseOwlTutorials cheers

[@FolaY]

Is there a way to filter a coloumn from a group coloumn. For example i have a group coloumn department sales and different departments under it and i want to get count of sales of all the department but excluding one. I dont want to create a new data set for this. How can i achieve this please?

[@WiseOwlTutorials]

Hi! You might find video 9.11 in this playlist useful kzbin.info/aero/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU&feature=shared I hope it helps!

[@Andrew-xc1gk]

Hi there, is there a way you can apply an OR statement as a filter, instead of it always being AND?

[@WiseOwlTutorials]

Hi Andrew, there certainly is! kzbin.info/www/bejne/j4CkfqOBgJ2cr5o I hope it helps!

[@faezah6317]

how we can create the database you have tell in previous videos. where you have told how to create simple report. when you connect the data base movie how it will work?????

[@WiseOwlTutorials]

Hi! You can see how to install the database in Part 8 of this playlist kzbin.info/aero/PLNIs-AWhQzcl9QXUSb0GRTxa2XlTHqDE8 I hope it helps!

[@bhowland810]

can you give a simply way to find consecutive dates

[@briangary5775]

I am trying to connect my Oracle database, but the Build option is Greyed out. I've installed ODAC, SSMS, SSRS, but still it does not show the database.

[@WiseOwlTutorials]

Hi! Sorry I don't have any experience with connecting to Oracle databases. It could be that the build option simply isn't available for that type of data source. If you need a connection strings reference connectionstrings.com is a great place to start. I hope it helps!

[@SarahE_01]

I stumbled across your videos and I really like them slow and easy to follow, but I have a question I'm not sure if you can answer or not. I have two fields called attributedID and Value. The problem I'm having is when I add the attribute ID or the Value field to the report even though I'm not calling it in the report it still displays the same line over and over again for 69 lines. If I go into the query an uncheck those two fields it just displays the one line so I'm only looking at one person just to make sure it's working correctly. So I thought if I type in this IIf statement =IIF(Fields!AttributeId.Value="438",Fields!Value.Value,"No Teacher found") It would it should only return what I'm asking it but what it does is it displays a whole bunch of no teachers found and then it gives me the answer that I'm asking for and then continues to display no teachers found for 69 lines. Is there a way for me to write this without have it display all these lines In the report?

[@WiseOwlTutorials]

Hi Sarah, are your AttributeID and Value fields in a different table to the other fields you're using in the query by any chance? It sounds as though this table is at the "many" end of a one-to-many relationship. There are many attributes for each row in your main table and the table in the report is displaying one row for each attribute. To get your table to show one row for each item in your main table you'll need to apply grouping to some part of your report. There are lots of ways you can do this depending on exactly how you'd like to present the information. Parts 6.1 to 6.4 in this playlist cover the basics of grouping kzbin.info/aero/PLNIs-AWhQzcmEFHyxCRwA_gb29WOz5SJU I hope that helps!

[@rainbowsunshinekitty3953]

My report come back blank when i filter a column with = operator. Ajy suggestions?

[@WiseOwlTutorials]

Hi! It's difficult to say without being able to see your data or your filter. Are you filtering text, numbers or dates? What value are you filtering for?

[@eshuncarlos6241]

I WILL LIKE TO KNOW HOW TO DESIGN A REPORT where i can have particulars of farmers, idnumber, picture and all relevant feilds for printing which will work as ID card for the farmers, this information will be printed

[@traciesmith6085]

How can I get a Top N total instead of the report total? Lets say the report total is $500,000 and top 10 total should only be $150,000.

[@WiseOwlTutorials]

Hi Tracie, good question! Probably the easiest way to do this (assuming you want the same table to display the full total and the top 10 total at the same time) is to calculate a rank value for the column in the query which populates your dataset. There are several ways to do this, here are three examples to rank the RunTimeMinutes column in the Movies database from highest to lowest: SELECT Title ,RunTimeMinutes ,ROW_NUMBER() OVER (ORDER BY RunTimeMinutes DESC) AS RunTimeRank ,RANK() OVER (ORDER BY RunTimeMinutes DESC) AS RunTimeRankSkip ,DENSE_RANK() OVER (ORDER BY RunTimeMinutes DESC) AS RunTimeRankDense FROM Film When you've created your table in the report, you can add a header or a footer row and create an expression like this to calculate the total RunTime for every row in the table (I know that you know this part already): =Sum(Fields!RunTimeMinutes.Value) To calculate the sum of the top 10 run times, you can construct a SumIf type expression like so: =Sum(IIf(Fields!RunTimeRank.Value

[@traciesmith6085]

@@WiseOwlTutorials Thank you. I will give this a try. Much appreciated. I am fairly new to SSRS reports and I'm finding your videos helpful.

[@WiseOwlTutorials]

@@traciesmith6085 No problem Tracie, good luck!