Skip to Content
avatar image
Former Member

union all data for different years in IDT

Hi All

I am very new to SAP and I am struggling with using the UNION function in SAP IDT, I am trying to view data from different years using a UNION function but I am struggling in away to implement it correctly within IDT, but Its working fine in MS SQL server management studio.

Can you guys help me or point me in a way to be able to get it working correctly. I need the data to be displayed in one line, something like these:

comparing 2015 and 2016

Month LastYear ThisYear January 5 7 February 2 5 March 6 8

The SQL code works well in MS SQL Management Studio, but not on SAP IDT.

Your help will be highly appreciated.

Kind Regards

Marvin Butji

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Mar 30, 2016 at 10:20 AM

    Hi,

    I don't know what is the SQL you ran on SQL Server.

    If you want to do a union in IDT, there is 2 options:

    • You create a derived table in the data foundation like:

    SELECT "Month", "LastYear", null as "ThisYear" FROM Where "the year you want to test" = @Prompt('Select the year','N')-1

    UNION

    SELECT "Month", null as "LastYear", "ThisYear" FROM Where "the year you want to test" = @Prompt('Select the year','N')

    • You create a parameter that defines the "ThisYear" and "LastYear" using Prompts like I define in the SQL syntax.
      The report creator define then the UNION in the query panel and select the parameters you have created.

    But I recommend you that yoou create a derived table in the Data Foundation nwith 2 sub-select like this.

    I assume that [Value] is the measure you want to aggregate

    SELECT a.[Month], a.[Value] as [LastYear], b.[Value] as [ThisYear]

      FROM

      (Select [Month], [Value] FROM [dbo].[My_Table] WHERE  [Year] = 2015) a,

      (Select [Month], [Value] FROM [dbo].[My_Table  WHERE  [Year] = 2016) b

      WHERE a.[Month] = b.[Month

    Now, if you want to let the user choosing Last Year and This Year then you can create an @prrompt and change the SQL statement like this:

    SELECT a.[Month], a.[Value] as [LastYear], b.[Value] as [ThisYear]

      FROM

      (Select [Month], [Value] FROM [dbo].[My_Table] WHERE  [Year] = @Prompt('Select the year','N')-1) a,

      (Select [Month], [Value] FROM [dbo].[My_Table  WHERE  [Year] = @Prompt('Select the year','N')) b

      WHERE a.[Month] = b.[Month

    Didier

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 31, 2016 at 01:18 PM

    Hi ,

    You can get the data in combination of universe and webi level.

    1) As mentioned by Didier, create two conditional objects in the universe to get the current year and previous year

    currentyear = @prompt('select year','N'...)

    previousyear= @prompt('select year','N'...)-1


    2) Create report , 1st data provider as month, value with current year prompt

    3) Create report , 2nd data provider as month, value with previous year prompt


    4) merge month n any additional dimensional objects from both the data providers

    5) use the merged month, 1stdp.value as current and 2nd dp.value as previous year data

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 06, 2016 at 09:21 AM

    Hi Guys

    I followed your tips and what I decided to was, Create a union all in SQL format, and to all my last year and current year SUM'S in the SQL and bring it to IDT as A single derived table and also allow start and end date as @prompt in the derived table and I used the DATEADD function to minus the dates to a year later.

    I then took all the SUM'S and changed then to measures in IDT.

    Regards

    Marvin

    Add comment
    10|10000 characters needed characters exceeded