Biplab

New Features of Crystal 2011 for stand alone

2011-09-10T01:02:00.000-07:00

1) Export to RPTR format which creates a read only file and can be opened later using only Crystal Reports 2011 Viewer and even cannot be opened using Desginer.

2) Export to Excel Data-only which support Excel 2007 formatting which supports 1 million records now on for a single sheet.

Crystal Reports 2008 Shared Variable Concept in 3 steps

2011-08-18T04:37:00.000-07:00

STEP (1)
MAIN RPT:
--------------
REPORT HEADER - FINALLY SUPRESS IT BUT DON'T DELETE IT

FORMULA NAME: DECLVAR

SHARED NUMBERVAR SUBTOTAL:=0;

STEP (2)
SUB RPT:
--------------
REPORT FOOTER - FINALLY SUPRESS IT BUT DON'T DELETE IT

FORMULA NAME: SETTOTAL

SHARED NUMBERVAR SUBTOTAL;
SUBTOTAL:=2;

STEP (3)
MAIN RPT:
--------------
REPORT FOOTER

FORMULA NAME: DISPSUBTOTAL

SHARED NUMBERVAR SUBTOTAL;
SUBTOTAL;

Business Intelligence class : Lesson two

2011-03-28T09:44:00.000-07:00

Business Intelligence

The BI system is modelled as a 3-tier architecture. The bottom and entry point is the data warehouse which is the interface to the source systems. This is where data is brought into the BI system from all the different data source type like Excel spreadsheets, Oracle databases or SAP R/3 systems.

The middle layer is the BI server, this is where all the data is stored and it is a format that is suitable for analytical processing, the data is checked for integrity and successful loading.

The top layer is the presentation layer, this is where all the tools are used to present meaningful information to the user. These tools are called the business explorer tools. Amongst them are the BEx query designer, BEx Analyzer etc. These tools will be covered in a later lesson

Business Intelligence

The middle layer is the BI server, this is where all the data is stored and it is a format that is suitable for analytical processing, the data is checked for integrity and successful loading.

The schema used to store transactional databases is the entity model where data is related in a top-down format. Business intelligence warehouses use the multi-dimensional star schema and SAP BI has it’s own version of the star schema which I will cover in another lesson.

Today I will be writing about the architecture of SAP business intelligence and the differences between the way data is stored in traditional and BI systems. This is the second post in this series, you can view the first one in this Business Intelligence Class Series here.

The BI system is modelled as a 3-tier architecture, the data warehouse, the BI platform and the reporting environment.

Data warehouse

The bottom and entry point is the data warehouse which is the interface to the source systems. This is where data is brought into the BI system from all the different data source type like Excel spreadsheets, Oracle databases or SAP R/3 systems.

BI platform

The middle layer is the BI platform, this is where all the data is stored and it is in a format that is suitable for analytical processing, the data is checked for integrity and successful loading.

Reporting environment

OLAP vs. OLTP

Data in a BI system cannot be stored using traditional formats. Why? Because the traditional way of storing data in relational databases is meant for easy and fast access but not fast enough for analytical and reporting purposes. The traditional way of storing data is optimized for online transactional processing purposes (OLTP), while BI data is stored for online analytical purposes (OLAP).

What are the differences between these two?

Current vs. historical data

The data in OLTP database are up to date in real time while the data in OLAP is historical data since it is used for reporting.

High number of users vs. low traffic

Transactional databases are optimized for simultaneous multi-user access while analytical databases are optimized for large volume of data and lower number of concurrent users

Detailed vs. aggregated

The data in BI systems are aggregated because of their analytical nature. We need to compute maximum values or average values for example and do not need the detailed instantaneous values that transactional databases need.

Read-write operations vs. read only

The data in transactional database can be modified unlike in data warehouse scenarios where the data is being copied over from the source systems. BI data warehouses tend to have copies of online databases in a format optimized for analytical purposes.

Entity relationship vs. Multi-dimensional

That is it for lesson two in this series. The business intelligence topic is very wide and there is so much information I am discovering daily and it is really very interesting. If you like this series, please leave a comment and let me know what you think.

Business Intelligence class : Lesson one

2011-03-28T09:41:00.000-07:00

This is the first of the series of posts I will be doing to document my business intelligence training. If you are new to this blog, let me explain.I have decided to pay some hard-earned money to get trained in SAP business intelligence. I have also decided to post some of my notes here for two reasons, 1. For you to be able to follow along and get educated along with me 2. To help myself understand what I am learning. I will not be able to write an article on something I don’t understand and this way I am forced to go and do some more research.Okay let’s start, the first course was about the basic ideas of business Intelligence, what it is all about and why it is important to companies.Why is business intelligence needed?Modern day Enterprise resource planning (ERP) systems have made it easier to gather data about almost anything. This can be the date and time a customer ordered a product, the number of orders over the lifetime of that customer etc. Multiply this amount of data by the number of customers the company has and you can easily see that for a customer that has millions of customers, the amount of data gathered is huge. Another problem is that the data might be saved in different formats, different databases.But this is necessarily not a problem. If you have a system that can efficiently gather the data from all theses sources and produce some meaning from it, that can potentially be powerful information for a company.This is the goal of SAP business intelligence, to streamline the whole process all the way from 1. Gathering the data from all available sources 2. Storing it efficiently for fast and easy access, 3. Analyzing the data and creating reports that can be used to draw meaningful information. 4. Presenting reports in a multimedia format, in the form of charts, images etc. 5. And ultimately help the user in efficient decision making.If someone asks you, what is a business intelligence system. You tell them it is one that basically makes sense of data.This is all I have for now, there was more to the lesson. Stuff about the Data warehouse side of the story andthe differences between a BI/Warehouse (OLAP) system and a classic OLTP system. This is better explained usinga video. So I might do a screencast next.Your comments are welcome as always….

This is the first of the series of posts I will be doing to document my SAP BI training. If you are new to this blog, let me explain.

I have decided to pay some hard-earned money to get trained in SAP business intelligence. I have also decided to post some of my notes here for two reasons,

For you to be able to follow along and get educated along with me.

To help myself understand what I am learning. I will not be able to write an article on something I don’t understand and this way I am forced to go and do some more research.

Okay let’s start, the first course was about the basic ideas of SAP BI business intelligence, what it is all about and why it is important to companies.

Why is business intelligence (BI) needed?

Modern day Enterprise resource planning (ERP) systems have made it easier to gather data about almost anything. This can be the date and time a customer ordered a product, the number of orders over the lifetime of that customer etc. Multiply this amount of data by the number of customers the company has and you can easily see that for a customer that has millions of customers, the amount of data gathered is huge. Another problem is that the data might be saved in different formats, different databases.

But this is necessarily not a problem. If you have a system that can efficiently gather the data from all theses sources and produce some meaning from it, that can potentially be powerful information for a company.

This is the goal of SAP business intelligence, to streamline the whole process all the way from

1. Gathering the data from all available sources

2. Storing it efficiently for fast and easy access,

3. Analyzing the data and creating reports that can be used to draw meaningful information.

4. Presenting reports in a multimedia format, in the form of charts, images etc.

5. And ultimately help the user in efficient decision making.

If someone asks you, what is a business intelligence system. You tell them it is one that basically makes sense of data.

This is all I have for now, there was more to the lesson. Stuff about the Data warehouse side of the story and the differences between a BI/Warehouse (OLAP) system and a classic OLTP system. This is better explained using a video. So I might do a screencast next.

Your comments are welcome as always….

You can find all the other lessons on SAP BI Training here.

Why SAP BI Training ?

2011-03-28T09:38:00.001-07:00

Well, business intelligence is one of the core components of the SAP Netweaver suite. Netweaver is the technology behind all of SAP mySAP ERP Business suite. Every company wanting to implement mySAP ERP will have to have a Netweaver foundation. Are you getting my drift? It is all about positioning myself in the sweet spot of the SAP eco-system.

Anyway, I will be documenting my notes in a series of posts on this blog. You are welcome to follow and read along.

Business Intelligence class : Lesson one

Business Intelligence class : Lesson two

SAP BI Lesson : What is an InfoObject ?

The Enterprise Data warehouse : BI Lesson

Business Intelligence Lesson: What is an InfoCube ?

The ETL Process : Business Intelligence Lesson

Transformation rules : Business Intelligence lesson

BusinessObjects and SAP Part I

2011-03-25T18:27:00.000-07:00

I left beautiful Singapore (it was not raining today) to go to India for two weeks (the forecast was rain and snow). So I am sitting here in the AirIndia plane writing my first in a series of blogs I am planning to write over the next couple of weeks.

I will focus on the current integration between SAP and Business Objects products and will try to outline some of the great options that we can offer in the BI area.

Myself - I started with Seagate Software (later Crystal Decisions) in 2005 as a Crystal Decisions trainer and moved to Chennai to work with the development group from SAP and Business Objects on the OEM partnership between both of them. I had great 4 years in Singapore and made some great friends (greetings to Aparajita and Hiren). I was given then the opportunity to move to India and in my latest role I am working on the SAP Business Objects.

So here are the topics I would like to cover in my blog series

1. Overview on current SAP connectivity integration with the Business Objects product suite

2. Crystal Reports and SAP BW

a. Creating a simple report with a SAP BW query

b. Using BW variables in Crystal Reports

c. Using hierarchies in Crystal Reports

3. Crystal Reports and SAP R/3

a. Using InfoSets in Crystal Reports

b. Using tables and ABAP functions in Crystal Reports

4. Web Intelligence and SAP BW

a. Building a universe on top of SAP BW

b. Using BW variables in Web Intelligence

c. Using hierarchies in Web Intelligence

5. Xcelsius and SAP datasources

a. Creating an Xcelsius dashboard with Crystal Reports

b. Creating an Xcelsius dashboard with Web Intelligence

c. Creating an Xcelsius dashboard with web services

The idea behind all these topics is to give you a brief overview of the capabilities and to provide a quick outline of how you can use this functionality in your own environment. I will try to use existing SAP content as much as possible so that each group can follow my steps in their own system.

As part of my first blog I will describe the available connectivity options for the Business Objects product suite.

This diagram shows the available sources from SAP R/3 and SAP BW.

Depending on the tool you are using you can either use all of the shown datasource or only some of them.

In the next diagram you can see the available options for Crystal Reports.

Crystal Reports is offering the following connectivity for SAP R/3

An InfoSet connection providing access to classic InfoSets and SAP queries
Table, cluster and ABAP function connectivity

Crystal Reports is offering the following connectivity for SAP BW

Access to SAP BW queries and SAP BW cubes
Direct access to SAP ODS objects

This diagram shows the current options for Web Intelligence.

Web Intelligence is offering connectivity to SAP BW queries and SAP BW cubes. As shown, Web Intelligence does not offer direct connectivity to SAP R/3 but instead works in combination with our Data Integrator product and our available Rapid Marts. In this way we can provide a data mart solution on top of SAP R/3 which then can be used in combination with Web Intelligence.

I hope this gave you a quick overview on the available connectivity for Crystal Reports and Web Intelligence in combination with SAP R/3 or SAP BW.

I would really appreciate suggestions for further blogs and would like to hear what is of most interest to people. I will then try to provide the information in the form of a short blog entry

Stay tuned for more on Crystal Reports, Web Intelligence, and Xcelsius on top of SAP R/3 and SAP BW.

Shared Variable Concept in Crystal Report Revealed

2009-08-20T04:58:00.000-07:00

SHARED VARIABLE CONCEPT IN CRYSTAL REPORT:

-----------------------------------------------

STEP (1)

MAIN RPT:

--------------

REPORT HEADER - FINALLY SUPRESS IT BUT DON'T DELETE IT

FORMULA: DECLVAR

SHARED NUMBERVAR SUBTOTAL:=0;

STEP (2)

SUB RPT:

--------------

REPORT FOOTER - FINALLY SUPRESS IT BUT DON'T DELETE IT

FORMULA: SETTOTAL

SHARED NUMBERVAR SUBTOTAL;

SUBTOTAL:=2;

STEP (3)

MAIN RPT:

--------------

REPORT FOOTER - FINALLY SUPRESS IT BUT DON'T DELETE IT

FORMULA: DISPSUBTOTAL

SHARED NUMBERVAR SUBTOTAL;

SUBTOTAL;

STEP (4)

MAIN RPT:

--------------

REPORT FOOTER

FORMULA: SUM

SHARED NUMBERVAR SUBTOTAL;

SUBTOTAL;

STEP (5)

MAIN RPT:

---------------

IF THERE ARE ANY COMMON PARAMETER FIELD THEN USE 'CHANGE SUBREPORT LINKS' BY RIGHT CLICKING IN THE SUBREPORT AND LINK THE PROPER FIELDS TO IT.

Encapsulation Page Failed or Database Vendor Code 1003 Error Solution

2009-06-05T20:55:00.001-07:00

There are no catalogs created inside the userid where BOE is installed, so create it to solve the problem.

Generally this comes when you are using Operating System in Server as AIX 5.3 version and BOE 3.1 is installed in Server machine and client machine are running with Window-XP SP2 and running with BOE 3.1 Client tools and designer will be CR 2008.

Group and Folder Rights for BOE 3.1 for CMC/InfoView

2009-06-05T20:38:00.000-07:00

In Folder of CMC you can find Top-Level Security for All Folders.

In that place set View rights with only Object Level and Un-check Sub-Object Level.

And for each individual folder set rights depepding on Adhoc, View, Schedule or Group relevant rights with Sub-Object Level.

This will restrict each folder level for any user and we can provide the relevant folder depending on User for BOE 3.1 SP 1.5 and CR 2008 Combinations.

Solution when prompts value does not appear in CR Template

2009-06-05T20:05:00.000-07:00

Sometime when you create report template using any base report from any other database or using other version in CR 2008 you will find that Parameters data type are editable and even you can delete the parameters, in that case its confirmed that its not going to take the parameter when you will run the report.

The best solution as of now, is to recreate the report template.

So always check while creating using base version whether the parameters are allowed to delete or not? If allowed means there is something wrong.

The data source for the report template is Stored Procedure. This total explanation is for that only.

Query To Bring "From Date" and "To Date" For Any Month For All Weeks

2009-06-05T19:56:00.000-07:00

Use the following query. The only issue in this is, if there is no record for a entire week, it will not bring data for that week.

select TICKET_TYPE_CD,case when month(week_st)<5 then week_end-(DAY(week_end)-1) days else week_st end ,
case when month(week_end)>5 then (week_st+1 months)-day(week_st+1 month) days else week_end end,
TOTAL_RIDE_ANT,TOTAL_FULL_AMT,TOTAL_CSC_AMT,TOTAL_CASH_AMT
from
(
select TICKET_TYPE_CD,PROCESSING_DATE-(wd-1) days as week_st, sum(TOTAL_RIDE_CNT) AS TOTAL_RIDE_ANT
                       ,sum(TOTAL_FULL_AMT) AS TOTAL_FULL_AMT
                       ,sum(TOTAL_CSC_AMT) AS TOTAL_CSC_AMT
                       ,sum(TOTAL_CASH_AMT) AS TOTAL_CASH_AMT,PROCESSING_DATE+(7-wd)days as week_end
from
(
Select TICKET_TYPE_CD,TCRRS.TOTAL_RIDE_CNT
                       ,TCRRS.TOTAL_FULL_AMT
                       ,TCRRS.TOTAL_CSC_AMT
                       ,TCRRS.TOTAL_CASH_AMT ,PROCESSING_DATE,DAYOFWEEK(PROCESSING_DATE) as wd
from TA_OWN.TC_CSC_RIDE_REV_STATS TCRRS where month(PROCESSING_DATE)=5 and year(PROCESSING_DATE)=2009
) as temp1

group by TICKET_TYPE_CD,PROCESSING_DATE-(wd-1) days,PROCESSING_DATE+(7-wd)days

) as temp2
order by TICKET_TYPE_CD,week_st

Regarding CR2008 and BOE3.1

2009-05-27T05:16:00.000-07:00

If after publishing the report while viewing if there is an error like "Encapsulating Page Failed" then please check the DB2 Server Connection with BOE Server, specially the Catalog whether its talking with the database server properly or not?

Same we need to check even if after scheduling the report we get error like "Database Vendor Code -1013".

This also can be understood if after scheduling the LOVs related to Business Views, while trying to edit the LOV Job Server will be failed.

Regards,

Biplab

CR 2008 Error

2009-05-16T23:42:00.000-07:00

In CR 2008 designer we cannot format the field if the field is put inside an text box, but this was not the case in earlier version like XI-R2.

In Trial Version of CR 2008..restriction

2009-05-08T04:38:00.000-07:00

In Trial Version of CR 2008 one of the major restriction is it never allows you to format any field type.

Business Views - BOE

2009-05-04T06:42:00.000-07:00

1 Business View can be created for many connections. By importing it all connections can be established.

SAP Logon

2009-05-02T21:50:00.000-07:00

After SAP taken over the BO its really difficult to find out several links such even where to logon, so for all who want to raise any PMR, the logon link is:

https://websmp203.sap-ag.de/BOSAP-DOWNLOADS

After clicking on the link of course one needs the Userid and Pwd to work further.