union all data for different years in IDT

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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