Post reply

Display all the data but calculate only some data

  • July 20, 2017 at 9:23 pm

    #339040

    Hi ,How can I show all the values for different month and only calculate some based on the condition in SSRS? The calculation that i want can achieve by this query    select * from ByDeptByMonth  where TheMonth<=dateadd(year,-1,GETDATE()) and     TheMonth>dateadd(year,-2,GETDATE()) I've put in stored procedure. But I don't know how to get my desire result  in SSRS report. And I need to report for different department.

  • July 21, 2017 at 4:24 am

    #1952025

    Newbi - Thursday, July 20, 2017 9:23 PM

    Hi ,How can I show all the values for different month and only calculate some based on the condition in SSRS? The calculation that i want can achieve by this query    select * from ByDeptByMonth  where TheMonth<=dateadd(year,-1,GETDATE()) and     TheMonth>dateadd(year,-2,GETDATE()) I've put in stored procedure. But I don't know how to get my desire result  in SSRS report. And I need to report for different department.

    Can you please elaborate further on this as the information posted is insufficient.
    😎
    Post the DDL (create table) script, sample data as an insert statement and the expected results.

  • July 21, 2017 at 10:36 am

    #1952147

    Table Definition:

    Create Table ByDeptByMonth(ForMonthOf date,Department nvarchar(50),TheMonth date,NoOfCases int,Sub1 decimal(6,2),Sub2 decimal(6,2),Sub3 decimal(6,2),Sub4 decimal(6,2),Sub5 decimal(6,2),Sub6 decimal(6,2))
    And Insert Statements:


    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-01-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-02-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-03-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-04-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-05-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-06-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-07-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-08-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-09-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-10-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-11-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2016-12-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2017-01-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2017-02-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2017-03-01',1,100,0.44,0.71,1.15,0,0)
    Insert into ByDeptByMonth Values(GETDATE(),'IT','2017-04-01',1,100,0.44,0.71,1.15,0,0)

    My concern is about calculation for last 12 months rows(the small blocks) And I have to display all the data that come from data set. My calculation for last 12 months is by using this select query:

    I've attached the desire output too.

  • July 24, 2017 at 9:16 am

    #1952398

    You can try something along these lines
    😎

    select
      bdbm.TheMonth
     ,SUM(
       CASE
        WHEN DATEDIFF(MONTH,bdbm.TheMonth,GETDATE()) < 12 THEN bdbm.Sub1
        ELSE 0
       END)
    from ByDeptByMonth bdbm
    GROUP BY bdbm.TheMonth

  • July 25, 2017 at 3:48 am

    #1952513

    Hi Thanks for the reply. Are you referring that I should use above query mentioned by you in one data set? Or combined with the original data (select * from ByDeptByMonth )?I need to have one dataset-show all the data in every month and calculate only for last 12 month's data. Because I need to add Grand Total at the very end of the department sections too. Can you please elaborate more?

  • July 25, 2017 at 4:02 am

    #1952515

    There's no need to add further SQL to your dataset. Add a SUM value at the bottom of your Number of Cases Column. Then changes the value of the expression from COUNT([Field!CaseID.Value] to something like:
    =COUNT(IIF(Field!CaseDate.Value > {Date Math for 12 months ago} AND Field!CaseDate.Value <= {Date Math for end of month, or use TODAY()), [Field!CaseID.Value], Nothing))
    This is untested, as there is no sample data. Field names are completely guessed. I have left you to do the Date math, so the above expression will need adjusting there, as well as the field names.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • July 27, 2017 at 4:53 am

    #1952972

    Hi Thom, In SSRS,  for calculating the Date Math, how could we express in a single field?

  • July 27, 2017 at 5:49 am

    #1952989

    Newbi - Thursday, July 27, 2017 4:53 AM

    Hi Thom, In SSRS,  for calculating the Date Math, how could we express in a single field?

    The expression is for a single field. You'd place it in the field at the top/bottom of your tablix, in your "totals" group.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 8 posts - 1 through 7 (of 7 total)

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