Author Topic: Show an image as derived from a database (url shortcut)  (Read 6952 times)

Offline collierd

  • Full Member
  • ***
  • Join Date: Aug 2008
  • Posts: 15
  • Forum Citizenship: +0/-0
Hello

We are storing URL's to images in a SQL database (datatype - varchar(100))
How do I use these on a report?

I've tried a few things

If I just drop and image and change report expression to the URL surrounded by quotes it works
e.g. '..samples/images/image.gif'

Yet, if I create a query with the correct column in and call it image
Then try and use this as a report expression, it fails (doesn't run)
e.g. [Query1].[image]


I've also tried ('[Query1].[image]')
This runs but the image is blank


Am I missing something obvious?

Thanks for any help

Damian.

Offline josepherwin

  • Community Leader
  • *****
  • Join Date: Jul 2008
  • Posts: 95
  • Forum Citizenship: +2/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #1 on: 07 Sep 2009 06:45:43 pm »
OK, there are 2 types of images. one is for Icon (header, footers, banners, etc) which is basically for displaying purposes, but there is also another one for reporting purposes (e.g. associated an image with a record in a database)

depending on the type of image, there is 2 different ways of making it to work.

The one that you've discovered below will basically work if you make the image as an icon.

However, if you want to display it in a list, you will need to:
1. Create a list table
2. From the Toolbox tab in insertable Object, drag Image to the list
3. Next in the property of the Image, you will then need to change the Source Type to be Data Item Value
 
DataStage, Informatica 7, Informatica 8.3, Cognos 7.4, Cognos 8.1, Cognos 8.3, Cognos 8.4, Oracle 9i, Oracle 10g, Sybase IQ

Offline david.stachon

  • Community Leader
  • *****
  • Join Date: Dec 2008
  • Posts: 196
  • Forum Citizenship: +9/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #2 on: 08 Sep 2009 12:46:30 pm »
...you are actually missing something easy (your approach is sound)

...if you want to use that column as a report expression, then the column needs to part of your list properties. (I assume you're getting a "variable not found error)

select the list > properties > check box on the column that has the image url

...josepherwin's approach works as well....and it's likely the way I would do it too.

Offline blom0344

  • Teamleader data processing
  • Global Moderator
  • Statesman
  • *****
  • Join Date: Feb 2007
  • Posts: 2,410
  • Forum Citizenship: +103/-2
  • have to let the first nerd go..
    • MRDM
Re: Show an image as derived from a database (url shortcut)
« Reply #3 on: 06 Jan 2010 02:01:56 pm »
OK, there are 2 types of images. one is for Icon (header, footers, banners, etc) which is basically for displaying purposes, but there is also another one for reporting purposes (e.g. associated an image with a record in a database)

depending on the type of image, there is 2 different ways of making it to work.

The one that you've discovered below will basically work if you make the image as an icon.

However, if you want to display it in a list, you will need to:
1. Create a list table
2. From the Toolbox tab in insertable Object, drag Image to the list
3. Next in the property of the Image, you will then need to change the Source Type to be Data Item Value
 


A bit of a late reponse, but we have been dabbling with these issues too and for reference-sake I'll add the following:

The requirement was to have a certain image based on an ID belonging to a property and add the image to a pdf output>

html-item:

Code: [Select]
'<iframe src="http://someserver/somefolder/ImageHandler.ashx?id= '+ [Id1]  +' &size=SMALL" frameborder="0" scrolling="NO" width="100%" height="100%">You need a Frames Capable browser to view this content.</iframe>'

works for HTML output only.

image in list:

Code: [Select]
'http://someserver/somefolder/ImageHandler.ashx?id= '+ [Id1]  +' &size=SMALL'

Both expressions stored in a data-item in the query . The last approach , however also seems to work for PDF output
Oracle9i/11g,DB2/AS400/Busobj 6.5/Powercenter7/Cognos8/10 /
SSIS 2005 / SQL SERVER 2008 /SDDM 3.3   /   Pentaho 4.4.0 Data Integrator / SSAS 2008 / PostgreSQL 9

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,357
  • Forum Citizenship: +624/-10
  • Cognos Software Muppet
Re: Show an image as derived from a database (url shortcut)
« Reply #4 on: 07 Jan 2010 03:39:40 am »
If you just want to be able to drag the query item for your image URL into your report and have it display as an image, you can define this in Framework Manager - change the "Display Type" property of the query item from "Value" to "Picture" and republish the package.

MF.
Meep!

Offline blom0344

  • Teamleader data processing
  • Global Moderator
  • Statesman
  • *****
  • Join Date: Feb 2007
  • Posts: 2,410
  • Forum Citizenship: +103/-2
  • have to let the first nerd go..
    • MRDM
Re: Show an image as derived from a database (url shortcut)
« Reply #5 on: 09 Jan 2010 03:00:34 pm »
If you just want to be able to drag the query item for your image URL into your report and have it display as an image, you can define this in Framework Manager - change the "Display Type" property of the query item from "Value" to "Picture" and republish the package.

MF.

I'll test your suggestion. The actual URL is dynamic , in my case depending on the [Id1] value. Do you by chance have an idea if we can scale images within report studio?
Oracle9i/11g,DB2/AS400/Busobj 6.5/Powercenter7/Cognos8/10 /
SSIS 2005 / SQL SERVER 2008 /SDDM 3.3   /   Pentaho 4.4.0 Data Integrator / SSAS 2008 / PostgreSQL 9

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,357
  • Forum Citizenship: +624/-10
  • Cognos Software Muppet
Re: Show an image as derived from a database (url shortcut)
« Reply #6 on: 11 Jan 2010 04:03:54 am »
I'll test your suggestion. The actual URL is dynamic , in my case depending on the [Id1] value. Do you by chance have an idea if we can scale images within report studio?

Yes - just use the Size & Overflow property to define the absolute size of the image in the report.  If the image object is (for example) within a list column, you will need to unlock the structure to be able to select it for sizing - just sizing the list column itself will not give desired results in my experience.

Cheers!

MF.
Meep!

Offline blom0344

  • Teamleader data processing
  • Global Moderator
  • Statesman
  • *****
  • Join Date: Feb 2007
  • Posts: 2,410
  • Forum Citizenship: +103/-2
  • have to let the first nerd go..
    • MRDM
Re: Show an image as derived from a database (url shortcut)
« Reply #7 on: 11 Jan 2010 05:09:57 am »
Your suggestion seems to work, but only after setting the 'visible' property to an explicite 'yes'. That was the missing link, I kept getting empty lists without that setting (very weird..)   
Oracle9i/11g,DB2/AS400/Busobj 6.5/Powercenter7/Cognos8/10 /
SSIS 2005 / SQL SERVER 2008 /SDDM 3.3   /   Pentaho 4.4.0 Data Integrator / SSAS 2008 / PostgreSQL 9

Offline koll2427

  • Associate
  • **
  • Join Date: Oct 2018
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #8 on: 09 Oct 2018 02:50:32 pm »
I know this is a VERY old post and I apologize up front if there is more recent information on displaying URL images in Cognos ReportNet reports.   I followed all the instructions in this post and got a report to render an image via a URL derived from a database.   So thank you for that info.   The problem is that only SOME of the jpeg images are rending in PDF and others are not.   All render in HTML, but only some render in PDF.   I can't figure out why some won't display when running in PDF format.   Any suggestions would be much appreciated.   Thank you!

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,357
  • Forum Citizenship: +624/-10
  • Cognos Software Muppet
Re: Show an image as derived from a database (url shortcut)
« Reply #9 on: 10 Oct 2018 03:40:53 am »
I know this is a VERY old post and I apologize up front if there is more recent information on displaying URL images in Cognos ReportNet reports.   I followed all the instructions in this post and got a report to render an image via a URL derived from a database.   So thank you for that info.   The problem is that only SOME of the jpeg images are rending in PDF and others are not.   All render in HTML, but only some render in PDF.   I can't figure out why some won't display when running in PDF format.   Any suggestions would be much appreciated.   Thank you!

I remember Cognos 8 being quite picky about displaying images at times. It's been a while, so my memory is quite hazy here, but I seem to recall relative URLs working consistently whereas absolute URLs being difficult to render in PDF. It might have been the web server rather than C8 at fault, though.

Can you give us an idea of the URL format you are using?

Cheers!

MF.
Meep!

Online CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,676
  • Forum Citizenship: +250/-1
    • Paul's Cognos Blog
Re: Show an image as derived from a database (url shortcut)
« Reply #10 on: 10 Oct 2018 07:36:58 am »
If it's working some times but not others, it might be due to the type of image in the database. Are they all jpgs or pngs? What happens if you take an image that fails to export, save it to a file system and call it directly?

Offline koll2427

  • Associate
  • **
  • Join Date: Oct 2018
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #11 on: 10 Oct 2018 08:39:08 am »
The database query is based on a person's ID to obtain the URL where the image is located.   I defined the URL field as an image in Framework Manager.   All images render in HTML via Cognos ReportNet, but the only jpg that works using PDF output format is a location outside our firewall.   All the URLs reference jpg files and each URL is pointing to different file servers, so the image itself is not in a database.   Could there be an issue with the file location URL containing spaces, or something like that?

Offline koll2427

  • Associate
  • **
  • Join Date: Oct 2018
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #12 on: 10 Oct 2018 08:56:50 am »
Some of the URL references are to a SharePoint folder on our fileserver.   here is a example:  https://uofstthomasmn.sharepoint.com/sites/GroupCI2/UDAR%20Constituents/Phil%20Smith%20Bio%2010099899.jpg?csf=1&e=dB1XSP

Online CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,676
  • Forum Citizenship: +250/-1
    • Paul's Cognos Blog
Re: Show an image as derived from a database (url shortcut)
« Reply #13 on: 11 Oct 2018 03:26:39 pm »
This sounds like a security problem. Does the Cognos service account have permissions to view those images?

Offline koll2427

  • Associate
  • **
  • Join Date: Oct 2018
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Show an image as derived from a database (url shortcut)
« Reply #14 on: 15 Oct 2018 02:34:37 pm »
I was able to get all the images to render in PDF format.   It was a server permission issue.   Thank you so much for your assistance on this.

 


       
Twittear