WIXLIB

BRIO QUERY FUNCTIONALITY IN COMPARISION TO CRYSTAL REPORTSCategoryBrio FunctionalityAvailableinCrystal?Downstream AnalysisPivot TablesNotesNested QueriesYesPossible through sub reports.Requires user to have technicalknowledgeAbility to create data setsYesThis is the SQL entered whencreating the Crystal ReportAbility to create tables and uploadtablesNoPivot TablesYesYesCross-Tab in Crystal ReportsCreate a new Formula 1. During Cross-tab creation.2. Edit Cross Tab using CrossTab Expert and create a newformula to add to Cross-tab.3. Use Field Explorer prior toCross-tab creation, then add toCross-TabYesCross-Tab in Crystal ReportsCalculating columns in pivot tablesCreate a single query with severalcrosstabsAbility to create a cross tab withmultiple fields as column valuessubtotaling each combination (ie,columns of cpt and FY) with units andcharges as the values by on the leftside, ACUNeed more info, but cross-tabreports are available in Crystalreports.1

Selecting DataCalculating “Distinct Counts” (doesn’talways work)YesSelect Summary type in CrossTab expertAbility to set multiple criteria limitsYesIn the Select Expert. Requiresentering SQL Statement formultiple limits.Creating Bridge tablesNoThis seems more of a DWfunctionality.Bridge table follow up: HSDW usesbridge tables whenever there is arepeating field in the data, IE, morethan one diagnosis on a charge. As aresult, in order to both see all of theDiag for a charge as well as the orderof importance, the charge is tied to thebridge table which is then tied to thediagnosis table. How will we accessthe diagnosis and the diagnosis order(ie, 1st to 4th)?YesYou can connect to an“intermediate” table to use as ajoin between two other tables.Multiple joinsYesWhen creating Data Set.YesJoin interface very similar toBrio.YesFormula EditorYes1. SQL Command2. Formula EditorAbility to choose the type of join(simple, left, right)Calculated columns in query andresults as well as pivotDistinct Count2

Change schema or table name withouthaving to completely rebuild.YesUsing Set DataSource Locationunder Database menu.Limit the query by a text file ofspecific values for a fieldYesYou can filter based on a text filejoin.Limit the results by calculated fieldcreated in queryYesFilter on calculated field.Options of “and” and “or” in the limitsYesColumn GroupingsYesFilter on columns in data modelYes2. Using Select Expert inReport menu.YesFiltering or sub-queries can becreated.Limit the results by calculated fieldcreated in resultsYesCalculated fields can be created.Limit results based on a GroupingColumn value(s)YesGrouping is available.Ability to rename a field in the resultsYesColumn headers can be renamed.Ability to limit based on fieldcontaining specific textYesFiltering.Field Manipulation in ResultsCreate a single query and limit theresults to a subset of within the queryand then change the limit to somethingelse, etc.These are standard SQLcommands.1. In Report creation wizard2. Using Column Expert inReports menu.1. In Report creation wizard3

Running QueryReturn # rows or process for # minutesfor testingYesAbility to halt a runaway queryYesSync with DatabaseYesExport and import SQLYesExport Results1. displays in status bar aftersuccessful completion of query2. Performance Informationunder Report menu.Click the Sop icon in theToolbar.Verify Database under Databasemenu.Import using Database Expert.Export using Show SQL Query.Both under Database menu.Copy and paste from Brio to excelCan “export” to Excel.Password changeYesIf backend password waschanged then a new passwordcan be provided when runningthe query. User is alwaysprompted for user id andpassword when executing aquery.Save multiple queries in one fileNoNot available in 1 file. Subreports can be created to achievethis.Save with or without resultsYesSave Data with Report underFile menu.General4

Copy a query and slightly modifywithin the same “file”For a report with several tables, theability to see all of the tables and all ofthe joins between each table to ensurejoin corrections or adjust the type ofjoinA query is one file.YesJoin interface very similar toBrio.Converting a Brio Report to Crystal ReportExport the SQL from Brio query.Open Crystal Reports.You will have the option to create one of the following reports.Standard Report Wizard: to display columns and rows as is from the SQL query. You will have ti option to select what columns to display.Blank Report: Allows you to place the columns and provides high flexibility for creating highly formatted reports. This will not be discussed inthis document.Cross-Tab Report Wizard: Creates a Pivot table output. You have to select the columns and rows.5

The other 2 options are not relevant for this conversion.Standard Report WizardClick on Standard Report Wizard under New Reports.Standard Report Creation Wizard dialog box is displayed.The Data selection window is the first window to be displayed.6

Expand Create New Connection and then ODBC (RDO). The ODBC (ROD) dialog box is displayed.Select HSDW. This is the DSN you created to connect to HSDW tables.Click Next.Enter User Id and Password in the Connection information dialog box.7

Click Finish to close the ODBC (RDO) dialog box.Under HSDW select Add Command and click on the sign in the center pane.8

The Add Command to Report window is displayed.Copy the SQL generated by the Brio report and paste it into this window and click OK9

The command is added to the Standard Report Creation Wizard.10

Click Next to display the Column (Field) selector window.11

Select the column you want to display on your report and click on to move the column under Fields to Display box. Click Nextwhen done selecting columns. This can be changed later if needed.If you want to Group your data by anu columns select the column in the Grouping window. Otherwise click Next. This step isoptional and column needn’t be selected here.12

You can filter on columns in your Data Set (SQL) in the Record Selection window that is displayed next.13

In this window, once a column is moved under the Filter Fields section you can select the comparison operator and the value for thefilter. Click Next when done.14

You can optionally select a template on the next window before clicking on Finish to display the results and to Save it.Cross-Tab Report WizardSelect the Cross-Tab Report Wizard under New Reports in order to create a Pivot Table with your Data Set.The Data selection window is displayed as in Standard Report Wizard.15

Select Create New Connection and expand ODBC (RDO). Select HSDW in the ODBC (RDO) window from the list of displayed System DSNs. Enteryour User Id and Password in the Connection Information.Under HSDW click on Add Command and click the to add it to the Selected Tables section.Copy the SQL generated by Brio and paste it into the Add Command to Report window.16

Click OK to proceed to the next step.Click Next once the command is added to your report. This means that the database connection was successful and the command (SQL) hasbeen verified.17

Select the Pivot Rows, Columns and Summary Fields in the Cross-Tab window. Click Next when done.18

Select a column and click on under Rows/Columns/Summary Fields to add the columns to that section of the pivot.19

Various Summarization options can be selected for the Summary Fields.In the next window you can optionally add a Chart to your report.20

Click Next when done with this window to display the optional Record Selection window.21

Select a Grid Style for your output and click Finish.22

Your report output is displayed.23

24

Brio. Calculated columns in query and results as well as pivot . Yes . Formula Editor . Distinct Count . Yes . 1. SQL Command . 2. Formula Editor . 3 Change schema or table name without having to completely rebuild. Yes . Using Set DataSource Location under Database menu. Limit the query by a text file of