How to Retreive List of Objects from an Universe

Visual walkthrough: How to extract list of all Objects, Conditions, Hierarchies, Tables, Joins, Contexts and Parameters from an Universe using Designer.

Topics Covered:
1) Exploring the Print/PDF Options of the Designer
2) Saving universe as PDF to get the list of all desired details of that universe

Steps:
[1] Once you have your universe opened, go to Tools > Options and select the appropriate options:



[2] Select your options. Make sure to select Objects and Hierarchies to get the tree structured view (see below, Step 6).



[3] Save the universe.



[4] Make sure to save the universe as PDF.



[5] Here's your universe summary:



[6] Universe details:



[7] And finally, snapshot of the actual universe; in this case, our favorite - Island Resort Marketing. You get the snapshot by selecting "Graphical Structure" from step 2.



End. 

How to create Custom LOVs in Dimensions

Video walkthrough - How to Create and or Edit Custom List Of Values (LOVs) in Dimensions of Business Objects Universes.

Topics Covered:

1) Creating and Editing Context based Custom LOVs

2) Sneak peak at how Business Objects actually generates all Dimensions' List Of Values

Scenario:

You have 35 Customers which is tied to a "Customer" dimension. Amongst these customers, 25 of them made reservations and they are tied to a dimension called "Reservations" and 21 of them actually converted into sales and they are tied to "Sales" dimension.

While creating reports using the universe containing the above dimensions, if your Marketing team wants to see only the 25 customers in their Query filter or if your Accounting department wants to see only the 21 customers in their Query filter while building WebI reports, we'd need to create 2 additional dimensions based off of the original "Customer" dimensions and we may call them "Reservations Customers" and "Sales Customers". From the Designer, we will have to go to the properties of these latter 2 dimensions and modify their default LOV.

Once we've successfully created custom LOVs of the 2 new customer dimensions, then if the Marketing team and the Accounting team drags in their respective customer dimensions in their Query Filter, they will only see 25 and 21 customers, not the entire list of 35 customers.

See video for complete walkthrough...

Migrate Universes from Development to Production

Universe Migration steps:

This is one of the ways to migrate a universe from Development to Production universe if you want to keep them consistent and identical.

We'll use a generic example: X and Y are the same universe in different environments-Development and Production respectively. It's always best practice to archive all universes that need to be updated, using Import Wizard's BIAR (Business Intelligence Archive Resource).

So we have:

* Dev Environment: Universe X

* Production Environment: Universe Y

1) Update: Universe X (add dimensions, tables, etc - update this universe)

2) Copy: Copy Universe X and name it "Universe X - Copy" (in Development)

3) Migrate: "Universe X - Copy" to Production from Development (Using Import Wizard)

4) Backup: Universe Y (Either create a copy of this in Production or BIAR it)

5) Designer: Open "Universe X - Copy" (in Production)

6) Data source: Update Data Source of "Universe X - Copy" with the Data Source of "Universe Y"

7) Publish to Prod: Rename / Replace "Universe X - Copy" to "Universe Y"

8) Delete Universe Connection that got carried over by "Universe X - Copy" from Dev to Production.

9) Delete "Universe X - Copy" from Production

10) Delete "Universe X - Copy" from Test

Finally, the updated Universe X from Development is now the new Universe Y in Production.

Free Hand SQL: Crystal Reports and Static Parameters

Video - Topics covered

(This is a follow up video of our previous videos on Crystal Reports (Standard Report Wizard on Crystal Reports XIR2 and Static Parameters in Crystal Reports.)

1) How to create Crystal Reports using free hand SQL.

2) How to Create Static Parameter with Static List of Values. (again, using free hand SQL).

Static Parameters in Crystal Reports

Video - Topics covered:

This is a follow up video of our previous video on How to create Crystal report using Standard Report Wizard.

1) Static Parameters - Static parameters are used in prompts values or List of Values that do not change, or do not change frequently. For example, if a report requires a prompt to select a particular State, it would ideal to use a Static LOVs because the number of states is unlikely to change any time soon. Also, static parameters are very fast becuase all the LOVs are loaded in the report and Crystal Reports does not have to query the data source to retrieve the LOVs.

2) Static List of Values - used by Static parameters, can be retrieved and stored with Static Parameters created on a Crystal report using the 'Append All Database Values' feature. You can also import the LOVs from external text files or simply type your own LOVs.

Standard Report Wizard on Crystal Reports XIR2

Video - Topics covered:

1) Standard Report Wizard - How to create Crystal reports using database tables - i.e. simply dragging and dropping tables and joining them - much like in Designer creating universes.

2) Group Expert - as it's name suggests, this is an extended front end version of the 'group by' function of Crystal reports. Very easy to use and very useful. Those of you who write SQL a lot, know sometime it gets very painful when using Group By clause in your code. Not in Crystal Reports.

3) Design view - introduction to the sectional layout of Crystal reports, including Report Header, Page Headers, Group Headers (if you do not use any grouping in your report, you won't have Group Headers), Details - main data, Group Footers, Report Footers , Page Footer. So, it looks like this in Design view:

Report Header

Page Header

Group Header

DETAILS

Group Footer

Report Footer

Page Footer

4) Select Expert (Selection Formulas) - The name of this feature isn't very intuitive but this can be thought of as an extended front end of version of Where clause. We can restrict and manipulate data using this feature. For example, if we wanted to assign a custom Parameter/promot to an available field, we would use Record Selection Formulas.

5) Formula Workshop - Formula Editor Very intuitive. You can create all sorts of formulas using SQL or Crystal syntax. For example, UPPERCASE, NULL behaviors, data type conversions, mathematical calculations and so on.

Stored Procedure in WebI Reports Part 3

Image walkthrough: Stored Procedure with Parameters / Prompts in WebI Reports

[1] We are starting with this WebI report which was created with a simple stored procedure, in Part 2: That stored procedure just returned 2 columns. Now, we are going to work with a stored procedure with 2 input parameters, aka 'prompts' (in WebI). Very useful.

[2] Our 2nd stored procedure returns 2 columns, based on users' response. The parameters are 'StateID' and 'StateAbbrev' - with datatype of number and string / varchar, respectively. And since we know where these 2 fields are from, a quick way to test this procedure would be to find and enter 2 valid parameters when executing the procedure, in this case, we'll pick '3' for state 'AR'.

Let's run the procedure:

Usage format:

EXEC Stored_Procedure StateID, StateAbbrev

EXEC Stored_Procedure 3, AR

> see results of this procedure at the bottom of the following pic

[3] So, back to WebI > SQL (View SQL) > Use Custom SQL:

We'll apply our store procedure: EXEC Stored_Procedure 3, AR

make sure, to use "SET NOCOUNT ON" and comment out (/* blah blah */) the codes generated by WebI - do NOT delete the codes.

Validation successful.

[4] Run it and the report runs. So, we got it to work. Don't worry about the column headers, you can rename them to suit your needs.

Now, let's create prompts.

[5] We'll use both types to be Alphanumeric

This your basic @Prompt syntax:

@Prompt(‘message’,‘type’,[lov],[MONO|MULTI],[FREE|CONSTRAINED])

So we have

-- EXEC Stored_Procedure 3, AR (Original format)

EXEC Stored_Procedure

@Prompt('Enter STATE ID' , 'A' , MONO , FREE , PERSISTENT) ,

@Prompt('Enter STATE ABBREVIATION' , 'A', MONO , FREE , PERSISTENT)

... And validation successful. Great.

[6] As we save and run the above modified WebI query, we are prompted - perfect! So, we'll once again enter '3' and 'AR' to match with the results we got when we ran this procedure on SQL M our result in SQL Server Management Studio.

[7] So, now let's run this stored procedure with prompts on WebI - bingo, Success!

[8] NOTE:

If you go back to the code, you'll see that the @Prompts are gone in custom SQL query. Don't worry, simply "close" the code and the report will still prompt you. BUT, if you hit "save", then it will only retain the value you see - in this case '3' and 'AR'.