Expand all | Collapse all

Confirm Report formating

  • 1.  Confirm Report formating

    Posted 01-22-2020 20:52
    Is it possible to place the images horizontally rather vertically in the report? its a defect list with multiple images and want to show them horizontally in the report. attached is the design and report view screenshot


  • 2.  RE: Confirm Report formating

    Posted 01-23-2020 19:35
    Hi Ash, depending on how you've written your SQL yes it is. Can I have a look at the code?

    Robyn Dilnot
    Bathurst Regional Council

  • 3.  RE: Confirm Report formating

    Posted 01-23-2020 20:44
      |   view attached
    Hi Robyn

    Hope you are doing well, and thanks for replying. In my query i have the defect images in one column as a line item and i am after a format function with in the report that might place the images horizontally instead of vertically. the SQL is attached.

    Ashraful Sadeque
    City Of Sydney


    SQL.txt   1K 1 version

  • 4.  RE: Confirm Report formating

    Posted 01-23-2020 22:34
    Edited by Robyn Dilnot 01-23-2020 22:36
      |   view attached
    OK, so the way your SQL is written, no you can't get the defect images to display horizontally, or at least not easily. If you change your sql to subselect for the images and have each image in a separate column on the same row of the report, then it's really easy to have them display horizontally.

    The downside of using a subselect to pick up the images is that you would have to limit yourself to selecting  a specific number of images.

    If you decide to so it, It's a very easy change to your sql. Your case statement would simply be repeated a few times and contain the sub select to get each separate document link. I've attached some new sql so you can see how.

    Some of your rows were being duplicated as well and I couldn't work out what you'd missed so I just re-worked the from and where clauses and it worked itself out. You also didn't need to join all the way to feature group. Feature type has the feature group code in it.

    I wrote the attached sql to work on my confirm db so you need to change the last two lines back to 'N' and 'STAI' to make it ready to go on yours.

    Hope this helps.

    Robyn Dilnot
    Bathurst Regional Council


    sql for Ash.txt   7K 1 version

  • 5.  RE: Confirm Report formating

    Posted 01-24-2020 00:21
    Thanks for your help, really appreciate that. I have managed to place the photos horizontally by querying out the photos in seperate columns, but I really wanted to show all photos though. I am searching for a data window expression function that will allow me to do this, didn't find anything yet, not sure is there one or not?

    Ashraful Sadeque
    City Of Sydney

  • 6.  RE: Confirm Report formating

    Posted 02-20-2020 10:34
    Hi Ashraful,

    There isn't a way of doing this using Infomaker expressions and with each image filepath contained within separate rows. Robyn's approach is the way to go, but will involve you having to create a bitmap expression for each of the images.

    The other issue you'll encounter is that the number of images each defect could have attached isn't consistant, and the only way around this is by using the Case statement provided in Robyn's SQL or by using a SQL pivot - example below:

       ,action_officer.officer_name as defect_action_officer_name
       ,[1] as defect_image_1
       ,[2] as defect_image_2
       ,[3] as defect_image_3
       ,[4] as defect_image_4
       ,[5] as defect_image_5
       ,[6] as defect_image_6
       ,[7] as defect_image_7
       ,[8] as defect_image_8
       ,[9] as defect_image_9
       ,[10] as defect_image_10
             ,rank() over (partition by defect_number order by document_number) document_no
    	      WHEN ss_store.setting_value = 'F' AND LEFT(document_link.document_name,10) = '$Document$' THEN 
    			      REPLACE ( document_link.document_name, '$Document$' , ss_path.setting_value + CONVERT(NVARCHAR(4), DATEPART(YYYY, document_link.document_date), 2)  + '\' +
    			      RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(MM, document_link.document_date) ), 2 ) + '\'  )
    	      WHEN ss_store.setting_value = 'F' THEN
    			      REPLACE(document_link.document_name, '$Document Store$' , ss_path.setting_value + '\' )
    	      ELSE document_link.document_name
             END as Defect_Image
             LEFT JOIN confirm.document_link on 
                defect.defect_number=document_link.entity_key and
             JOIN system_setting ss_path ON
                ss_path.setting_id = 'DOCUMENT_LINK_PATH'
             JOIN system_setting ss_store ON
                ss_store.setting_id = 'DOCUMENT_STORE'
       ) unpvt
       PIVOT (MAX(defect_image) FOR document_no IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt,
       pvt.defect_number = defect.defect_number AND
       feature.site_code = defect.site_code AND
       feature.plot_number = defect.plot_number AND
       defect_type.defect_type_code=defect.defect_type AND
       feature_type.feature_type_code=feature.feature_type_code AND
       action_officer.officer_code=defect.officer_code AND
       feature_type.feature_group_code='FOOT' AND

    In the example, you can see that I've allowed for up to 10 images only.

    I'd suggest raising this as an idea on the portal for product enhancement.

    Kind regards,

    Steve Bish
    Senior Software Support Analyst
    Pitney Bowes Software Inc.

  • 7.  RE: Confirm Report formating

    Posted 02-26-2020 21:51
    Thanks Steve. Really appreciate your help.