Data and Reporting Services - Tips on Using BrioQuery

On this page:

How to Show Remarks or Find the Definition of the Table(s) and Field(s) You are Selecting

After you have placed the table(s) on the Query screen:

• For a table, highlight the table name, and press CTRL+I keys (for Windows and Citrix users) and COMMAND+I keys (Old Mac OS9 users).
• For a field, highlight the field name, and press the CTRL+I keys (for Windows and Citrix users) and COMMAND+I keys (Old Mac OS9 users).

Learn in Advance How Many Records Your Query Will Retrieve

Use the Query Size command under the Query menu. However, be aware that asking for the size of your query involves all the work of processing your query except returning the rows. If you believe your query will return at least several thousand rows, you may want to go ahead and process it.

To Clear Data out of Reports: Pivots, Charts and Details

BrioQuery stores a "printing image" in the report tabs with your query showing information from the last time the query was processed. Clearing data from report tabs before closing your report will ensure that information is kept secure.

To do this, process your query with a false limit. Instead of selecting a Profit Center ID, for example, type in a custom value such as "xxxx". Process the query - it should return zero rows, and both the results and all detail tabs should not have any data.

See the next tip for some related information.

Logoff From the Warehouse Server When You are Away From Your Desk

Once BrioQuery is open and a user logged on, either by processing a query or by requesting the table catalog, the connection to the warehouse server is left open until it is terminated. It is a good idea to develop the practice of logging off the server when you are going to be away from your desk. That way, no one can process queries using your access in your absence.

To log off, either go under the File pull-down menu and select Logoff, or double click on the connection icon in the lower right corner of your screen (it looks somewhat like a dumbbell). After doing either action, the connection icon will have a red X through it, signifying you are no longer connected to the server.

Control the Size of the Query Results That Will be Displayed on Your Screen

Limit the results. For example, you can indicate "return first 100 rows only." To do this, go to the Request Line and double click on the word "Request". When the Modify Request screen appears, click on the Return First box (the default is 100), and then click OK.

What Numbers to Use to Calculate Sums

When your query uses both Fact and Dimension tables, do not put any number fields (such as Authorized Total) from a Dimension table into the lower right side of the Outliner. Number fields in Dimension tables are not meant to be summed in this context and should be treated as text. If you need to find sums, take fields from the appropriate Fact tables.

End a Query: (Stop the Row Retrieval Process)

• For Windows and Citrix users, press ALT+END keys.
• For old Mac OS9 users, press the COMMAND+PERIOD keys.
  This action will cut off communication with the server. Your query will continue to run in the server until it is finished.

To Correct the Disappearing Shadow Box Problem on a Macintosh

Click once in the title bar area of the box, and drag it to a new location. For a disappearing or partially obscured table catalog, try moving the table catalog and press the Refresh Table Catalog button at the bottom of that box.

Get Correct Date When Exporting a Section from BrioQuery to Excel

Some users note that when they export dates to Microsoft Excel, the date that is transferred is one day greater that what is in the BrioQuery report data. To resolve this, go into Excel and make sure it is not set for the 1904 date system:

• Macintosh Users: Tools/Preferences/Calculations: uncheck the box that says 1904 date system.
• Windows Users: Tools/Options/Calculations: uncheck the box that says 1904 date system.

Add an Informational Alert box to a query that will pop up when it is opened

From the File menu, choose Document Scripts. In the pop-up box that opens, under Event Trigger in the center top, it should say "OnStartup."
In the section at the left, click the plus sign by Application, click Methods, double click Alert. You will see "Application.Alert()" appear in the large, empty box. Type your descriptive information within the parentheses – it must be surrounded by quotation marks. When you are done, click OK at the bottom right of the box. The next time you open the query, a pop up will appear containing what you typed in the alert box.

Related Links

Data and Reporting Services