Intro
At this document is described the Product Business Crystal Report V1.3 Configuration. The configuration have 2 different parts. The User Configuration under Onedealer and the Reports configuration under the Reporting Service.
The Business reports feature implementation use dynamic generated controls functionality for each parameter of the a report. This functionality will be described at the section "Crystal Report Parameters (IDMS_CR_PARAMETER table)".
User configuration
Each report have a business case where should be configured under a related role. This role should by applied to a user group. The users where are belong to this group they can see the list of the Business Crystal Reports at ~/list/ReportsGridList.
Report BCs
Report Code | BC Name | Report File | BC |
|---|---|---|---|
| ODPRODREPORDMATBRANAL | GetReportODPRODREPORDMATBRANAL | OneDealer_OrdersMaturity_Branch_Analytical_EN_v1.3 | 30060 |
| ODPRODREPORDMATBRGROUP | GetReportODPRODREPORDMATBRGROUP | OneDealer_OrdersMaturity_Branch_Grouped_EN_v1.3 | 30061 |
| ODPRODREPORDERTIME | GetReportODPRODREPORDERTIME | OneDealer_OrderTime_EN_v1.3 | 30062 |
| ODPRODREPPERADVINFO | GetReportODPRODREPPERADVINFO | OneDealer_PerAdvisor_EN_v1.3 | 30063 |
| ODPRODREPPERADVINFOOPINQ | GetReportODPRODREPPERADVINFOOPINQ | OneDealer_PerAdvisor_Perf_Opp_Inq_EN_v1.3 | 30064 |
| ODPRODREPPERFMINFO | GetReportODPRODREPPERFMINFO | OneDealer_PerFamilyModel_EN_v1.3 | 30065 |
| ODPRODREPPERSCINFO | GetReportODPRODREPPERSCINFO | OneDealer_PerSourceChannel_EN_v1.3 | 30066 |
| ODPRODREPSALESOPPBR | GetReportODPRODREPSALESOPPBR | OneDealer_SalesOpportunities_Branch_EN_v1.3 | 30067 |
| ODPRODREPSALESORDBR | GetReportODPRODREPSALESORDBR | OneDealer_SalesOrders_Branch_EN_v1.3 | 30068 |
| ODPRODINSREPSALORDSALAREA | GetReportODPRODINSREPSALORDSALAREA | OneDealer_SalesOrders_SalesArea_EN_v1.3 | 30069 |
| ODPRODINSREPORDMATSALAREA | GetReportODPRODINSREPORDMATSALAREA | OneDealer_OrdersMaturity_SalesArea_EN_v1.3 | 30070 |
| ODPRODINSREPSALOPPSALAREA | GetReportODPRODINSREPSALOPPSALAREA | OneDealer_SalesOpportunities_SalesArea_EN_v1.3 | 30071 |
****Warning: For the Sales areas reports should be configured at Sales Areas branches. Otherwise these reports will not working at the onedealer. "Error at the report popup of a sales area report. The error is not user friendly the popoup breaks with error in console "split method error on undefined param".
-How to create a New Business case for a report.
At the new BC creation popup you will find the follow fields.
Fields | Value | Usage |
|---|---|---|
| Business Case ID: | Add a unique business case ID. Now The OD products reports BC Ids are Start from 30060 to 30071 you can continue to add ids from 30072 if are not used to keep some conformity at the IDs | |
| Name: | Most of the already stored BCs for the reports are using as Name the follow wording GetReport<The Code of the report at the CR_Report table> Example : GetReportODPRODREPSALESORDBR. Again this usage is only for the conformity of the data. There isnot any business behind of the name. | |
| Action Type: | R | Means reports. This value should always the same. |
| Method Name: | <ReportCode> | Must be the report code from the CR_Report. This is used from sp where opens to the user the report with this code in Business Report list. |
| Is Logged: | Yes | |
| Needs Authorization | Yes | Needs always the user Authorization to see the report in the list |
| Component | REPORT | Always the same |
| BC group | nothing to select | nothing to select |
| Entity Name: | IDMS_CrReport |
Reporting Service Configuration/Deployment
The Crystal Reporting Service is a web application service where can accept a request with the report file name, the parameters with values where are applied by the user and a sessionId (is applied by the system) from the side of the OneDealer application.
This request is a compressed/encrypted query string where the service can retrieve and decompress/decrypt it. After the service sends the template file name with the parameters loaded on the a specific sql script where the template using to the Sap Crsytal Report Engine to generate report on .pdf format.
Reporting Service Supports Hana coupled Hana Decoupled and MSSql schemas.
MSSql Reporting service version.
The already deployed Reporting services at the environments have version where is not supporting MSSql version. The only Reporting service where supports MSSql version rpt reports is deployed at the dev environment in 10.65.14.201 with url : https://reporting-dev.onedealer.com/.
For MSSql must add under the ~/Content/Reports the MSSql version .rpts.
-Environment pre requisites setups:
- Sap Crystal Reports 2020 Viewer SP2. (14.3.2.4073)
- Sap Crystal Reports runtime engine for Net Framework (32bit). (13.0.19.2312)
- Sap Crystal Reports runtime engine for Net Framework (64bit). (13.0.19.2312)
-Configurations
- Each Onedealer Application should have a dedicated CR reporting service where the reports files (.rpt) should be configured under the path ~/Content/Reports under iis where is deployed.
- Onedealer should have configured at the IDMS_Setup table the follow setup key "ReportingRootUrl" with the url of the service. Example "https://<domain.name of the service>/CrystalReport/Report"
- At the Web.config of the service should have 2 connection strings configured, common and company database (LocalHana/DirectCommon) the same dbs where the connected OneDealer Application is operating.
Crystal Report Parameters (IDMS_CR_PARAMETER table)
Each report have parameters these parameters are configured in IDMS_CR_PARAMETER and the columns of the table are related with the report template parameters (the .rpt file) and the control type (Dropdown/text input/datetime control etc.) where will be shown at the UI at the report popup where the user apply values to the parameters.
The follow table describes the business logic of the table and how to configure it. Some parameters are following a specific logic of configuration and have also relation with other parameters. Logic relation between the rows of the table depending specific values at the fields it will explained to other section.
Note: This table explains most of the properties.
Column Name | Values | Usage |
|---|---|---|
| IDMS_Report Code | A unique Report Code where is used to Connect the parameters with the report. | |
| IDMS_Param Name | The parameter name in the .rpt template (example: PSourceName). The specs for the parameters you will find in the .txt of file with the same name of the report. | |
| IDMS_Param Type | The parameter type (string,intenger,datetime) in .rpt template. The specs for the parameters you will find in the .txt of file with the same name of the report. | |
| IDMS_Is_Static | 0 or 1 | If is 0 means the parameter(by the business) needs user input else the value is always the same and is configured at the column IDMS_Default Value. |
| IDMS_Default Value. | The default value. If IDMS_Is_Static is 1 there isn't user input so the value at the parameter at the report is always the default value. Make sure about the value type to apply it with the correct format as string or datetime etc. | |
| IDMS_Resource Code | The Resource Code. The translation at the Label of the control in the popup. | |
| IDMS_Entity | At this field depending the Control type we apply a value like, from where the options of the dropdown will be field. Example : Value : IDMS_INQSOURCEVIEW at the parameter PSourceName means that the options of the dropdown at the UI control will have values from this View. This field also is relates with the follow fields: IDMS_Entity Field, IDMS_UI Type, IDMS_Is Common Entity, IDMS_Values Query, IDMS_Lookup Columns these altogether are describe to the implementation how will build the control at the UI. | |
| IDMS_Entity Field | The property name at the entity. | |
| IDMS_Is Common Entity | 0 or 1 | If the entity is in common or not. 1 means is in common. |
| IDMS_Param Description | The Text at the label at the UI control. | |
| IDMS_UI Type | DropDown/DatePicker/LookUp/Numeric/RelatedTo/MultipleLookUp/MultipleLookUpDistinct/Switch | The Control Type of the UI Control. The relatesTo value refers always to another configured parameter. (Logic relation between the rows of the table). |
| IDMS_Values Query | The value could be entity prop name or a date format or an sql query or a property name. The is depending on the implementation of the parameter and the control type. | |
| IDMS_Lookup Columns | The property names at of the view model where will be created to bind the data at the control. | |
U_IDMS_Required | 0 or 1 | If the field is a mandatory user input the value is 1 and at the UI the control is mandatory. |
****Warning: For the parameters PSourceCode, PChannelCode are used the IDMS_INQCHANNELVIEW, IDMS_INQSOURCEVIEW this views are using a query where returns data from Idms_Inq_Source or from Idms_InquiryChannel where the U__IDMS_Installation Code should have value "KOSMOCAR". If this configuration will not done then the options at UI of the Source and Channel will be empty.
Crystal Reports (IDMS_CR_Report table)
At the IDMS_CR_Report table are configured the .rpt templates. The follow table describes the business logic of the table and how to configure it.
Column Name | Values | Usage |
|---|---|---|
| Code | Example: Value for Prod Reports V1.3 "ODPRODINSREPSALORDSALAREA" | A unique Report Code where is used to Connect the parameters with the report. |
| Name | Example: Sales Orders Sales Area V1.3 | A unique Name of the report |
| IDMS_Report File | Example: OneDealer_SalesOrders_SalesArea_EN_v1.3 | The Original report file name without the format. The report file name under the reporting service. |
| IDMS_Desrciption | Example: Sales Orders Sales Area V1.3 | A description of the business report. |
| IDMS_Resource Code | Exmpale: OD0005868 | The translation of the name of the report at the list of the Business Reports in onedealer. |
| IDMS_Is Document Report | 0 | 0 value Business Reports .rpts 1 for document .rpts where are used at the workflows of the onedealer "offer document, order document". |
| IDMS_TeamType | 1 | Team type should have always value 1 |
Parameters Implementation Examples
At this section you will find examples of params configuration. Where are the most common in use and have some complicated configuration depending the implementation of the them.
-LookUp UI type Control. (List options at UI with single selection)
U_IDMS_Report Code | U_IDMS_Param Name | U_IDMS_Param Type | U_IDMS_Is Static | U_IDMS_Default Value | U_IDMS_Entity | U_IDMS_Entity Field | U_IDMS_Is Common Entity | U_IDMS_UI Type | U_IDMS_Values Query | U_IDMS_Lookup Columns | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| REPORDER | PSourceCode | Integer | 0 | IDMS_INQSOURCEVIEW | Code | 0 | LookUp | |||||
| REPORDER | PSourceName | String | 0 | IDMS_INQSOURCEVIEW | Name | 0 | RelatedTo | PSourceCode | Name,Code |
Explanation:
The report template with code "REPORDER" have parameters PSourceName and PSourceCode where PSourceCode is integer type in template and PSourceName is string. The Control at the UI will be a Lookup and the data options will be from the IDMS_INQSOURCEVIEW. The view model object will have props Name, Code. The View prop names are Code and Name where will be applied at the viewModel. A Lookup UI type param always have a related parameter where in our case now is the PSourceName and with UI type value RelatedTo and U_IDMS_Values Query field with value the Param Name = PSourceCode.
-MultipleLookUp UI type Control.
(List options at UI with multi selection functionality with checkboxes)
U_IDMS_Report Code | U_IDMS_Param Name | U_IDMS_Param Type | U_IDMS_Is Static | U_IDMS_Default Value | U_IDMS_Entity | U_IDMS_Entity Field | U_IDMS_Is Common Entity | U_IDMS_UI Type | U_IDMS_Values Query | U_IDMS_Lookup Columns | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ODPRODREPORDMATBRGROUP | PListOfMakes | Integer | 0 | IDMS_MakeView | Code | 0 | MultipleLookUp | Name,Code | ||||
| ODPRODREPORDMATBRGROUP | PListOfMakeNames | String | 0 | IDMS_MakeView | Name | 0 | RelatedTo | PListOfMakes | Name,Code |
Explanation:
The report template with code "INSREPPERSCINFO" have parameters PListOfMakes and PListOfMakeNames where PListOfMakes is integer type in template and PListOfMakeNames is string. The Control at the UI will be a MultipleLookUp and the data options will be from the IDMS_MakeView. The view model object will have props Name, Code. The View prop names are Code and Name where will be applied at the viewModel. A MultipleLookUp UI type param always have a related parameter where in our case now is the PListOfMakeNames and with UI type value RelatedTo and U_IDMS_Values Query field with value the Param Name = PListOfMakes. The final result of the selection will be an object array with string values as the spec in the .txt file of the report template file.
PListOfMakes = ['1','2', 3']
PListOfMakesName = ['BMW','VW', AUDI']
-DropDown UI type Control.
(List options at UI with single selection)
U_IDMS_Report Code | U_IDMS_Param Name | U_IDMS_Param Type | U_IDMS_Is Static | U_IDMS_Default Value | U_IDMS_Entity | U_IDMS_Entity Field | U_IDMS_Is Common Entity | U_IDMS_UI Type | U_IDMS_Values Query | U_IDMS_Lookup Columns | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| INSREPPERSCINFO | POpportunityTypeCode | String | 0 | 0 | DropDown | select 9999 as "POpportunityTypeCode",'All' as "OpportunityType" from dummy union all select 0,'Retail sale' from dummy union all select 1,'Customers from Service' from dummy union all select 2,'Lease' from dummy union all select 3,'Small Fleet' from dummy union all select 4,'Fleet' from dummy; | ||||||
| INSREPPERSCINFO | POpportunityTypeName | String | 0 | 0 | RelatedTo | POpportunityTypeCode | Text,Value |
Explanation:
The report template with code "INSREPPERSCINFO" have parameters POpportunityTypeCode and POpportunityTypeName where POpportunityTypeCode is string type in template and POpportunityTypeName is string. The Control at the UI will be a static DropDown and the data options will be from the "Values Query" . The view model object will have props Text, Value. A DropDown UI type param always have a related parameter where in our case now is the POpportunityTypeCode and with UI type value RelatedTo and U_IDMS_Values Query field with value the Param Name = POpportunityTypeCode. The final result of the selection will be an object array with string values as the spec in the .txt file of the report template file.
-LookUp UI type Control.
(List options at UI with single selection)
Specific case for reports for Sales Areas.
U_IDMS_Report Code | U_IDMS_Param Name | U_IDMS_Param Type | U_IDMS_Is Static | U_IDMS_Default Value | U_IDMS_Entity | U_IDMS_Entity Field | U_IDMS_Is Common Entity | U_IDMS_UI Type | U_IDMS_Values Query | U_IDMS_Lookup Columns | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ODPRODINSREPORDMATSALAREA | PListOfRangeBranches | String | 0 | IDMS_SALESAREAMAKEDIM_VIEW+One.Core.Models.Entities.SalesAreaBranch | SalesAreaID,BranchID | 1 | LookUp | Name,SalesAreaID | ||||
| ODPRODINSREPORDMATSALAREA | PListOfRangeBranchesNames | String | 0 | One.Core.Models.Entities.Branch | Description,Code | 0 | RelatedTo | PListOfRangeBranches | SalesAreaID |
Explanation:
The report template with code "ODPRODINSREPORDMATSALAREA" have parameters PListOfRangeBranches and PListOfRangeBranchesNames where PListOfRangeBranches is string type in template and PListOfRangeBranchesNames is string. The Control at the UI will be a static LookUp and the data options will be from the "IDMS_SALESAREAMAKEDIM_VIEW+One.Core.Models.Entities.SalesAreaBranch" . The view model object will have props Name,SalesAreaID. A LookUp UI type param always have a related parameter where in our case now is the PListOfRangeBranchesNames and with UI type value RelatedTo and U_IDMS_Values Query field with value the Param Name = PListOfRangeBranches.
-LookUp UI type Control.
(List options at UI with multi selection functionality with checkboxes)
U_IDMS_Report Code | U_IDMS_Param Name | U_IDMS_Param Type | U_IDMS_Is Static | U_IDMS_Default Value | U_IDMS_Entity | U_IDMS_Entity Field | U_IDMS_Is Common Entity | U_IDMS_UI Type | U_IDMS_Values Query | U_IDMS_Lookup Columns | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ODPRODREPPERADVINFOOPINQ | PListOfRangeBranches | String | 0 | IDMS_DIMBRANCH_VIEW | BranchCode | 1 | MultipleLookUp | BranchDescription,BranchCode | ||||
| ODPRODREPPERADVINFOOPINQ | PListOfRangeBranchesNames | String | 0 | IDMS_DIMBRANCH_VIEW | BranchCode | 1 | RelatedTo | PListOfRangeBranches | BranchDescription,BranchCode |
Explanation:
The report template with code "ODPRODREPPERADVINFOOPINQ" have parameters PListOfRangeBranches and PListOfRangeBranchesNames where PListOfRangeBranches is string type in template and PListOfRangeBranchesNames is string. The Control at the UI will be a static MultipleLookUp and the data options will be from the "IDMS_DIMBRANCH_VIEW" . The view model object will have props BranchDescription,BranchCode. A MultipleLookUp UI type param always have a related parameter where in our case now is the PListOfRangeBranchesNames and with UI type value RelatedTo and U_IDMS_Values Query field with value the Param Name = PListOfRangeBranches. The final result of the selection will be an object array with string values as the spec in the .txt file of the report template file.
Note: Most of the Parameters of the cr reports are implemented at the RCGENDEV so for new Report Configuration just copy any existing Parameter in CR_Parameter table and apply the related values at the new copied parameter to connect it with the new report. For new parameters where are not implemented ever in the onedealer and the specs of them are not maching with existing implemented parameters may you need Development Task.
Deployment Process
-New Environment (Hana/Coupled/MSSQL)
Deploy crystal reporting service at env. Deployment steps are described at the "Reporting Service Configuration/Deployment" section at this page
Sync data from RCGENDEV the follow tables
- IDMS_CR_Reports
- IDMS_CR_Parameters
- Resources from common
- Business Cases from common
Add the url of the service at the setup key "ReportingRootUrl"
Apply the related configuration for the Users. See: "User configuration" section.
-Existing Environment (Hana/Coupled/MSSQL)
All the the reports V1.3 are configured with Codes where are starting with ODPROD.
So make sure that these reports are not existing at the Db of the environment. If not exists the Sync data from the tables :
- IDMS_CR_Reports
- IDMS_CR_Parameters
- Resources from common
- Business Cases from common
If the reports exists then the remaining process is to check if:
- The url of the service at the setup key "ReportingRootUrl"
- The related user configuration. See: "User configuration" section.
0 Comments