Grid Setting 2005

[Former Member]

When I attach a DB-column through Grid ( I query the database and make a datatable out of the result set to show in the Grid), the data as shown in the Grid is not in the same format as defined in the standard SBO settings. For example a field of type float if stores a data 48.000000 and my SBO settings are “only 2 digits after decimal” , then the grid shall show this value as 48.00 but this does not happen.

This was not the problem if I use matrix bounded to a DB DataSource because the DB datasource used to take care of that.

Am I missing some Grid setting?

[Former Member · Accepted solution]

This might be a good opportunity to share a function I have created that re-applies the grid layout to the grid after binding it to a DataTable. The DataTable overrides all the columns in the grid so you lose all your formatting, but you can re-apply the formatting of the grid after the DataTable is bound, and it will honor the settings of the last format settings that were applied. This function will re-read the grid XML from an SRF file and re-apply the columns to the grid control with LoadBatchActions.

   Public Shared Sub ReapplyGridLayout(ByVal srfFileName As String, ByVal formUid As String, ByVal gridUid As String)
      Dim xmlFormatString As New System.IO.StringWriter
      Dim xmlFmt As New XmlTextWriter(xmlFormatString)
      Dim strIn As System.IO.TextReader = New System.IO.StreamReader(srfFileName)
      Dim xmlIn As New XmlTextReader(strIn)

      Try
         xmlIn.ReadStartElement("Application")
         xmlFmt.WriteStartElement("Application")
         xmlIn.ReadStartElement("forms")
         xmlFmt.WriteStartElement("forms")
         xmlIn.ReadStartElement("action")
         xmlFmt.WriteStartElement("action")
         xmlFmt.WriteAttributeString("type", "update")
         xmlIn.ReadStartElement("form")
         xmlFmt.WriteStartElement("form")
         xmlFmt.WriteAttributeString("uid", formUid)
         Do
            xmlIn.Read()
         Loop Until xmlIn.LocalName = "items"
         xmlIn.ReadStartElement("items")
         xmlFmt.WriteStartElement("items")
         xmlIn.ReadStartElement("action")
         xmlFmt.WriteStartElement("action")
         xmlFmt.WriteAttributeString("type", "update")
         Do While xmlIn.Read()
            If xmlIn.IsStartElement() AndAlso String.Compare(xmlIn.LocalName, "item", True) = 0 Then
               If String.Compare(xmlIn.GetAttribute("uid"), gridUid, True) = 0 Then Exit Do Else xmlIn.Skip()
            Else
               xmlIn.Skip()
            End If
         Loop
         xmlFmt.WriteNode(xmlIn, False)
         xmlFmt.Close() ' Automatically closes underlying stream
         B1Application.LoadBatchActions(xmlFormatString.ToString())
         strIn.Close()
         xmlIn.Close()
         strIn = New System.IO.StringReader(B1Application.GetLastBatchResults())
         xmlIn = New XmlTextReader(strIn)
         Dim errs As New System.Text.StringBuilder
         While xmlIn.Read()
            If errs.Length > 0 Then errs.Append("; ")
            If xmlIn.IsStartElement("error") Then
               errs.Append(xmlIn.GetAttribute("descr"))
            End If
         End While
         If errs.Length > 0 Then
            B1Application.StatusBar.SetText(errs.ToString())
         Else
            ' Must manually apply DisplayType because XML doesn't seem to support it
            For Each gc As SAPbouiCOM.GridColumn In DirectCast(B1Application.Forms.Item(formUid).Items.Item(gridUid).Specific, SAPbouiCOM.Grid).Columns
               If gc.Type = SAPbouiCOM.BoGridColumnType.gct_ComboBox Then
                  DirectCast(gc, SAPbouiCOM.ComboBoxColumn).DisplayType = SAPbouiCOM.BoComboDisplayType.cdt_Description
               End If
            Next
         End If
      Finally
         strIn.Close()
         xmlIn.Close()
         xmlFmt.Close() ' Repeated calls to Close (like Dispose) do not fail
      End Try
   End Sub

[Former Member · Accepted solution]

Hi Ben,

Thanx for your reply.

Is there a way to reapply column settings without the xml. My columns are being generated dynamically on run time.

oForm.DataSources.DataTables.Add("InvoiceRpt")

oForm.DataSources.DataTables.Item(0).ExecuteQuery(SQL)

oDataTable = oForm.DataSources.DataTables.Item("InvoiceRpt")

oGrid.DataTable = oForm.DataSources.DataTables.Item("InvoiceRpt")

Manu

[Former Member · Accepted solution]

I just realized that neither the grid nor the datatable seems to have the ability to specify a column as a "Price" for example. You can only specify Float for the datatable and EditText for the grid column. So this probably doesn't apply to your problem. Some column settings can be re-applied after data is bound, but unfortunately the decimal precision is not one of these... there is no setting for it.

[Former Member · Accepted solution]

Hi,

But isn't this really strange i mean how do i use a grid when my SBO settings cannot be taken care of. what i thought was a Grid would be an extension of the Matrix control but it does not cater to the minimal settings Matrix had.

May be SAP shall look into this and the moderators please convey this........im in really bad shape

Manu

[Former Member · Accepted solution]

That's a good point -- we are probably going to run into the same problem because many of our matricies use prices. The only reason I hadn't noticed the problem yet is because I only upgraded 1 of our many matrix objects, and it didn't happen to have a price. This will be a serious problem for us too.

[Former Member · Accepted solution]

Hi,

the grid column takes the column type of the table itself.

else you can also define the column datatype like datatable.column.add("")

Amudha

[Former Member · Accepted solution]

The problem is a DataTable does not support all the necessary types. It does not support a "Price" type, so there is no way to show the correct number of decimal places on numeric columns that represent a price.

[Former Member]

Hi Manu,

You can work out column format with SQL itself. For example, following query

SELECT ItemCode, ItemName, OnHand FROM OITM

will result grid column with following format.

ITEM CODE ITEM NAME OnHand

-

---

-

---

-

---

A100-100 Test 87.0000

A100-200 Sample 67.2500

Where as if you do formating using these CONVERT like following query,

SELECT ItemCode, ItemName, CONVERT(decimal(10,2), OnHand) AS FormaatedQty FROM OITM"

will result you GRID you exepect.

ITEM CODE ITEM NAME OnHand

-

---

-

---

-

---

A100-100 Test 87.00

A100-200 Sample 67.25

HTH

With Regards

B.Ravi Shankar

[Former Member]

This doesn't work. It only affects the text output of the query. The numeric values are still shown with 6 decimal places in the grid. To demonstrate this, I tried the following query in Screen Painter and previewed the results in a grid:

select AcctName, convert(decimal(15,2), CurrTotal) as FormattedTotal from OACT

To fix the formatting issue, I could cast it to a string like this:

select AcctName, convert(nvarchar(18),convert(decimal(15,2), CurrTotal)) as FormattedTotal from OACT

But that's not what I want either. I want to allow the column to be handled as a number and I want B1 to perform it's normal formatting (if the user changes the value) and validation without having to write all kinds of extra code.

[Former Member]

Hi Ben Marty,

I used following function and it worked with me. I tested it in SAPB1 2005A SP:00 PL:2. Since SP00 Screen Painter does not have GRID feature, I tried it by code. I also checked it with GRID validation and it also working fine.

Private Sub LoadFormWithGridWithFormattedColumn()

Dim oForm As SAPbouiCOM.Form

Dim oGrid As SAPbouiCOM.Grid

Dim odtItem As SAPbouiCOM.DataTable

oForm = oApplication.Forms.Add("MyFrm", SAPbouiCOM.BoFormTypes.ft_Sizable)

oForm.Left = 300

oForm.Top = 200

oForm.Width = 500

oForm.Height = 350

oForm.Visible = True

odtItem = oForm.DataSources.DataTables.Add("ITM")

odtItem.ExecuteQuery("SELECT ItemCode, ItemName, OnHand, CONVERT(decimal(10,2), OnHand) AS FormaatedQty FROM OITM")

With oForm.Items.Add("myGrid", SAPbouiCOM.BoFormItemTypes.it_GRID)

.Top = 5

.Width = 5

.Height = oForm.Height - 10

.Width = oForm.Width - 10

oGrid = .Specific()

End With

oGrid.DataTable = odtItem

End Sub

With Regards

B.Ravi Shankar

[Former Member]

I copied and pasted the code in VB6 (with a couple minor modifications -- just adding "Set" -- because I guess it was written in VB .NET) and I still see the same behavior. The formatted item quantity column is showing 6 decimal places instead of 2. This behavior must be new in SP01. I am using SP:01 PL:03.

The strange thing is the un-formatted "OnHand" column shows 3 decimal places and the formatted one shows 6! I can't make the formatted column show 3 decimal places no matter what I do.

[Former Member]

Hi Ben,

I've noticed some strange behavior with layouts also in SP:01. They're also with 6 decimals. I thought this issue was going to be solver in PL04, but it's not. Maybe the grid issue is related to this. I really don't know.

Maybe you can make a post to SAP and tell us about it.

Regards,

Ian

[Former Member]

SAP has created Development Request number 0120031469 0001976547 2006 from info I submitted to them in order to try resolving this problem in the future.

[Former Member]

Hi All,

This issue has been solved in SP01 PL12.

Regards,

Ian

[Former Member]

I see that note 980519 describes this problem and the fix. But this hard-wires the precision to the "Amount" precision. What if I want to display a price or a quantity?

[Former Member]

Hi Manu,

Which version of SBO are you using?

Ian

[Former Member]

Hi Ian

I am using SBO 2005 PL: 02 SP: 01

Any help would be appreciated.

Manu