Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

Dynamic refresh of report model in the report builder

Is it possible to dynamically refresh the report model of the report builder?

could it even be using code with any of the interfaces?

When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.

Please review the following threads:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=363475&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1101825&SiteID=1

Dynamic Query View?

This summary is not available. Please click here to view the post.

Dynamic Query Report Server 2005

Because of several reasons i have to set the query statement of a report
dynamically. Does anybody know if that is possible just before rendering
a report? I use the web Services interface for accessing the reports.
thanks for any suggestions
MarkusYou cannot modify the query statement through e.g. SOAP. You would need to
republish the report. But is this really necessary? Did you look into using
an expression-based query commandtext (which is already available in
RS2000)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Another thing you could do is to write a Stored Procedure which accepts a
parameter and issues one of several queries, based on the parameter, or
perhaps generates the query string and executes it via sp_executesql
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Markus" <markus@.m-s-wunderlich.de> wrote in message
news:cu36m2$dq9$1@.online.de...
> Because of several reasons i have to set the query statement of a report
> dynamically. Does anybody know if that is possible just before rendering a
> report? I use the web Services interface for accessing the reports.
> thanks for any suggestions
> Markus|||Robert, what do you mean "expression-based query commandtext"?
Here is my problem - I need to pass in a parameter with multiple values.
What is the recommended way to accomplish this?
The only suggestion I've gotten so far is to create a user based
function on the reporting SQL server that can parse my parameter string
into a table. I don't wish to do this if there is an easier way.
So then I think, hey! maybe I can generate my own sql then pass it to
the report. Wrong. Apparently I can define the query if I'm creating a
data driven subscription but not if I just want to pass it to an
existing Report? This makes absolutely no sense to me.
Does anyone have a recommendation for me?
thanks,
Ian Stallings
Robert Bruckner [MSFT] wrote:
> You cannot modify the query statement through e.g. SOAP. You would need to
> republish the report. But is this really necessary? Did you look into using
> an expression-based query commandtext (which is already available in
> RS2000)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Markus" <markus@.m-s-wunderlich.de> wrote in message
> news:cu36m2$dq9$1@.online.de...
>>Because of several reasons i have to set the query statement of a report
>>dynamically. Does anybody know if that is possible just before rendering a
>>report? I use the web Services interface for accessing the reports.
>>thanks for any suggestions
>>Markus
>
>|||By expression based he means the following. Go to the generic view of the
query designer (hover over the buttons to the right of the ... to find the
one to click on). You can do either of these two things:
select * from sometable
or you can put in an expression:
="select * from sometable"
I use this technique for having a parameter specify my order by but you
could use this for your needs as well. Note that you have to make everything
perfect for this to work. I usually first just have a report with parameters
and a single textbox on the report (no query to start off with). I assign
the expression to the textbox and test it out until I see the proper SQL
string.
Here is an example for using a parameter
="SELECT * FROM sometable order by " & parameters!SortBy.value
The point here is that you are dynamically creating the SQL statement.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ian Stallings" <jovian_moon@.hotmail.com> wrote in message
news:%23Lm1WnjNFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
> > You cannot modify the query statement through e.g. SOAP. You would need
to
> > republish the report. But is this really necessary? Did you look into
using
> > an expression-based query commandtext (which is already available in
> > RS2000)?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Markus" <markus@.m-s-wunderlich.de> wrote in message
> > news:cu36m2$dq9$1@.online.de...
> >
> >>Because of several reasons i have to set the query statement of a report
> >>dynamically. Does anybody know if that is possible just before rendering
a
> >>report? I use the web Services interface for accessing the reports.
> >>thanks for any suggestions
> >>
> >>Markus
> >
> >
> >|||Just a follow up, I have fixed this problem. I ended up using a user
defined function to parse the parameter (which is passed in as '1,2,3'
.. etc) and then return a table with the datatypes I need, I then query
against that and return a recordset that I use in my dataset query.
Here are more details in case anyone comes searching later:
http://weblogs.asp.net/jmoon/archive/2005/04/01/396649.aspx
- Ian Stallings
Ian Stallings wrote:
> Robert, what do you mean "expression-based query commandtext"?
>
> Here is my problem - I need to pass in a parameter with multiple values.
> What is the recommended way to accomplish this?
> The only suggestion I've gotten so far is to create a user based
> function on the reporting SQL server that can parse my parameter string
> into a table. I don't wish to do this if there is an easier way.
> So then I think, hey! maybe I can generate my own sql then pass it to
> the report. Wrong. Apparently I can define the query if I'm creating a
> data driven subscription but not if I just want to pass it to an
> existing Report? This makes absolutely no sense to me.
>
> Does anyone have a recommendation for me?
> thanks,
> Ian Stallings
>
>
> Robert Bruckner [MSFT] wrote:
>> You cannot modify the query statement through e.g. SOAP. You would
>> need to republish the report. But is this really necessary? Did you
>> look into using an expression-based query commandtext (which is
>> already available in RS2000)?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Markus" <markus@.m-s-wunderlich.de> wrote in message
>> news:cu36m2$dq9$1@.online.de...
>> Because of several reasons i have to set the query statement of a
>> report dynamically. Does anybody know if that is possible just before
>> rendering a report? I use the web Services interface for accessing
>> the reports.
>> thanks for any suggestions
>> Markus
>>
>>

Dynamic Query

I use IIF in the dynamic query to dynamically change the Select, Group By, and Order By statements.

In the table grouping properties, i also use IIF to change the grouping Field.

There are no errors, the report processes OK, but the report is not grouped or shows any Field values for which i have to use dynamic query. What could be the problem? Can anybody help please.

Thanks

Can you post the dynamic query you are using, as well as the group expression? Also if you enable tracing on the database side, is the query being executed correct?|||

Thank you for answering to my problem. I did get over it after much trying. The dynamic query looks like this:

="SELECT SUM(BASE_UNIT) AS BASE_UNIT "
& IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", ", Provider_Name")) &
" as grouping FROM TBL_EOM
WHERE (MONTH(ENTRY_DATETIME) =@.RepMonth) AND (YEAR(ENTRY_DATETIME) = @.RepYear)" &
IIF(Parameters!Pract.Value = "*** ALL ***", " ", " AND (NAME = @.Prac) ") &
" GROUP BY Provider_Name, NAME" & IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", " "))

In layout view i have this strig for field, grouping and sorting:

IIF(Parameters!Type.Value <> "Provider", Fields!grouping.Value, " ")

Thank you

Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."

>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;

And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic Period Over Period Growth Without Hierarchy in Time/Date Dimension?

The time dimension in our AS2005 cube is not hierarchical. Therefore, how can I dynamically do a period over period growth calculated member? I'd like it to determine the growth whether the user is viewing it yearly, quarterly or monthly.

If our time dimension was indeed hierarchical, I would define the metric as follows:

([Time].[Currentmember], [Measures].[Sales] - ([Time].[CurrentMember].[PrevMember], [Measures].[Sales])

Right now, I have only been able to do it for a specific level whether it's year, quarter or month. For example, for a year over year growth, I've defined it as follows:

([Date].[Year].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Year].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

If I wanted to do the same for months, I'd replace "Year" with "Month" as follows:

([Date].[Month].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Month].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

Is it possible to do a dynamic period over period growth in 1 calculated member based on how our Date dimension is setup?

Could you explain what attribute relations exist in your time dimension - are quarters related to months, and years to quarters? Or is the [Month] like a "month-of-year" and quarter like a "quarter-of-year"? In that case, you could try a scoped assignment like:

Create [Measures].[SalesGrowth];

Scope([Measures].[SalesGrowth]);

Scope([Date].[Year].[Year]);

this = [Measures].[Sales USD] - ([Date].[Year].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Quarter].[Quarter]);

this = [Measures].[Sales USD] - ([Date].[Quarter].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Month].[Month]);

this = [Measures].[Sales USD] - ([Date].[Month].PREVMEMBER, [Measures].[Sales USD]);

End Scope;

End Scope;

End Scope;

End Scope;

|||Deepak,

I've never used SCOPE before, but this looks like it could work. Where do I use SCOPE? In the definition of the [SalesGrowth] calculated member? Or do I have to somehow make use of a new SCRIPT command? I've never done this either. The only Script Command I have is at the default "CALCULATE". that goes before all of my calculated members.

Thanks!
|||The code above already includes a statement to create [SalesGrowth], at the beginning - you could append this snippet to your existing script, after the other calculated members.|||Deepak,

Thanks for exposing me to SCOPE! I was able to acheive what I wanted with just a few tweaks.

Dynamic Page Header Height...

Group,
Does anyone know how to set the height of the Page Header dynamically, it's
not letting me set the Height property of the Page Header to an Expression
[something like = IIf(Globals!PageNumber = 1, 1.5in, 1in)?
Or is there any other way to achieve the following:
I want a certain page header on page 1 and then a different one (shorter in
height) to appear on all subsequent pages without the extra header
whitespace.
Thanks group.As far as I know report header is static - I think it is on the wish list for
the next release. [I saw a post on this from msft - search around & you will
see it]
"Terry Mulvany" wrote:
> Group,
> Does anyone know how to set the height of the Page Header dynamically, it's
> not letting me set the Height property of the Page Header to an Expression
> [something like = IIf(Globals!PageNumber = 1, 1.5in, 1in)?
> Or is there any other way to achieve the following:
> I want a certain page header on page 1 and then a different one (shorter in
> height) to appear on all subsequent pages without the extra header
> whitespace.
> Thanks group.
>
>

Dynamic Order By Clause

I'm trying to dynamically determine the Order By clause, like I would in VB,
but I can't figure out the syntax. This is my SP. Thanks
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100),
@.mOrderBy varchar(20)
AS
Declare @.OrderByField as varchar(20)
Select @.OrderByField =
CASE
WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
WHEN @.mOrderBy = "Author" THEN ' users.UName'
WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
ELSE 'MB_Posts.ID!'
END
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY @.OrderByField
GOHi,
See the below URL:-
http://www.sommarskog.se/dynamic_sql.html#Order_by
Thanks
Hari
SQL Server MVP
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Order By clause requires a literal string. You might want to take a look at
Erland's article for some info/work around.
http://www.sommarskog.se/dynamic_sql.html#Order_by
-oj
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Thanks for pointing me in the right direction. I have different data types,
so I had to use the second format. The only problem is that it won't sort i
n
DESC order. This is what is looks like now. Any suggestions?
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100) =null,
@.mOrderBy varchar(20)=null
AS
IF RIGHT(@.mOrderBy,4)='Desc'
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPostDesc' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'AuthorDesc' THEN users.UName ELSE
NULL END,
CASE @.mOrderBy WHEN 'RepliesDesc' THEN MB_Posts.Replies
ELSE NULL END,
CASE @.mOrderBy WHEN 'TopicDesc' THEN MB_Posts.Subject ELSE
NULL END
DESC
ELSE
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPost' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'Author' THEN users.UName ELSE NULL END,
CASE @.mOrderBy WHEN 'Replies' THEN MB_Posts.Replies ELSE
NULL END,
CASE @.mOrderBy WHEN 'Topic' THEN MB_Posts.Subject ELSE
NULL END
ASC
"Phill" wrote:

> I'm trying to dynamically determine the Order By clause, like I would in V
B,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.
ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Pos
t'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Repli
es'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subjec
t'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>

Dynamic modification of data flow objects

My idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.

You cannot alter the metadata of the data-flow pipeline. In english, that means you cannot change the names and data-types of the columns, not can you add or remove them.

However, you CAN dynamically set the external sources and destinations. Would this be sufficient for you?

-Jamie

|||Hi Jamie - thanks for the quick reply. I don't think this will be sufficient. The 200 tables are all different - we are replicationg tables from an Oracle 8i ERP database to SQL for reporting and analysis purposes. The metadata on each source-destination combination will be different from the next so this will be a problem. As I see it the only way to accomplish this concept is to dynamically create a new package for each table i.e each iteration of the ForEach loop. Do you agree?|||

