Tablix Properties: Filters

Hi

I tried using the DateDiff function but struggled with the syntax. I would be great if the stairway had some examples. I also tried using DateDiff in a SQL Computed Column directly in a SQL table and finally got it to accept the following syntax DateDiff(D,[Need Date],[Date Identified]) but to my surprise SQL would not allow me to save the table telling me I had to drop the table first at which point I gave up. I am amazed it is so hard to get the number of days between two dates returned as an integer when both fields are date format.

I was trying to follow this example:

http://www.blackwasp.co.uk/SQLComputedColumns.aspx

It would be really helpful if more information and assistance was provided about how to use Tablix Filters with practicle examples. Another good candidate for a sample would be how to return only records that have value in a specific field - i.e. if field Department is blank do not return this record.

I was not able to get IsBlank to work in this filter.

It seems strange to me that adding a column would cause the table to be dropped. Did you use the ALTER TABLE statement?

In my opinion, the blank column issue could easily be implemented in the WHERE clause of the data set: WHERE column IS NOT NULL AND column <> ''.

In the following tip filters are described:

Dataset and tablix filtering in SQL Server Reporting Services[/url]

I can clearly see the operator <> in the list, so I would assume you could filter on column <> "".

(I also see the between operator, to answer your earlier question)

I agree Koen it seems strange to require that the table be dropped and I found no advise if this requirement in the many articles I reviewed on how to create Computed Columns.

I was able to resolve the problem by removing the check box "prevent saving changes that require Table re-creation" which appears to be on by default. I found this option in SQL Management Studio - Tools - Options - Designers - Table Options.

Not sure why there is no advice published on this requirement if this is what you need to do to make it work using SQL Management Studio.

I understand the logic of what you are saying Koen however I am as a learner trying to apply this to the layout of the UI as presented. In the DataSet properties Filters UI I have Expression where I have chosen the field I want to test, which evaluates to =Fields!fieldname.value. In Operator I selected "<>" and in the value section "Null". i don't see where to select the WHERE column as you have written it with the UI as presented.

My question was basically how do you use the DataSet Properties Filters UI?

When I preview the report I get no errors but the report is returning the blank records as well as those which test field is not null so no filtering is applied.

The WHERE clause is for the SQL statement in the data set 🙂

Instead of using NULL in the filter, use "Nothing" instead.

Fine, but how do you get to edit the SQL query using the interface provided?

In the DataSet propertiesi have a query option however I can do nothing here except Refresh Fields?

In the data set, the option query type must be set to Text.

Then click Query Designer, which will open the query editor, where you can edit the text.

Wow I stumbled on the realisation that I can indeed see the underlying SQL query associated with the DataSet if I select the rsd file in Solution Explorer. I wasted a lot of time searching through all the menues of Visual Studio DT 2013 because of this massive UI design flaw. Why can you not get to the properties of the DataSet query from the query tab of the Report Data tool?

This is precisely why for those learning these new data tools, changed substantially from BIDS, learners need guidance on how to navigate such a confusing and somewhat convoluted interface.

I'm not clear where logically the Report Data applet fits with Solution Explorer and why the DataSet property boxes should be so different. Expert users will not have these problems however the stairways are designed for learners.

Hello

Here is what appears to filter as expected.

In DataSet properties Filter I added in the Expression field =IsNothing(Fields!myField.Value)<>TRUE. Changed type to Boolean, Operator to "=" and Value =true.

Remarkabily with this in place and returning the correct records if I double click the Solution Explorer representation of the Shared DataSet the filter section shows no filter but it can be reviewed DataSet properties section of the Report Data applet. An interface designed to confuse could not be better constructed and it took a real long time to achieve this simple filter. I hope these observations help others.

Yeah, SSRS is not really a beacon of usability.

With shared datasets, you have to look at the data set in solution explorer, not really the data set stub in the report itself.

Hi Koen

I appreciate your assistance which has been very helpful.

I think it has something to do with the ability to set filters at DataSet, Report and perhaps Tablix level too based on how you interpret the documentation. It would indeed be helpful if Microsoft put better titles in the title area of the respective applets and even sytled them modestly differently to help people understand their operational context.

The changes in layout from BIDS have not been an improvement for those learning.