on 03-30-2016 8:35 AM
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
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:
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')
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.