Displaying the months data depending on the given date range using SSRS 2005.

Question

Displaying the months data depending on the given date range using SSRS 2005.

tenga

SSC-Addicted

Points: 432
More actions
June 18, 2009 at 8:37 am

#206041

i have two parameters which are of date type and i want to have the fields in the report for the months which falls under the given date range and the day here doesn't matters in this case.
Let me explain in detail:
i want to generate report in following way:
- page wise i have year data
- group by productID
- for detail portion i want to display month wise data. And for different months i have different columns.
suppose the report parameters are From date: 3/28/2008 and To date: 6/22/2009
and now i want to display the data for the months from march 2008 to the june of 2009, irrespective of the day which i have in my date range.
I hope i am clear with the problem and any help is truly appriciated... and Thanks in advance....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

does it mean you want to show it from 1st march 2008 to 30th june 2009?

When you get the parameter into the stored procedure then get the month and year from the parameter start date and end date and try to find out first day of the parameter month (start date) and last day of the parameter month (end date).

i hope it helps.

Vijay

tenga SSC-Addicted Points: 432 More actions · Answer 2

First of all , thank you so much for the prompt response.

here in this case i dont want to consider the day part of the date(eg. here in this case 28 and the 30 day is not considered at all) only the data should be displayed on the basis of the month and the year. Here i can't use the stored procedure as i have my data coming from a cube which i have built from the analysis services 2005.

Thanks again..

htilburgs Ten Centuries Points: 1117 More actions · Answer 3

Tenga,

Maybe there is a better way, but what I would do is the next:

* Create parameter P_Startdate for Startdate

* Create parameter P_Enddate for Enddate

In the dataset you use to get the information do something like this:

select ......
from ......
where STARTYEAR=year(@P_Startdate) AND STARTMONTH=month(@P_Startdate) AND ENDYEAR=year(@P_Enddate) AND ENDMONTH=month(@P_Enddate)

Succes!

tenga SSC-Addicted Points: 432 More actions · Answer 4

I have created 2 parameters,but i want to use the MDX query for fetching the data from the cube which i have already generated through the analysis services but i don't want to use the data set, so this wont work for my case. anyways thanks for the response and any help in this is greatly appreciated.

thank you