1 2 3 18 Previous Next

SAP BusinessObjects Web Intelligence

266 Posts

Interested in the new Commentary capabilities in WebIntelligence 4.2 then read on.

Firstly a short disclaimer - I work in the PreSales team within SAP Australia, specialising in Business Intelligence, HANA and Information Management. Secondly, these are my own opinions and observations and should not be taken as SAP’s official position. You can find me on LinkedIn at https://au.linkedin.com/in/jamesredman.

I cannot think of an RFP in recent years – and I have answered a few – where the customer has not requested some form of commentary be available in their Business Intelligence toolset. The reason for this is quite simple – the most prolific BI tool on the planet has it – Microsoft Excel. Simply right click on a cell and add a comment. It’s that simple.

The reasons for commentary are many and varied but usually revolve around explaining why a number is too high or too low. You could call it the ‘excuses function’. This is especially the case with financial reporting – ‘our sales are below budget because [fill in blank here]’. And to be honest, SAP’s financial application, Budgeting Planning & Consolidation (BPC) has had this capability for a while - mostly because it has an Excel interface. However, the capability has never really been properly implemented in a more general purpose BI tool. The BI Platform from SAP BusinessObjects has kind of had it – you can create a note against a report – but this was really for collaboration rather than commentary. Report authors could also create a free form text cell and put their thoughts in there as well but it was pretty clunky and only available to the author.

The WebIntelligence developers at SAP have finally put their heads together and begun rolling out much more sophisticated capabilities with the BI4.2 release and with the recently released Service Pack 3 have taken this a huge step further with cell based comments – just like you can in an Excel sheet.

This blog will explore that capability.

Below is a very basic report based around a cross-tab with data from our old friend the ‘eFashion’ universe. By clicking on the new icon on the left hand side we get a new comments panel. I can now start writing a comment specific to the contents of a particular cell.

w1.png

Now, when I hover my mouse pointer over any cell with the commentary flag in the top right hand corner (similar to Excel) I get to see that comment. I also get to see who wrote it.

w2.png

Multiple comments can be added to the same cell and are all displayed in the left had dialog box. There is a report property that says whether the first or last comment should be shown when hovering the mouse pointer over the cell.

w3.png

Also, the comment is context sensitive – in other words it remembers the crossing where it was added. In the example below, I have added a quick filter on year and you can see the comment has moved with the column.

w4.png

And it doesn’t just work for tables. You can comment on charts and report blocks as well. Notice the different icon symbol in the top right hand corner.

w5.png

Unfortunately, you cannot comment on a particular chart component like a bar or segment. To be honest this might be tricky as I am not sure how you would tell the viewer that a comment was available.

It also does not take into account any filters or input controls that affect the underlying numbers. In the example below, I added an input control for the product line. When a user changes this, like I have to ‘overcoats’, then obviously all the numbers in the table will have changed and the comment would more than likely no longer apply. I would be nice if the context remembered this as well but you can’t have everything. :-)

w7.jpg

One neat trick I did find is making use of the new Comment() function to surface the cell’s underlying comment as a string. If you then leave some blank space in the report for comments then different report viewers can add their comments and have them all show up in the report – even when it is printed.

w6.png

You can also use the new Comment() function to hide comments unless they have been validated by a responsible person. Please check out the BI Platform Administration Guide for further details on that gem. You can also find more details there of how the comments are stored and also all the security implications and configuration.

So, you are now looking at a very happy RFP responder who can tick the box to one more requirement. I hope there are also many happy customers who will start using it and you weren’t all just adding that question to your RFPs just to be difficult! :-)

Enjoy.

 

Environment: SAP BI Platform 4.1 SP6 P4

 

I have got this error while refreshing a Web Intelligence report. After reading blogs I even tried to ping the Database server from the Application Server where I installed the SAP BusinessObjects. I get results.

 

But the issue here was with port 1433.

 

Port 1433 from Application Server to Database Server was closed. With help of Firewall Admin team this was opened and everything works fine after that.

 

Hope this helps!

 

MK

 

This issue in general is due to the mismatch in the versions

 

I would like to describe how I got it and how I resolved it

 

Environment: SAP BI Platform 4.1 SP 6 P4

Data Source: MS SQL Server 2014

 

The Native client installed on the machine are 10 and 11

 

But if we look in the patch of

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\oledb

 

sqlsrv.sbo file I see that the 2014 Sql Server uses 12 as the driver

 

</DataBase>

         <DataBase Active="Yes" Name="MS SQL Server 2014">

             <Library>dbd_wsqloledb</Library>

             <Parameter Name="Provider CLSID">SQLNCLI12</Parameter>

             <Parameter Name="Enumerator CLSID">SQLNCLI12 Enumerator</Parameter>

             <Parameter Name="Extensions">sqlsrv2014,sqlsrv,oledb</Parameter>

             <Parameter Name="Driver Capabilities">Query,Procedures,Cancel</Parameter>

 

But the once installed are 10 and 11. I did not try installing 12 rather I changed this file to point to 11. Something like this

 

</DataBase>

         <DataBase Active="Yes" Name="MS SQL Server 2014">

             <Library>dbd_wsqloledb</Library>

             <Parameter Name="Provider CLSID">SQLNCLI11</Parameter>

             <Parameter Name="Enumerator CLSID">SQLNCLI11 Enumerator</Parameter>

             <Parameter Name="Extensions">sqlsrv2014,sqlsrv,oledb</Parameter>

             <Parameter Name="Driver Capabilities">Query,Procedures,Cancel</Parameter>

 

and the error goes away.

 

Hope this helps someone!

 

MK

I am getting an error when trying to view the script on WebI through the NBP system. The error message reads:

     Your security profile does not include permission to view the SQL generated by the query. (Error: ERR_WIS_30256) (Error: INF )


I am wondering if there is a specific role I need to apply for in order to get passed this. If not what can I do to get passed this?


Thank you,

Nicole

Many a time, we've seen developers/users asking for Cascading Prompts (similar to Crystal Reports), where the LOV's of the subsequent filter (on object which is next level lower in hierarchy) should dynamically be filtered depending on the value chosen in the first filter and so on.

 

I'm using BusinessObjects 4.1 SP5 and eFashion Universe converted to UNX in this scenario.

 

Example (check out the below image): Assume we have are 3 prompts in a report, on State, City and Store name. Our requirement is, as soon as we select a State (say Texas), the LOV for State should list only the Cities in Texas, and when we select a City (say Houston), then the LOV for Store name should only show list of Store names in Houston. We've such a feature in Crystal Reports, but not in Webi. (Side note: Cascading Input Controls was added in BI 4.2 SP3 which is cool!) What we want now are Cascading Prompts, and let me remind you, I'm not talking about Cascading LOVs (in which LOVs are displayed Hierarchically), there are a lot of blogs on how to achieve that already.

Snap 01.png

So, let's see how to achieve that in Webi, oh wait, actually most (if not all) of the work is done in the Universe. The solution (idea) is simple:

  • Create an LOV for City, with a Prompt on State in it, and associate that LOV to City dimension
  • Create an LOV for Store name, with a Prompt on City in it, and associate that LOV to Store name dimension

 

Step 1: Launch the IDT (Information Design Tool) -> open the Business Layer (BLX) -> go to Parameters and Lists of Values -> create a List of values based on business layer objects for City with City in Result Objects and Prompt on State as below:

Snap 02.png

Assign this new City LOV to the City dimension

Snap 03.png

Step 2: Go back to Parameters and List of Values -> create a List of values based on business layer objects for Store name with Store name in Result Objects and Prompt on City as below

Snap 04.png

Assign this new Store Name LOV to Store name dimension

Snap 05.png

Step 3: Save and Publish the Universe to Repository

Step 4: Create a report to get few Store Details in Result Objects and a prompt on Store Name only as below, and click Run Query

Snap 06.png

Step 5: Click Run Query, we see 3 prompts, even though we created prompt on Store name (3rd prompt in the below image) only in the query. The first 2 are the prompts we created in LOVs of City and Store name in the Universe.

Snap 07.png

Something cool to note is, when you click on Enter City or Enter Store Name directly without answering the prompt for State is, we see a message that this prompt needs values for one of more following prompts as shown below:

Snap 08.png

Snap 09.png

Now, when we select a State (say Texas) in the prompt for State, and then click on prompt for City, we'll see the LOV show a message Refreshing list of values (if your machine is slow as mine, or you may not get it)

Snap 10.png

And then, we'll see the Cities in Texas as shown below.

Snap 11.png

And then, when we select a City (say Houston), we'll see the Store names from Houston in the LOVs for Store name in prompt for Store name.

 

Voila, we got it.


Bonus: If you want to be able to select multiple values in State, City or Store name, you'll have to update the Prompts in LOVs with In List option.

 

Hope you enjoyed this blog and will try it.

My name is Robert Twigg and I work on the SAP Webi Support team.  I also have some background with the RESTful Web Services SDK for Web Intelligence. While working on the Webi support team, I have noticed that a frequent and significant issue that is encountered is Webi documents no longer having connectivity to their universe.  In many of these cases, the solution is to repoint the Webi document to the universe.  This is a particular headache when there are numerous Webi documents that need to be repointed.  With my understanding of the Webi RESTful Web Services SDK, I thought that this is something that could be made a lot easier by finding an automated way to do this.  So, I decided to put something out on the SAP community that can be used to make this easier.  I have created a wiki in the SDK portion of SCN which can be reach at the following link:

 

 

Tool that uses the RESTful Web Services SDK for Web Intelligence to perform a change source of multiple Webi documents in an automated way

 

 

On the wiki, you will find a tool that allows for a change source to be done to a single Webi document or to a number of Webi documents where the document Ids are stored in a file.  The source code for this tool is also included in the link.  The wiki also includes information about how this tool works.  Please check out the above wiki and provide any comments there.

 

 

Good luck!

Many of us might have come across a requirement in Webi, where we needed to relatively position a block with respect to, not One, but Two (or more) other blocks. We can also say that we needed a Container (component of Dashboard (Xcelsius)) in Webi.

 

Check out the below image to understand the requirement using 2 different scenarios:

Snap 01.png

We need to develop logic, using which, Block 3 will be vertically positioned to be below either Block 1 or Block 2, depending on whichever one has more data, so there is no overlap between the blocks on top and bottom.

 

Let's check out this cool logic to address this requirement: (Note: This logic works 90% of the times (or more, but not 100%), I'll explain the reason behind it, later.)

 

Step 1:

  • Create a simple Report with Excel as data source (Data I used is attached, which just few columns from the Employee table (Xtreme Sample Database), in addition to fake data column Achievements.)
  • After pulling data in the report, create a Section on Name, remove the Section Title cell and generate the report structure as below.

Snap 02.png

Report Structure Details: Separate Horizontal tables used to show each row (Name, Position, Hire Data, Salary, and Achievements), to address the scenario, what if one of the Employees doesn't have an Achievement? In that case, we need to hide it and that will affect the relative positioning of Block 3.

    • Employee Details header is a Free Standing Cell with properties:
      • Cell Width 5.5 cm and Height 0.45 cm
      • Font Arial, Size 8, Style Bold, Aligned Left
      • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right
      • No Borders
      • Layout -> Relative Position -> Horizontal: 0.5 cm from Left of Section and Vertical: 0.25 from Top of Section
    • Notes header is a Free Standing Cell with properties:
      • Cell Width 8 cm and Height 0.45 cm
      • Font Arial, Size 8, Style Bold, Aligned Left
      • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right
      • No Borders
      • Layout -> Relative Position -> Horizontal: 0.25 cm from Right of Employee Details block and Vertical: 0 cm from Right of Employee Details block
    • Name, Position, Hire Date, and Salary blocks are Horizontal tables as mentioned above, with Properties
      • Header Cell
        • Cell width 1.5 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
        • Font Arial, Size 7, Style Regular, Aligned Left
        • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
        • No Borders
      • Body Cell
        • Cell width 4 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
        • Font Arial, Size 7, Style Regular, Aligned Right
        • Alignment Right and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
        • No Borders
      • Layout -> Relative Position: Block for Name is vertically positioned to be 0.1 cm below Employee Details header cell and horzizontally positioned to be 0 cm from the left of Employee Details header cell. Blocks for Position, Hire Date, and Salary are vertically positioned to be 0 cm below the block aboe and horizontally positioned to be 0 cm from left of Employee Details header cell.
    • Achievements block is also a Horizontal table, with Properties
      • Header Cell
        • Cell width 1.5 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
        • Font Arial, Size 7, Style Regular
        • Aligned Left and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
        • No Borders
      • Body Cell
        • Cell width 4 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
        • Font Arial, Size 7, Style Regular
        • Alignment Right and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
        • No Borders
      • General -> Display -> check the box for Hide when following formula is true, write formula as =Isnull([Achievements])=1, click Apply and OK, as we want to hide the this block if there is no data or if an Employee doesn't have an Achievement. (Note: This is very important)
    • Notes text is a Vertical table, with properties
      • Table Header is hidden
      • Cell width 8 cm and Auto Height is checked and minimum Height 0.4 cm (Note: ***Width 8 cm is very important, you'll see in Step 2, why.)
      • Font Arial, Size 7, Style Regular
      • Alignment Horizontal Top and Vertical Left, Padding 0.05 cm each on on Top, Bottom, Left, and Right, and Display as Wrap Text (Note: Wrap Text and Padding 0.05 cm is very important)
      • No Borders

 

Step 2:

The first step in the idea behind vertically positioning Block 3 dynamically below Block 1 or Block 3, is to find out the number of lines of data we are showing in each of the Blocks 1, 2. (Lines of data is an important term, please keep that in mind)

 

Finding Lines of Data is easy for Block 1, which is Employee Details, we can create a variable as below using the formula

  • v_Lines in Employee Details =4 + (If(IsNull([Achievements])=1) Then 0 Else 1)

Here, 4 specifies the 4 lines of data (Name, Position, Hire Date and Salary) which is shown for all Employees. The next part of formula is to add 1 to that 4 (if an Employee has any Achievement), or add 0 (if an Employee has no Achievements), as we won't be displaying that block.

 

The fun begins here: Now, let's try to calculate the height of lines of data in Employee Details (height of data). What do I mean by that? Checkout the below pic:

Snap 03.png

From the pic, we see that the height of blank cell with char(13) padded 3 times, is almost (if not exactly) equal to the 2 lines of data in Employee details (Name and Position), which is great, and can be used as a scale in the logic. So, for every 2 lines of data in Employee Details, we want char(13) padded 3 times, and for an additional line of data, we need char(13) padded 2 times. Make sense? Lets create a variable with generic formula which works for any number of lines in Employee Details.

  • v_Height of Employee Details =If(Mod([v_Lines in Employee Details];2)=1) Then ((Floor([v_Lines in Employee Details]/2)*3)+2) ElseIf(Mod([v_Lines in Employee Details];2)=0) Then (([v_Lines in Employee Details]/2)*3)

(Height of Data/whatever block is an important term, please keep that in mind)

 

Finding the Lines of Data of Block 2, which is text for Notes, is tricky, because this block has data coming from one row of one column (unlike Block 1). As we know that the text is wrapped, we need to make an assumption by calculation, how many rows is the data going to be displayed in after Wrap Text? Look at the below image:

Snap 04.PNG

To our eyes, we see that its 8 lines, but how can we calculate that, this is where the importance of Width 8 cm comes into play (font size, padding are equally important). After wrapping the text, count the number of characters in each line, after that, I came to a conclusion that 63 characters per line might be a good idea, then create the below variable to get the approximate number of lines in notes after wrap text. We get that number by ceil of number we get by dividing the length of Notes by 63. (Note: This is why I said, this logic works 90% of the times (and not 100%). I dare say, this is never going to be perfect (except if you're lucky few times)). Because, we can't assume how many characters are going to be displayed in a line after text wrap, as it depends on various factors. Ex: If the last word in a line is lengthy, it may go on to the next line and the number of characters in that line may be far less than our assumption, as you see in the image that words like International (line 2), company (line 4), Seattle (line 7), and Association (line 8) are moved to next lines. That is why, I did few tests and found 63 to be a good idea in this case.

  • v_Approx Number of Lines in Notes after Wrap Text =Ceil(Length([Notes])/63)

 

Now, lets calculate the height of lines of data in Notes (Height of Notes). This formula for this is a little different that (v_Height of Employee Details), because the lines (data) in wrap text are much closer to each other when compared to Employee Details. So, what I figured is:

If lines of data is 1, then we need char(13) padded 2 times (if n=1, then n+1)

If lines of data is 2, then we need chart(13) padded 3 times

If lines of data is 3, then we need chart(13) padded 4 times

so on until

If lines of data is 9, then we need chart(13) padded 10 times and

If lines of data is 10 (or more), then we need chart(13) padded 12 times (if n=10, then n+2)

and so on (hopefully, we won't have Notes going over 15 lines).

 

Using that idea, I created the variable for Height of Notes (variable is names Approx as its never going to be perfect)

  • v_Approx Height of Notes after Wrap Text  =If([v_Approx Number of Lines in Notes after Wrap Text] Between (1;9)) Then ([v_Approx Number of Lines in Notes after Wrap Text]+1) Else ([v_Approx Number of Lines in Notes after Wrap Text]+2)

 

Step 3:

Why did we go through all the hassle of steps 1 & 2, to create only ONE background cell or Container (Container as in dashboards) for both Employee Details and Notes, so we can relatively position Block 3 using that background cell or container.

(Background cell, Container are important terms, please keep them in mind)

 

As I said in the beginning, that the idea is to find which block (1 or 2) has mode data, so we can position block 3 depending on that, as we have the heights of data in both the blocks, lets create a variable to get the the greater one as below (which will be the height of background cell or container):

  • v_Height of Background Cell for Employee Details and Notes

=If([v_Height of Employee Details] > [v_Approx Height of Notes after Wrap Text]) Then [v_Height of Employee Details]

ElseIf([v_Approx Height of Notes after Wrap Text] > [v_Height of Employee Details]) Then [v_Approx Height of Notes after Wrap Text]

ElseIf([v_Approx Height of Notes after Wrap Text] = [v_Height of Employee Details]) Then [v_Height of Employee Details]

 

Step 4:

Let's use a Free Standing Cell to create a background cell or container, with properties as below:

  • Cell Width 14.75 cm and Auto Height and minimum Height of 0.13 cm (Note: Checking Auto Height box is most important, change width to 0.1 cm when report creation is done)
  • Font Arial, Size 6, Style Bold, Aligned Left (Note: Font Size 6 is very important)
  • Alignment -> Padding 0.00 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.00 cm is very important)
  • No Borders
  • Appearance: Make the background color as light grey (Note: change the background color to White when report creation is done)
  • Layout -> Relative Position -> Horizontal: 0.0 cm from Left of Section and Vertical: 0.00 cm from top of Name (horizontal table) (Note: 0.00 cm from top of horizontal table for Name is very important)

And use the below variable in the background cell

  • v_Formula for Background Cell for Employee Details and Notes =RightPad("";[v_Height of Background Cell for Employee Details and Notes];Char(13))

 

As it's called the background cell, lets send it to back by, right click on it -> Order -> Send to back. This is how the report looks now.

Snap 05.png

Step 5:

Create a dummy table to be used as block 3, as in the image (the text in it is a free hand text) and set it relative position as, horizontal position to be 0.00 cm from left of Employee Headers table and vertical position to be 0.15 cm below the Background cell we created in Step 4.

Snap 06.PNG

I've removed the borders of all the blocks, background color of the background cell and this is how the report looks now.

Snap 07.png

We've 90% successfully positioned a block with respect to 2 different blocks or created a Container in Webi. Just so you know, the scenario (requirement) that gave me an inspiration to create this logic is much more complex, and has few other cool things, will share them sometime later. Please let me know of typos and/or other corrections to improve this blog post.

 

Please find the file attached, download, decompress the file and change its extension from .jpg to .wid.

 

Hope you have enjoyed this blog and may have a lot of comments.

Refresh the report for every N Seconds in Web Intelligence

Dear All,

This document will give you an Information about "How to Refresh the Report automatically for every N seconds" in SAP Business Objects Web Intelligence



First open the Web Intelligence Rich Client and select the source connection and select the objects into the Result Objects Panel

 

Rpt 1.png

Then Run Query, after generating the report click Save the report

Rpt2.png

After that, Just reopen that same report, Go to properties--> Documents --> Select REFRESH ON OPEN and save the report again.


Rpt 3.png

After that Go to Report Elements --> Cell--> Blank and Just drag and drop the Blank in to the report panel

Rpt 4.png

Select that blank and write the code like below:

<script>

  1. self.parent._askConfirmationBeforeClosingDoc=false;

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

</script>

Rpt 5.png

But in the above screen shot “n” is the number of seconds. So, depends on the requirement can change the “n” like below.

For this select 10 sec.

Rpt 6.png

After that go to the Blank Cell and right click and select Format cell

Rpt 7.png

Then, Go to Read content as and select HTML and apply

Rpt 8.png

 

Just save that report and close once. Again open that same report and will see: Every 10 sec it will refresh automatically.

Rpt 9.png



Regards,

Ravikanth M


Over the years, many posters on both SCN and BOB have been confused by how to use these two handy functions.

 

They are similar functions, which can often be used together to manipulate dates, but we'll come to that later.

 

FormatDate

 

What does it do?

FormatDate allows you to pass in a date object and outputs a string in the format specified.

 

The basic construct is =FormatDate([date object];"date format")

 

I have a date object, Sales Date, that I want to display as a string in the format year month - 25th Jan 2016 should show as 2016-01

 

To achieve this, I'd write =FormatDate([Sales Date];"yyyy-MM")

 

The key thing to note here is the capitalisation of months - MM is used for months and mm is used for minutes. This applies to both FormatDate and ToDate.

 

ToDate

 

What does it do?

ToDate allows you to pass a character string or object and converts this to a date, based in the input mask provided

 

The basic construct is =ToDate([string object];"input format")

 

I have been given a flat file input and want to convert the date string object that represents a date to a date. An example date string is 26062016 - 26th June 2016

 

To achieve this, I'd write =ToDate([date string];"ddMMyyyy")

 

Note that the input format reflects the format of the character string, not the date format that you want the date to be displayed in when using it. This is a common trap that That is, if you want the above date string to show as 06/26/16 in your report, you would not use "MM/dd/yyyy" as your input format. Input format is about the way that the date string is structured; what you should do is format your new date object using the right-click, format number functionality.

 

Combined Usage Example - Getting the first day of a given month 

One simple way to get the first day of the month is to combine the two functions:

 

=ToDate(FormatDate([Sales Date];"yyyyMM")+"01";"yyyyMMdd")

 

So what does that do? Well, working from the inside, the first thing done is to get the sales year month - 201601 in our example above - as a string. Then, we tag 01 on the end of it, to make it a date string 20160101. We then apply ToDate to that, specifying the correct input mask.

 

You could then take this a step further and use the RelativeDate to get the last day of the previous month - given that not all months are the same lengths, it's easier to add months based on first days then work backwards.

 

 

The key take away from this is to remember that for ToDate, the format that you are specifying relates to the object you are placing into the function, not the desired output date. This is the opposite for FormatDate, where you are specifying the output format.

 

 

Another use for this functionality has come about with the latest Webi release, 4.2SP3, which allows us to merge variables with objects when working with multiple data providers. This will make things like comparing budgets to sales easier; budgets are typically for the month and sales by day. Now you can format your sales date as a month, you can merge this new variable with your budget month and compare the two easily.

WIS 30270.jpg

 

I have seen many blogs regarding this message. I had the same message and was able to figure out the solution, thought of sharing if this might help anyone.

 

Environment: SAP BI Platform 4.1 SP 06 Patch 4

 

Reason:

Having 2 objects with same name in one class.

 

Solution:

Refresh the Universe Structure

Check the Integrity

Change the name or

Place one object in different class

Export the universe and it works fine no more issue.

 

FYI: I see that this is resolved in SP09 for 4.1

 

 

MK!

Hello All,

 

During recent search I found that some of the posts are requested to add/Subtract Hours from a TimeStamp or manually changing the Time Zones at report Level, unfortunately we do not have a direct way Like AddHours(). So I am explaining below a method that can be useful in such cases.

 

Let us experiment this using CurrentDate(). Now a simple rule is that When using RelativeDate() function it needs the number of days that you want to add or subtract but, we need for Hours.

 

So the twist is that if we divide a day in hours we have 24 Hours now if we want x no of hours to be added or Subtracted then we just need to take that part from the day i.e 24 Hrs and add/subtract that form the Current Time.

 

For e.g. to get 1 Hrs added or subtracted we will take 1 part from 24 which is 1/24 similarly for 2 Hrs it will be 2/24 and so on.

 

For half hour it would be 0.5/24.

 

Lets see how it works at report level.

 

Capture.jpg

 

So as you can see we have two columns Current Date & Time and Modified Date & Time. As we can notice that Modified Date & Time is 1 hr behind the Current Date & Time.

 

Here's the code how I have handled it at Report Level.

 

[Current Date & Time]=FormatDate(CurrentDate();"MM/dd/yyyy hh:mm:ss a")

 

[Modified Date & Time]=FormatDate(RelativeDate(CurrentDate();-(1/24));"MM/dd/yyyy hh:mm:ss a")

 

If you notice the underlined part of Modified Date & Time it shows how we have deducted 1 Hr from the Current Date.

 

The above method can be useful when you want to convert a Local Time Zone to a different Time Zone at report level.

 

Please share your valuable inputs and comments if we have a better way to deal with it.

 

Regards

Niraj

One of my colleagues had an interesting requirement (and I'm pretty sure many users might've), to show halfhourly timestamps in a chart, so they can show number of calls received to report issues every half hour (in a day) vs how many of them were resolved.

 

Updated (6/15/2016 8:44 AM EST): As few people suggested and/or requested, I'm updating this blog with another way to do this. Thanks to Amit Kumar, for the formula he suggested.

 

There are 2 ways of doing this, which means, 2 different formulas can be used to create the Halfhourly buckets.

  1. Get the timestamp using the formula =Formatdate([Date timestamp];"HH:mm:ss") and then, create the 48 different halfhourly timestamp buckets.
  2. Get the timestamp using the same formula, convert each timestamp (every second) of a day to a numeric value and then, create 48 different halfhourly timestamp buckets.

 

Both solutions will work perfect.

 

Below is the data that we'll be using during this exercise:

 

Date TimestampCalls ReceivedCalls Resolved
6/12/2016 12:00:01 AM98
6/12/2016 12:15:00 AM21
6/12/2016 12:30:00 AM32
6/12/2016 12:45:00 AM32
6/12/2016 1:00:00 AM43
6/12/2016 1:15:00 AM54
6/12/2016 1:30:00 AM76
6/12/2016 1:45:00 AM32
6/12/2016 2:00:00 AM87
6/12/2016 2:15:00 AM43
6/12/2016 2:30:00 AM76
6/12/2016 2:45:00 AM76
6/12/2016 3:00:00 AM32
6/12/2016 3:15:00 AM65
6/12/2016 3:30:00 AM65
6/12/2016 3:45:00 AM75
6/12/2016 4:00:00 AM31
6/12/2016 4:15:00 AM20
6/12/2016 4:30:00 AM42
6/12/2016 4:45:00 AM86
6/12/2016 5:00:00 AM42
6/12/2016 5:15:00 AM86
6/12/2016 5:30:00 AM42
6/12/2016 5:45:00 AM75
6/12/2016 6:00:00 AM86
6/12/2016 6:15:00 AM41
6/12/2016 6:30:00 AM52
6/12/2016 6:45:00 AM41
6/12/2016 7:00:00 AM41
6/12/2016 7:15:00 AM85
6/12/2016 7:30:00 AM40
6/12/2016 7:45:00 AM73
6/12/2016 8:00:00 AM51
6/12/2016 8:15:00 AM51
6/12/2016 8:30:00 AM62
6/12/2016 8:45:00 AM84
6/12/2016 9:00:00 AM40
6/12/2016 9:15:00 AM84
6/12/2016 9:30:00 AM40
6/12/2016 9:45:00 AM84
6/12/2016 10:00:00 AM43
6/12/2016 10:15:00 AM76
6/12/2016 10:30:00 AM43
6/12/2016 10:45:00 AM43
6/12/2016 11:00:00 AM54
6/12/2016 11:15:00 AM87
6/12/2016 11:30:00 AM32
6/12/2016 11:45:00 AM32
6/12/2016 12:00:00 PM65
6/12/2016 12:15:00 PM43
6/12/2016 12:30:00 PM76
6/12/2016 12:45:00 PM51
6/12/2016 1:00:00 PM73
6/12/2016 1:15:00 PM73
6/12/2016 1:30:00 PM73
6/12/2016 1:45:00 PM62
6/12/2016 2:00:00 PM32
6/12/2016 2:15:00 PM76
6/12/2016 2:30:00 PM32
6/12/2016 2:45:00 PM65
6/12/2016 3:00:00 PM32
6/12/2016 3:15:00 PM76
6/12/2016 3:30:00 PM32
6/12/2016 3:45:00 PM76
6/12/2016 4:00:00 PM31
6/12/2016 4:15:00 PM75
6/12/2016 4:30:00 PM31
6/12/2016 4:45:00 PM75
6/12/2016 5:00:00 PM64
6/12/2016 5:15:00 PM64
6/12/2016 5:30:00 PM20
6/12/2016 5:45:00 PM75
6/12/2016 6:00:00 PM64
6/12/2016 6:15:00 PM42
6/12/2016 6:30:00 PM75
6/12/2016 6:45:00 PM53
6/12/2016 7:00:00 PM72
6/12/2016 7:15:00 PM72
6/12/2016 7:30:00 PM32
6/12/2016 7:45:00 PM61
6/12/2016 8:00:00 PM32
6/12/2016 8:15:00 PM72
6/12/2016 8:30:00 PM33
6/12/2016 8:45:00 PM61
6/12/2016 9:00:00 PM33
6/12/2016 9:15:00 PM72
6/12/2016 9:30:00 PM32
6/12/2016 9:45:00 PM72
6/12/2016 10:00:00 PM32
6/12/2016 10:15:00 PM76
6/12/2016 10:30:00 PM32
6/12/2016 10:45:00 PM76
6/12/2016 11:00:00 PM64
6/12/2016 11:15:00 PM65
6/12/2016 11:30:00 PM20
6/12/2016 11:45:00 PM64
6/12/2016 11:59:59 PM92


Solution 1:

Step 1:

Create the below Dimension variables

  • v_TimeStamp =FormatDate([Date Timestamp];"HH:mm:ss") (to convert the Date Timestamp to HH:mm:ss format (24 hr format))

Updated (6/30/2016 3:57 PM EST): We can use a simple one line formula to replace the huge one with 40 If conditions. (Courtesy of Clemens Potter)

  • v_Halfhourly Buckets (this creates 48 different buckets depending on the v_TimeStamp)

=FormatDate([Date Timestamp];"HH:") + (If(FormatDate([Date Timestamp];"mm") < "30") Then "00:00" Else "30:00")

 

Disregard the below one:

=If([v_TimeStamp]  Between  ("00:00:00";"00:29:59"))  Then  "00:00:00"

ElseIf([v_TimeStamp]  Between  ("00:30:00";"00:59:59"))  Then  "00:30:00"

ElseIf([v_TimeStamp]  Between  ("01:00:00";"01:29:59"))  Then  "01:00:00"

ElseIf([v_TimeStamp]  Between  ("01:30:00";"01:59:59"))  Then  "01:30:00"

ElseIf([v_TimeStamp]  Between  ("02:00:00";"02:29:59"))  Then  "02:00:00"

ElseIf([v_TimeStamp]  Between  ("02:30:00";"02:59:59"))  Then  "02:30:00"

ElseIf([v_TimeStamp]  Between  ("03:00:00";"03:29:59"))  Then  "03:00:00"

ElseIf([v_TimeStamp]  Between  ("03:30:00";"03:59:59"))  Then  "03:30:00"

ElseIf([v_TimeStamp]  Between  ("04:00:00";"04:29:59"))  Then  "04:00:00"

ElseIf([v_TimeStamp]  Between  ("04:30:00";"04:59:59"))  Then  "04:30:00"

ElseIf([v_TimeStamp]  Between  ("05:00:00";"05:29:59"))  Then  "05:00:00"

ElseIf([v_TimeStamp]  Between  ("05:30:00";"05:59:59"))  Then  "05:30:00"

ElseIf([v_TimeStamp]  Between  ("06:00:00";"06:29:59"))  Then  "06:00:00"

ElseIf([v_TimeStamp]  Between  ("06:30:00";"06:59:59"))  Then  "06:30:00"

ElseIf([v_TimeStamp]  Between  ("07:00:00";"07:29:59"))  Then  "07:00:00"

ElseIf([v_TimeStamp]  Between  ("07:30:00";"07:59:59"))  Then  "07:30:00"

ElseIf([v_TimeStamp]  Between  ("08:00:00";"08:29:59"))  Then  "08:00:00"

ElseIf([v_TimeStamp]  Between  ("08:30:00";"08:59:59"))  Then  "08:30:00"

ElseIf([v_TimeStamp]  Between  ("09:00:00";"09:29:59"))  Then  "09:00:00"

ElseIf([v_TimeStamp]  Between  ("09:30:00";"09:59:59"))  Then  "09:30:00"

ElseIf([v_TimeStamp]  Between  ("10:00:00";"10:29:59"))  Then  "10:00:00"

ElseIf([v_TimeStamp]  Between  ("10:30:00";"10:59:59"))  Then  "10:30:00"

ElseIf([v_TimeStamp]  Between  ("11:00:00";"11:29:59"))  Then  "11:00:00"

ElseIf([v_TimeStamp]  Between  ("11:30:00";"11:59:59"))  Then  "11:30:00"

ElseIf([v_TimeStamp]  Between  ("12:00:00";"12:29:59"))  Then  "12:00:00"

ElseIf([v_TimeStamp]  Between  ("12:30:00";"12:59:59"))  Then  "12:30:00"

ElseIf([v_TimeStamp]  Between  ("13:00:00";"13:29:59"))  Then  "13:00:00"

ElseIf([v_TimeStamp]  Between  ("13:30:00";"13:59:59"))  Then  "13:30:00"

ElseIf([v_TimeStamp]  Between  ("14:00:00";"14:29:59"))  Then  "14:00:00"

ElseIf([v_TimeStamp]  Between  ("14:30:00";"14:59:59"))  Then  "14:30:00"

ElseIf([v_TimeStamp]  Between  ("15:00:00";"15:29:59"))  Then  "15:00:00"

ElseIf([v_TimeStamp]  Between  ("15:30:00";"15:59:59"))  Then  "15:30:00"

ElseIf([v_TimeStamp]  Between  ("16:00:00";"16:29:59"))  Then  "16:00:00"

ElseIf([v_TimeStamp]  Between  ("16:30:00";"16:59:59"))  Then  "16:30:00"

ElseIf([v_TimeStamp]  Between  ("17:00:00";"17:29:59"))  Then  "17:00:00"

ElseIf([v_TimeStamp]  Between  ("17:30:00";"17:59:59"))  Then  "17:30:00"

ElseIf([v_TimeStamp]  Between  ("18:00:00";"18:29:59"))  Then  "18:00:00"

ElseIf([v_TimeStamp]  Between  ("18:30:00";"18:59:59"))  Then  "18:30:00"

ElseIf([v_TimeStamp]  Between  ("19:00:00";"19:29:59"))  Then  "19:00:00"

ElseIf([v_TimeStamp]  Between  ("19:30:00";"19:59:59"))  Then  "19:30:00"

ElseIf([v_TimeStamp]  Between  ("20:00:00";"20:29:59"))  Then  "20:00:00"

ElseIf([v_TimeStamp]  Between  ("20:30:00";"20:59:59"))  Then  "20:30:00"

ElseIf([v_TimeStamp]  Between  ("21:00:00";"21:29:59"))  Then  "21:00:00"

ElseIf([v_TimeStamp]  Between  ("21:30:00";"21:59:59"))  Then  "21:30:00"

ElseIf([v_TimeStamp]  Between  ("22:00:00";"22:29:59"))  Then  "22:00:00"

ElseIf([v_TimeStamp]  Between  ("22:30:00";"22:59:59"))  Then  "22:30:00"

ElseIf([v_TimeStamp]  Between  ("23:00:00";"23:29:59"))  Then  "23:00:00"

ElseIf([v_TimeStamp]  Between  ("23:30:00";"23:59:59"))  Then  "23:30:00"

 

Step 2:

  1. Create a Column Chart, with v_Halfhourly Buckets on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
  2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
  3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that'll make the bars for Calls Received and Calls Resolved for a Halfhourly Bucket look close to each other, which looks nice to compare the 2 values. Apply and OK.
  4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.


Solution 2:
Step 1:

Create the below Dimension variables

  • v_Date TimeStamp =ToDate(FormatDate([Date Timestamp];"M/d/yyyy HH:mm:ss");"M/d/yyyy HH:mm:ss") (to convert the Date Timestamp to 24 hr format) 
  • v_Hours =ToNumber(FormatDate([DateTimeStamp];"HH")) (to get the Hour in 24 hr format in numeric datatype, we want it to be 24 hr format, so we don't have to worry about AM or PM)
  • v_Minutes =ToNumber(FormatDate([DateTimeStamp];"mm")) (to get the mins in numeric datatype)
  • v_Seconds =ToNumber(FormatDate([DateTimeStamp];"ss")) (to get the seconds datatype)
  • v_Numeric Value =([v_Hours]*3600)+([v_Minutes]*60)+[v_Seconds] (this creates a numeric value for every sec of the day (0 - 86399), as a day has 86400 secs, logic is simple, multiple Hours by 3600 as an hour has 3600 secs, multiple Minutes by 60 as a minute has 60 secs and add the Seconds)

Now, main variable of this logic

  • v_Halfhourly Timestamps (this creates 48 different buckets depending on the v_Numeric Value)

=If([v_Numeric Value]  Between  (0;1799))  Then  "00:00:00"

ElseIf([v_Numeric Value]  Between  (1800;3599))  Then  "00:30:00"

ElseIf([v_Numeric Value]  Between  (3600;5399))  Then  "01:00:00"

ElseIf([v_Numeric Value]  Between  (5400;7199))  Then  "01:30:00"

ElseIf([v_Numeric Value]  Between  (7200;8999))  Then  "02:00:00"

ElseIf([v_Numeric Value]  Between  (9000;10799))  Then  "02:30:00"

ElseIf([v_Numeric Value]  Between  (10800;12599))  Then  "03:00:00"

ElseIf([v_Numeric Value]  Between  (12600;14399))  Then  "03:30:00"

ElseIf([v_Numeric Value]  Between  (14400;16199))  Then  "04:00:00"

ElseIf([v_Numeric Value]  Between  (16200;17999))  Then  "04:30:00"

ElseIf([v_Numeric Value]  Between  (18000;19799))  Then  "05:00:00"

ElseIf([v_Numeric Value]  Between  (19800;21599))  Then  "05:30:00"

ElseIf([v_Numeric Value]  Between  (21600;23399))  Then  "06:00:00"

ElseIf([v_Numeric Value]  Between  (23400;25199))  Then  "06:30:00"

ElseIf([v_Numeric Value]  Between  (25200;26999))  Then  "07:00:00"

ElseIf([v_Numeric Value]  Between  (27000;28799))  Then  "07:30:00"

ElseIf([v_Numeric Value]  Between  (28800;30599))  Then  "08:00:00"

ElseIf([v_Numeric Value]  Between  (30600;32399))  Then  "08:30:00"

ElseIf([v_Numeric Value]  Between  (32400;34199))  Then  "09:00:00"

ElseIf([v_Numeric Value]  Between  (34200;35999))  Then  "09:30:00"

ElseIf([v_Numeric Value]  Between  (36000;37799))  Then  "10:00:00"

ElseIf([v_Numeric Value]  Between  (37800;39599))  Then  "10:30:00"

ElseIf([v_Numeric Value]  Between  (39600;41399))  Then  "11:00:00"

ElseIf([v_Numeric Value]  Between  (41400;43199))  Then  "11:30:00"

ElseIf([v_Numeric Value]  Between  (43200;44999))  Then  "12:00:00"

ElseIf([v_Numeric Value]  Between  (45000;46799))  Then  "12:30:00"

ElseIf([v_Numeric Value]  Between  (46800;48599))  Then  "13:00:00"

ElseIf([v_Numeric Value]  Between  (48600;50399))  Then  "13:30:00"

ElseIf([v_Numeric Value]  Between  (50400;52199))  Then  "14:00:00"

ElseIf([v_Numeric Value]  Between  (52200;53999))  Then  "14:30:00"

ElseIf([v_Numeric Value]  Between  (54000;55799))  Then  "15:00:00"

ElseIf([v_Numeric Value]  Between  (55800;57599))  Then  "15:30:00"

ElseIf([v_Numeric Value]  Between  (57600;59399))  Then  "16:00:00"

ElseIf([v_Numeric Value]  Between  (59400;61199))  Then  "16:30:00"

ElseIf([v_Numeric Value]  Between  (61200;62999))  Then  "17:00:00"

ElseIf([v_Numeric Value]  Between  (63000;64799))  Then  "17:30:00"

ElseIf([v_Numeric Value]  Between  (64800;66599))  Then  "18:00:00"

ElseIf([v_Numeric Value]  Between  (66600;68399))  Then  "18:30:00"

ElseIf([v_Numeric Value]  Between  (68400;70199))  Then  "19:00:00"

ElseIf([v_Numeric Value]  Between  (70200;71999))  Then  "19:30:00"

ElseIf([v_Numeric Value]  Between  (72000;73799))  Then  "20:00:00"

ElseIf([v_Numeric Value]  Between  (73800;75599))  Then  "20:30:00"

ElseIf([v_Numeric Value]  Between  (75600;77399))  Then  "21:00:00"

ElseIf([v_Numeric Value]  Between  (77400;79199))  Then  "21:30:00"

ElseIf([v_Numeric Value]  Between  (79200;80999))  Then  "22:00:00"

ElseIf([v_Numeric Value]  Between  (81000;82799))  Then  "22:30:00"

ElseIf([v_Numeric Value]  Between  (82800;84599))  Then  "23:00:00"

ElseIf([v_Numeric Value]  Between  (84600;86399))  Then  "23:30:00"

 

Step 2:

  1. Create a Column Chart, with v_Halfhourly Timestamps on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
  2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
  3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that'll make the bars for Calls Received and Calls Resolved for a Halfhourly Timestamp look close to each other, which looks nice to compare the 2 values. Apply and OK.
  4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.

 

Voila, we did it. This is what the chart looks like, now.

Snap 01.png

 

Please try it and let me know your feedback, so I can fix if there are any issues or improve it further.


Thanks,
Mahboob Mohammed

I've seen quite a few times, people requesting how to conditionally change font color of hyperlinked columns depending on the values in the cell. Let's see how to approach and address that request. I'll be using the below sample data for this exercise:

Snap 01.PNG

After applying a hyperlink on the Company Name (with the website value), let's see how can we change the font color of Apple to Aqua, Google to Green, Oracle to Orange, and so on.

 

Steps to add hyperlink:

  • Right click on the Company Name column -> Linking -> click Add Hyperlink
  • In the Link to Web page, in the space given to enter the web address, write the formula =[Website] and click Parse. (we already have the link value in [Website] object.
  • Choose the Target window as New window
  • In Tooltip, click the down arrow on the right, click build formula, write formula as ="http://"+[Website], and click OK
  • Click Apply and OK

Snap 03.pngSnap 02.png

Steps to format hyperlink:

  • Click inside the cell of one of the Company Names to see the html code in the Formula bar, which would be ="<a href=\"=[Website]\" title=\""+("http://"+[Website])+"\" target=\"_blank\" nav=\"web\">"+[Company Name]+"</a>" as shown below.

Snap 04.png

 

  • Between "a" and "href" in the "a href" in the beginning , add the text style='text-decoration:none;" +  (If([Company Name]="Apple") Then "color:aqua" ElseIf([Company Name]="Facebook") Then "color:#4766A9"  ElseIf([Company Name]="Google") Then "color:green" ElseIf([Company Name]="Oracle") Then "color:red" ElseIf([Company Name]="SAP") Then "color:orange") + "' and parse and apply the formula.

 

Note: The whole text in the cell would now be:

="<a  style='text-decoration:none;" +  (If([Company Name]="Apple") Then "color:aqua" ElseIf([Company Name]="Facebook") Then "color:#4766A9"  ElseIf([Company Name]="Google") Then "color:green" ElseIf([Company Name]="Oracle") Then "color:red" ElseIf([Company Name]="SAP") Then "color:orange") + "' href=\"=[Website]\" title=\""+("http://"+[Website])+"\" target=\"_blank\" nav=\"web\">"+[Company Name]+"</a>"

 

This is what the result looks like, Facebook is showing up before Apple, because of the color code we're using for Facebook will come before color name we're using for Apple, when sorted. We can add Company Name in the beginning, so it sorts using Company Name and then hide that extra column by right clicking on it -> Hide -> click on Hide Dimension.

Snap 05.png

Please try it and let me know your feedback.

 

Thanks,
Mahboob Mohammed

Hi Team,

 

Every now and then we will have requirement to show Pie chart for multiple measures in Webi (It is quite straight and easy in EXCEL !!) .In WebI ,answer is NO .


Case


Assume we have BW data for Accounts Receivables at Document and Line Item level .To bring in all this data into WebI and deriving ageing buckets with respect to dates will hamper performance .

 

In BEx it is easy to derive Amount from dates into Ageing Buckets 0-30 , 31-45 and 46-60 .

 

So , now we have 3 key figures .

 

Measures.png

Users always keen in seeing this totals in PIE . Here starts the problem , because PIE in WebI must be derived based out of dimension values and we don’t have one.

 

Solution 1: We will have a personal data provider (Excel/Text) with all the required bucket names as values , then create universe , use it as another data provider to build report .

 

Solution 2:In BO 3.x and BI 4.x , in case you already have a relational universe. Select that universe as a data source , select any of the dimension object ,use Custom SQL

 

Select '0-30' as "Ageing Bucket" from table name UNION Select '31-45'  as "Ageing Bucket" from table name UNION Select '46-60' as "Ageing Bucket" rom table name

 

make sure you append from clause for every select .

 

Pre requisites for using relational connection.


We need one connection that points to any of the relational databases (SQL server/ Oracle..)

Don’t bother if there is no database in your environment .Think , luckily most of us at least use one for BOBJ Auditing or our native teacher EFASHION That is going to be our relational connection !!

 

Solution 3 : Viable Solution (I’m thinking of !!)


From BI 4.1 SP6 , Free Hand SQL as a data source supports at great extension .  We are going to derive the Custom dimension to build the PIE .

 

Code

Select '0-30' as "Ageing Bucket" UNION Select '31-45'  as "Ageing Bucket" UNION Select '46-60' as "Ageing Bucket"

That’s all , our custom dimension is ready to create PIE. Use this code and run the query .


In Report

Create a variable to redefine all the 3 key figures as one measure with respect to our custom dimension .

Amount =If([Ageing Bucket]="0-30";[0-30];If([Ageing Bucket]="31-45";[31-45];If([Ageing Bucket]="46-60";[46-60])))


Create Chart

Now create PIE with Ageing Bucket Dimension (from Free Hand SQL source) and Amount variable

 

Pie Chart.PNG

 

Hope this small piece of information saves our time .

 

Thank you for reading

 

Please share your inputs .

Many a time, we've seen users and/or developers wanting to be able to display (and/or print) continuous/sequential page numbers in a multi-tab Webi document.

 

Suppose, we've a Webi Document with 3 tabs, Tab 1 having 3 pages worth of data in Page mode, Tab 2 having 5 pages worth of data in Page mode, and Tab 3 having 10 pages worth of data in Page mode. We'd like to display (and/or print):

  • Page numbers 1-3 in Tab 1
  • Page numbers 4-8  in Tab 2 (instead of restarting numbering from 1 again and displaying 1-5) and
  • Page numbers 9-18 in Tab 3 (instead of restarting numbering from 1 again and displaying 1-10)

 

This workaround will come handy.

 

Step 1:

Create the below variables:

  • Total Number of Pages in Tab 1 = 0 (don't worry, its OK, we'll update it later, to function dynamically)
  • Total Number of Pages in Tab 2 = 0 (don't worry, its OK, we'll update it later, to function dynamically)
  • Total Number of Pages in Tab 3 = 0 (don't worry, its OK, we'll update it later, to function dynamically)
  • Total Number of Pages in Document = [Total Number of Pages in Tab 1] + [Total Number of Pages in Tab 2] + [Total Number of Pages in Tab 3]
  • Page Numbers in Tab 1 = Page() + " of " + [Total Number of Pages in Document]
  • Page Numbers in Tab 2 = ([Total Number of Pages in Tab 1] + Page()) +" of " + [Total Number of Pages in Document]
  • Page Numbers in Tab 3 = ([Total Number of Pages in Tab 1] + [Total Number of Pages in Tab 2] + Page()) +" of " + [Total Number of Pages in Document]

 

Step 2:

Input Controls:

  • In Tab 1, create a single value Input Control with the variable object [Total Number of Pages in Tab 1] with Entry Field option, so we're able to manually type-in values.
  • Similarly, create an input control in Tab 2 with [Total Number of Pages in Tab 2]
  • Similarly, create an input control in Tab 3 with [Total Number of Pages in Tab 3]

 

Step 3:

Displaying the Page Numbers:

  • Drag and drop a blank cell in Tab 1 in Header or Footer (wherever we want to show the Page Numbers) and add =[Page Numbers in Tab 1] variable in it formula.
  • Similarly, in Tab 2, using [Page Numbers in Tab 2]
  • Similarly, in Tab 3, using [Page Numbers in Tab 3]

 

Voila, we're done.

 

Very Important: Every time we refresh the report or update the structure of the report by adding tables or charts, we need to open each tab and manually check the last page number and type that number in the Input Control of that tab. And then, we can print it or save it to PDF.

 

Note: This will display (and/or print) page numbers as 1 of 18 and so on, as we've a [Total Number of Pages in Document] variable. We can customize if we don't need that " of 18" part.

 

Very Important: Please make sure to type in Numbers only in the Input Control, if we type in text, it'll mess up the formulas and hence the page numbers displayed in one or all tabs may be wrong.

 

Try it and let me know your feedback.

 

Thanks,
Mahboob Mohammed

Actions

Filter Blog

By author:
By date:
By tag: