SAP BO BI OVERVIEW & FORMULAS

This content is very help full to who are new to Sap BO BI.

BW, BI and BIW (Business Information Warehouse) are the same product. BW is definitely the passe term, but I am not sure what the correct current term is. Probably BI. It’s SAP’s traditional Business Intelligence product. They might have originally bought in the 90’s (I’m not sure) but they’ve had it so long no ever thinks of that.

BO and BOBJ are the same thing. BO is what consultants call it (Business Objects), BOBJ (Bob-J) is what the sales people call it (sounds cooler right?). Bob-J might have been a term that became more popular after SAP bought them in 2008 (or thereabouts).

BOBJ and SAP BI were competitors in the market for a while, so there was (and still is) a lot of common functionality. Here are some differences:

1. Product Pricing: SAP BI is virtually free for SAP ERP customers, especially for the “full” licenses. E.g. if you have license for only Employee Self Service or Warehouse Operator functions, you wont be able to use BI. But for any of the power user licenses, BI use would be free. Not all add-on modules are free, but a lot of the basic functionality needed in an average BI project is included in the cost.
   BOBJ is sold separately, and on a piece-meal basis. Reporting, ETL, Data Quality, pre-configured data marts are all priced separately. With BI all of this is included.
2. Server requirements: BOBJ can run fairly well on an average-sized desktop. BI needs a server (you can wing it of course, but system requirements are much higher than for BOBJ). I’ve installed BOBJ (reporting and ETL) in a few hours (including time to read documentation and learn the process). BI needs to be installed by a basis consultant and takes a day or more.
3. Productivity: Doing stuff with BOBJ feels like playing compared to BI. My BI experience comes from at least 3 years ago, but back then it was a cinch to set up ETL using BOBJ compared to BI. The reporting tools were really cool compared to BI as well. So if you need some basic BI functionality, BOBJ is an easy way to get up and running.

Document Name: Sap BO overview

                    

                                                                        INDEX

(Note: Hyperlink is provided for each point; hold down the control (Ctrl) key and then click on hyperlink to redirect to particular page)

SR NO.	Topic Name	Page Number
1.	BI_overview	3
2.	Reporting applications	5
3.	Scheduling	6
4.	Publication	8
5.	BI_launch_pad	10
6.	UDT_and_IDT	11
7.	Webi 7.1 Features_of_Webi 7.2 Webi_Data_sources 7.3 Navigation_through_Webi 7.4 Creating_Report 7.5 Input_Controls_in_Webi 7.6 Prompts 7.7 Breaks 7.8 Sections 7.9 Variables_and_formulas_in_Webi 7.10 Sort_function 7.11 Ranking Function 7.12 Document properties 7.13 Application properties 7.14 Data_Tracking 7.15 Hyperlink 7.16 Drill function 7.17 Publisher_in_Webi 7.18 Merge	14 14 15 15 17 18 19 20 23 25 25 26 27 29 29 33 35 36 37
8.	Live_Office_connection	41
9.	Dashboard 9.1 Data flow process 9.2 difference between Xcelsius and dashboard	43 43 45
10.	Formulas....

SAP BO

1. 1. BI (also called as BO) Overview:

BI is an abbreviation of the two words Business Intelligence, bringing the right information at the right time to the right people in the right format.

Purpose of BO – Is to provide BI reporting tools to the business in order to satisfy their analytics requirement.

This data should be reported, analyzed and distributed to the right people at the right time in the right format. The figure below shows these steps.

                                                                                                                                              End User

                                                                                                                                                           

Fig. 1 data flow process from source to end user

                                  

BI systems:

• Extract data from many sources, such as Customer Relationship Management (CRM), Supply Chain Management (SCM), and Enterprise Resource Planning (ERP) systems, and other applications.
• Centralize, organize, and standardize information in repositories, such as data warehouses and data marts. This may also involve cleaning the data and appending additional data.

  Advantages of BO

1. 1. BO can be from any ETL/Database where as BEX is mostly for SAP only, now  that integration is there we can access the BEX query from BO too.

2 .BO can connect to any Database, very flexible in design.

           

2. Reporting Applications:

1. 1. Crystal Reports

2 .Xcelsius/Dashboards

1. 3. Web Intelligence
2. 4. Explorer

• Crystal Reports — Enables users to design and generate reports
• Xcelsius/Dashboards — Allows users to create interactive dashboards that contain charts and graphs for visualizing data
• Web Intelligence — Provides a self-service environment for creating ad hoc queries and analysis of data both online and offline
• Explorer — Allows users to search through BI data sources using an iTunes-like interface. Users do not have to create queries to search the data and results are shown with a chart that indicates the best information match.

   

3. Scheduling:

We can Schedule a document/report so that an instance of it is generated on one time or recurring basis.

We can define following settings in scheduling:

• Recurrence
• Database logon
• Filters
• Format
• Destination
• Schedule server group
• Language

The function of each option is as follow:

Recurrence: In this option we can specify how often the reports run. It consist of Run objects

Such as-now, once, hourly, daily, weekly, monthly, nth day of month.

Select the appropriate run object and then set start time and end time.

Filters: In this we can review the existing filters in the report.

Format: on the format tab we can choose the output format for the instance generated by the schedule.

Following are the options in format tab:

-Webi

-Microsoft excel

-Acrobat

-CSV

-Plaintext

Destination: on the destination tab we can choose where the generated instances will be located.

It consists of following option:

-Default Enterprise Location

-BI Inbox

-Email

-FTP server

-File system

Schedule Server Group: It specifies which servers are used for report balancing.

Language: It chooses the language in which report should be generated and then schedule the report.

4. Publication:

Publication is one of the important features provided by BI launch pad.

Publication allows multiple reports to be sent to multiple users with multiple conditions.

1. E. g Consider vehicle production data plant wise (with pune and Jamshedpur plant).

This report is required by all plant heads and manager’s .Managers of pune will consider reports for pune i.e. all of them need reports specific to their plant.

To process this requirement publication is used so the reports can be classified based on condition to specific business users and they will receive their respective report.

Report Recipients consist of

1. 1.      Dynamic Recipients
2. 2.      Enterprise Recipients

Dynamic Recipients:

Dynamic Recipients consist of list of users who are going to view the reports (also called as end users) and email-id’s.

Enterprise Recipients:

The ‘Enterprise Recipients’ are business users   who use Business objects reports and will receive the report in BI Launchpad itself.

We can create publication in BI launch pad to publish multiple documents at the same time on a desired schedule. Other user can then subscribe to the publication to receive the results of the schedule.

Steps to create publication in BI launch Pad:

-Click on document tab in that select publication, Enter Title, Description and Keyword of publication.

-In the title option select the documents to be published.

-Then modify the following settings:

    1. On the Enterprise Recipients and Dynamic Recipients Page, specify which users will receive the publication.

    2. On the Personalization page, add a filter to any web intelligence documents

    3. On the Format page, choose the output format for the instances generated by the publication.

    4.  On the Destination page, choose where the generated instances will be located.

    5. On the Prompts page, select values for web intelligence prompts.

    6. On the Recurrence page, specify when the schedule runs. By default the publication runs now.

    7. On the Event page, choose the event that trigger publication.

    8. On the Scheduling Server Group page, specify which server is used for load balancing.

    9. On the Advanced page, define merge and report bursting setting.

-Save the publication.

-As a final step, test the publication to ensure it is working as expected.

   5. BI launch pad

BI platform includes BI launch pad, a web application that acts as a window to business information.

By using BI launch pad we can do following:

• Access crystal reports, web intelligence documents, and other objects and organize them to suit business needs.
• View information in a web browser, export it to other business applications (such as Microsoft Excel) and save it to specified location
• Use analytic tools to explore the business information in detail

User Interface

• Header panel– displays the name of the user account that is logged on to BI launch pad and provides access to the following menus:
• Home tab– contains the default modules, which use to manage documents and BI launch pad features
• Document tab– use the document tab to view and manage objects in the repository
• To log on to BI launch pad:

      1.      Open the web browser

       2.      Type the URL for BI launch pad: http://webserver:portnumber/BOE/BI/

Through BI launch pad we can view, schedule and work with objects

• For viewing
• Viewing objects
• Viewing crystal reports
• Viewing web intelligence document
• For scheduling
• Scheduling objects
• Working with instances
• For working with objects:
• Managing objects in BI launch pad
• Creating and adding new objects in BI launch pad
• Sorting and filtering objects
• To set the object properties
• To copy, move the objects
• To create hyperlink 

6. UDT and IDT

UDT-Universe Design Tool

IDT-Information Design Tool

Purpose:

UDT and IDT act as semantic layer between datasource (BW) and reporting tool. We can do Aggregation function as well as manipulation at UDT and IDT

UDT can connect to one data source at a time. If we have multiple data sources then we have to create Universe for each of them.

UDT universes are stored in .unv format.

2 connecting to source using UDT

In IDT, the biggest advantage is that you have create universe based on multiple data sources. It could be different relational data sources (must be 64-bit), or different SAP BW source or them combined.

IDT universes are stored in .unx format.

 

3 connecting to sources using IDT

SAP BO hardware requirements:

UDT consist of:

• Classes
• Objects
• Conditions

Different types of Folders will come under Classes.

Characteristics, Keyfigures and attributes will come under Objects.

Restrictions will come under Conditions.

Characteristics also called as Dimensions at BO level, keyfigures are called as Measures and conditions are called as filters.

Process to create UDT:

    1.      Create connection to datasource

    2.      Test the connection

    3.      Refresh report so we will get all objects, classes and conditions from BW

    4.      Make required changes in objects

    5.      Check integrity

    6.      If ok, Export the created universe to repository to make it available to end user.

Steps to create UDT:

-Before creating aUDT for particular query we have to enable Allow extended access to this query option in BEx query designer then only it is available in UDT designer.

    1.Create connection to datasource in this three types of connections are available

–Personal: Used for testing on our personal query

–Secured: In BI launch pad we use secured connection option throughout the development.

–Shared: It is used when many users are working on it.

    2. Select Test option to check the connection, if ok save it.

    3. Select Authentication mode as Use single sign-on.

    4.  After this we get all classes, objects, characteristics and keyfigurs of respective query.

    5. Then we make necessary changes in characteristics and measures for this we use aggregated functions such as Min, Max, Count, and Sum, we usually use Sum.

Note: If any object (char. Or keyfigure) needs to be added add it at BEx query side and then refresh at UDT side so added objects are available.

    7.      Export it to repository to make it available for reporting. Use Import to use it at local site.

 

7. Webi

Web Intelligence is a simple to use tool for the production of reports with the help of a web browser. „Webi “is extremely easy to use and offers several efficient features. All user groups will find it easy to use the application and to achieve rapid work results.

Without programming, users can produce formatted reports in the web and carry out analysis as well as distributing the work results on the BI platform or exporting them into other formats such as PDF or MS-Excel.

In addition to the semantic layer of SAP Business Objects (universes), it is also possible to access SAP BEx Queries directly as a data source.

SAP BW hierarchies are supported in the display as well as with regard to filter criteria selection. Within the scope of the introduction of the SAP BI 4.0 platform, this new interface represents one of the most important features.

Web Intelligence is closely linked to the BI platform and has interfaces to mobile end devices (Mobile BI). Time- or event-oriented steering can be ensured automatically (Publishing). It is also possible to program distribution processes on the basis of individually definable rules.

The integration into MS-Office and the possibility to analyze local data sources with a local software installation complete the set of features.

7.1 Features of Webi include

• Enhanced and more intuitive on-report filtering ability and control
• Additional prompt features including the ability to make prompts options
• Enhanced variable creating and editing capability
• Additional on-report formatting functions available directly in InfoView or BI launch pad.
• Improved report linking management

7.2 Data sources available in Webi:

1. 1.      Universe
2. 2.      Excel
3. 3.      BEx
4. 4.      Text
5. 5.      Web services
6. 6.      Analysis view
7. 7.      Blank document

Here, we mainly use Universe and BEx as data source.

Advantages of using Universe as data source are:

1. 1.      We can do certain manipulation: such as converting characteristics to measure or vice versa as per report requirement.
2. 2.      We can create restriction/filters at universe level.
3. 3.      We can use Projection function/ Aggregation functions (Max, Min, Count, Avg, and Sum).
4. 4.      We can check integrity of object.

7.3 Navigation through Webi:

Webi portal consist of 3 main parts

1. 1.      Result objects
2. 2.      Query filters
3. 3.      Data preview

1. 1.      Result objects

In this part we can drag the required objects to be appeared in report.

2.      Query filters

In this part we can put certain restrictions also called as filters.

Here, we drag the objects on which we have to put the filters and then apply the appropriate functions.

1.      Constant: We can filter based on constant value provided at query filter level.

E. g. if I select year 20015 as a constant value, then it only shows the data of 2015 in report.

2.      List of values: -Here, we use following options

In list- Used to select multiple values. When using “in list,” separate items with a semi-colon.

-Less than

-Greater than

-Less than equal to

-Greater than equal

-Between- To select values that falls under certain range.

-Equal to

-Not equal to

3.      Prompt: Here, we allow end users to put restriction based on their requirements.

We can apply filters at two levels within a document:

• Query filters
• Report filters

1. 7.4 Creating Report:

Fig 4 Process to create Report in Webi

7.5 Input Control type in Webi: Used for filtering based on requirement.

Using Input Controls we create a dynamic report depending on the user’s selection in order to enhance the report presentation and usability.

Input controls are set up by the report writers.  If these are available they will display various options such as tick boxes and radio buttons to enable you to filter the data displayed within the report.

Input Controls are HTML Elements such as Radio Box, List Box, Combo Box and so on which can be associated with an object(s) to populate a selection list which, when users select value(s), can be passed as filters to other report elements such as tables, charts, sections, reports, etc. as dependents of this Input Control to display values related to selected items only.

Fig.5 snapshots of input controls in Webi

As shown in the figure, we have Input Controls namely, Entry Field, Combo Box, Radio Buttons, List Box, Calendar, Spinner, Simple Slider and Multiple value Input Controls namely Check Box, List Box, Double Slider.

7.6 Prompts

Prompt is a dynamic filter that displays a question every time you refresh a report. You type or select values that you want to limit the data retrieved from database by a query. No other data will be available on the report.

To set a prompt you need to:

• Drag-and-drop an object you want to set filter on to Query Filters pane
• Change the properties of the filter by select Prompt

6 snapshot showing filter options in Webi

• Enter the question that will be displayed after refreshing the report
• Click Run Query. Prompts window will appear. Select the values of the filter that you want to retrieve from the database and click Run query.

7.7 Breaks

Purpose: group data based on certain criteria.

In Webi, we apply a break to a single table or block of data.

When we apply a break to a dimension in a table, each unique value of the dimension is sub-grouped within the table.  This helps make the data easier to view and interpret.

A break also inserts a blank row or column after each sub-grouping, providing an area to insert aggregates, (Sum, Count, Min, Max, etc.).

In the example below, the table on the left is a simple table containing the columns Year, Quarter, and Sales Revenue. The same table is shown on the right but becomes much clearer when we apply a break on the Year column.

To do this just select the block on which you want to apply break (here, we are applying a break function on year object).

Right click on it and select break.

In the example below, the table on the left has a break on the year column. The same table is shown on the right with a break on the Year then the Quarter Column.

7 snapshot showing break function

  7.8 Sections

Sections are similar to breaks in that when a section is applied to a dimension in a table, each unique value of the dimension is sub-grouped. However, while a break groups data inside the table, a section groups data outside the table, effectively sectioning the entire report.

Unlike a break, a section does not add a blank row after each sub-grouping. So, we can’t perform aggregation functions on it.

Sections can contain tables, charts sections, and other types of objects.

Tables placed within sections can contain breaks.

Sections are used to easily display related tables and charts filtered to the same value.

In the example below, the same simple table containing the columns Year, Quarter, and Sales Revenue is shown with a section applied to the year column. Notice how the report is now divided into sections representing each instance of the Year value.

 

Fig.8 snapshot showing the working of section function

  7.9 Variables and formulas in Webi:

• Report creation is not like only dragging the required objects and showing the results, we need to perform certain calculation on objects. We can do it by using formula.
• The scope of the formula is within the report. We cannot use it in another report.
• If we want to reuse to reuse the formula in different blocks or in different report then we can convert the formula to variables.
• A variable is a reusable object wherever we want we use this variable.
• In Webi there are three types of variables; these are:

7.10 Sort function:

Sorting is nothing but arranging values in given order. We can have three types of sort options.

1. Ascending order
2. Descending order
3. Custom Sort

Custom sort allows to sort the values in table as per requirement and developer can set sort order as per requirement which is not necessarily ascending or descending order.

7.11 Ranking function:

Web intelligence enables us to determine Top or Bottom members in a dimension for a specific measure by applying a ranking to our data.

Process to use ranking function:

1. Select the column on which you have to perform ranking.
2. Click the Analysis tab.
3. Click the Ranking list.
4. Select Top or Bottom option, choose Ranked by dimension
5. Finally, click on Apply
6. g if I want to display 5 stores with highest annual sales revenue then I first select sales revenue column.

5

  Then I select Top        

Finally, I will give store_name in Ranked by option.

So, the result will be 5 store names with highest sales revenue.

  7.12 Document properties in Webi:

1. 1.      Refresh on open
2. 2.      Query stripping
3. 3.      Extended merge dimension
4. 4.      Permanent regional formatting
5. 5.      Hide warning icon chart
6. 6.      Merge prompt in BEx query
7. 7.      Use query drill
8. 8.      Auto merge dimension
9. 9.      Automatic refresh

  1.      Refresh on open:

The Refresh on open option ensures that, the latest data is displayed each time the report opens.

  2.      Query stripping:

Query stripping feature optimizes the query to fetch only DATA which will be directly or indirectly rendered or displayed to the User, thus decreasing the response time and faster display of the report for the user.

It will fetch only the objects which are dragged in Result Object part of Webi report not all.

This feature is only available for OLAP universes.

3.      Extended merge dimension

Extended merge dimension option will merge certain common objects because we are taking data from multiple universes.

4.      Permanent regional formatting:

Permanent regional formatting option permanently applies current date, time, and currency local to the report.

5.      Hide warning icon chart:

Whenever we perform conversion such as converting table into chart, we use this property to hide the warning from users.

6.      Merge prompt in BEx query

If we are using more than one document, it may consist of same condition in several document (e.g. the prompt Enter year may be in 2 or 3 reports). If we enable Merge prompt in BEx query option it will ask only once, means same conditions are merged together in single one.

7.      Auto merge dimension:

The Auto merge dimension synchronizes the dimensions that have the same name but are derived from different queries.

When this option is checked, Web Intelligence automatically merges dimensions with the same name and from the same universe. We see the merged dimension in the list of available objects with the dimensions merged within it below. Merged dimensions are the Web Intelligence mechanism for synchronizing data from different data providers.

8.      Automatic refresh:

This option will automatically refresh the document after some time interval.

    

7.13 Application properties in Webi:

        1.      Default universe

        2.      Local

        3.      Drilldown option

        4.      Viewing proxy

          1.      Default universe

If multiple queries make use of same universe then we can set that universe as default universe.

  2.      Local

It is used for report formatting, means setting language on which report should be displayed.

  3.      Drilldown option

Drilling on reports lets you look deeper into data to discover the details behind a summary result displayed on a table, chart, or section.

For example if a table includes sales results per quarter and you want to analyze the results for each month in Q4, you can drill down on “Q4.” The drilled report displays the results for each month in Q4

  7.14 Data Tracking:

Data tracking is a Web Intelligence feature that allows you to see the differences between two versions of your query results. For example, if you run a query daily you can compare today’s results to the prior day’s results. The previous data set is called the “reference set”. Web Intelligence keeps the previous results as well as the current results, and compares the two. Then it shows you the differences with lots of pretty colors.

 

Note: The data tracking feature automatically flags insertion and deletion of dimension members, Changes to details and increases and decreases to measures and other numeric values.   We can perform formatting (setting colors for insertion, deletion etc) based on business requirements.

 

 

• Trans Amt with Green color indicated increased amt
• Trans Amt with Red color indicates decreased amt
• Trans Amt with Strikethrough indicates deleted values
• Trans Amt with Blue color indicates decreased amt

9 snapshot showing the function of data tracking

                                                               

7.15 Setting Hyperlink in Webi report:

Through Hyperlink we can find detail level information about a particular object.

1. E. g.

1. Fig. Report1

1. Fig. Report 2

1. Fig. Report1 after creating hyperlink.

 

Fig.10 snapshot showing working of drill function

  Fig. Report when we click on Texas

The First report shows the sales revenue and quantity sold for all states and second report shows the sales revenue detail for the particular selected state.

For this, we select state column in Report1 and create a hyperlink by giving the name of Report2. So when we click on California we will get the detail info about sales revenue made by that state.

7.16 Drill function:

If hierarchy of object is there in Webi report, then we can find deeper information about object by using Drill function.

1. g 1.consider the top level object country by drilling on country we come to know about state, city and so on.

                       

 

1. E. g.2consider below snapshot Prestige Cars is a top level object. We can perform drill function on it to see models available in it.

7.17 Publisher in Webi:

We can publish web services from Webi to another system tools for an example to OBIEE by using publisher function.

                                                                    Publisher

                                                      

Report in Webi

11 publisher function

7.18 Merge: synchronizing data from different data providers.

Sometimes report consists of objects from different universe. In this case we perform merge operation in order to ensure synchronization.

Rules to perform merge operation:

A) Reports can be built where multiple source data can be synchronized.

B) Two or more similar dimensions of the same type can be merged. These different dimensions may come from different data sources or Report Queries.

C) Report Objects from different Queries can be included in the same Report Block by using the Merge Operation for synchronizing different Report Queries.

D) It is possible in Web Intelligence to merge large number of Queries.

E)  N-number of dimension objects can be merged between 2 Queries provided the objects have the same data type.

F) While merging the dimension objects it should be kept in mind that if the 2 objects to be merged are of same type and value but the values are of different case (Lower Case & Upper Case) then the values will be treated as 2 distinct values.

G) If the values of 2 objects to be merged look exactly the same but if any one of them is having trailing blanks and the other one doesn’t then those values will be treated as 2 distinct values.

H) The most important point to be addressed is that only dimensions defined at the Universe can be merged we cannot merge using variable.

We can create queries based on multiple universes in Webi. In situation where those universes share similar data we can merge the dimensions so that data from both sources can be displayed in the same table.

1. E.g.

                                                                                                                                            

12 Merge function in Webi

Consider above example, it consist of three universes, each universe is having some result objects. In this Report1 takes result objects from universe1 and universe2.where as Report2 takes result objects from Universe1 and Universe3.

However, it is possible to take data from different universes only by using Merge function provided by Webi.

1. E. g

Consider two tables:

First table with: emp_id, name, salary. And

Second table with: cust_id, name, revenue

Employee table

Customer table

In this example Revenue and salary is measure and all others are dimensions.

Here, we perform merge on emp_id and cust_id and rename it as merge_id .

After merging the report will look like as follow

:

Fig.13 snapshot showing the result of merge operation

 

  8. Live Office connection

Live office:

Live Office is an Add-On provided by Business Objects that will enable the users to integrate BO Universe data with Microsoft Office, embedding up-to-the-minute corporate data in Microsoft PowerPoint, Excel, and Word documents.

Live Office is included as part of the Business Objects Enterprise Premium license and the Business Objects Edge Series. It is also available as an add-on to Business Objects Enterprise Professional.

In BO data comes from BW.

In Webi data comes from Universe.

In Dashboard data comes from excel

So to place the data into excel we need to use LiveOffice.

Difference between QAAWS (Query As A Web Service) and Live Office

      

  9. Dashboard

In dashboard we can’t directly assign data to component. The data first has to come to spreadsheet (i.e. Excel) from spreadsheet only we assign it to component. From spreadsheet it can be binded to component. To do this we use Microsoft live office. This will fetch the data to excel, so we can easily use it in dashboard.

9.1 How Data flows to dashboard:

                                                                                          LO connection (QAAWS/Query

                                                                                                           Browser)

Here, dashboard takes data from Webi through QAAWS or Query browser.

When to use QAAWS and Query browser:

QAAWS- If data complexity is less then we can go with QAAWS.

Query browser- If some complex calculations are required then we use Query browser.

Dashboards initial screen consist of three main tabs

1. 1.      Query browser- used to import query from Webi.
2. 2.      Mobile compatibility- It will show which components are compatible with mobile and which are not.
3. 3.      Component- It is the main part while designing a dashboard. Dashboard components can be charts, tables, gauges, and metrics. some of these are pie chart, column chat, Bar chart, Line chart.

• The following dashboards are created (with pi chat and column chart) using the below Excel file.

Excel file:

sales by Region
Region	Sales
Central	10000
Eastern	20000
Western	25000
Northern	11000

Pie chart for sales

Column chart for sales

15 snapshot of sample dashboard

                                                                                       

                                                                                     

  9.2 Difference between Xcelsius 2008 and Dashboard:

• In xcelsius we are having two files:
1. 1.      With .xlf extension and another
2. 2.      With .swf extension

• .xlf is a source file or a development file where we do all changes as per requirement.
• .swf also called as flash file is a compiled version of the .xlf file which is placed on web site to view by users.
• From .xlf file we generate .swf file
• .xlf file contains more data than .swf file
• In dashboard we are having only one file with .DB extension

                                               

                                                                                                                                           

1. Formulas..:

=Substr([L01 Data loading date Key];7;4)+Substr([L01 Data loading date Key];4;2)+Substr([L01 Data loading date Key];1;2)

=Max([Loading Date])

=”Data Range: From “+Substr([Min YearMonth];5;2)+”.”+Substr([Min YearMonth];1;4)+” to “+Substr([Max YearMonth];5;2)+”.”+Substr([Max YearMonth];1;4)

=Concatenation(“Last Refreshed On:” ;(FormatDate(LastExecutionDate();”dd-mmm-yyyy”)))

1. 1.      =If([L01 Storage location Key (Not Compounded)] InList(“EVEH”;”ECKD”;”VVEH”)) Then “Supply Float” ElseIf([L01 Storage location Key (Not Compounded)] InList (“DE53″;”HC53″;”JC53″;”JF53″;”JK53″;”EI76″;”JM53”)) Then “Inspection Float”
2. 2.      =If([L01 Material Major serial number (Key)]>”2100″ Or [L01 Material Business Unit (Key)]=”TMPC” ) Then “PVBU” ElseIf([L01 Material Major serial number (Key)]<“2100″ Or [L01 Material Business Unit (Key)]=”TMCV” Or (Right([L01 Material Product hier.lev. 1 (Key)];3)=”CVD”)) Then “CVBU”
3. 3.      =If([L01 Material Division (Key)] InList(“17″;”12″;”24″;”19″;”16″;”20″;”22″;”11″;”15″;”10”)) Then “CVBU” ElseIf ([L01Material Division (Key)] InList(“31″;”01″;”30″;”32”)) Then “PVBU”
4. 4.      =[Product Hierarchy] Where ([Product Level]=2)
5. 5.      =(If([AGE STATUS]<2) Then “One Day” ElseIf ( [AGE STATUS]>=2 And [AGE STATUS]<=15) Then “2 to15 Days” ElseIf ([AGE STATUS]>15 And [AGE STATUS]<=21) Then “15 to 21 Days”  ElseIf ( [AGE STATUS]>21 And [AGE STATUS]<=30) Then “>21 Days” ElseIf ( [AGE STATUS]>30 And [AGE STATUS]<=60) Then “> 1 Months” ElseIf ([AGE STATUS]>60 And [AGE STATUS]<=90) Then “>2 Months” ElseIf ([AGE STATUS]>90 And [AGE STATUS]<=120) Then “> 3 Months” ElseIf ( [AGE STATUS]>120 And [AGE STATUS]<=150) Then “> 4 Months” ElseIf ( [AGE STATUS]>150 And [AGE STATUS]<=180) Then “>5 Months” ElseIf ( [AGE STATUS]>180 And [AGE STATUS]<=210) Then “>6 Months” ElseIf ( [AGE STATUS]>210 And [AGE STATUS]<=240) Then “> 7 Months” ElseIf ( [AGE STATUS]>240 And [AGE STATUS]<=270) Then “> 8 Months” ElseIf ( [AGE STATUS]>270 And [AGE STATUS]<=300) Then “> 9 Months” ElseIf ( [AGE STATUS]>300 And [AGE STATUS]<=330) Then “>10 Months” ElseIf ( [AGE STATUS]>330 And [AGE STATUS]<=365) Then “> 11 Months” Else “More than an Year”) Where([Status]=”RDY”)
6. 6.      =[Accidental Volume]Where([L01 Sold-to party Key]=”#”)

IMP FORMULAS:

1. 7.      =IF(Sheet2!$M$2=1,BE125,IF(Sheet2!$M$2=2,BE137,IF(Sheet2!$M$2=3,BE220,IF(Sheet2!$M$2=4,BE116,IF(Sheet2!$M$2=5,Sheet1!BE120,BE305)))))
2. 8.      =IF(Sheet2!$I$2=4,BE321,IF(Sheet2!$I$2=5,BE317,IF(Sheet2!$I$2=6,BE320,BE318)))
3. 9.      =INDEX($H$13:$AZ$128,MATCH($BC152,$F$13:$F$128,0),IF(Sheet2!$A$1=1,(Sheet1!$BA$7+BE$2),IF(Sheet2!$A$1=2,(Sheet1!$BB$7+BE$2),(Sheet1!$BC$7+BE$2))))
4. 10.  =INDEX($BE$55:$BP$66,1,$BE$76)
5. 11.  =$BC55*BE$318
6. 12.  =IF(BE$131=0,””,INDEX(Sheet3!$A$3:$B$80,BE$131,2))

Go to Format Cells > Custom. Under Type, input “000000000” where there used to be “General” to set minimum number of digits displayed = 9

1. 13.  TEXT(Value, “00”) = 02 =TEXT(A1,”00-0-000-000-0000″)       00-1-234-555-6789
2. 14.  =(LEFT(TEXT(Value, “000000000”),4)
3. 15.  =left(A1;length(a1)-6)
4. 16.  =IF(Sheet2!$A$1=1,BZ14,IF(Sheet2!$A$1=2,BZ24,CG11))
5. 17.  =VLOOKUP(LEFT(A1,3)&”*”,B$2:B$22,1,FALSE)
6. 18.  =VLOOKUP(Left(A1,1),B$2:B$22,2,FALSE)           (A1,2)
7. 19.  =CONCATENATE(text1, [text2], …)
8. 20.  =IFERROR(VLOOKUP($F$4,”!A:G,2,FALSE),””)
9. 21.  =IFERROR(VLOOKUP($F$4,’Shipper Addresses (Formatted)’!A:G,6,FALSE),””)
10. 22.  =IF(ISERROR(VLOOKUP(I4,Sheet1!B:C,2,0)),””,VLOOKUP(I4,Sheet1!B:C,2,0))
11. 23.  =IF(F3=5,K4,””)
12. 24.  =IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”),””)), “”)
13. 25.  =INDEX($D$2:$D$10,MATCH(“Japan”,$B$2:$B$10,0))
14. 26.  =INDEX(Sheet1!B5:B1032,Sheet2!A3,0)
15. 27.  =MATCH(B3,Sheet1!B5:B1032,0)
16. 28.  =INDEX(Sheet1!C5:C1032,Sheet2!A3,0)
17. 29.  =INDEX(Sheet1!C5:C1032,MATCH(B3,Sheet1!B5:B1032,0))  }}}
18. 30.  =INDEX(D5:D10, MATCH(TRUE, INDEX(B5:B10=A2, 0), 0))
19. 31.  =INDEX($A$1:$E$11, MATCH($H$2,$B$1:$B$11,0), MATCH($H$3,$A$1:$E$1,0))
20. 32.  =IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),”No match is found. Please try again!”)
21. 33.  =INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))
22. 34.  =VLOOKUP([@ItemNum],tbl_items,3,0)
23. 35.  =IFERROR(VLOOKUP([@ItemNum],tbl_items,3,0),””)expression .InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
24. 36.  =MATCH(B3,Sheet1!B5:B1032,0)
25. 37.  =INDEX(Sheet1!B5:B1032,Sheet2!A3,0)
26. 38.  =IF(D6=”ALL”,D14,D6)
27. 39.  =IF(Sheet2!A1=1,IF(A3=”#”,0,1),0)                                    
28. 40.  =CONCATENATE(“*”,I5,”*”)
29. 41.  =”03.”&IF(MONTH(B8)>=4,YEAR(B8)+1,YEAR(B8))
30. 42.  IF(ISERROR(M2),” “,M2)
31. 43.  VLOOKUP(G2,$B$2:$C$11,2,FALSE)
32. 44.  =INDEX($H$13:$AZ$181,MATCH($BC377,$F$13:$F$181,0),IF(Sheet2!$A$1=1,(Sheet1!$BA$7+BE$2),IF(Sheet2!$A$1=2,(Sheet1!$BB$7+BE$2),(Sheet1!$BC$7+BE$2))))
33. 45.  =INDEX($H$13:$AZ$181,MATCH($BC377,$F$13:$F$181,0),IF(Sheet2!$A$1=1,(Sheet1!$BA$7+BF$2),IF(Sheet2!$A$1=2,(Sheet1!$BB$7+BF$2),(Sheet1!$BC$7+BF$2))))

           Parameter         H1       H2

          Disc_Avl           0          0          =SUM(BE210:BJ210)   =SUM(BK210:BP210)

          CD Debit Amount        0          =SUM (BE213:BJ213)        =SUM(BK213:BP213)

          Disc_Performance         0           =IF(BX78>0,BX78/BX77,0)                                        =IF(BY78>0,BY78/BY77,0)

1. 46.  =If(Left([L01 Order & Pay Month];3) = “APR”) Then 1 ElseIf(Left([L01 Order & Pay Month];3) = “MAY”) Then 2 ElseIf(Left([L01 Order & Pay Month];3) = “JUN”) Then 3 ElseIf(Left([L01 Order & Pay Month];3) = “JUL”) Then 4 ElseIf(Left([L01 Order & Pay Month];3) = “AUG”) Then 5 ElseIf(Left([L01 Order & Pay Month];3) = “SEP”) Then 6 ElseIf(Left([L01 Order & Pay Month];3) = “OCT”) Then 7 ElseIf(Left([L01 Order & Pay Month];3) = “NOV”) Then 8 ElseIf(Left([L01 Order & Pay Month];3) = “DEC”) Then 9 ElseIf(Left([L01 Order & Pay Month];3) = “JAN”) Then 10 ElseIf(Left([L01 Order & Pay Month];3) = “FEB”) Then 11 ElseIf(Left([L01 Order & Pay Month];3) = “MAR”) Then 12
2. 47.  = If 0>Pos(“SES”;”S”) Then “SES”

         1)  If( [objectname] ) like “%S%”) then found else not found

         2) Use the match() function,

         3) if match([objectname], “%S%” ) then found else not found.

senapathi999 wrote:

          if([obj] like “TEST%”; “SUCCESS”;”FAIL”)

1. 48.  =IF(Match([obj];”TEST*”); “SUCCESS”;”FAIL”)
2. 49.  =Trim(If (Pos(ReportFilterSummary(“Report 1”);”MyNumber Equal “) > 0) Then Substr(ReportFilterSummary(“Report 1”);Pos(ReportFilterSummary(“Report 1”);”MyNumber Equal “) + Length(“MyNumber Equal “);1) Else “”)

Code:

1. 50.  @Prompt(‘Enter Value’,’A’,’LOV’,multi,free,Not_Persistent,,User:0)
2. 51. =If ([L01 Plant]<>”CV PUN   Direct Mat” And [L01 Plant] <>”CV PUN Winger”) Then (If ([L01 Plant] = “CV DWD   Direct Mat”) Then “Dharwad” ElseIf ([L01 Plant] = “CV JSR Direct Mat”) Then “Jamshedpur” ElseIf ([L01 Plant] = “CV LKN   Direct Mat”) Then “Lucknow” ElseIf ([L01 Plant] = “CV UTK   Direct Mat”) Then “Uttarakhand”) Else “Pune”
3. 52. =If IsError(([TOT_Schedule_cm_D_E]-[TOT_Rollout_cumm_cm_D_E])/([No_Of_WorkingDays] – [No_Of_lapsed_days])) Then 0 Else ([TOT_Schedule_cm_D_E]-[TOT_Rollout_cumm_cm_D_E])/([No_Of_WorkingDays] – [No_Of_lapsed_days])
4. 53. =ToNumber([L01 No of lapsed days in Key] Where ([Now].[L01 Plant Key] = “1001”;[Now].[L01 Plant Key]) In Report)
5. 54. =ToNumber([L01 No of working days Key] Where ([Now].[L01 Plant Key] = “1001”;[Now].[L01 Plant Key]) In Report)
6. 55. =[TOT_Rollout_cumm_cm_D_E]/[No_Of_lapsed_days]
7. 56. =[TOT_Schedule_cm_D_E]/[No_Of_WorkingDays]
8. 57. =If [Plants]=”Pune” Then [Despatch PrdQty]Where ([Query 8].[L01 Plant Key] InList(“1001″;”1020”) ;[Query 8].[L01 Plant Key]) Else [Despatch_CM]
9. 58. =If [Plants]=”Pune” Then ([Inspection float] Where ([Query 8].[L01 Plant Key] InList (“1001″;”1020”);[Query 8].[L01 Plant Key]) ) Else [InspectionFloat_CM]
10. 59. =If [Plants]=”Pune” Then ([Roll Prd Quantity] Where ([Query 8].[L01 Plant Key] =”1020″;[Query 8].[L01 Plant Key])) + ([Roll Prd Quantity] Where ([Query 8].[L01 Plant Key]=”1001″;[Query 8].[L01 Plant Key]) ) Else [RollOut_CM_Cumm]
11. 60. =If [Plants]=”Pune” Then ([RollOut_CM_day] Where ([Query 7].[L01 Plant Key] InList(“1001”; “1020”);[Query 7].[L01 Plant Key])) Else [RollOut_CM_day]
12. 61. =If [Plants]=”Pune” Then ([Query 9].[Planned Qty] Where ([Query 9].[L01 Plant Key] InList(“1001”; “1020”);[Query 9].[L01 Plant Key])) Else [Query 9].[Planned Qty]
    1. 62.  =If [Plants]=”Pune” Then [Supply FloaT] Where ([Query 8].[L01 Plant Key] InList (“1001″;”1020”);[Query 8].[L01 Plant Key]) Else [SupplyFloat_CM]           
    2. 63. =100-([Query 7].[Deduction]  Where ([Query 7].[Service Values]=[DR_Actuals_Mod] Where ([Query 7].[L01 Main Criteria]=”02″ And [Query 7].[L01 Sub Criteria]=”07″))  In ([L01 PLANT ISR])) ForEach ([L01 Vendor Key]) –
    3. 64. [Query 7].[Deduction] Where ([Query 7].[L01 Main Criteria]=”02″ And [Query 7].[L01 Sub Criteria]=”08″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key]) –
    4. 65. [Query 7].[Deduction] Where ([Query 7].[L01 Main Criteria]=”02″ And [Query 7].[L01 Sub Criteria]=”09″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key])
    5. 66. =[Target Scoring] Where ([Query 2].[L01 Main Criteria]=”01″ And [Query 2].[L01 Sub Criteria]=”05″ And [Query 2].[L01 Commodity]=UserResponse(“Commodity”) And [L01 subcomodity]=UserResponse(“Sub Commodity”))
    6. 67. =If( Max([Query 1].[Service Values]) Where ([Query 1].[L01 Main Criteria]=”01″ And [Query 1].[L01 Sub Criteria]=”05″) In ([L01 Vendor Key];[L01 PLANT ISR]) In ([L01 Vendor Key])=0 Or IsNull(Max([Query 1].[Service Values]) Where ([Query 1].[L01 Main Criteria]=”01″ And [Query 1].[L01 Sub Criteria]=”05″) In ([L01 Vendor Key];[L01 PLANT ISR]) In ([L01 Vendor Key]));0;Max([Query 1].[Service Values]) Where ([Query 1].[L01 Main Criteria]=”01″ And [Query 1].[L01 Sub Criteria]=”05″) In ([L01 Vendor Key];[L01 PLANT ISR]) In ([L01 Vendor Key]))
    7. 68. =If([DR_Actuals]=0 Or IsNull([DR_Actuals]);100;[DR_Actuals])
    8. 69. =If([Final_Score] ForEach ([L01 Vendor Key])<=[OTarget] ;”R”;”G”)
    9. 70. =(([Aggregate Quality]) ForEach ([L01 PLANT ISR])
    10. 71. *[Weight_Quality] + [Aggregate Service] ForEach ([L01 PLANT ISR]) *[Weight_Service]+([FRR_Actuals_Mod]) ForEach ([L01 PLANT ISR])*[Weight_Risk]+[Aggregate D&D] ForEach([L01 PLANT ISR]) *[Weight_D&D])/100
    11. 72. =If([TS_Certification]=”NO” Or
    12. 73. [WCSQ Certification]=”NO” Or [NBH_S]=”R” ;”R” ;[Overall_S])
    13. 74. =If([Query 4].[L01 TS 16949 Certif.Stat]=””;”WIP”;[Query 4].[L01 TS 16949 Certif.Stat])
    14. 75. =If([Receipt Quantity 6]>0;If([Overall_status]=”G”;”1″;”2″);”3″)
    15. 76. =”<divSTYLE=layout-flow:vertical-ideographic>Quality</div>”
    16. 77. =If(([Quality_S]=”R” Or [Service_S]=”R” Or [Risk_S]=”R” Or [D&D_s]=”R” Or [TS_Certification]=”NO” Or [WCSQ Certification]=”NO” Or [Overall_NBH]=”YES”) And (Min([OverAll Score]  ForAll ([L01 PLANT ISR])))>[OTarget];([OTarget]-1);Min([OverAll Score]  ForAll ([L01 PLANT ISR]))) ForEach ([L01 Vendor Key])
    17. 78. =If([A]>=[B] And [A]>=[C];[A];If([B]>=[A] And [B]>=[C];[B];[C]))
    18. 79. =If(Count([NBH_1])=2;”YES”;”NO”)
    19. 80. =If([TS_Certification]=”NO” Or
    20. 81. [WCSQ Certification]=”NO” Or [NBH_S]=”R” ;”R” ;[Overall_S])
    21. 82. =If((Min([Aggregate Quality] In ([L01 PLANT ISR]))) <= ([Target Scoring] Where ([Query 2].[L01 Main Criteria]=”01″ And [Query 2].[L01 Sub Criteria]=”Not assigned”)ForAll ([L01 Vendor Key]) In Report);”R”;”G”)
    22. 83. =Min([Aggregate Quality] In ([L01 PLANT ISR]))

FISICAL YEAR in EXCEL:

1. 1. =IF(MONTH(A2)>=4,YEAR(A2)&”-“&YEAR(A2)+1,YEAR(A2)-1&”-“&YEAR(A2))
2. 2. =”Fixed Cost”&”-“&CRPI!C2&”(Rs in Lakhs)”
3. 3. =VLOOKUP(CRPI!N6,$Q$16:$Z$20,1,0)
4. 4. =NOW()
5. 5. =IF(MONTH(A2)>=4,YEAR(A2)&”-“&YEAR(A2)+1,YEAR(A2)-1&”-“&YEAR(A2))
6. 6. =”Cost Reduction Performance”&”-“&C2
7. 7. =”Low Contribution Model during “&”-“&CRPI!C2&”(Rs in Cr)”
8. 8. =”High Contribution Model during “&”-“&CRPI!C2&”(Rs in Cr)”
9. 9. =”Fixed Cost”&”-“&CRPI!C2&”(Rs in Lakhs)”
10. 10. Lapsed days & Date: =FormatDate(CurrentDate();”dd.MM.yyyy”)
11. 11. =ToNumber([No of lapsed days in – Key]  Where([DATE – Key]=[current date var]))In([Plant – Key])
12. 12. =FormatDate(ToDate([DATE – Key];”dd.mm.yyyy”);”dd.mm.yyyy”)
13. 13. =If [Plants]=”Pune” Then ([Roll Prd Quantity] Where ([Query 8].[L01 Plant Key] =”1020″;[Query 8].[L01 Plant Key])) + ([Roll Prd Quantity] Where ([Query 8].[L01 Plant Key]=”1001″;[Query 8].[L01 Plant Key]) ) Else [RollOut_CM_Cumm]
14. 14. =If [Plants]=”Pune” Then ([Query 9].[Planned Qty] Where ([Query 9].[L01 Plant Key] InList(“1001”; “1020”);[Query 9].[L01 Plant Key])) Else [Query 9].[Planned Qty]
15. 15. =If [L01 Plant]=”Pune” Then ([No_Of_lapsed_days] Where ([L01 Plant] InList (“1001”; “1020”);[L01 Plant]) ) Else [No_Of_lapsed_days]

ISR OVERALL FORMULAS:

1. 1. OVER ALL SCORE

=(([Aggregate Quality]) ForEach ([L01 PLANT ISR])

*[Weight_Quality] + [Aggregate Service] ForEach ([L01 PLANT ISR]) *[Weight_Service]+([FRR_Actuals_Mod]) ForEach ([L01 PLANT ISR])*[Weight_Risk]+[Aggregate D&D] ForEach([L01 PLANT ISR]) *[Weight_D&D])/100

1. 2. Aggregate Quality

=100- [Query 1].[Deduction] Where ([Query 1].[L01 Main Criteria]=”01″ And [Query 1].[L01 Sub Criteria]=”04″)  In ([L01 PLANT ISR])  ForEach ([L01 Vendor Key])- [Query 1].[Deduction] Where ([Query 1].[L01 Main Criteria]=”01″ And [Query 1].[L01 Sub Criteria]=”05″)  In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key]) -[MaxABC]  In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key])

1. 3. Weight_Quality

=[weightage] Where ([Query 6].[L01 Main Criteria]=”01″) ForAll([L01 Vendor Key]) ForAll([L01 PLANT ISR]) In Report

1. 4. Aggregate Service

=100-([Query 1].[Deduction]  Where ([Query 1].[Service Values]=[DR_Actuals_Mod] Where ([Query 1].[L01 Main Criteria]=”02″ And [Query 1].[L01 Sub Criteria]=”07″))  In ([L01 PLANT ISR])) ForEach ([L01 Vendor Key]) –

[Query 1].[Deduction] Where ([Query 1].[L01 Main Criteria]=”02″ And [Query 1].[L01 Sub Criteria]=”08″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key]) –

[Query 1].[Deduction] Where ([Query 1].[L01 Main Criteria]=”02″ And [Query 1].[L01 Sub Criteria]=”09″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key])

1. 5. DR_Actuals_Mod

=If([DR_Actuals]=0 Or IsNull([DR_Actuals]);100;[DR_Actuals])

1. 6. DR_Actuals

=Min([Query 1].[Service Values]) Where ([Query 1].[L01 Main Criteria]=”02″ And [Query 1].[L01 Sub Criteria]=”07″)  In ([L01 Vendor Key];[L01 PLANT ISR])

1. 7. Weight_Service

=[weightage] Where ([Query 6].[L01 Main Criteria]=”02″) ForAll([L01 Vendor Key]) ForAll([L01 PLANT ISR]) In Report

1. 8. Weight_Risk

=[weightage] Where ([Query 6].[L01 Main Criteria]=”03″) ForAll([L01 Vendor Key]) ForAll([L01 PLANT ISR]) In Report

1. 9.Aggregate D&D

=100- [Query 4].[Deduction] Where ([Query 4].[L01 Main Criteria]=”04″ And [Query 4].[L01 Sub Criteria]=”01″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key]) – [Query 4].[Deduction] Where ([Query 4].[L01 Main Criteria]=”04″ And [Query 4].[L01 Sub Criteria]=”02″) In ([L01 PLANT ISR]) ForEach ([L01 Vendor Key])

1. 10. FRR_Actuals_Mod

=If([FRR_Actuals]=0 Or IsNull([FRR_Actuals]);100;[FRR_Actuals])

1. 11. FRR_Actuals

=Min([Query 4].[Service Values]) Where ([Query 4].[L01 Main Criteria]=”03″ And [Query 4].[L01 Sub Criteria]=”01″)  In ([L01 Vendor Key];[L01 PLANT ISR])

1. 12. Weight_D&D

=[weightage] Where ([Query 6].[L01 Main Criteria]=”04″)ForAll([L01 Vendor Key]) ForAll([L01 PLANT ISR]) In Report

1. 13. Final score

=If(([Quality_S]=”R” Or [Service_S]=”R” Or [Risk_S]=”R” Or [D&D_s]=”R” Or [TS_Certification]=”NO” Or [WCSQ Certification]=”NO” Or [Overall_NBH]=”YES”) And (Min([OverAll Score]  ForAll ([L01 PLANT ISR])))>[OTarget];([OTarget]-1);Min([OverAll Score]  ForAll ([L01 PLANT ISR]))) ForEach ([L01 Vendor Key])

1. 14. Quality_S

=If((Min([Aggregate Quality] In ([L01 PLANT ISR]))) <= ([Target Scoring] Where ([Query 2].[L01 Main Criteria]=”01″ And [Query 2].[L01 Sub Criteria]=”Not assigned”)ForAll ([L01 Vendor Key]) In Report);”R”;”G”)