Solution:
Just set the visibility expression to:
=IIF(Fields!Comments.Value = "", IIF(Fields!Comments.Value is nothing, IIF(Len(Trim(Fields!Comments.Value)) = 0,True, False), False), False)
Monday, December 5, 2011
SSRS check for NULL or Empty String
Alright..I have a column called "Comments" and need to hide that column if it's NULL or empty or have spaces!
Solution:
Just set the visibility expression to:
Solution:
Just set the visibility expression to:
Submitted a SQL Tribal Knowledge abstract
Jen McCown ( Twitter | Blog ) is calling all the unpublished SQL Server folk and non MVPs!
She has this brilliant idea about a community-written book of tribal SQL Server knowledge.
http://www.midnightdba.com/Jen/2011/11/sql-tribal-knowledge/
Anyway I've managed to contact her and she was generous to accpet my 3 abstracts after she closed the submition timeline.
I'm So excited that I could write for the SQL community and share the knowledge,These are actually what came to my mind back then, but really regret not sending anything about SQL 2012 as it would be more suitable.
She has this brilliant idea about a community-written book of tribal SQL Server knowledge.
http://www.midnightdba.com/Jen/2011/11/sql-tribal-knowledge/
Anyway I've managed to contact her and she was generous to accpet my 3 abstracts after she closed the submition timeline.
I'm So excited that I could write for the SQL community and share the knowledge,These are actually what came to my mind back then, but really regret not sending anything about SQL 2012 as it would be more suitable.
Title: SSIS notifications, using “SEND MAIL
TASK”.
Category: Advanced SSIS
Level: 300
Goal: Learn about the various ways and the capabilities
of SSIS notifications.
Abstract: Examining
the common and the possible ways to send e-mail notifications for various
scenarios, on error, on success using the built in “Send Mail Task” and
learning abut it’s limitations, the ways to extend it using a script component utilizing
a .NET code, sending HTML e-mails, sending Table results to E-mail, using
expressions in description and e-mail body, addressing the way to minimize creating
notifications by creating a portable template that could be plugged to any package
using child / parent variables.
A-Overview,
usage and its limitations
B-Using
.NET in a Script Component.
C-Using
add-on community tasks.
D-Sending
HTML E-mail notifications.
E-HTML
Table Results.
F-Expression
driven dynamic notifications.
G-Creating
portable notification templates using variables.
Title:
SSIS Dynamic
capability, using variables and expressions.
Category: Advanced SSIS
Level: 400
Goal: Learn about the power of dynamic SSIS
using expressions and the ways to pass values across components using variables.
Abstract: Learning
about moving your package to the next level and leverage the usage by utilizing
variables and the impressively expressions that can change and configure itself
at runtime.
Get to know the best naming conventions for your variables, how variables
are being created, it’s scope and its various data types.
Using variables in your script task, using dispensers, passing variables
to a child package.
A-Overview,
usage and scope.
B-
Variables data Types.
C-Naming
best practices.
D-Variables
Using .NET in Script Task.
E-Expressions
and Dynamic SSIS
F-Using
the expressions builder and the Expression Tester App.
G-Operators,
Functions, Literals and Casting.
H-Commons
Sting, Numeric, NULL, Conditional and Date & Time examples.
Title: SSIS SMO Tasks (SQL Server Management Objects)
Category: Advanced SSIS
Level: 200
Goal: Do you know how many times you’ve used
SSIS and you’re not aware! While using the import and export wizard within
SSMS, while scheduling maintenance plans within SSMS.
Learn
about the various tasks that could help the DBA to make their work faster,
Move, copy, detach databases, logins and SQL agent jobs between servers.
Move,
copy database objects such as tables, views and stored procedures within
tables.
A- Transfer Database Task.
B- Transfer Error Messages Task
C- Transfer Jobs Task
D- Transfer Logins Task
E- Transfer Master Stored Procedures Task
F- Transfer SQL Server Objects Task
Friday, December 2, 2011
SSRS can be also used for user inputs!
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028
Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!
And that’s what I was bargaining about…
1-I started by creating a new project in VS 2008, “Report Server Project” type.
2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”
CREATE DATABASE [Admin]
3-I created a test table called “Employees” contains only 3 columns.
4- Inserted 4 test records…
5-back to VS, Created a Shared Data Source to my Admin DB
6-created 3 Blank reports, using the shared Data Source.
1- To display the records which in the table.
2- A user summary and confirm screen of which record will be updated and with what.
3- A done screen, which will call a stored procedure or just have a simple inline T-SQL query.
That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value. And also if you need an empty comment, such as to clear the comments field or whatever.
7-The design…
Report1: 1ReportsToDB.rdl
Just a basic table, drag the 3 fields to the table, added an extra field with an expression of =CHR(0254) and Font of: Wingdings Just to display a shape without using an image.
Please refer to my article for shapes in
SSRS http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
Also I added an action to that text field to go to report 2 and pass the ID.
Report2: 2ReportsToDB-Confirm.rdl
Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of: =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.
Report3: 3ReportsToDB-Done.rdl
I added 3 text fields, but it’s all optional (for fun).
Just with an action to go back to the first report and the summary or what was done!!
Lets’ run and test it….
I’ll click next to my name on the check mark to update my comments…
That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.
Remove the NULL check mark and enter the new comments….
You’ll get the confirmation above…
Now click on the check mark to confirm the change, yes you guessed it right…it’s the wingdings shapes again ¿
And that’s what you get..
I’ll click on my GO Back, to the first report to check the data.
Done.. my record was updated in the DB.
Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part, it could get more complicated and done more professionally using Custom .NET code and functions.
Hope that helps someone…
The project can be downloaded from
http://www.box.com/s/sskhxfppful1l7sbytzh
References:
http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
http://www.purplehell.com/riddletools/wingdingschart.htm
Thanks
Jason Yousef
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028
Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!
And that’s what I was bargaining about…
1-I started by creating a new project in VS 2008, “Report Server Project” type.
2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”
CREATE DATABASE [Admin]
3-I created a test table called “Employees” contains only 3 columns.
Create TABLE Employees
(
ID int IDENTITY (1,1),
Name varchar(50),
Comments varchar(MAX)
)
4- Inserted 4 test records…
Insert Employees VALUES('Jason', 'Lazy Employee')
,('Yousef', NULL)
,('John', 'Anything Goes')
,('Smith', NULL)
5-back to VS, Created a Shared Data Source to my Admin DB
6-created 3 Blank reports, using the shared Data Source.
1- To display the records which in the table.
2- A user summary and confirm screen of which record will be updated and with what.
3- A done screen, which will call a stored procedure or just have a simple inline T-SQL query.
That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value. And also if you need an empty comment, such as to clear the comments field or whatever.
7-The design…
Report1: 1ReportsToDB.rdl
Just a basic table, drag the 3 fields to the table, added an extra field with an expression of =CHR(0254) and Font of: Wingdings Just to display a shape without using an image.
Please refer to my article for shapes in
SSRS http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
Report2: 2ReportsToDB-Confirm.rdl
Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of: =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.
Report3: 3ReportsToDB-Done.rdl
I added 3 text fields, but it’s all optional (for fun).
Just with an action to go back to the first report and the summary or what was done!!
Lets’ run and test it….
I’ll click next to my name on the check mark to update my comments…
That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.
Remove the NULL check mark and enter the new comments….
You’ll get the confirmation above…
Now click on the check mark to confirm the change, yes you guessed it right…it’s the wingdings shapes again ¿
And that’s what you get..
I’ll click on my GO Back, to the first report to check the data.
Done.. my record was updated in the DB.
Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part, it could get more complicated and done more professionally using Custom .NET code and functions.
Hope that helps someone…
The project can be downloaded from
http://www.box.com/s/sskhxfppful1l7sbytzh
References:
http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
http://www.purplehell.com/riddletools/wingdingschart.htm
Thanks
Jason Yousef
Subscribe to:
Posts (Atom)