Interactive Reporting Query Developer Guide

UW-Stout Enterprise Information Services

Interactive Reporting Query Developer Guide

Table of Contents 1

Overview ............................................................................................................................................... 3

2

Migrating local Hyperion 8.5 queries to Interactive Reporting v11 ..................................................... 3

3

Using Web Client to create queries ...................................................................................................... 5

4

3.1

Access ............................................................................................................................................ 5

3.2

Create a document using a Template Query ................................................................................ 5

3.3

Save locally .................................................................................................................................... 6

3.4

Open a locally saved query ........................................................................................................... 6

3.5

Work Offline .................................................................................................................................. 7

Appendix ............................................................................................................................................... 9 4.1

Query development standards ..................................................................................................... 9

4.1.1

Fonts...................................................................................................................................... 9

4.1.2

“Required” sections .............................................................................................................. 9

4.1.3

[HIDE] Developer Notes ...................................................................................................... 12

4.2

General Guidelines ...................................................................................................................... 13

4.2.1

Empty data .......................................................................................................................... 13

4.2.2

Compress Document ........................................................................................................... 14

Page 2 of 15

Interactive Reporting Query Developer Guide

1 Overview This document is intended for use by current users of the Hyperion 8.5 Intelligence Explorer/Designer desktop client to help ease the transition to developing queries in the Interactive Reporting web client. Users intending on developing with the web client will still need to have database login ids and passwords for any data sources they intend to access. This is not a substitute for Interactive Reporting tool training but should provide basics about creating, saving, and opening .bqy files using the web client.

2 Migrating local Hyperion 8.5 queries to Interactive Reporting v11 If you currently do development using the Hyperion 8.5 desktop client, chances are you have queries saved locally. In order for the local queries to be used in the new environment, a migration procedure has to be implemented in order to save these queries in the web client with a published data model. The Interactive Reporting (Hyperion) admins will have to do this migration. To get queries migrated to the web client: 1. Empty your query of any data. The Hyperion admins may not have access to the data you are accessing in the query. Since we don’t need to actually run the query, we don’t need the data, only to know the name of the OCE you are using to run the query.  If you just have results sections, just do File  Save Options  Save query results with document, and uncheck all of the query boxes.  If you have pivot sections:  This is trickier. You need to run your query(s) with a limit that returns 0 rows.  Either select a value for an existing limit so that 0 rows are returned, or add a limit for a field and put in a bogus value, and run the query so 0 rows are returned. (i.e. put a limit of term or emplid=0 usually works well)  Remove/reset your limits but do not run the query again. 2. Save your query. 3. Copy your queries to O:\Hyperion\IR Migration Queries\[your name or id]. Contact the Hyperion admin to let them know that you put queries out there to be migrated. Note: this folder is not secured, so do NOT put queries out there that have data in them. Only put .bqy files that are empty. If you cannot empty your query for some reason, zip the queries to be migrated up and send them to the Interactive Reporting (Hyperion) Admin. 4. The Hyperion admin will migrate the queries and put the queries back out in the same folder – renamed as [query name] IR Web.bqy – and notify you when the query migration is done. 5. Copy the migrated .bqy file to your locale pc and delete your original and migrated queries on the O:\ drive (just for clean-up purposes) If you have any questions or concerns about the migration procedure, contact the Interactive Reporting (Hyperion) admin. Page 3 of 15

Interactive Reporting Query Developer Guide To open a migrated query, you must have gone through the Interactive Reporting Web Plug-in download and install instructions. Then 1. Either:  Drag your new migrated .bqy file into an Internet Explorer window.  Right-click and select Open With  Internet Explorer. 2. If you are not logged in to the IR Website, you will be prompted to log in. Select your campus and click select, and you will be prompted to enter your email username and password. Enter your username and password and click Login. Note: You are not REQUIRED to log into the site to use the web plug-in but you will need to in order to connect to the databases (and change or process the query); see Working Offline section.

3. You may get two messages like this (see below). If so, click on the OK button in each of the messages.

Page 4 of 15

Interactive Reporting Query Developer Guide 4. Your query will be opened in the web client, and you should be allowed to edit/modify the query as needed.

3 Using Web Client to create queries The Interactive Reporting Web client provides all of the functionality needed to create local queries and reports using published OCEs. There are some small differences from the previous web client version:      

You can have multiple queries open at once. Limits are now called Filters The Outliner is now called Data Layout You can append/merge multiple queries together (like union) If you use dashboards, you can upload an image once and reuse it multiple times in a query using the Resource Manager. The section catalog (where the tables are listed) now has a search feature where a wildcard * can be used, making it easier to find the table you are looking for.

3.1 Access You will need to be part of the STOUT QUERY DEVELOPERS group to be allowed access to create new queries. You will also need to have database access (a username and password) in order to access the tables and data on the systems.

3.2 Create a document using a Template Query Several template queries have been created for you to use as a starting point for your query. They are already set up with a Home page and [HIDE] Developer Notes page. These templates are located under the STOUT\Query Templates folder and are named according to the OCE file(s) that they access. If you don’t want the additional sections, you can just delete them. Page 5 of 15

Interactive Reporting Query Developer Guide

To create a new query from a template query: 1. Double-click on the template query in the Explore view to open it in the web client. 2. Once the query opens in the web client, click File  Save as…

3. Choose the location to save your file, rename it, and click the Save button. 4. You can develop the query in the IR 11 web client the same way you would in the Hyperion 8.5 desktop client. Notice that the data model catalog (elements pane when the query section is selected) contains a search box. You can type in part of a table name (e.g. REC_*) and click on the little magnifying glass icon button to find the tables you are looking for.

3.3 Save locally To locally save a query you are working on in the Web Client: 1. Select File  Save As… from the menu bar in the web client.

2. Choose the location on your PC to save your file and click the Save button.

3.4 Open a locally saved query In order to open a query that was saved locally from the web client, you have to open the .bqy file in Internet Explorer. 1. Either:  Drag your new migrated .bqy file into an Internet Explorer window.  Right-click and select Open With  Internet Explorer. 2. If you are not logged in to the IR Website, you will be prompted to log in. Select your campus and click select, and you will be prompted to enter your email username and password. Enter your username and password and click Login. Note: You are not REQUIRED to log into the site to use the web plug-in but you will need to in order to connect to the databases (and change or process the query); see Working Offline section. Page 6 of 15

Interactive Reporting Query Developer Guide

3. Your query will be opened in the web client, and you should be allowed to edit/modify the query as needed.

3.5 Work Offline You are not required to log-in to the site to use the Interactive Reporting plug-in. If you do not log-in, you will not see any of the tables in the section catalog, and you will not be able to process your query. You should still be able to modify, sort and filter any locally saved results and create pivots. To work offline: 1. Either: a. Drag your new migrated .bqy file into an Internet Explorer window. b. Right-click and select Open With  Internet Explorer. 2. You will be prompted to log-in. Click the little x in the top right hand corner of the log-in window to close it.

Page 7 of 15

Interactive Reporting Query Developer Guide

3. A little pop-up will give you a message saying “Unknown Server Error”. Click the OK button on this pop-up to close it.

4. Another little pop-up will ask you if you want to connect to the server or work offline. Click on the offline button.

5. Your query should open up in the browser window. You will see the query, but you will not be able to modify the data model structure. The tables section in the Elements pane will be empty (as you are not connected to a database). You will be allowed to create and/or modify results, tables, pivots, charts and reports and work with any local data that you may have saved in the query. You will also be permitted to add a new query, but that query will be created with no connection. Page 8 of 15

Interactive Reporting Query Developer Guide

4 Appendix 4.1 Query development standards ** These standards are target more toward individuals who intend to have their queries published. If you’re developing queries for local use, following these standards aren’t necessary and you can ignore this section. 4.1.1 Fonts The following list of fonts is compatible with the Interactive Reporting server. Although using other fonts is allowable, when run and published on the server warning messages are generated when the server substitutes a different font in. These warning messages fill up the log files and can affect performance. Please use fonts from this list when creating your queries and reports. Arial-Bold Arial-BoldItalic Arial-Italic Arial ArialNarrow-Bold ArialNarrow-BoldItalic ArialNarrow-Italic ArialNarrow Bembo-Bold Bembo-BoldItalic Bembo-Italic Bembo BookAntiqua-Bold BookAntiqua-BoldItalic BookAntiqua-Italic BookAntiqua BookmanOldStyle-Bold BookmanOldStyle-BoldItalic BookmanOldStyle-Italic BookmanOldStyle CenturyGothic-Bold CenturyGothic-BoldItalic CenturyGothic-Italic CenturyGothic Corsiva CourierNew-Bold CourierNew-BoldItalic CourierNew-Italic

CourierNew FreeMono FreeMonoBold FreeMonoBoldOblique FreeMonoOblique FreeSans FreeSansBold FreeSansBoldOblique FreeSansOblique FreeSerif FreeSerifBold FreeSerifBoldItalic FreeSerifItalic GillSans-Bold GillSans-BoldItalic GillSans-Italic GillSans LucidaBrightDemiBold LucidaBrightDemiItalic LucidaBrightItalic LucidaBrightRegular LucidaSansDemiBold LucidaSansDemiOblique LucidaSansOblique LucidaSansRegular LucidaTypewriterBold LucidaTypewriterBoldOblique LucidaTypewriterOblique

