SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Expressions Tips and Tricks


SSRS Expressions Tips and Tricks

Author
Message
Frank Fernando
Frank Fernando
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 328
Comments posted to this topic are about the item SSRS Expressions Tips and Tricks
Fatherjack
Fatherjack
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 627
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.
Misha_SQL
Misha_SQL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1678 Visits: 1010
Love this collection of tips! I think this article deserves a continuation.

Thank you.



crunchycocktail
crunchycocktail
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 84
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...
bendy1999
bendy1999
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 147
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
jammy
jammy
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 69
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
Michael-321617
Michael-321617
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 55
Thank you very much Frank...I am a newbie to SSRS and this will very helpful :-D
emmettjarlath
emmettjarlath
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 292
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search