Friday, August 26, 2011

How to Know the Number of Days in a Month in T-SQL

Sometimes we may need to know the number of days in a given month. For example, in calculating the number of days in a month or last day of the month or any other scenario.

Here is a small script that will give you the number of days for a month. For example, I'll pick month from the current date.

declare @numberOfDaysInMonth int;

set @numberOfDaysInMonth = DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(getdate()), getdate())) - 1);

Read more...

How Do You Know the Name of the Month in the Monthly Index of T-SQL?

When working with reports, is that every time I'm looking for. Everyone gives the report date as input, and wants to show the month name in the report instead of the monthly index [1-12]. So how?

please use the below script to get the name of the month by month index.

DateName(month, convert( datetime, '2007-' + cast(month(getdate()) as varchar(2)) + '-01', 120))

Here month(getdate()) returns the current month index from getdate() function. Hope this helps...

Read more...

How to make columns collapsible, expandable in table Sql server reporting services.

I asked some people how to do this, many people have offered the use of the matrix. However, my data is the data simple and has 14 columns like name, surname, state, city, zip code, Q1, Q2, Q3, H1, H2, H3, etc...

I need to show the columns Q1, Q2, Q3, H1, H2, H3, expandable and collapsible. By default, when you see the report, I show only the name, state, city, postal code, Q1, H1. But q1, H1, with the columns to expand, collapse icons.

When any one clicked to expand i need to show q2,q3, and same for h2, h3.

Here are the steps i followed to make it work.

No Matrix, no other,. i am done with tables only.

Just a small trick

1. Select all the columns that you want to make expandable, collapsible.

2. Hit F4 [Properties window] –> Select Visibility, and in that, set these values, Hidden = true and ToggleItem = ID of the textbox [Where you want to show the Expand[+], Collpse[-] symbols that text box id. in other words parent text box id.]

3. Done, Here we go.

Read more...