SQL Reporting Services and alternating images

orther
orther used Ask the Experts™
on
I have a SQL report where I have placed a embedded image.  This is a small banner ad that will print at the bottom of our quote.  I have 3 separate images that I would like to have alternate on the report every hour.  Ex. If a quote is generated at 8:00AM the quote will display Banner1.png. When a quote is created at 9:15AM it will display Banner2.png and finally when a quote is created at 10:35AM it will display Banner3.png.  At 11:00AM quotes will again print Banner1.png.  All of the banner ads are exactly the same size.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I have all 3 images imported into the report.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Please check the Value in expression with DateTime field. =IF(Time="8:00 AM", Field!Image1.Value, IF(Time="9:15 AM", Field!Image2.Value, Field!Image3.Value))

Author

Commented:
I am trying different variations but can't seem to get it.

=IIF(TimeOfDay>="8:00 AM" and TimeOfDay<= "8:59 AM", Fields!banner1.PNG.Value),
    IIF(TimeOfDay>="9:00 AM" and TimeOfDay<= "9:59 AM", Fields!banner2.PNG
    , Fields!banner3.PNG.Value))
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Us an expression for the visibility of the banners. For your firs banner use:
=(hour(now) mod 3)<>0
for the second
=(hour(now) mod 3)<>1
for the third:
=(hour(now) mod 3)<>2
Place the three banners on top of each other.

Author

Commented:
so if I wanted to rotate between the three every hour how would I set this?
It can also be done with one image control. Suppose you have the three images embedded in you report and given them Banner1, Banner2 and Banner3 as name. In you image control you can now use this expression as the value:
=choose((hour(now()) mod 3)+1,"Banner1","Banner2","Banner3")
See attached example. Rename the file Banner.txt to Banner.rdl (EE doesn't allow rdl files).



Banner.txt

Author

Commented:
I cannot get that banner.rdl to work, but do I add this expression to each of the banner ads

Author

Commented:
oh wait not visibility but Under General for all 3.
<<I cannot get that banner.rdl to work>>
Strange. What error messages are you getting?

Sorry for the confusion. Forget about visibility. You need 3 embedded images in your report (which you probably already have). You need only one image control with this expression for the Value property:
=choose((hour(now()) mod 3)+1,"Banner1","Banner2","Banner3")
(replace the BannerX names with the names of your images). See screenshot below:

   Banner

Author

Commented:
yea, my bad, I left the visibility code in there and so of course it wasn't working.  It looks like that did the trick.  Thanks much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial