.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

report expression for month and year

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi , Can anyone help me how to write a report expression to get the following out put? Actually this value is the month of previous month. I want to set default month name of the parameter to previous month. This is sep.So it should display like this; Aug - 10 Thanks shamen

View Complete Post

More Related Resource Links

List of Month and Year in SSRS report parameter?

Hi, I have two parameters in my SSRS Report i.e. Month and Year. I want to show the list of all Months in dropdown and years for a particular range say 1995-Current Year.
Can any one suggest me the query for how to get it to work.
Thanks!! MCP

Matrix Report With Month/Year Columns - Display date even when there are no records for that month


I've created a matrix report that displays the quantity of different products  set to expire by month/year. The stored procedure returns records for a variety of products and each record contains an expiration date. If there are no products/records that contain an expiration date of lets say 6/2010 then 6/2010 will not appear as a column in the report.

I need a way to force these month/year columns to appear in the report.


Does anyone have a suggestion on how I can make that happen?


Rick Dowdall


Expression in Report Column

Hi Guys, One of the column got expression and pointing out to other subreport using iff . However, it throwing me following error message.      An error occurred during client rendering. Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index I am using below expression  =iif(fields!Banner_Type_ID.Value  ="FWR","","Click To Show Business Owners") and underneath the field name red squally line appearing, when you move the cursor on that line it stats unknown collection member. But  that field name does exist in the dataset. Not sure why it appearing in red.  Can anyone shed some light please, thanks, D  

Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known. We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date. I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something. For sorting, when records have same Year, I would have another column to do relative sorting... So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any. Thanks in advance. --Sharad 

Dynamic or Expression-based Report Page Properties

Hello, I have a tablix(table) with column grouping making the number of columns dynamic. Although our customers have been warned that this could lead to the columns going beyond the edge of the normal landscape 8.5x11 inch page, they have asked for alternate solutions. Beyond rotating the column labels and reducing fontsizes, the issue is not so much that it goes off the page, but the fact that in print preview mode the data is broken up requiring that you go to the next page to get the remaining data. What I am investigating is whether the Page Properties of the report body, either the pagesize property and/or the interactivesize property, can be manipulated at run time? This way if a user, based on their experience with the parameters they usually select and pass into the report, knows that the results will span the length of the page onto a second page, could choose a new parameter such as pagesize/interactivesize so that it changes from 8.5/11 to perhaps the next standard page size of 8.5/14 inches? This does not mean the contents would change during the course of rendering the report, just a new input that could decide at run time what paper size to use in print preview mode.   Thanks.

Display the Sum of hours and Month Year using sql Query

Hi,  I have,hours(in varchar),Id in table tbl_x ,i need the sum of hours, monthyear(eg. mar 2010)for the last three months.But with two separate queries i am getting these results.but actually i need in the following format   Month/Year      TotalHours-----------------    ----------------Mar 2010            0400Apr 2010             0450-----------------------------------to get month/Year i am using this querySELECT right(convert(varchar, fromdate, 106), 8) from tbl_x where id='11101' for the sum of hours SELECT SUM(CAST(hours AS INT))AS TOTALHRS FROM tbl_x WHERE id='11101'

SSRS BUG? When a FontStyle expression is used in a report, the Browser Text Size setting overrides t

I have created a report that has the same font size for all the sections in the report. Some sections' fields use a FontStyle expression that changes the FontStyle between Normal and Italics conditionally. I run the report through the reportserver interface and the report looks fine. I run the report through the ReportViewer control and the the fields which use the FontStyle formula are overridden by the Browser's Page Text Size. Also, when I look at the inline HTML Style that's generated, the fields with the formula don't have a style, they are blank, while the fields with without a FontStyle expression have an inline HTML Style generated. And this is probably why the browser text size setting can override. This seems like a ReportViewer HTML render bug... Or is there a way around it??

Ignore date and consider month and year to match


Hi there,

It is required to retrieve the records where ONLY Start Month, Year AND End Month, Year are passed. Trying below SQL to get these with no success. 

registrationdate BETWEEN(MONTH((registrationdate) = 01) AND (Year(registrationdate)= 2009)) AND ((Month(registrationdate) =03) AND (Year(registrationdate)=2010))


