Generate Smartcuts for all objects under a folder 10

This entry was posted in Planning Smartcut SQL TaskList Workspace on by (updated 2367 days ago)
This post is all about Smartcuts šŸ™‚ (I like that term)

There was a post in OTN where OP was looking for Smartcuts of all PDF reports under a folder.

The below SQL will give the name of the file, its path and the Smartcut. (I stole it (hmm actually part of it) from network54, see I’m a shameless person :))

SELECT    
  A.NAME,    
  REPLACE(B.PATH,'/Root','') AS PATH,   
  'http://yourservername:19000/workspace/browse/get'||REPLACE(REPLACE (B.PATH,'/Root',''),' ','%20')||'/'||REPLACE(A.NAME,' ','%20') as SmartCut    
  FROM V8_CONTAINER A, (    
  SELECT    
  V8_CONTAINER.NAME ,    
  PARENT_FOLDER_UUID,    
  CONTAINER_UUID,    
  CONNECT_BY_ISLEAF "IsLeaf",    
  SYS_CONNECT_BY_PATH(V8_CONTAINER.NAME,'/') AS PATH    
  FROM    
  V8_CONTAINER    
  WHERE    
   META_TYPE_UUID = 'ID03'    
  CONNECT BY PRIOR CONTAINER_UUID=PARENT_FOLDER_UUID    
  START WITH CONTAINER_UUID='REPORTMART') B    
  WHERE    
  A.PARENT_FOLDER_UUID = B.CONTAINER_UUID    
  AND A.META_TYPE_UUID='ID35'    
  AND B.PATH like '%/TBC SAMPLES'  
 ORDER BY A.META_TYPE_UUID ASC, PATH ASC;

If you are wondering what those IDs (META_TYPE_UUID) are, here is a list of META_TYPE_UUID for common files.(ID03 stands for folder)

  • ID35 – PDF
  • ID20 – Word Doc
  • ID136 – Word 2007
  • ID22 – PPT
  • ID135 – PPT 2007
  • ID21 – EXCEL
  • ID134 – EXCEL 2007
  • ID38 – TXT
  • ID13 – HTML
  • ID32 – Zip
  • ID24 – JPG
  • ID18 – GIF
  • ID200 – Financial Reports
  • ID300 – WebAnalysis Reports
  • ID29 – Interactive Reporting
  • ID16 – Production Reporting

I know that you are aware of using urls in Planning Tasklist.

If you are not then have a look at Adding Tasks to Task Lists

So what if I’ve an FR report which I want to show to the user after he is done with his data entry.

I can use the Smartcut of the FR/WA report and add that as a URL in tasklist. However it doesn’t work that way.


You’ll receive an error “EPM Workspace cannot function inside of a frame. Contact your system administrator.”


There is a solution article available at http://support.oracle.com


When Opening an Hyperion FR or WA Report Embeded Through Smartcut URL In A Planning Task List Results in Error: “EPM Workspace cannot function inside of a frame. Contact your system administrator.” [ID 1314405.1]

Support asks to use a special url instead to launch the report.

http://server.example.com:19000/workspace/WorkspaceLaunch.jsp?uri=index.jsp%3fmodule%3dwksp.relatedcontent%26repository_path%3d%2Fmyfolder-name-here%2Fmy-report-name-here

Couple of things I noted while using the special url, I’ll call it SmartTaskLink šŸ˜‰

If you have special characters in your folder name or report name those should be encoded. What encoding are they talking about?

Well it is URL encoding they are referring to, for example if you have a space in the folder then instead of space use %20. I’ve covered that in my SQL, where spaces are replaced with %20. If you are using any other special character, check http://meyerweb.com/eric/tools/dencoder for special character URL encoding.

Once you launch the FR report, Planning Task List will show this message “This window can now be closed since the application has been launched in a separate window. Note: Popup blockers may prevent this application from working properly.”













Don’t close the window. It is your planning window (don’t worry about the message, I know you wont)













As you can see that WA report (same applies for FR) will open in a different tab and you’ll get all the functionality (as if you opened it from Explore)

Let’s say that once the report is open and you are now back to Planning tab and trying to navigate to forms folder. When you click on a Forms, you will get a pop-up asking whether to stay on the page or leave the page. Always click on “Stay on Page”.













If you click “Leave this page”, you know what will happen šŸ˜‰

Here is the SQL to create SmartTaskLink šŸ˜‰

SELECT    
  A.NAME,    
  REPLACE(B.PATH,'/Root','') AS PATH,   
  'http://yourservername:19000/workspace/WorkspaceLaunch.jsp?uri=index.jsp%3fmodule%3dwksp.relatedcontent%26repository_path%3d'||REPLACE(REPLACE(REPLACE (B.PATH,'/Root',''),' ','%20')||'/'||REPLACE(A.NAME,' ','%20'),'/','%2F') as SmartTaskLink   
  FROM V8_CONTAINER A, (    
  SELECT    
  V8_CONTAINER.NAME ,    
  PARENT_FOLDER_UUID,    
  CONTAINER_UUID,    
  CONNECT_BY_ISLEAF "IsLeaf",    
  SYS_CONNECT_BY_PATH(V8_CONTAINER.NAME,'/') AS PATH    
  FROM    
  V8_CONTAINER    
  WHERE    
   META_TYPE_UUID = 'ID03'    
  CONNECT BY PRIOR CONTAINER_UUID=PARENT_FOLDER_UUID    
  START WITH CONTAINER_UUID='REPORTMART') B    
  WHERE    
  A.PARENT_FOLDER_UUID = B.CONTAINER_UUID    
  AND A.META_TYPE_UUID='ID300'    
  AND B.PATH like '%/TBC SAMPLES'  
 ORDER BY A.META_TYPE_UUID ASC, PATH ASC;


HTH
Update 11/24/2014

The above given SQL to generate SmartTaskList will not work in 11.1.2.3.500, Oracle changed the way how reports are identified. It is now using UUID (which makes more sense rather than going by path and report name). If you are on 11.1.2.3.500 and trying to generate the url for opening a FR report in Planning tasklist then use the one below.

SELECT    
  A.NAME,    
  REPLACE(B.PATH,'/Root','') AS PATH,   
  'http://yourservername:port/workspace/WorkspaceLaunch.jsp?uri=index.jsp%3fmodule%3dwksp.relatedcontent%26repository_uuid='||A.CONTAINER_UUID as SmartTaskLink   
  FROM V8_CONTAINER A, (    
  SELECT    
  V8_CONTAINER.NAME ,    
  PARENT_FOLDER_UUID,    
  CONTAINER_UUID,    
  CONNECT_BY_ISLEAF "IsLeaf",    
  SYS_CONNECT_BY_PATH(V8_CONTAINER.NAME,'/') AS PATH    
  FROM    
  V8_CONTAINER    
  WHERE    
   META_TYPE_UUID = 'ID03'    
  CONNECT BY PRIOR CONTAINER_UUID=PARENT_FOLDER_UUID    
  START WITH CONTAINER_UUID='REPORTMART') B    
  WHERE    
  A.PARENT_FOLDER_UUID = B.CONTAINER_UUID    
  AND A.META_TYPE_UUID='ID202'    
  AND B.PATH like '%/FR Books'  
 ORDER BY A.META_TYPE_UUID ASC, PATH ASC;

About Celvin Kattookaran

Iā€™m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with ā€œIntekgrate Corporationā€ based out of Aurora office. Iā€™m from Godā€™s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). Iā€™m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

10 thoughts on “Generate Smartcuts for all objects under a folder”

    • Reply ↓
      Sandra

      No doubt, when using web-based services, you may need to generate a URL in the code. This involves URL encoding (Encode) and decoding (Decode).
      A small tool for manual Encode/Decode
      https://url-decode.com/
      that tool not only provides URL decode/encode, base64 decode/encode, but also dozens of other tools as well.

  • Reply ↓
    Anonymous

    Hi šŸ™‚

    How am I suppose to run a report/job directly in background mode through smartcut url in EPM workspace.

    It's urgent. Appreciate your help šŸ™‚

  • Reply ↓
    Jessika

    Hi!

    Celvin, I'm using EPM 11.1.2.3 and I have to create a link for excel and PDF files and all the users need to access the link to download.

    I have used directory and UUID to create a link, and I can open the files with both ways in a new tab of IE, but if I use the link into tasklist show error "EPM Workspace cannot function inside a frame".
    PS: I have selected "use single sing on".

    Do you have any suggestion?

    Thanks!

    Jessika

  • Reply ↓
    Suvani

    Hi Celvin,

    I have 11.1.2.3.500 on IE11 on Enterprise Mode, when I use the SmartCut URL using the %20 for the spaces on Task Lists for the FRS report and try to open the report from My Task Lists, I get the following error
    “This product could not be displayed because you are using an unsupported browser. Contact your administrator”. When I click OK, the new window opens and gives Bindows error.

    Any suggestions would be highly appreciated.

    Thanks
    Suvani