Create Dynamic prompt on WEBI report.

Compared to Crystal Report, create a dynamic prompt on Web Intelligence report is simplier. The design view of WEBI is always has two window: Top window contain any data field you drag and drop from the Business Objects Universe as display on the report. Bottom window contain any filters you could build. The filter can also turn to prompt.

1.in WEBI report design window, drag a data field into the Query Filter window, select a condition as "prompt".

2.When user access this WEBI report, the prompt windows will display. in this case, user must select a data value (Year name) from a list of values. 

3. Then click Refresh Query, the WEBI report display the filtered data based on user selection prompt.

4. One thing about Business Objects WEBI is quick. One click can turn filter to prompt. In this case, one right click can turn a report table into graphic. Just select "Turn To"

5.Select the graph you prefer then click OK

6. Report table now turns to bar graph.

SQL: loading data from one table to another using INSERT INTO

Here is a quick script to load data from one table to another one. You can just copy the data or add optional condition with the WHERE statement. The syntax works like this

INSERT INTO dbo.targettable ( [column1],[columnN])
SELECT [column1],[columnN]
FROM dbo.sourcetable GO

INSERT INTO AdventureWorksDW2008.dbo.FactInternetSales
([RevisionNumber],[OrderQuantity],[UnitPrice]
,[ExtendedAmount],[UnitPriceDiscountPct],[DiscountAmount]
,[ProductStandardCost],[TotalProductCost])
SELECT
[RevisionNumber],[OrderQuantity],[UnitPrice]
,[ExtendedAmount],[UnitPriceDiscountPct],[DiscountAmount]
,[ProductStandardCost],[TotalProductCost]
FROM AdventureWorksDW.dbo.[FactInternetSales]
WHERE OrderQuantity > 200
GO

Crystal Report: Build a User prompt for filtering report data

As a report builder, quite often I built predefined filters for the reports.  Sometimes report users like to filter the report data as they saw fit. In that case a dynamic prompt is needed so user can click and select  data to be displayed on the report.

in this example, a dynamic prompt is needed to allow report viewers select data by State 

1. From the Crystal Report Design View, click on Parameter to create new parameter.

2. From Parameter window, select "Dynamic" as the List of Values (or, you can specify a static list for user to select.) on Choose a Data Source, select "State" (you can select any column field of a table where Crystal Report is connected to.)

3. After you specify the value, the parameter is named as "?by State"

4. After click OK a new parameter "By State" is created, drag to the Report area.

5. On Crystal Report Design View, click on the icon "Selection Expert" to create a filter. Click on the drop down menu to select the parameter {?By State} as a filter, then OK.



6. That's it. a user prompt is created on the report, when users open this report, they will first see this window prompt for State selection.

7. After a state been selected, the report will open with specific data displayed.

Web intelligent: using variables on report.

Variables can be created in Universe Designer or build into the Webi report.  Using the sample database, a US company has sales revenue data collected from multiple states. Let’s assume multiple states charge various percentage of state corporate tax.

There are States and Sales Revenue data in the database which being retrieve to build the web report.  A variable is needed to be added to calculate the amounts of the State Income Tax from three different states.

1. From the exiting webi report add a blank column

 

2. Name the blank column title as state income tax

3. From webi report tool bar, click Variable Editor to create a variable

4. The variable will calculate the amount of state income tax based on the names listed on the [State] column

5. In Varialbe Editor, enter the formula like this:

         =IF( [State]="California";[Sales revenue]*0.05;
           IF ([State]="Colorado"; [Sales revenue]*0.1;
           IF( [State] ="New York";[Sales revenue]*0.08)))

6. Now the amount of state income tax of the three states are shown on the report.

Find large tables in a database by row count

The following SQL query is use to list the number of tables within the database that exceed 10000 rows.

          USE AdventureWorksDW2008
          GO
          select distinct(O.[name]),I.ROWCNT
          FROM sysobjects O join sysindexes I ON O.[id] = I.[id]
          where I.rowcnt >10000

Querying Active Directory and export data

A friend of mine who is a System Administrator for a office of few hundred users. One of  a task is print out a company phone list from time to time. Sys Admin and HR manager used to walk around everyone's desk to get the phone extension number, then compile a total employee name and phone list on a spreadsheet. They are looking for a easy way to do this task.

First of all everyone in a office should enter his/her contact information into the Global Address List (GAL) Sys Admin already spend lots of time to manage everyone's email address, distribution list, email alias, mailbox size...mean while everyone in office has phone extension number, personal or company's blackberry number,  and they might have a i phone too, and they might change numbers later on. or they might move to different cubicles thus phone list need to update again....

Therefore, it is better for users to manage their own contact information in GAL. Microsoft has the utility called GALMOD so user can edit only his/her own contact information in GAL. Visit this site to download GALMOD:
  http://support.microsoft.com/kb/242223  

Activity Directory can store more data than just user login name and password. After users contacts are updated through GALMOD, administrator can pull information or statistic figure by querying AD. To build and export a company phone list is only one of the many things AD query can do:

Build a query

    * Open Active Directory Users and Computer

    * Select Saved Queries -> New -> Query

    * Give a name to your query

    * Select the root that contains all your users (be careful not to get computers as well)

    * Select Include sub containers if required

    * Build a query string -  the simplest is Users -> Name -> Has a value

    * The Windows MMC has a option to save the query as .xml file, so it can be reused later on.

Export the data

    * Right click in the data section, select View -> Add/Remove Columns

    * Select 3 columns in the order of username, email, fullname e.g.

          o Make sure you remove the default columns

    * Right click in the data section, select Export List ..

    * Enter a file name

    * Change Save as type: to _Text (Comma Delimited)(*.csv)

Crystal Report: highlight report data with color.

Crystal Report can direct connect to most of database servers in market, retrieving data then generate reports.

When data is on a report you might want to highlight them for easy reading. One way to do so is apply a condition of font color.

Here is a sample report with a list of sales performance figures. Let's apply a condition: when a unit sales figure is less than 4000 sales figure will displayed as red. Otherwise will be green. 

From the Crystal Report Design window, go to the Front Property of the Quantity Sold field. then insert a string like this:

After that, save the property and close. Switch Crystal Report from Design to Preview mode. The Quantity Sold data is highlighted as the condition specified. 

Convert table collation

Sometimes when performing a join operation between two tables with different collations you might come across an error like this:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP850_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

A method to work around this is to make all tables using the same collation. However, modify a collation usually requires a drop and re-create the table itself.

Well, we can use this query to modify the collation of a table on the fly:

    USE AdventureWorks2008
    GO
    ALTER TABLE dbo.Person
    ALTER COLUMN FirstName
    NVARCHAR(50) COLLATE Latin1_General_CI_AS NULL

Run this to the column(s) having problem.
P.S: Latin1_General_CI_AS  is a default database collation in SQL Server.