LucidaTypewriterRegular MonotypeCenturySchoolbook-Bold MonotypeCenturySchoolbook-BoldItalic MonotypeCenturySchoolbook-Italic MonotypeCenturySchoolbook MonotypeSorts Rockwell-Bold Rockwell-BoldItalic Rockwell-Italic Rockwell Symbol TimesNewRoman-Bold TimesNewRoman-BoldItalic TimesNewRoman-Italic TimesNewRoman Vera VeraBI VeraBd VeraFontsCopyright VeraIt VeraMoBI VeraMoBd VeraMoIt VeraMono VeraSe VeraSeBd batang

4.1.2 “Required” sections The following sections are considered “required” by EIS before a query will be published. Note: Some of the required sections are Dashboard type section that just displays static text and images. These have been somewhat difficult to use. An Appendix at the end of this document gives a brief overview of how to add and rename a dashboard section, as well as well as how to include some basic elements. 4.1.2.1 Home Page 9 of 15

Interactive Reporting Query Developer Guide The Home page is a dashboard that is set to be the first section displayed when the user first opens an Interactive Reporting document. In most cases, it is a static text page that just contains a top image, query name, brief description, brief instructions on how to run the query, and a spreadsheet-like image that shows what the filters, results, and pivot fields (for user visible sections) are. An example of the Home page is below.

To create/insert a Home page dashboard:  

Select Insert  New Dashboard from the menu. Rename the section Home. This can be done by double-clicking the section in the Sections pane, or right-click and select Rename Section…



It should automatically put the dashboard into design mode (which will allow you to edit and update the Home page). If not, you can toggle between design and preview modes by clicking CTRL+D.

Page 10 of 15

Interactive Reporting Query Developer Guide General Tips: 

To insert a graphic, line, shape, static text label, etc onto your dashboard, select Dashboard  Insert Graphic  … from the menu. You can also drag the element from the Graphics section of the Elements pane into the dashboard (only available in design mode)



To insert a control (i.e. textbox, button) onto the dashboard (if you are building additional scripting functionality in) select Dashboard  Insert Control… from the menu, or drag the control from the Elements Pane.

4.1.2.2 Top Image This is used as the top image for most of the published queries. The image below was resized to fit the page. Dimensions of the original image are 905 x 117 pixels

4.1.2.3 Query Name The query name is added in the space of the top image using a text label. It should be bold, easy to read, and use an approved font. Arial Narrow, Bold, Size 18 Darkest Blue works well.

4.1.2.4 Description and Instructions Page 11 of 15

Interactive Reporting Query Developer Guide The Description and Instructions Text Label should contain text that briefly describes what the purpose of the query is. It should also contain instructions on how to run the query and view the results. Example below:

4.1.2.5 Filter, Results, and Pivot Details This is typically an image from taken from a spreadsheet. It should show the variable and static filters on a query, and any fields in a visible results, pivot, or table section.

4.1.3 [HIDE] Developer Notes This section is a static text dashboard that contains an image, title, and a text box listing the date, id, version number and change details of the specified IR document. Its intended audience is the developers ONLY. This helps us keep track of changes and versions across developers who may be updating the same documents. 4.1.3.1 Suggested Format The section should be named [HIDE] Developer Notes, and should be hidden from end user. Format can vary if creating from scratch. Below is an example of the format:

Page 12 of 15

Interactive Reporting Query Developer Guide

4.2 General Guidelines 4.2.1 Empty data In order to protect data, you should empty the data from your query before saving it. Emptying the data from the saved query reduces the size of the query, ensures that the user is looking at the most recent data, and eliminates the risk that is included with saving data in a document. Depending on the sections that are included in your document, there may be a few things you have to do. 4.2.1.1 File  Save Options Save Query Results with Document This is the easiest way to ensure that your results sections have no data in them. 1. From the menu bar, select File  Save Options  Save Query Results with Document…

2. Make sure that the checkbox beside any query is cleared, and click the Ok button.

Page 13 of 15

Interactive Reporting Query Developer Guide 3. Save the document. If you close the document and re-open it, you’ll notice that the query results sections are grayed out.

4.2.1.2 Run Query for 0 results and reset filter(s) (limits) Before a query is uploaded, the results sections for any included queries need to be cleared so the file can be saved without any data to prevent any possible data exposure. To clear all results sections, you will need to enter an invalid value into at least one of the filters so that each query that is defined returns 0 rows. To empty the results section: 1. Select Tools  Process Query  All to run all queries.

2. If prompted for a variable filter, click on the Custom Values button on the filter dialog box, and enter an invalid value. For example, use 0 for term. Then click Ok.

3. Check all results sections to ensure that all are empty. If the query does not have any filters or a variable filter, you may have to temporarily add a filter in order to clear the results. If so, add the filter, run the query, and delete the filter. 4.2.2 Compress Document To save space, it’s a good idea to compress the document. This is a simple Save Options setting. Go to File  Save Options and select “Compress Document”. Page 14 of 15

Interactive Reporting Query Developer Guide

Page 15 of 15