Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Expressions Tips and Tricks Expand / Collapse
Author
Message
Posted Tuesday, December 22, 2009 10:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 6:21 PM
Points: 83, Visits: 328
Comments posted to this topic are about the item SSRS Expressions Tips and Tricks
Post #838066
Posted Wednesday, December 23, 2009 4:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:44 AM
Points: 278, Visits: 571
Frank, there are some cases where your formulas dont give the correct values. When you use a series of dates from 2008 formulas 3 + 4 return the wrong days.
Post #838449
Posted Wednesday, December 23, 2009 11:22 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 5:37 PM
Points: 536, Visits: 757
Love this collection of tips! I think this article deserves a continuation.

Thank you.



Post #838863
Posted Tuesday, January 5, 2010 5:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 2:53 PM
Points: 6, Visits: 71
Hi guys. i have tried one of your expressions:

iff( rownumber(nothing) mod2, "blue","green")


i have a two groups and in the details again have grouped by three fields so that i can get sum for same kind of items. so i have applied this formatting to the details row but when i see the report it is showing two rows same color and again one row different and again two rows same. i dont get to know what is happening...
Post #842506
Posted Monday, January 11, 2010 3:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:33 AM
Points: 44, Visits: 131
Thanks for the Tips, some of them have already come in quite handy.

For the Expression 4, I must warn you that they will not always return the last day of the month. Let me explain this in an example:

Lets assume today is the 22nd of March. Now lets run that through your Expression.

First, we deduct the day-number from the current date

DateAdd("d", -1.0 * DatePart("d", Today)

that leaves us with 2010-02-28, the last date of the LAST month. so far so good.

Now you add one month to get the final result

=DateAdd("m", 1, "QueryAbove", Today))

This will result in the following date: 2010-03-28 which is not the last date of this month.

I've been using a very similar expression for quite some time and noticed my mistake only after some customers started to complain. Fortunately only default-dates were affected, so the customers could manually override them. It was a painful workaround, but at least they had one.

I suggest to reverse your Expression to the following

=DateAdd("d", -1.0 * DatePart("d", DateAdd("M", 1, Today)), DatePart("d", DateAdd("M", 1, Today)))

Like this, you will first jump to the next month and then subtract all excess days.

I've written these Expressions out of my head and haven't tested them. So you might find the odd typo or something.

Best Regards

Bendy
Post #845261
Posted Friday, March 5, 2010 4:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 9:39 PM
Points: 2, Visits: 68
Excellent post! very helpful. I am waiting for rest of tricks in the bag

Just another expressions which I am working on currently. Please let me know if you have any ideas.
I have report with "Jump to Report" set. It is working fine. I would like to disable the hyperlink if value is 0. Is it possible to do using expressions?

thanks a lot! and keep up good work
Post #877524
Posted Thursday, July 29, 2010 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 14, 2011 1:32 PM
Points: 3, Visits: 55
Thank you very much Frank...I am a newbie to SSRS and this will very helpful
Post #960781
Posted Monday, March 4, 2013 2:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 8:06 AM
Points: 7, Visits: 137
Hi Frank,

Your last point: "As a quick note, did you know that you can upload Excel, Access, HTML and other files to reporting services. Very handy especially if you want to include a help file with your report etc"

Can you expand on this point?

Regards,
Emmett
Post #1426135
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse