This blog is intended to provide some tips and tricks for creating custom reports or data sources in SAP Business ByDesign. It’s aimed at users that don’t have a reporting background, so if you’re used to building SQL-based reports, just bear with me. This isn’t an end-to-end guide from data source to report, but serves as a reference to use if you’re just getting started, or have gotten stuck trying to figure out how to do something.
In this blog I’ll cover:
- Display Repeated Texts
- Zero Suppression
- Understanding Results Rows
- Show Attributes
- Create Your Own Data Sources
- Joined Data Sources
- Restricted Key Figures
- Calculated Key Figures
- Exception Aggregation
- Combined Data Sources
1. Display Repeated Texts
If you would like to export data from SAP Business ByDesign to use within Excel, you’ll find the option “Display Repeated Texts” very useful for anything VLOOKUP-based. For example, if we have data in this format:
We go to Settings -> Table
And check the Display Repeated Texts indicator.
Now, each field in each row contains a value, allowing you to use functions like VLOOKUP or SUMIF without having to manually populate these fields.
2. Zero Suppression
If you want to exclude zero or missing values within a report, you can use options to exclude these rows or columns. For example, within the report below, we have a few rows that have zero values only and a few columns that have zero values only.
If you go to Settings -> Key Figures
Then select the General tab
You can apply Zero Suppression to rows, columns, or both.
Note that there are two types – “Suppress When All Results Equal Zero” will apply suppression when the totals for a row or column equal zero, even though there may be individual values. For example, if you were reporting on manual journal entry vouchers by month, and you wanted to exclude months in which all the journal entry vouchers that had been posted had also been reversed in the same month, you’d select “Suppress When All Results Equal Zero.”
The other option is “Suppress When All Values Equal Zero”, which is stricter – it will only suppress a row or column when there are no non-zero values. So, in the above example, even the months with a net value of zero would still appear. In the below example, we can see how we’ve reduced the number of rows and columns.
3. Understanding Results Rows
Let’s say we want to see subtotals by account in the example below:
In order to do so, you’ll need to go to Settings -> Characteristics
Now, everyone’s first impulse is to say “I need a subtotal by account, so I’ll set the account results row to show."
However, if you set this and click OK, you won’t see a subtotal by account.
Why is this? It’s because the results row shows the total of all the values within that characteristic. By thinking of the results row using the words of all will take the guesswork out of your results row.
So, if we have accounts and months as our row characteristics, I ask myself:
Do I want a subtotal of all accounts? No.
Do I want a subtotal of all months within an account? Yes.
By answering these questions above, I can set up my result row options as shown below.
4. Show Attributes
While most reports contain the characteristics that you need, you may have occasion to want to include particular data of a characteristic that you use within the report.
Generally, this will apply to informational data only, in the sense that the data we bring in will appear alongside the other characteristics, but we don’t want it to influence the grouping, sorting, or any of the data we see.
For example, let’s say we’re looking at historical sales order volume, and we’d like to know what the lifecycle status is for the accounts that we’re seeing.
I don’t want to exclude blocked or obsolete accounts, nor do I want to group or sort by the lifecycle status – I’d just like to know what it currently is. In this case, I’d use an attribute of a characteristics. To do this, I go to Settings -> Characteristics
You’ll see that there’s an “Attribute Shown” column on the far-right. Click on the value selector.
In the value selector I can see all the additional attributes I can include for the associated account. I can see that Status is an attribute that I can use. I select that row, then click on the > arrow.
Now, when I look at my account data, I see that in addition to the description and ID of my account, I have an informational status column.
5. Create Your Own Data Sources
Users will occasionally want reports that go outside the standard SAP created reports. This most frequently occurs when they want to bring together data from different functional areas of SAP Business ByDesign, or when they want detailed data on a variety of different objects within similar areas of ByDesign.
To be able to do this, you have the ability to create your own data sources – either a joined data source or a combined data source. One of the types of question I get most frequently relates to the differences between a joined and combined data source.
When trying to determine which will be right for your purposes, consider this:
You’ll usually use a joined data source when you expect not only the types of characteristics to be the same, but that they also refer to the same thing.
Whereas a combined data source is used when the types of characteristics are the same, but they may refer to different things.
While there’s some good information that describes the differences between the two types within the SAP Business ByDesign documentation, my rule of thumb to use (at least as a starting point) is as follows:
Will there be a problem if data gets replicated?
If the answer is “Yes”, then I will start with a combined data source.If not, then I’ll start with a joined data source.
There are other questions you can ask to help steer you in the right direction, which I’ll touch on in the section on combined data sources.
Here’s an example:
One of our clients wanted to be able to estimate project revenue by project task for fixed fee projects. In order to do this, we needed to maintain an estimated billing rate for each employee on the project.
While I could enter a billing rate at the project staffing screen, this would only allow me to estimate revenue at a total project level.
So, I needed to create a data source that would contain both my estimated billing rates and project task hours detail data.
I identified my two data sources, then had to decide how to bring them together. So I ask the question – will there be a problem if data gets replicated?
Because the estimated billing rate for the combination of project and team member could relate to multiple project tasks, I’m going to have to replicate data – I’ll need to transfer a single combination of project and team member estimated billing rate to multiple project tasks.
Here’s an example for a combined data source:
A client wanted a data source to contain detailed data from both sales orders and outbound deliveries.
I identified the sources to use (sales order items and outbound delivery items) – and because these data sources potentially contained multiple items for a sales order or for an outbound delivery, I needed to merge them carefully.
So I ask the questions – will there be a problem if data gets replicated? Well, because a single sales order Item could potentially be fulfilled with multiple outbound delivery items, trying to join the two sources means that I’d have the data for that single sales order item replicated for each outbound delivery item – so, any other key figures (like net amount) would also be replicated, leading to inaccurate key figure values. So yes, there will be a problem if data gets replicated.
This rule will not always lead you directly to whether to use a joined or combined source, but it’s a good starting point.
6. Joined Data Sources
For this blog, I’ve added an extension field for the “Estimated Billing Rate”. For how to do this, see the blog post on Customizing SAP Business ByDesign Part 1.
Now, it’s time to create my custom data source. I’ll get into which type to use under different circumstances later in the blog post – for now, just know that we’re creating a joined data source. I go to Business Analytics -> Design Data Sources
Then, click on New -> Joined Data Sources
First, I provide a name and description – it’s good to make these detailed enough to understand the purpose of this data source if you, or someone else, has to come back to it at a later point in time.
Next, you have to pick a “Join Type”. You have one of two options:
A “Left Outer Join” means that you will see all the data from the “source” data source you add (this will default to the first one), but you will only see data from the second data source if the two share the same values of the characteristics you specify to join (we’ll get to that in a second).
An “Inner Join” means that you will only see data when both data sources actually have data within the characteristics you specify to join.
So, to start, we click on “Add Data Source”, then we search for the data source we want to use. In this case, we specify “Projects, Baselines, and Snapshots”, then scroll through and select the characteristics and key figures that you want to use, then click “OK”
Click “Add Data Source” again, then add the project team member data sources and select the characteristics and key figures you need from there as well.
Now, we need to specify our joins.
First, we join project and project – this means that I want to associate the data from a particular project in the “Projects, Baselines, and Snapshots” with the same project within “Project Team Member”.
Likewise, I join service responsible and team member – while the characteristics names are different, they are actually referring to the same thing. Additionally, this join means that I want to associate data from project team member (in this case, the estimated billing rate) with the “Projects, Baselines, and Snapshots” when the project is the same and the service responsible/team member is the same – so essentially, this allows me to associate an expected billing rate with things such as actual work hours, provided the two joined characteristics are the same for both.
If you’re finding this difficult to understand, there are a huge amount of resources on the web – essentially, what I’m talking about is very similar to SQL joins and SQL unions – so, searching for those topics may yield other explanations that you understand better.
7. Restricted Key Figures
At this point, we’re going to take a slight detour to the key figures. After all, we’re creating this data source so that we can use existing key figures to generate new key figures.
A restricted key figure allows you to take data and apply filters to that key figure only (as opposed to the selections you make in the report, which apply the filters to all data).
For our example, we’ve created our data source that joins the project hours data to the project team members data.
However, as the data source we used contains data not only from the current project values, but the baselines and snapshots as well, we’ll need to restrict the data that we use in our new key figures.
To start, we’ll go to Business Analytics -> Design Key Figures
Within this work list, click on “New”.
We’ll start by giving the key figure a name and description – make sure you do this now, as chaining multiple key figures together can really get confusing if you forget what one of them was for.
Then, you need to specify the data source that we’re adding it to – in this case, it’s our custom data source, so type that in to the “Data Source Name” field and select the ID that appears.
Now, we select the key figure we want to restrict – in this example, it’s the actual work (in hours).
Then click “Next”.
The next screen allows us to specify the characteristics we use to restrict the key figure. These are from the same list of characteristics that we chose when creating the data source.
In my case, I know that the projects, baselines, and snapshots key figures are separated by the “Source of Data” characteristics, which identifies what the value relates to.
So I select source of data as a characteristic. You can add multiple rows here to restrict by multiple characteristics.
You also have two options for the “Restriction Type”.
The first is “Fixed”, which means that you can specify how the key figure is restricted here, and it can’t be changed, except by going back into this screen to edit it.
Alternatively, you can specify “Variable”, which means you can allow users to pick the restriction when the report is run (you can also make this mandatory). This will cause a special field to show up in the selections area, but that field will only affect the single restricted key figure.
In our case, we’re going to use a “Fixed” restriction type. Once we’ve specified this, click on “Set Fixed Value Selections”.
A pop-up appears where we can specify the values for the selection – in our case, I use “Source of Data” = 13 – Project Time Confirmation, which will restrict the actual work hours to only be those from project time confirmations (i.e. team members’ time sheets). This restriction ensures that I will exclude baselines and snapshots actual work hours.
Now, click on “Next”, then “Finish”.
8. Calculated Key Figures
Now that we have a restricted key figure to give us only the current actual work hours, we want a key figure to multiply these by the estimated billing rate – this will provide us with our estimated revenue.
Again, I go to Business Analytics -> New Key Figure
Within the screen, I specify my name, description, and data source. I specify that this key figure will be calculated, which means I don’t have to select a key figure (like I did with the restricted key figure).
Upon clicking “Next”, I’m able to set the key figure calculation. On the left side of the screen, I see my list of key figures, including the custom ones that I’ve created.
So, for this calculation, I take my custom restricted key figure for “Current Actual Work” and specify that it is multiplied by the “Estimated Billing Rate”. Then, I click “Next”, but not “Finish” quite yet.
The last step is that I need to specify “Exception Aggregation”. I’ll explain why next.
9. Exception Aggregation
Exception Aggregation allows you to adjust the behavior of key figures when they’re aggregated. The main use for this is when the aggregation of a key figure should calculate differently compared to when the key figure is not aggregated.
For example, in our “Current Estimated Revenue Contribution”, we calculate this by taking a team member’s actual work hours from a project task, then multiply this by the estimated billing rate.
This works perfectly fine in a non-aggregated scenario.
But, what if we aren’t looking at the amounts on a team member by team member basis? Let’s imagine I just want to see the total “Current Estimated Revenue Contribution” for the project task.
What SAP Business ByDesign will first do is aggregate all the current actual work hours and also aggregate all the estimated billing rates for all of the team members within that task.
Then it will multiply them together.Which won’t work.
For example, if I have a project task “A”, with three team members, who each worked 10 hours and had estimated billing rates of $100, I’d end up with the calculation of 30 current actual work hours multiplied by the sum of $300 total estimated billing rate, leading to $9,000 current estimated revenue contribution, where instead it really should be $3,000.
What Exception Aggregation allows me to do is specify that when SAP Business ByDesign is calculating this key figure, and aggregating multiple team members, ByDesign should instead total up the individual calculated values for each team member (that is, calculate the current estimated revenue contribution for each team member individually, then just sum up the resultant calculated values).
10. Combined Data Sources
An additional question that may steer you to the use of a combined data source instead of a joined data source is this: Are the two data sources closely related? If the answer is "No", then chances are a combined data source might make more sense.
The reason is this: You can combine the data source characteristics into a common characteristic. For example, if an opportunity has a “Month/Calendar Year” to identify when it was expected to end, and a sales order had a “Month/Calendar Year” to identify when it was opened, then I can combine them together into a single characteristic for my reports that will show either opportunity end date or the sales order start date.
Here’s an example. Note that this isn’t really something you’d use for real report, but it serves to demonstrate a point.
You create a combined data source in a similar manner to a joined data source – you go to Business Analytics -> Design Data Sources -> New ->Combined Data Source
However, with a combined data source, I can specifying that I want to group the data from the two sources as long as the types of characteristics are the same, even though the actual values themselves do not have to be identical between both data sources.
For example, in the above, if specify that the type of characteristic “Month/Calendar Year” is combined for both opportunities and sales order – this means that while the types of values I see for this characteristic are the same, they actually refer to two completely different things. For example, for “Month/Calendar Year” if a row is an opportunity, this would refer to the expected end date of that opportunity, but if a row is a sales order, it refers to the entry date, even though I would see both in the same column.
As I can see, within January 2010, I only see opportunity expected value. However, within December 2010, I see sales order net value alongside opportunity expected value.
Another example would be to combine opportunity ID and sales order ID – this means that I can pull an “ID” type characteristic into the report, and see either the opportunity ID or sales order ID alongside each other in the same column.
Obviously, because of this, you have to be careful when creating and validating your own data sources – you may end up with unexpected results.
And remember, the guidelines within this blog are simply something to use as a reference point. You may have circumstances or requirements that result in a data source that is combined in a scenario where it may normally be joined, or vice versa. So, keep that in mind – if you can’t get one type of data source to show you the correct data, try the other one.
The best way to learn, though, is to try it out - and if you get stuck, well, you can always ask an expert!
Feel free to contact me for more information or if you need help with SAP Business ByDesign.
Read more SAP Business ByDesign articles here.
Written by: Dan Edelman, Senior SAP Business ByDesign Consultant
FMT Consultants is a leading SAP Business ByDesign partner and consulting firm serving all of the US and Canada.
- SAP Managed Tags:
12 Comments
