Visual Composer (VC) is the greatest achievement of SAP for SAP NetWeaver technology, especially when you have to build a lot of reports based on data sources that were developed with another high levels business components. We know how it is profit to get the any analytic report by VC, how it is fast to implement the excel export, insert any chart area and speed up the developing the necessary for our clients reports, but sometime we have situation which surprised us, because obviously it must be working but we can't achieve that in moment. Yes, it presents in any technology, especially for newest modern macro languages when we just in start of using that.
The aim of that article is to show step by step how to implement range data filter into our report project created by Visual Composer. It will be shown by practical example and we have to achieve the next result:
Assume that we have to create report about business activity which is based on the data source BPM_BUSINESS_LOG_DS. You can find description about this data source on sap.help.com For more information, see Business Log DataSource. What we are going to do with that DS to create our report:
1) At first step we need to create our project in Visual Composer and then create there composite view which can combine all elements that is necessary for our report. I named this vie as "LukCAD_TEST" (Picture 1) and put there only the four components:
- Start. To provide start point of report creation (via our filter form).
- Form view. To provide filter form where we put all elements for filtering and button submit. The name of that form is "Make your choice". The configuration of that form you can see on the Picture 2.
- Data Source. It is source of our data with Filter input which is connected to our form view and with DATA output which is connected to Table view. We included in our design the data source which has been created by BPM_BISINESS_LOG_DS object previously. The configuration of DS is on the picture 3.
- Table view. This component provides the output of our report into web page where our user can look at grid of data from our datasource according to our filter and accourding to number of coulumnd which has been selected for data representation. The main fonfiguration of that view is presented on the picture 4.
Picture 1. The full design of our report for our composite view "LukCAD_TEST".
Picture 2. The fragment of our Form View design with configuration properties.
Picture 3. The fragment where our data source with configuration is presented.
Picture 4. The fragment where Table View with configuration data is presented.
2) We have to create the necessary fields in layout of our form view to be able use it for our filter process.We switch our compose working area from design to layout where we create:
- datetime picker to select date "From" and put it in variable @DATETIME1. See picture 5.
- datetime picker to select date "To" and put it in variable @DATETIME2. See picture 6.
- button to make filtering with name "Find" and with action "SUBMIT" to provide submitting variables to Filter datasource. See picture 7.
Note: I use datetime piker to be ensured that the DSRT will convert correctly my selected date in expression which is used for mapping data.
Picture 5. The layout with selected datetime picker "From" and the configuration parameters of it. Variable @DATETIME1 will be responsible for selected date "From".
Picture 6. The layout with selected datetime picker "To" and the configuration parameters of it. Variable @DATETIME2 will be responsible for selected date "To".
Picture 7. The layout with selected button "Find" and the configuration parameters of it. The action "SUBMIT" is important here.
3) On third step you have to decide which filtering variable of your data source is suitable for filtering.
According to documentation from sap.help.com you are able to use field with type "DATE" if you wish to implement range filtering.
In this example we use the data source where only one variable can be used and it is "OCCURENCE_D". The example of usage is the next:
OCCURRENCE_D = 3/23/10 - 3/24/10 - this range will filter data from data source which has values BETWEEN 3/23/20 and 3/24/10.
4) On this step you have to open Map Data form to be able to create the function which is necessary for mapping values between of our variables from filtering form view and filter input variables.
To start the map process you have to select highlighted submit line which is connect the "OUT"of our form "Make your choice" and "FILTER" of our data source, and after selection that line you have to open context menu where you will be able to select "Map Data..." as following picture shows it:
Picture 8. Way to open the Map Data form to be able start adjust it.
5) The step where you need to make mapping and enter formula to provide correct data transformation for data range filtering.
This is important step, because it is main aim of my blog to show where problem whith mapping data range selection. The main thing is here is correct formula. You have to write it near to required target field (in our case it is OCCURRENCE_D) into Assigned value. Look at the picture 9. I will write that formula and give you some explanation about each element of it. It is simple but indeed it is not obviously for first time:
= DSTR(@DATETIME1,"DD/MM/YY")&" - "&DSTR(@DATETIME2,"DD/MM/YY")
where:
@DATETIME1 is the value of date "From" (Lower range) which goes from Form View.
@DATETIME2 is also value from Form View but about date "To" (Upper range).
DSTR - is function transformation of datetime to string format according to pattern "DD/MM/YY" (Pattern depends of format in your data source).
& - it is operator of concantenation two strings.
This formula converts the datetime format which have been entered by datetime pickers (these controls must be used) into the string format. For example if we select in datetime picker "From" "02/01/2013" and in datetime picker "To" "23/01/2013" the formula will calculate (after submitting) the string which will be next one: "02/01/13 - 23/01/13" and that value will be entered like filter for our data source.
Picture 9. The mapping area where for target field we have to put assigned value. In this moment the OCCURENCE_D is mapped to expression which return the rande of date according to requirements for this filtering field.
6) In the end you have to adjust your Table View (in our example it is "Table1") where you need to select the appropriate fields which you wish to sow by your grid.
To do it you have to select Table View object and open context menu where you can press "Define Data" as picture 10 shows it.
Then you have to mark the appropriate fields which you wish to be shown by grid. Look at the picture 11 to image how.
Picture 10. The way to open form "Define data" where you will be able to select which fields you want for your table view.
Picture 11. The "Define data" form in moment when you make choice which fields will be shown (in this case these where you have placed marks) and which won't.
7) Finally you have to save your project and deploy it. I deployed on SAP Portal to demonstrate it.
The next picture is confirmation of previously done steps that your search filtering is reality and you can start use it (but be very accurate with types and formats):
Picture 12. Final test. We selected the range From "01/01/2013" up to "11/01/2013" and in result we got data between that values as you can see.
That's it.
Thank you for your attention and i hope that is help.
Sincerely, LukCAD.
- SAP Managed Tags:
2 Comments