OK, you have to create 200 packages. But you only have to create them once.

You are correct that the only other option is to dynamically build the package. That's not much fun, believe me!

-Jamie

|||

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

|||

Peter G D wrote:

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

200 different requirements means 200 things to build. The complexity is in your requirement. I'm slightly confused how it could be made more elegant. I'd welcome your ideas though.

Peter G D wrote:

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Interesting one. I don't know is the honest answer but I'd love to know. It depends on alot of things, mainly on the amount of data you're moving. The larger dataset then the less the proportionate time to build the package.

Peter G D wrote:

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

No way. You won't be able to do this using VBScript. I don't even think you can do it in the Script Task. You are in custom task territory.

-Jamie

|||

i think you you need to use ado.net to iterate over a lookup table that has the table name, source info, and destination info. for each table, you read the data into a recordset, then insert that data into the destination table. you should also probably use a transaction to rollback everything in the event of an error. all of this can be accomplished in a script task.

hope this helps.

|||

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

|||

Peter G D wrote:

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

Correct. You cannot do that.

-Jamie

|||

Peter G D wrote:

I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

actually, i rather back away from the recordset solution. a better method would be to use raw files instead (for performance reasons). perhaps you could stage the data as raw xml when pulling it out of the source -- i'm not sure if this is the best way. then, you could load that staged data into the destination.

unfortunately, i don't know of any examples to point you towards. all i can tell you is that this solution requires knowledge of ado.net.

Dynamic Memory Problem

Hello,
I have a SQL install that I only want to use 4098MB of memory. I originally
configured the server to dynamically use between 0 and 4096MB of RAM. This
was working fine. I then decided to experiment with a fixed amount to see
how the server would perform at peak memory usage, so I set the fixed amount
to 4096MB. After testing I switched the memory back to dynamic and the
original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
install ALWAYS uses the full 4096MB now. I have tried restarting the
service, rebooting the server, but whenever the SQL service starts it
immediately eats up 4096MB of RAM.
Any ideas on how to fix this?
btw - server is running SP3a with all the latest critical hotfixes.
Thanks,
JBaileyI figured this out.
I ended up having to disable AWE in SQL, restart the SQL service, and then
reenable AWE
Everything is working normally now.
Wonder why it happened though
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Actually I was wrong. Rebooted the box and I'm still having the same issue.
:(
Any ideas?
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||With AWE enabled, SQL server will take as much memory it can get -- if there
is a max, it goes to the max, else it goes to the max the system allows it.
So what you see is the standard behavior.
Quentin
"JBailey" <abc@.123.com> wrote in message
news:Ol40$QClDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Actually I was wrong. Rebooted the box and I'm still having the same
issue.
> :(
> Any ideas?
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||When you use AWE it will fix the memory at what the Max Memory setting is.
That is how AWE works. You can not dynamically allocate memory with AWE set
on.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Alright, I understand that, but I have a follow up.
I have three instances of SQL installed on a 2 node cluster.
One instance is set to use AWE and a max memory of 4096. This is the
instance that immediately takes up the 4GB of RAM
Two other instances are set to use AWE, and both are set to use max memory
of 2048. Neither of these instances use up the RAM immediately like the
first instance. Is this because neither of them are set to use more than 4GB
of RAM?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> When you use AWE it will fix the memory at what the Max Memory setting is.
> That is how AWE works. You can not dynamically allocate memory with AWE
set
> on.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||Sorry for the late reply. AWE is not used in the other 2 instances since
the memory can only be 2 GB. There are times when SQL Server just ignores
the AWE settings when they don't make sense and can not be used.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:%23Fhk%23zKlDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Alright, I understand that, but I have a follow up.
> I have three instances of SQL installed on a 2 node cluster.
> One instance is set to use AWE and a max memory of 4096. This is the
> instance that immediately takes up the 4GB of RAM
> Two other instances are set to use AWE, and both are set to use max memory
> of 2048. Neither of these instances use up the RAM immediately like the
> first instance. Is this because neither of them are set to use more than
4GB
> of RAM?
> Thanks,
> JBailey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > When you use AWE it will fix the memory at what the Max Memory setting
is.
> > That is how AWE works. You can not dynamically allocate memory with AWE
> set
> > on.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > I have a SQL install that I only want to use 4098MB of memory. I
> > originally
> > > configured the server to dynamically use between 0 and 4096MB of RAM.
> This
> > > was working fine. I then decided to experiment with a fixed amount to
> see
> > > how the server would perform at peak memory usage, so I set the fixed
> > amount
> > > to 4096MB. After testing I switched the memory back to dynamic and the
> > > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > > service, rebooting the server, but whenever the SQL service starts it
> > > immediately eats up 4096MB of RAM.
> > >
> > > Any ideas on how to fix this?
> > >
> > > btw - server is running SP3a with all the latest critical hotfixes.
> > >
> > > Thanks,
> > >
> > > JBailey
> > >
> > >
> >
> >
>

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sql

Dynamic measures

Hi,

I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.

The parameter values has, Actual, Budget, Target

For the one field, base on the above parameter, will select,

if the value for the parameter is Actual, then we will have only a column of Actual values

if it's Budget, then we will have only a column of Budget values

if it's Target, then we will have only a column of Target values

How should I write the mdx query for this?

I'm really desperate for the answer.

Thanks a lot for your help.

The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.

Dynamic Lookup .... is it in SQL 2008?

I'm finding that not having the ability to dynamically change the contents of the query in the lookup transform is a major, major problem. Has anyone looked to see if this is in the SQL 2008 CTP?

Does anyone have any good work arounds?

Thanks,

Michael

Can you provide an example of how you vision this to work?|||

I have a very large table that's partitioned into 56 partitions. I bring in data based on partition and match on an ID field. Whether the data matches or not sends it through different transformations in the same data flow. The table itself has 1.2 billion records and about 4TB and is growing quickly. I'd like the lookup to only pull the records from the partition I'm working with. In the end (this is unrelated to the lookup), I just swap the new partition into the table. It's actually pretty quick. But the gist of it is I want to dynamically limit the lookup query to the range of the partition I'm working on. Right now, I'm using a View and changing the definition to use the partition being used before I go into the dataflow. The problem with that is now I can't run data loads in parallel. So, the other possibility is to have 56 different packages, at least for the data flow part.

Thanks,

Michael

|||

MichaelT wrote:

I'm finding that not having the ability to dynamically change the contents of the query in the lookup transform is a major, major problem. Has anyone looked to see if this is in the SQL 2008 CTP?

It doesn't appear in the current CTP. That is not to say it won't appear in a future CTP of katmai.

This is a question for Microsoft really so...

[Microsoft follow-up] Is there any information you guys can provide?

-Jamie

|||

Thanks, Jamie. You're right, there're no signifcant changes to the Lookup component in the current CTP.

Michael, I think you might be able to use an OLEDB Source, Merge Join, and Conditional Split to achieve your goal. What do you think?

|||

The current plan (subject to change and all usual disclaimers about non-released products) is to make SQL statement property expressionable - so you'll be able to dynamically change this query using property expressions on data flow task.

Does it work for your scenario?

|||

Yes, having an expressionable SQL Statement property for the Lookup Transform would work great for me. Hopefully, it'll make it to SQL 2008.

Thanks,

Michael

|||

Michael Entin - MSFT wrote:

The current plan (subject to change and all usual disclaimers about non-released products) is to make SQL statement property expressionable - so you'll be able to dynamically change this query using property expressions on data flow task.

Does it work for your scenario?

Michael,

That would be fantastic.

-Jamie

Dynamic listbox?

Hi!
Is it possible to fill lisboxes dynamically? What I want to do is select a time frame from one listbox, say Year-Month, and pass a fieldname as a value to the query/stored proc that fills the second listbox. I know that it is possible to take a value from one listbox and use it in another one but I can't get this to work.
Thanks in advance!
// Maria
--
Message posted via http://www.sqlmonster.comYes to both questions. First, you are wanting to set the selection for a
parameter to a list. Do the following:
1. create a dataset that you will use that creates the first list.
2. In layout you pick the report menu->report parameters
3. Select available values, from query. You have to set two things: label
and value. Label is what the user sees to select and value is what is
returned as a parameter.
I suggest having a textbox on you form for testing that is set to an
expression that shows the parameter so you can make sure you are getting the
parameters correctly.
4. create a second dataset with a query parameter (a report and query
parameter are two different things but can seem like they are one and the
same because RS creates a report parameter automatically for you). Note, if
you call your query parameter by the same name as the parameter you created
above then they will be mapped to one another, otherwise RS will create
another report parameter. For instance: if you called your report parameter
YearMonth and then your query parameter @.YearMonth (note, case sensitivity
for report paraemeters, match case exactly).
5. Add a second report parameter and have it use as its source the dataset
from 4
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Maria via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:12b79303565b463c838e86b956e656b7@.SQLMonster.com...
> Hi!
> Is it possible to fill lisboxes dynamically? What I want to do is select
a time frame from one listbox, say Year-Month, and pass a fieldname as a
value to the query/stored proc that fills the second listbox. I know that it
is possible to take a value from one listbox and use it in another one but I
can't get this to work.
> Thanks in advance!
> // Maria
> --
> Message posted via http://www.sqlmonster.com|||Thank You, it works perfect! This is really useful for me and the type of reports I'm creating :)
--
Message posted via http://www.sqlmonster.comsql

Dynamic Images with ASPX extensions in Reports

I want to create a report that shows dynamically created images from a web
site. It looks like it will work OK, I can put the graphic in and a link
http://localhost/image.aspx?123 but the image doiesnot show in the reports.
I am guessing there is a miss match between the extension and the MIMEType
thats stuffing it up. Can anybody confirm this, before I rush into building
a HTTPRequestHandler.
Message posted via http://www.sqlmonster.com
I think you meant to post this in the reporting services newsgroup.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:acbdec17de10456a86dee6a044719188@.SQLMonster.c om...
> I want to create a report that shows dynamically created images from a web
> site. It looks like it will work OK, I can put the graphic in and a link
> http://localhost/image.aspx?123 but the image doiesnot show in the
reports.
> I am guessing there is a miss match between the extension and the MIMEType
> thats stuffing it up. Can anybody confirm this, before I rush into
building
> a HTTPRequestHandler.
> --
> Message posted via http://www.sqlmonster.com
|||Tom
I am facing the same problem! Did you ever find a way to show an image
using a url such as http://localhost/image.aspx?123
Any help would be great as I am starting to tear my hair out!!!
Richard
Message posted via http://www.sqlmonster.com

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))

dynamic images in sql reporting services 2005

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

One way to do this is to include a hyperlink to the image. The URL of the hyperlink can be an expression controlled by the data in your report.|||

can we pass the URL using a parameter.

or can we do it like this.

i have a image file name in db(FirstName is the image name)

i have image path thru parameter

and in expression can i give like this

=Parameters!IPath.Value+Fields!FirstName.Value+".png"

it is working in the preview but not after deployment and in the runtime in IE

|||

RameshP wrote:

Hi all,

I am currently working on some reports where I need to display images dynamically.

there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.

for example,

if the range is between

80% - 100% smily face.

60% - 80% normal face

40% - 60% sad face.

Can any one help in approaching this.

Initially I worked with only static embeded images.

It also helps me in solving another problem.

I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.

Thanks in advance.

waiting for an early help as it is very urgent for me.

Regards,

Ramesh P

|||

We have similiar dashboard with traffic lights

I put an image in the field, and "value" field =

Code Snippet

=IIF(Fields!capacity_available.Value < 0.2, "icon_red-light.gif",

iif(Fields!capacity_available.Value < 0.4, "icon_yellow-light.gif",

iif(Fields!capacity_available.Value < 0.8, "icon_green-light.gif",

"icon_green-light.gif")))