Post reply

Visual formatting of data returned from a SAP database

  • April 2, 2014 at 9:28 pm

    #287188

    Hi Everyone,

    I am working with extracting data from a SAP database, and I would like to perform some 'visual formatting' on the returned data. Because the SQL code that I am writing will be used in a custom Report it is not possible for me to use '3rd Party' formatting tools. Furthermore because the database is hosted on an MS SQL 2008 R2 RDBMS it is not possible to use the IIF() function.

    Here is a sample of the data that I am getting back at the moment -

    Here is how I would like the data to be returned (with the exception that the blue lines will ideally be blank cells) -

    I have come to believe that I should be using the ROW_NUMBER() function, along with the OVER() function and possibly the 'partition by' keywords.

    Here is my original code -

    select

    td.ItemCode as 'Item Code'

    , td.Dscription as 'Item Description', td.Quantity as 'Order Qty'

    , titm.OnOrder as 'PO Qty'

    , th.CardCode as 'BP Code', th.CardName as 'BP Name'

    , th.DocNum as 'Sales Order Number'

    , tsp.SlpName as 'Sales Person'

    , twhs.WhsCode as 'Whs Code'

    , isnull(tbloc.BINLABEL, '') as 'BIN Label', isnull(cast(tbloc.QUANTITY as nvarchar(20)), '') as 'BIN Qty'

    from AU.dbo.RDR1 td

    left outer join AU.dbo.ORDR th on th.DocEntry = td.DocEntry

    left outer join AU.dbo.OITM titm on titm.ItemCode = td.ItemCode

    left outer join AU.dbo.OITW twhs on twhs.ItemCode = td.ItemCode

    left outer join AU.dbo.OSLP tsp on tsp.SlpCode = td.SlpCode

    left outer join A1Warehouse.dbo.BINLOCAT tbloc on tbloc.PRODUCT = td.ItemCode collate SQL_Latin1_General_CP850_CI_AS

    where

    td.LineStatus = 'O' and td.ItemCode = 'DR1124' and twhs.WhsCode like @whCode

    order by td.ItemCode, th.DocNum

    I am now trying to incorporate the previously mentioned functions with limited success.

    Notice above that I only see the 'Seq' and no other data. How can I go about modifying this code so that I see all of the data columns I desire?

    If I further 'refine' the above, by adding case statements and the individual columns that I want to see (as opposed to using the * wildcard) I get a long list of error messages.

    I am very new to MS SQL programming so I figure that I have probably made some 'largish' errors above. If anybody can help me correct my code (the longer the explanation the better) it will be much appreciated.

    Kind Regards,

    Davo

  • April 2, 2014 at 9:47 pm

    #1702710

    David,

    This was really easy in SSRS. Here are the steps:

    1. Add a tablix to the report canvas (or whatever it's called).

    2. Add Order Qty and Bin Qty to the tablix.

    3. Add 3 Row Groups in this order:

    - Order Qty

    - Item Description

    - Item Code

    done. <smile>

  • April 2, 2014 at 10:06 pm

    #1702714

    Unfortunately I cannot use SSRS as I will be plugging the code into SAP Business One.

  • April 2, 2014 at 10:09 pm

    #1702716

    Oh, I see now... pity, because as I said, it's simple in SSRS. Did it in about 30 seconds. Normally, you do all the formatting in the front end or the reporting layer.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply