CODEWITHSUNDEEP
reporting-services
M.JAY
M.JAY

Reputation: 193

SSRS:How to get actual month data

I created a Report in SSRS. This report display data from a month. Now I change every month manual in the query the actual date. My query:

Select intervaldate as Datum, 
    intervaldateMonth as Monat,
    Sum(case when NameDefinition = 'Production' then calculationUnits else 0 end) as Prod

    from CountDefinition
    where IntervalDate BETWEEN '2017-02-01 00:00:00.000' AND '2017-02-28 00:00:00.000'

    group by intervaldate, intervaldatemonth

I would like to have that the date should change automatically.

The month data:

March: Between '2017-02-27 00:00:00.000' AND '2017-04-02 00:00:00.000'
April: Between '2017-04-03 00:00:00.000' AND '2017-04-30 00:00:00.000'
May  : Between '2017-05-01 00:00:00.000' AND '2017-05-28 00:00:00.000'
....

But I would like to show only the actual date, without the previous date data.

Upvotes: 0

Views: 76

Answers (2)

sridark
sridark

Reputation: 81

try this for current month query to run without any selection or making any change::

where IntervalDate BETWEEN dateadd(dd,-day(dateadd(mm,1,getdate())),dateadd(mm,0,getdate())) +1 and dateadd(dd,-day(dateadd(mm,1,getdate())),dateadd(mm,1,getdate()))

tks, SK

Upvotes: 1

Sanja
Sanja

Reputation: 21

To do this use the parameters in the query:

Select intervaldate as Datum, 
    intervaldateMonth as Monat,
    Sum(case when NameDefinition = 'Production' then calculationUnits else 0   
    end) as Prod
from CountDefinition
where IntervalDate >= @StartDate 
      AND IntervalDate < @EndDate
group by intervaldate, intervaldatemonth

Then define your periods in a new query where you will define which period need be taken depending on the current month.

After that, you have to define @DefaulStartDate as internal text parameter which will take the available value and default value from the previous query and then define @StartDate as date parameter with DefaultValue =CDate(Parameters!DefaulStartDate.Value).

Same for @EndDate.

Upvotes: 0

Related Questions