on 04-24-2014 5:58 AM
Hi Frzz,
I have a requirement, in which i need to generate the fieds based on the value set dynamically. Is it possible in HANA with scripted Calculation view??
Please guide.
Best Regards,
Krishna.
Hello Krishna G,
Can you share the use-case?
Could you not use generic fields...
or different table and then output data using union
warm regards
aadi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Krishna,
If i understood your problem
Your data looks like this
ActualField | SuperiorField |
1 | |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 2 |
6 | 2 |
7 | 3 |
8 | 3 |
9 | 3 |
your output needs to look like this:
Level 1 | Level 2 | Level 3 |
1 | 2 | 4 |
- | 3 | 5 |
- | - | 6 |
- | - | 7 |
- | - | 8 |
- | - | 9 |
if so, dynamic sql might seem a good option.
My experience suggest there may be a better way...
For Starters ... check out
Window Functions - SQL Reference - SAP Library
Let me know if you see a way forward...or any problems as i am sure there is more to your use case that you cannot reveal on an open forum...So tell me the problem you face if you try it using Window functions.
Warm regards
aadi
Hi Krishna G,
More details are required. Some initial feedback:
1) Are you working with BW on HANA or native HANA?
2) What reporting tools do you have to work with?
3) Self-join is possible in SQL, but is not possible in an Attribute View. It may be possible in a Calculation View, I haven't tried - but this could cause bad performance.
Ultimately it sounds like you have a parent-child structure that you want to display in a leveled structure. I'd recommend building a parent-child hierarchy in your Attribute or Calculation View, building a report in a front-end tool that supports MDX, and give the user the option to drill into the hierarchy.
Another potential solution is to persist the flattened hierarchy via your ETL tool of choice.
You have quite a few options. Please provide more details so that we can help guide the best solution based on the constraints you're facing.
Hi Jody,
Am working on stand alone HANA. Not BW on HANA.
We are using Webi reports on HANA Views.
We can do self join in Attribute Views by joing the table with same table as per Krishna's post above.
I have a table in which i have field with superior node information. based on this table i need to create a flat hierarchy. As suggested by Krishna, we can do self join and get the expected output. Correct me if am wrong.
Krishna.
Hi Krishna,
DYNAMIC SQL is not possible with script based calculation view.
We have to use EXEC or EXECUTE IMMEDIATE in a procedure.
Regards,
Krishna Tangudu
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
When you write "READS SQL DATA" you cannot have dynamic SQL.
So if you are able to get the logic without using dynamic sql then you can use as you mentioned.
The issue here is, in your script based calculation view, you are defining your output parameter names and lenghts right? Hence you are making the output static.
Regards,
Krishna Tangudu
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
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.