Help is appreciated.

I need to display and group by month/year


I have this date format from



(CHAR(10),(INVC_DT),112) = 20090102 

but I just need the month and year in order to do the aggregations with...

month / year formatted dates


I need to convert an invc_date from 2009-01-01 00:00:00.000 to MM-YYYY


I have tried some of the stuff but not quite working for me yet

Get the month and year?


How do I write my SQL so I get this result?

... and so on
... and so on

I'm building a blog and want to have this as links. I have a field in the database "CreateDate".

Cant put report item more than 1 in 1 expression, in footer


Help, this is emergency.

been developing too much with rs. cant turn my back now. too late. but im stuck. damn.

issue is in master report contain subreport.

since i cant get subreport footer to show up in master. i find tricky way by using summary dataset in master report.
but there is one problem, i need to display function display number as words. should have 2 parameters, money, and the currency, got this from report item.
the problem is one expression cant contain more than 1 report item. and when i try in custom code, cant do that also since i cant access report item there.
i cant use parameter since I really have to access report item. the report item is field in dataset of list in master table that group accounts and display their banking account statement.
i think its stupid, why in the world i couldnt use more than 1 report item in one expression.


What is the Expression for calculating Sum of percentages in SSRS report.


Hi All,

I want to calculate Sum of percentages in my SSRS report.

I have two fields :

Field1   Field2

For Field1 I am using the following formula --- Fields!suminsured/Sum(Fields!no.ofRisks) * 100

The  Field1  formula is working correctly....

Now I want to calculate Field2 from  Field1 ....

My Field2 Formula is Sum(Fields!suminsured/Sum(Fields!no.ofRisks)*100) --- But when I am doing this way its giving error...

Can any body let me know how to acheive Field2 ....??

Field2 is nothing but Sum(Field1) ....But for Field1 I am using Percentage Forumula....So field2 should be Sum(Field1) percentages....

Any ideas in this regard will be appreciated...

Waiting for the responses..




sql query for month and year of date

I have a table with smallint columns for day,month,year. Now I want to select data lets say for month 10,year 2009 to month 2,year 2010. Can anyone help me in writing this query?

Get datetime out in 3 dropdownlists (day, month, year)



How do I get my Datetime out in 3 dropdownlists :

ddlDay = Day

ddlMonth = Month

ddlYear = Year


Time Dimension for YTD using assigned Month and Year integers without Date datatype


Hi, most time dimensions are setup using a base Date field in the fact table, and they have plenty of issues for time analysis as it is. However my fact sales records have the time aspect assigned by pre-calculated periods, because depending on various factors, monthly final invoices are all raised on varying days (usually 2nd friday of month but can change). The monthly period is therefore not a straight calendar month. Probably a very common scenario.

So, the invoicing system already assigns the year (ie 2009, 2010, 2011) and monthly period (1, 2, 3 ... 12 with 3 representing march, even though that might represent 13th march to 9th april) and I want to use those as Time dim so we can do YTD, growth-on-prev-year etc.

It looks like its best to setup 2 Dimensions to link to 2 DataColumns/Attributes in the Fact table (say, FYear and FMonth, both integers). That way I can assigned attribute names like March to key column 3. If I combined them into 1 dimension with both fields making up a single key column, would have to either repeat the month names or link it to another Star schema I believe.

I can use the Add Business Intelligence wizard to make the Dimensions into Time ones instead of regular but I'm still not totally sure if this is the best structure/method and once done, how to use the YTD calcs to show in the cube browser (and my MDX knowle

ORDER BY MONTH, Year - SQL Server 2000


When below SQL is used to print the MONTH Names in SQL SERVER 2000, order by Month name ordering in Alphabetical order, which is incorrect.

Let me know any work around to get this order by Montly order and then Year.

         WHEN MONTH(a.registrationdate) = 1 THEN 'Jan' 
         WHEN MONTH(a.registrationdate) = 2 THEN 'Feb' 
         WHEN MONTH(a.registrationdate) = 3 THEN 'Mar' 
         WHEN MONTH(a.registrationdate) = 4 THEN 'Apr' 
         WHEN MONTH(a.registrationdate) = 5 THEN 'May' 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend