1 2 3 13 Previous Next

SAP BusinessObjects Web Intelligence

187 Posts

Hi,

recently I came across a requirement that was to test if a value was in list where the list was in a variable. The variable, [list] = "123;234;456;1114678;768;999111", I I need to test if the value of a certain dimension , [numero], was in the list [list]. As you know, the InlList operator can be used only with a literal list (that´s it a text, like "123;234;456;1114678;768;999111", not in a variable. Then testing for [numero] InList([test]) will throw an error.

To overcome this situation, one can test for the Pos of [test] in [list], if the result is >0, [numero] is present in [list]. The downside is when [numero] is contained in another value for instance , "111" is not part of the list, but is contained in "1114678". To parcialy overcome this issue, you can test for ";"+[numero] and [numero]+";". So my formula will be :

 

If( (Pos([list];";"+[numero)>0)or (Pos([list];[numero]+";")>0);"InList";"NotInList")

 

Regards,

Rogerio

Hi,

 

I would like to share a way of removing all spaces from a Text.

My text is [Text] = "Rogerio Plank Goulart" and I want to see this as "RogerioPlankGoulart".

To do so, Replace all spaces (" ") with null (""), use Replace([Text];" ";"").

 

You can either prefer to Show each part of the text in a separate line to do so, use Replace([Text];" ";Char(10)). It will show [Text] as

Rogerio

Plank

Goulart

 

 

 

Regards,

Rogerio

Hi,

this blog post is about how to get the number and the names of reports in a document.

As this is a Javascript code, it will only work when viewing-editing the document in HTML mode.

 

The information about the reports can be found in webiViewFrame.DS.arrReports, the number of reports is given by webiViewFrame.DS.arrReports.length and the name of them is given, looping through arrReports

for (i=0;i<webiViewFrame.DS.arrReports.length-1;++i)

{ name = webiViewFrame.DS.arrReports[i].name }

 

To set the script, drop a blank cell over the report and set it's text to

<script>

numberOfReports = webiViewFrame.DS.arrReports.length;

for(i = 0;i<numberOfReports-1;++i)

alert(webiViewFrame.DS.arrReports[i].name);

</script>

 

Set the property of the cell "Read As" to HTML.

Cheers,

Rogerio

I would like to share a way to assign specific colors to a selected data series in a chart. In this sample, I will alter the color of a pie slica. I ´ll use the sample report "Input Controls And Charts" located at Public Folders->Web Intelligence Samples.

 

The pie chart is this sample report is shown below. pie chart.png

 

I´ll change the color of the slice Lines = Acessories to red.

To do so, click on the slice , from the menu choose click on the tab Formatting , click on cell and Background color. Choose the color you want, in this case red.

 

menu.png

 

Below s the result of this operation.

 

final chart.png

 

 

Alternatevely, instead of clicking in the slice, you can click of the slice label on the legend.

 

This approaqch also works on different charts like bar, lines among others.

Cheers,

Rogerio]

Hi ,

 

In WEBI 4.x there is a functionality that autosaves the document that you working on in the ~WebIntelligence folder under My Favorites.

But what if you need to save it overitself, just like you´rte pressing the save button.

 

Steps :

 

- Drop a blank cell onto the report;

- Set it´s value to :

       <script>

        setInterval(function () { self.parent.save();} , n*1000);

        </script>,

where n is the number of seconds to the autosave


- Set the cell´s property "Read As" to HTML.

Cheers,

Rogerio

Hi,

 

I would like to show how to avoid timeout , from client side, in a WEBI Doc, through the use of Javascript. This will only work if you´re working with the document in HTML work.

Here are the steps :

 

- Drop a blank cell over the report;

- Set it´s value to

     <HTML>

     <HEAD>

     <META HTTP-EQUIV="refresh" CONTENT="120">

       <TITLE></TITLE>

      </HEAD>

      < BODY>

     </BODY>

       </HTML>

- Set the cell property "Read as " to HTML.

- Save the report

The number in red is the interval, in seconds, for the refresh.

How does it work :

The page will do a soft refresh (not refreshing the Doc, but the iframe where the HTML code is, sending to the server a request. So,

Cheers,

Rogerio

 

It works in both BO 3.1 and BO 4.x

I would like to tell the users the importance of Reset Dimension Parameter in Rank Function.


Recently, I was asked to create a simple rank report that shows the Rank in ascending order.


The rank should be based on the sales value & partners, but specific to the country. for e.g., if there are 10 partners in a country there sales should be ranked independently from 1-10. Other partners in a different country should be given separate ranks.


For this scenario, The Rank function has a reset dimension parameter, that helps us to get to this very easily.

It has below syntax,

=Rank([Measure];Top/Bottom;[Dimension])

example:

=Rank([Total_Sales];Top;([Country Name]))

Top – refers that the rank will applied in ascending order.

[Country Name] – this is reset dimension.

Hope this example helps to understand the importance of Reset Dimension Parameter in Rank Function.

Kindly share your feedback on this.

Hi,

I am writing a blog on the Main Differences between the HTML query panel (web) and the Java query panel (Advanced) in Web Intelligence. I hope this blog post might help. Request to rate and comment on this blog.

 

The Java Report Panel has the following that HTML Report Panel does not:

 

At the query level:

 

•Sub-queries

•Object Comparison

•Database Rank (Query Rank)

•Combined Queries (Union, Intersect, Minus)

•Edit and Save SQL, with the HTML Report Panel you can only view.

•Customize the Scope of Analysis

•Purge the data from the micro cube before saving

 

At the Report level:

 

•Rank is possible (Workaround for HTML: Use the formula language to add a # rank e.g. =Rank(measure; (dimensions); [TOP|BOTTOM];(reset_dimensions)]))

•Create alerter (in HTML interactive you can view and turn on/off Alerters that have been created by others using the Java report panel). Workaround: Java Report Panel uses variables as input to trigger Alerters.  Interactive View users can edit variables that change what the Alerters display.

•Structure View

•Print directly via a printer driver in stead of converting to PDF first.

 

The HTML Report Panel has the following features that the Java Report Panel does not:

 

Left-hand "Context Panel”

•Document and Data Summary

•Document Structure and Filters – Java Report Panel lists filters within the Report Map, but doesn't have the same User Interface

•User prompt input panel enables on report modification of the filters – In Java Report Panel user “refreshes” to change prompts.

•Zoom

•Edit in-place of cells (Java Report Panel uses formula tool bar only)

•Multiple levels of undo/redo (Java Report Panel has one undo)

 

The best Web Intelligence experience has always required Java in the web browser and BI 4.0 is no exception.

HTML is (web) and Java-based is(Rich Internet Application)

The web version continues to evolve but is always behind its more powerful Java-based relative..

Important :

You can perform numerous actions on documents with InfoView/Interactive without having to actually open Webi and edit the document in the Java Report Panel itself.

 

You can modify reports using HTML with the Web Viewer in Design mode, but it is equivalent to the Interactive Viewer in XI 3 and not a true (fully functional) report panel.

 

Here some more differences between Java and dhtml:

 

In BI 4.0, the following features are currently exclusive to the Java-based Rich Internet Application:

•Query support for BEx and Analysis Views

•Custom Cascading Style Sheets

•Custom number formats

•Conditional formatting

•Many usability features in the Java-based User Interface, including Data View

• Edit or create BEx data provider 

• BI Web Services

• Data Tab

• Create conditional formatting (aka alerters)

• Change Data provider

• Create report on analysis view

 

 

Thank You

Ganesh.

Hi,

 

there are a lot we can acomplish using Javascript from within a WEBI document. But it will only work if your viewwing the document through the HTML client.

 

For this first blog post, I will show how to set a Document to autorefresh itself each n seconds.

 

What you have to do is :

- Create a Document and set it to refresh on open;

- Drag a blank cell over the report and set its text to

 

  <script>

  self.parent._askConfirmationBeforeClosingDoc=false;

  setInterval(function(){window.parent.parent.location.reload();},n*1000);

  </script>

 

where n is the number of secons to refresh,

 

Save the report.

Re-open , right click on the blank cell inserted , choose 'Format Cell' and under 'Read content as' choose HTML.

 

Save the report again.

 

That´s it, it will refresh each n seconds without manual intervention.

 

Cheers,

Rogerio

 

It works in both BO 3.1 and BO 4.x

Hi All,

 

In this blog post Getting to know about the Context Operators (IN, ForEach and ForAll). First let me explain on this; IN, ForEach and ForAll these three operators are called Context operators. Do you know, why today i would like to share my knowledge about this topic because context operators mostly used in webi report.

 

Even you know by using (IN, ForEach and ForAll) operators; you just like Kill Two words with one stone, in other words to solve two problems at one time with a single action.

 

Have you ever noticed that IN, ForEach and ForAll these three webi functions like poweful keywords which most/ mostly serachable in Google search engine. Also in this blog post i covered all aspects about context operators; when any why required at the time of creating webi report.

 

I explain on these three operators below using an example:


In context operator - when / why we use?


For to me using In context operator to specifies dimensions explicitly in a context.


Example:- you have a report showing Year and Sales Revenue, but our requiremnent is to add one more column which showing maximum revenue by quarter. also you have the Quarter object in query panel but you do not include this dimension in the report block. Instead, you want to include an additional column to show the maximum revenue by quarter in each year.


Solution:- By Using the In context operator, you can achieve the Max Quarterly Revenue using below formula.


Max ([Sales Revenue] In ([Year];[Quarter])) In ([Year])


Finally Your report looks like this:

 

In-function-webi-report.png

 

ForEach context operator - when and why we use?


For to me using the ForEach operator to add dimensions to a context.


Example:- How To show the maximum revenue for each Quarter in a report which having the Quarter dimension in query panel but does not include it in the block:


Solution:- Using the ForEach context operator, you can achieve the same result with using below formula:


Max ([Sales Revenue] ForEach ([Quarter])) In ([Year])


How does work/ Above formula explanation:- You know Year dimension is the default input context in the block. So, by using the ForEach operator, you add the Quarter dimension to the context, by giving an input context of ([Year];[Quarter]).


Finally Your report looks like this:

 

In-function-webi-report.png

ForAll context operator - when and why we use?

 

For to me using the ForAll context operator to remove dimensions from a context.

 

Example:- You have a report showing Year, Quarter and Sales Revenue and you want to add a column that shows the Yearly total revenue.

 

Solution:- Using the ForAll context operator, you can achieve the same result with using below formula:

 

Sum([Sales Revenue] ForAll ([Quarter]))

 

How does work/ Above formula explanation:- By default you have input context (Year; Quarter), for total revenue by year the input context needs to be (Year); Therefore, you can remove Quarter from the input context by specifying ForAll ([Quarter]) in the formula, which looks like above.

 

Note:- You can also achieve the same result by using the the In operator; in this case the formula is below:

 

Sum([Sales Revenue] In ([Year]))

 

Finally Your report looks like this:

 

ForAll-operator-in-webi-report.png

 

I hope you enjoyed lots and learned something new while reading this document.

 

Cheers,

Amrendra

In this blog post I  would like to share a way to set user level security to a WEBI report.

By user level security I mean  restricting the visualization of certain partsa of reports to some users, based on a user secutity level.

For the sake of demonstration I  will work with three users and three levels of security.

This are my users and their security levels

 

userlevel sec
Roger1
etizz2
humga3

 

I will use an Excel sheet as a DP to hold these data.

There are some restritions on the use of this approach. In order this to work, users can only visualize the documents, not being able to edit it.

 

I will use e-fashion with the following objects in the result of my query :

State, City , Lines , Category and Sales revenuee

My user profile will be :

level sec = 1 - Users can see State , City and Sales Revenue;

level sec = 2 - Users Can see State, City, Lines and Sales Revenue;

level sec = 3 - Users can see all data.

 

 

To start, I create a document with my query from e-fashion :

 

 

 

 

query.png

Which gives me the following table

 

 

 

table.png

 

After that, I import my Excel sheet as a personal DP, which gives me the following tables :

 

 

both tables.png

 

Create a variable global_user_sec = =([level sec] Where ([user]=CurrentUser())) In Report,

This will give me the user´s security level according to my users table.

 

Then I can create two dim variables :

{Lines security] = If( ([ global_user_sec] >=2;[Lines]) and

[Category security] = If ( [global_user_sec] >=3 [Category]).

 

Dropping [State], [City] , [Lines security] and [Category security] and [Sales revenue] will do the trick.

 

Cheers,

Rogerio

Happy Holidays!  Hope you've all had a good 2014. Before closing out this Year, I'd like to put up a reminder:

 

Revisit the sizing of Web Intelligence Processing Servers on your deployment of SAP BusinessObjects BI 4.1

 

If you've done so in the past eight months or so, that's great!  But pencil that task into your calendar for 2015.

 

If you've haven't done so in a year, then I strongly recommend scheduling a sizing exercise for 2015Q1.

 

I support Web Intelligence.  I've supported it since before BI 4.0 Ramp-Up, through its General Availability on September 16, 2011, and to now with BI 4.1

 

It's been over four years, and through all the years, the three most important considerations that affect the smooth operation of BI 4.x WebI on your system is (1) sizing, (2) sizing, and (3) sizing.

 

If the sizing of your BI architecture is wrong, then you're going to face issues with performance, stability and availability, if not now then in the near future as demand on your system steadily grows. You might even find yourself spending quality time with me on the phone, and one of the first things I'll be asking you is "When did you last size your system?"

 

If the answer is before February 2014, then I'll be pointing you to the Business Intelligence Sizing and Deployment page, where you'll find the BI 4 Sizing Guide, updated February 2014. There's been changes to the recommended configuration of Web Intelligence in the newest edition of the Guide.

 

Rule: Always start your sizing exercise using the most recent Sizing Guide

 

When you get to the Sizing page, I recommend you click the "Follow" page, so that you'll be notified whenever the page is updated. Or go to https://service.sap.com/sizing and find the BI 4 Sizing Guide, where you can "Subscribe" to the Guide and be notified when it's updated.


There were changes to the recommended configuration of Web Intelligence services in the latest update, so I highly recommend reading through the entire Guide, just to make sure your BI 4.1 deployment is following recommended practices.

 

I've spoken with the Platform Product Owner, Sada, and he anticipates no changes to the Sizing Guide until at least BI 4.2.   But don't take my word for it.  Before starting any sizing exercise, make sure you go and download the latest version of the Guide.

 

Rule:  Do not use XI 3.1 sizing guides or best practices to size BI 4.1

 

One time, a customer pulled up their architecture deployment sizing document for their BI 4.0 system and in there was a references to XI 3.1. Turns out they had been keeping the same sizing architecture since their XI 3.1 days.  That definitely did not work, and they had lots of issues that mostly disappeared once they had redone their sizing exercise anew.

 

XI 3.1 and BI 4.1 are entirely different beasts, and trying to fit BI 4.1 components into a XI 3.1 architecture plan is like harnessing thoroughbreds to a dog sled. No matter how strong the beasts, they're just not gonna perform well when they keep tripping over each other and getting into each other's way.

 

The major differences between the two versions:

 

  • BI 4.1 WebIPS is a 64-bit process.  XI 3.1 runs as a 32-bit process, even on 64-bit machines.
  • BI 4.1 WebIPS delegates query and chart generation to a different Service.  XI 3.1 the generation is in-process.

 

Because of these differences, the sizing best practices developed for XI 3.1 is inapplicable, even harmful, to BI 4.1

 

Let's walk through the best practices we had for XI 3.1, and compare to BI 4.1:

 

XI 3.1 - deploy one WebIPS for each CPU core on the machine.

BI 4.1 - deploy one WebIPS per machine.

 

There wasn't a real good reason why you'd want to tie the number of XI 3.1 WebIPS to the number of CPU cores - the WebIPS threading model parallelized concurrent report processing requests just fine across multiple cores. Each thread would process a single document request, but the threads can run on different CPU cores.  WebIPS does not impose CPU core affinity.

 

XI 3.1 being 32-bit meant that memory resource limited how many processing jobs a single WebIPS could efficiently handle. Back then, proper management of memory utilization was an important part of sizing WebI.  Increased usage would quickly demand more processing power to be added to the mix, and because of the memory limits, the only reliable way was to add additional WebIPS to the deployment.  Having one WebIPS per CPU core was just a pretty good rule-of-thumb when it came to deciding whether you needed to add additional machines to the system.

 

But BI 4.1 WebIPS is a 64-bit process, and has a much expanded memory space. If I had a nickel for every time I've had to recommend customers alter the memory handling setting for their BI 4.x WebIPS, then I could afford, well, not even a stick of gum.

 

As you can see in the Sizing Guide, the BI 4.1 WebIPS is limited by IO.  You want to ensure the WebIPS have access to as much IO bandwidth as possible, and that means, especially on Virtual machine environments, having just one WebIPS per machine.

 

Very early on in BI 4.0, there was an issue with WebI failover across different machines (a problem with the Document Recovery Service), so I used to recommend two WebIPS per machine (failover of WebI Sessions on two WebIPS on the same machine doesn't require use of the Document Recovery Service).  But that time is long past.

 

XI 3.1 - Enable Memory Analysis

BI 4.1 - Do Not Enable Memory Analysis


(added 2015-01-16)

I'm adding this item since it does come up often enough, and I recently got confirmation from a WebI developer that this is what's recommended now.


Disable the "Enable Memory Analysis" option for BI 4.1 WebIPS.  It's enabled by default currently (that will likely change soon), but it's a holdover from XI 3.1 that's not needed and may cause issues.


In XI 3.1, it was a noble effort to try and preserve the precious 32-bit memory space. Enabling memory analysis causes the WebIPS to reject any further incoming processing requests, other than saving the document, when its memory utilization goes above the "Memory Upper Threshold" value.  The hope was that this would give Users an opportunity to save their work before the WebIPS starts to run out of memory. Beyond "Memory Maximum Threshold", all requests were rejected in the hope that the WebIPS can recover before it crashed.


BI 4.1 WebIPS works in a 64-bit memory space and has been upgraded to behave much more gracefully when it does reach resource limits. The "Enable Memory Analysis" is a cure without a disease. In fact, keeping it enabled may cause instability - here's one notable example that's affected some customers.

 

During your next scheduled BI Platform downtime, log onto the CMC, go to Servers -> Service Categories -> Web Intelligence Services, select each WebIPS, and in the Properties uncheck "Enable Memory Analysis".

 

 

XI 3.1 - set the Maximum Connections at 50 to start, increase slightly if necessary.

BI 4.1 - set the Maximum Connections at 200 to start, increase slightly if necessary.

 

Maximum Connections is generally determined by the number of active users you have on the system.  If you anticipate about 200 active users on the system at any one time, then you can set the Maximum Connections to 200.  But that might be problematic, since the WebIPS will keep a Connection open till idle timeout.  So even if you have at most 200 active users, there might be slightly more than 200 open connections.  Thus the recommendation to increase the number slightly above the number of active users.

 

The change from the older Sizing Guide is that, with testing, the "sweet spot" for the BI 4.1 was determined to be 200 maximum connections - that the WebIPS for 4.1 was more than capable of handling beyond 50 connections.

 

If you do have more than 200 active users, then bringing in a new machine into the cluster, to deploy new WebIPS, should be a consideration.

 

XI 3.1 - configure Output Cache Directory for all WebIPS on a common network share.

BI 4.1 - configure Output Cache Directory for WebIPS on local disk and do not use network share.


When the WebIPS first opens a WebI document, it has to unzip the wid file and parse the contents to build an internal representation of the document.  It then caches this representation to disk, that it uses subsequently for further processing. This opening/unzipping/parsing does take a bit of processing.  The good thing is that the WebIPS, when asked to open the same document, first checks to see if there's already a cached representation from when it opened the document earlier.  If there is, it proceeds to process the cached version, so that it doesn't have to spend the time opening/zipping/parsing the wid.


Now, all WebIPS that share the same Output Cache Directory can share any cached representation stored to disk by any other WebIPS.


This improves performance on opening a WebI document on deployments where there are many WebIPSes running.  A given WebI doc needs only be parsed once, then the cached representation can be used on subsequent requests for the same doc, regardless of which WebIPS the request goes to.


In XI 3.1, there were usually a lot of WebIPS running around.  One per CPU core, one each for 50 active users.  It wasn't surprising for me to work on systems with more than 16 WebIPS, clustered.  If there were no cache sharing, then the high likelihood of hitting a WebIPS that hadn't already cached the document was high enough to bring down the performance.

 

But for BI 4.1, there would be far less number of WebIPS running on the system, since it's recommended to have one per machine, one per 200 active users. Instead of 8 XI 3.1 WebIPS, there would be 2 BI 4.1 WebIPS.  Far less chance of a cache miss. Furthermore, since what limits BI 4.1 WebIPS is IO, every request to the cache would have a performance hit if the cache was located on a network share.


Because of this, the recommendation for BI 4.1 WebIPS is to configure the Output Cache Directory to fast local disk. 


There are other considerations that may discourage setting the Cache Directory to a network share.  There's an issue with older builds where, if the Cache Directory points to a network share, cleanup is not done and can fill up the disk (SAP KBase 2050700). Network access issues can cause the WebIPS to hang (SAP KBase 1757824). Network issues affecting cache access can even cause the WebIPS to shut itself down intermittently (SAP KBase 2057341) - I've resolved intermittent WebIPS shutdown issues by moving the cache to local disk.


All in all, it's better in BI 4.1 to have Output Cache Directory point to local disk.


If you do have a very complex and large WebI document that takes significant time for the WebIPS to parse the wid on open, what you can do is create a Server Group containing a specific WebIPS. Then all processing request for that WebI doc would preferentially go to that WebIPS and no other, so would consistently open from cache.


Rule: Revisit Sizing Regularly.  And Remember that the Sizing Guide is a Starting Point - a Guide and not the Rule


Remember that usage of your BI system will change with time - new documents will be created, the amount of data reported on will multiply, and the number of users consuming reports will increase.  That's a good thing, since that means your users think the BI system so good that they want to use more of it.  But that also demands the necessity of continuously revisiting sizing: determining the number and size of documents being processed by reporting off of auditing and monitoring the load on your WebIPS. 


Resize your system, at least once a year.


And remember that the BI 4 Sizing Guide is a starting point. The people who wrote the Guide are pretty sharp, and have done a lot of testing to make sure what they recommend is reasonable.  But the tests they ran, the level of activity for an "active user" that they considered, the typical size of a "large" WebI document they used, will all differ from your actual deployment.  Only you know what's going on in your system, and ultimately, sizing means tuning. Although you might size the system once a year, monitoring the heath of your system must be done continuously.


Rule: Split and Size the Adaptive Processing Server


This is a very important rule, and a subject unto itself.  I'll leave this subject for my next blog entry.


I will say this: the out-of-the-box default deployment of BI 4.1 has a single Adaptive Processing Server that is inadequate for any purpose other than a POC on a single machine.  If the APS isn't split and sized correctly (SAP KBase 1694041), your deployment will encounter issues.


Summary

 

The most important consideration that will ensure a stable and performant processing of Web Intelligence on SAP BusinessObjects BI Platform 4.1 is sizing.  In this blog, I briefly covered sizing of the Web Intelligence Processing Server.  Next blog, I'll briefly cover the sizing of the Adaptive Processing Server.





Requirement- How to Create Hyperlink in SAP Web Intelligence Report or, How to link between two documents in (3.x or 4.x)

 

Solution

 

·        Create two reports in Web Intelligence and export both into the Repository.

·        Take on example, the First report shows the sales value and quantity for all the states (in aggregate) and second report shows the sales values detail for the particular selected state.

·        To create Hyperlink in first report, open Infoview or LaunchPad and open the WebIntelligence document .

.        Just click on the report elememts tab which shown above > click on document or hyperlink tab.
.        Select the Object that is to be linked with the second report then RIGHT CLICK –> Hyperlink –> New
·        A new window will open for creating hyperlink as shown below, select Link to document tab

·        Click Browse, select the second report and select ok.

·        Select Refresh on open option under Hyperlink properties in Create Hyperlink Window and select the options like: (you want to open in same window or new window).

·         Then the report will open, as hyperlinks for the object that is selected.

·        Click the link from Infoview or LaunchPad, which leads to second report after a confirmation from default web browser. Click Ok.

Today I would like to show a way to merge two queries with a 1xN  relationship.

WEBI is good on merging queries ( DP1 and DP2 ) in which for one row of DP1 there’s, at most, one row in DP2 and vice versa.

When you come to a situation in which for each row in DP1 you can have more than one row in DP2 it’s a problem.

Let’s take an example. Suppose you have a query with students I will call it DP1. In DP1 you got: Student code, Student Name, Mother’s name and a measure (meas1). A second query DP 2 contains the course in which students are enrolled, DP2 has Student code, Student Name, Course code and a measure meas2.

Each student can be enrolled in zero or more courses

I will populate this queries with the following data :

 

DP1

Student code

Student Name

Mother's Name

meas1

B1

Roger

Jeanne

1

B2

Nadia

Maria

1

B3

Cecili

Nadia

1

 

DP2

 

Student code

Student Name

Course Code

meas2

B1

Roger

Course1

1

B1

Roger

Course2

1

B1

Roger

Course3

1

B2

Nadia

Course1

1

 

 

 

 

My task here is to bring all data in a single table, like :

 

Student code

Student Name

Course Code

Mother's Name

B1

Roger

Course1

Jeanne

B1

Roger

Course2

Jeanne

B1

Roger

Course3

Jeanne

B2

Nadia

Course1

Maria

B3

Cecilia

 

Nadia

 

 

 

The data is on two MsExcel sheets , so, firstly, I need to import it to WEBI Rich Client.

 

After importing the sheets, that’s how my data looks like.tables.png

 

 

 

I will merge the two queries on Student name and Student code, which are the common objects.

A table with the merged Student name and Student code will present the data from both data providers.

 

To show the not merged dimensions coming from DP1 and DP2, I must create detail variables. Let’s do it with Mother’s name

 

mothers name detail.png

 

 

 

For Mother’s name detail I choose , for the associated dimension the merged Student code. Dropping it on the table containing merged Student code and merged Student name :

 

table with mothers detail.png

 

 

The challenge now is to bring course code into the table. If I create a detail  variable over course code , this will generate a #MULTIVALUE error since , for example, Student “Rogerio” is enrolled in 3 courses.

 

So, we need to , force the relationship to be reflected on the table. We can achieve it by dropping the Course code dimension in the table, which will result in the following table :

 

 

course code.png

 

The problem here is that Student “Cecil” that isn’t enrolled in any course won’t show up.

In order to show “Cecil”, select the table , right click and go to “Format table” and check “Show rows with empty dimension values”

Now the table shows the value for “Cecil”shows up

 

merged final.png

 

As a rule of thumb , when trying to merge DP’s with a 1xN relationship :

1 – Merge the common fields;

2 – Use the dimension coming from the N side query;

3 – Create detail variables from the 1 side query for each dimension needed with associated dimension equal the merged dimension;

4 – Check “Show rows with empty dimension values”on Table formatting for each table using dimensions coming from both queries.

 

Any comments will be much appreciated.

Cheers,

 

Rogerio

Hi,

 

this blog post is about creating Set Operations , at Report side,

There are a few restrictions on using Combined Queries to get Union , Intersection or Minus queries. Some of them are

1 - One cannot perform Combined Queries over different DP;

2 - The objects used on the Result Set couldn´t appear on the query panel;

3 - The objects for both queries must be in the same order, be of the same type  and be on the same number (if you retrieve 3 objects from the first query, you must retrieve the same numbger on the other(s) query(ies).

 

The approach suggested allow users to combine different DPs, with different number of objects and with different types.

 

As I cannot demonstrate properly how it works using e-fashion, I will use two excel sheets . The Excel sheets represents some People registered for 2 different course, let´s say Course 1 and Course 2.

 

Course 1 contains the following information :

Student Code, Student Name , Student Addres, specialization and measure From DP1

Here´s the data for Course 1

 

student codestudent namestudent addressspecialization

measure From DP1

1RogerSt 1 13Cooking1
2MariaSt 2 14Engineering2
3NadiaSt 3 15Writing3
4RobertSt 3 16Cooking4
5MarcusSt 3 17Engineering5
6AnaSt 4 18Writing6
7EmersonSt 4 19Cooking7
8HumbertoSt 5 20Engineering8
9ClaudiaSt a 21Writing9
10RobertaSt b 22Cooking10

 

 

Course 2 is composed by

Student Code, Student Name , Student Addres, Interest and measure from DP2

 

and here´s the data for Course 2

 

student codestudent namestudent addressInterest

measure from DP2

1RogerSt 1 13Repolrting1
4RobertSt 3 16Big Data4
5MarcusSt 3 17Visualization5
6AnaSt 4 18Repolrting6
7EmersonSt 4 19Big Data7
11PauloSt 4 20Visualization11
12SergioSt 4 21Repolrting12
13MargaridaSt 4 22Big Data13
14NairSt 4 23Visualization

14

 

All objects except [measure from DP1] and [measure from DP2] are dimension, both are measures.

 

The challenge here is to determine, at report side :

The Union of both queries and display it on a table .([student sode] coming from either queries

 

The same for Intersection ([Student Code] that belongs to Query 1 and Query 2 simultanesously) , Course 1 - Course 2 (]Student Code] that is in Query 1 but not in Query 2) and Course 2 - Course 1 ([Student Code] in Query 2 but not in Query 1) . Based on student code.

 

Firstly I will import both Excel sheetsa into WEBI Rich Client which gives the following tables

tables.png

 

Then I will merge both DP in [student code], [student name] and [student address] , and Create detail variables for the other dimensions ([specialization det] and [interest det]) based on the merged [student code] dimension

 

 

Create two measures variables [Test 1] = If( Not ( IsNull([val1]));1;0) and [Test 2] = If(Not(IsNull([val 2]));1;0)

 

 

The Union is given , by default droppoing the three merged dimensions and the detail variables on a table

 

Union.png

 

[Intersection] = If ([Test 1] = 1 AND [Test 2] = 1;1;0)

[Course 1 - Course 2] = If( [Test 1] = 1 AND [Test 2] = 0;1;0)

[Course 2 - Course 1] = If([Test 1] = 0 AND [Test 2] = 1)

 

Filter by the "1" on the desired Operation ( Intersection , Course 1 - Course 2, or Course 2 - Course 1 ).

Heres the table with all the test.

 

final table.png

 

Cheers,

Rogerio

 

Thanks to Mak 1 from BOBJForum for his help

Actions

Filter Blog

By author:
By date:
By tag: