Dynamic Row Level Security

Hi,
Is it possible to configure Reporting Services 2005 so that the same report
will apply different data filters depending on the user running the report ?
i.e A German user will only see German data and an English user will only
see English data even if they enter a parameter for 'All Europe'.
Thanks.RS supports a property User!UserID which returns the identity of the
interactive user (assuming Windows authentication). You can pass the user
identity to the data source as a query parameter to implement data filtering
at the data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
"Duncan Allen" <DuncanAllen@.discussions.microsoft.com> wrote in message
news:B76E2612-99F1-490B-AFBF-E11B5280BE43@.microsoft.com...
> Hi,
> Is it possible to configure Reporting Services 2005 so that the same
> report
> will apply different data filters depending on the user running the report
> ?
> i.e A German user will only see German data and an English user will only
> see English data even if they enter a parameter for 'All Europe'.
> Thanks.

Dynamic reports

Yooo... I'm trying to build a dynamic report with Reporting Services. The problem is that I have a stored procedure that returns a different number of columns with different name for the columns almost each time. So... how can I get dynamic the number and the name of the columns at runtime.

He is an example of the SP:

CREATE PROCEDURE [dbo].[Test]
@.nrCol INT
, @.CarCol CHAR(5)
AS
CREATE TABLE #Part(DenPart CHAR(10))
DECLARE @.i INT
SET @.i = 0
WHILE @.i < @.nrCol
BEGIN
EXEC('ALTER TABLE #Part ADD [' + @.CarCol + @.i + '] NUMERIC(18,2) NOT NULL DEFAULT(0)')
SET @.i = @.i + 1
END
INSERT INTO #Part (DenPart) VALUES('A')
INSERT INTO #Part (DenPart) VALUES('B')
SELECT * FROM #Part

Any ideeas?

Thanks

I wont think we can use the above stored procedure. To develop a report we need to have a result set at design time. Report need to know what are the data fields at design time.sql

dynamic reporting

Hi Experts...
I want to create report on the fly, for given mdx query , I mean without
having a pre build report format, and I am using FoodMart 2000 sample
database.
pls give me some ideas.....
Thanks in Advance...If I understand what you mean correctly (and using RS) . . . . . I guess
you'd need to create the RDL file dynamically and have it executed
(I think) 8^)
- peteZ
"Sumudu Prasad" <sumudu@.logicalasia.com> wrote in message
news:uo%23XGEhmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Hi Experts...
> I want to create report on the fly, for given mdx query , I mean without
> having a pre build report format, and I am using FoodMart 2000 sample
> database.
> pls give me some ideas.....
> Thanks in Advance...
>
>

Dynamic report with several datasources

Hello,

I am currently working with SQL 2005 Reporting Services and MS Visual studio 2005.

In my SQL server I have data about organizations, so that each organization's data is stored in an individual database.
In addition, the number of organizations/databases is variable. I know the number of organizations and the name of each one (that it agrees with the name of its database)
because there is a database (organizationsDB) with a table (organizationsTB) containing the list of the organizations.

So, It would be possible to create a report containing a summary of each organization? .... for example a table in which each row contains data of each organization.

Could you give me any ideas?

Thanks in advance!

You could probably do something with a stored procedure, temp tables and some dynamic sql... eg.

DECLARE @.sql as varchar(8000)

DECLARE @.mydbname

DECLARE @.tempTable TABLE

{
-- insert table schema for org summary here
}

-- insert cursor to populate each database name from the table

Set @.Sql = 'Insert into @.tempTable Select * from ' + @.mydbname + '.dbo.Mytable'

EXEC(@.Sql)

-- loop cursor

-- display on report.

Select * from @.tempTable

I'm not a fan of cursors but that should work.

cheers,

Andrew

|||

Hello,

I am trying the solution that Andrew proposed but I got this Error: Must declare the scalar variable @.tempTable

The stored procedure code is the following:

ALTER PROCEDURE [dbo].[usp_GetListOrganizations]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.sql as varchar(8000);

DECLARE @.mydbname as varchar(50);

DECLARE @.tempTable as TABLE(SumMaxSize bigint NOT NULL,SumActualSize bigint NOT NULL, NumberOfUsers int NOT NULL);

-- insert cursor to populate each database name from the table

Declare myCursor Cursor For SELECT DomainName FROM domainsDB.dbo.domainsTB

Open myCursor

Fetch Next From myCursor

Into @.mydbname

While @.@.FETCH_STATUS = 0

Begin

Set @.Sql = 'Insert into @.tempTable Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'

EXEC(@.Sql)

-- loop cursor

Fetch Next From myCursor

Into @.mydbname

End

Close myCursor

SELECT * FROM @.tempTable

END

what is wrong? Could you help me?

Thanks in advance!

|||

...I have proved to use sp_executesql too:

Set @.Sql = 'Insert into ' + @.tempTable + ' Select COUNT(MaxSize) AS SumMaxSize, COUNT(ActualSize) AS SumActualSize, COUNT(SamName) AS NumberOfUsers from [' + @.mydbname + '].[dbo].[Users]'

EXEC sp_executesql @.Sql, N'@.tempTable TABLE, @.mydbname varchar(50)', @.tempTable, @.mydbname

But result is 'Must declare the scalar variable @.tempTable' twice

Thanks!

|||

Unfortunately that won't work very well since it's running in a different memory space.

What about

INSERT INTO @.tempTable

EXEC(@.Sql)

where @.Sql is the dynamic select SQL you are working with?

Alternatively, you could use a physical table and some sort of common identifier like user id + datetime.

Hope this works. Also check syntax on @.tempTable declaration. Not sure if it is correct.

cheers,

Andrew

Dynamic Report Subscriptions

I'm using Reporting Services 2003 and I'm trying to set up a Subscription
whereby the report is kicked off and sent on email when and only when data is
updated on the server. Any suggestions?Chad,
I believe you may be able to do this using data driven subscriptions.
See:
http://msdn2.microsoft.com/en-us/library/ms169673.aspx
o
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_92ex.asp?frame=true
p.s. I am assuming you meant Reporting Services 2005!
regards
Wozza
"Chad Webber" wrote:
> I'm using Reporting Services 2003 and I'm trying to set up a Subscription
> whereby the report is kicked off and sent on email when and only when data is
> updated on the server. Any suggestions?|||I did mean 2005 and thanks, the information did help. I was able to get my
subscription set up and working the way I wanted.
"Wozza" wrote:
> Chad,
> I believe you may be able to do this using data driven subscriptions.
> See:
> http://msdn2.microsoft.com/en-us/library/ms169673.aspx
> or
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_92ex.asp?frame=true
> p.s. I am assuming you meant Reporting Services 2005!
> regards
> Wozza
> "Chad Webber" wrote:
> > I'm using Reporting Services 2003 and I'm trying to set up a Subscription
> > whereby the report is kicked off and sent on email when and only when data is
> > updated on the server. Any suggestions?

dynamic query

Hello,

Could anyone please tell me the error in my query...I use sql reporting 2005.

select * from table1 T

where (T.startdate >= Parameters!Startdate.value and

T.startdate <= Parameters!Enddate.value)

The report runs good but does not return any rows.

Thanks,

asiaindian

Hi,

I got it.....

sql

dynamic query

I need to change the query, according to the parameter I get from url to the reporting services.

For example,

if url =http://localhost:reportserver/parmeter=true?param1=A

then, I need to use a "query1" for the report . If param1 = B, then I need to use "query2".

I would like to know whether this is possible..

thanks,

I suggest using a stored procedure as the dataset for your target report.

The stored procedure would accept as its one parameter the report parameter of the target report and run the required query accordingly.

Dynamic query

Hello!
I tryed the example from Reporting Services Books Online but it didn't work.
I copied ="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
in generic query designer and when I ran the report I received the following
error: Cannot use empty object or column names. Use a single space if
necessary.If department ID is a string then you need to embed it in single quotes. I
suggest doing the following, create a textbox and assign expression to it so
you can see whether you have created it correctly.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:FCD2E55C-37F9-43C9-AF60-BAF2E9FF468E@.microsoft.com...
> Hello!
> I tryed the example from Reporting Services Books Online but it didn't
work.
> I copied ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> in generic query designer and when I ran the report I received the
following
> error: Cannot use empty object or column names. Use a single space if
> necessary.
>

Dynamic Queries Errors in Report Manager

I am trying to replicate the use of Dynamic Queries in SQL Server 2000
Reporting Services documented in the "Hitchhiker's Guide to SS 2000 RS."
I have done everything the book has told me to do. I can build and run the
project in test mode, and deploy to my test server
(http:\\localhost\ReportServer) without error.
I have already set up the datasouce for the application in the Report Server
when I try to run the deployed project. Every time I run the project I get
the following:
1) An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
2) Query execution failed for data set 'dsCustomers'.
(rsErrorExecutingCommand) Get Online Help
3) Line 1: Incorrect syntax near '='.
I think it's pointing at my Data set in my data tab, but am not sure why it
will point there when it worked in test mode!
Does anyone know how to fix this problem?Hello,
I am guessing that by dynamic queries you mean queries with parameters.
I am using them with a teradata db and to get the parameters to work in
the query I have to precede the query with =" and end it with ". To
insert the parameters you put "+Parameter!Name.Value+".
I hope this helps,
Lilja|||Lilja, first I want to thank you for lending a hand. I appreciate it very
much.
Second, I want to restate you comments in my own words. For a dynamic query
with Parameters, I will start my statement with an equals sign (=).
To the left of the equal sign there will be nothing. To the right of the
equals I need to put the equation (which calls an assembly outside of my
project) in between double quotes: ="proj.class.method()".
Whenever I want to add a parameter to the equation I drop out of the string,
and add the literal with a + before and after the parameter:
="proj.class.method("+Parameter!Name.Value+")"
When I run the project in local debug mode, it works fine. When I change
the settings to Debug, give it a path "http://localhost/ReportServer" and
deploy it, I still get the same error. Is there something else that could be
the problem?
My equation runs over into a second line. Does it need the VB underscore to
carry the line over? ' _ '
Thank you very much again.
KurT
"kisa" wrote:
> Hello,
> I am guessing that by dynamic queries you mean queries with parameters.
> I am using them with a teradata db and to get the parameters to work in
> the query I have to precede the query with =" and end it with ". To
> insert the parameters you put "+Parameter!Name.Value+".
> I hope this helps,
> Lilja
>

dynamic queries

Hello All,
I are just starting to use reporting server (2003) and I have hit a
problem. The reports I have created so far have had 3 or 4 sets of possible
parameters, and I have created a different report based on each set. i.e.
agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
The problem now is the next report has far, far too many parameter to
possibly create a report for each permutation. I can't figure out how to
dynamically create the where clause based on which parameters the report
gets passed. Just to clarify, the entire where clause needs to be dynamic,
not just the value.
I need something like this
SELECT blah
FROM table
WHERE IIF(Params!series <> null, "series=Params!series", "")
but this doesn't work.
any help is greatly appreciated,
CraigThe best option for this is to make use of Dynamic queries where you
build your queries dynamically inside the stored proc. You can pass the
superset of all parameters to the SP and check inside the SP. Refer
example below
Inputs to SP;
in_Name
in_Age
in_Sex
in_AcctNum
var str = 'Select * from ACCT_MASTER where'
if (in_name != null)
str = str + 'Name = ' + in_Name
etc.. etc...
finally execute the sp as
exec(str)
This is one of the way of satisfying your requirement|||YOu can also do
="SELECT blah FROM table "
& IIF(Parameters!Series.Value is nothing, ""," Where series='" &
Paramters!Series.Value & "'")
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Craig L" wrote:
> Hello All,
> I are just starting to use reporting server (2003) and I have hit a
> problem. The reports I have created so far have had 3 or 4 sets of possible
> parameters, and I have created a different report based on each set. i.e.
> agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
> agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
> The problem now is the next report has far, far too many parameter to
> possibly create a report for each permutation. I can't figure out how to
> dynamically create the where clause based on which parameters the report
> gets passed. Just to clarify, the entire where clause needs to be dynamic,
> not just the value.
> I need something like this
> SELECT blah
> FROM table
> WHERE IIF(Params!series <> null, "series=Params!series", "")
> but this doesn't work.
> any help is greatly appreciated,
> Craig
>
>

Dynamic Page Breaks

Hi everyone,

This is my first thread. I have been working with Reporting Services and love the way I can change the Groupings and Sortings on the fly. I need to be able to dynamically change which Groupings have a Page Break either before or after as well. There is no expression to enter for the Page break attributes however!

Does anyone know of a way to dynamically change the Page break attributes at run time?

Thanks,

Randy

No, dynamically changing page breaks is not supported.|||

is there any alternative fang?

Like having two groups for the same field with one page break and one with no page break and hiding or changing the expression of a group based on condition!

|||you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

Dynamic Page Breaks

Hi everyone,

This is my first thread. I have been working with Reporting Services and love the way I can change the Groupings and Sortings on the fly. I need to be able to dynamically change which Groupings have a Page Break either before or after as well. There is no expression to enter for the Page break attributes however!

Does anyone know of a way to dynamically change the Page break attributes at run time?

Thanks,

Randy

No, dynamically changing page breaks is not supported.|||

is there any alternative fang?

Like having two groups for the same field with one page break and one with no page break and hiding or changing the expression of a group based on condition!

|||you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

Dynamic menu ?

Hi all,
I'm working with a report in Reporting Services 2005 and I have a problem
that I don't know how to solve.
In my report I have two drop down menus(not mvp) where the first one holds
countries. The second one holds
cities. I want the second menu to be dynamic, i.e. only show cities for the
country I selected in the first menu.
Is this possible to do in RS?
Thanks!Yes, it is. You are talking about cascading parameters.
Create your country parameter and define its datasource. This can be
either a value list or a dataset.
Create a cities dataset using the country parameter in a where clause.
Create your city parameter and define its datasource as a the dataset
that you just created. The "cascading" effect will happen automatically
because the city drop-down cannot be populated until you assign a value
to the country parameter.
Does this explanation make sense?
-Josh
Malin Davidsson (at) wrote:
> Hi all,
> I'm working with a report in Reporting Services 2005 and I have a problem
> that I don't know how to solve.
> In my report I have two drop down menus(not mvp) where the first one holds
> countries. The second one holds
> cities. I want the second menu to be dynamic, i.e. only show cities for the
> country I selected in the first menu.
> Is this possible to do in RS?
> Thanks!|||If you can populate the second list using SQL based on a parameter from the
first list, it should be possible I'd think. I haven't tried though.
Randall Arnold
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:eSpYAgM2GHA.2176@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I'm working with a report in Reporting Services 2005 and I have a problem
> that I don't know how to solve.
> In my report I have two drop down menus(not mvp) where the first one holds
> countries. The second one holds
> cities. I want the second menu to be dynamic, i.e. only show cities for
> the country I selected in the first menu.
> Is this possible to do in RS?
> Thanks!
>|||Thank you, that solved the problem =)
<bell.joshua@.gmail.com> wrote in message
news:1158330681.278038.205980@.k70g2000cwa.googlegroups.com...
> Yes, it is. You are talking about cascading parameters.
> Create your country parameter and define its datasource. This can be
> either a value list or a dataset.
> Create a cities dataset using the country parameter in a where clause.
> Create your city parameter and define its datasource as a the dataset
> that you just created. The "cascading" effect will happen automatically
> because the city drop-down cannot be populated until you assign a value
> to the country parameter.
> Does this explanation make sense?
> -Josh
>
> Malin Davidsson (at) wrote:
>> Hi all,
>> I'm working with a report in Reporting Services 2005 and I have a problem
>> that I don't know how to solve.
>> In my report I have two drop down menus(not mvp) where the first one
>> holds
>> countries. The second one holds
>> cities. I want the second menu to be dynamic, i.e. only show cities for
>> the
>> country I selected in the first menu.
>> Is this possible to do in RS?
>> Thanks!
>

Dynamic MDX Parameter Query Question

Hi:
Thanks first.Did anyone know how to send the query statement for dynamic
MDX Parameter query in sql server reporting service 2005.For example in
2000,we could send the statement like this:
= " select {[Measures].[aaa]} on axis(0),
{" + Parameters@.Parm_time.value + "} on axis(1)
from [Cube] "
,but it failes in 2005.Anyone know why it failes?
--
zhouhuituanI'm pretty sure you need to write the parameters like this:
Parameters!Parm_time.Value in 2005.
Also, make sure everything is on the same line (there will be page breaks,
but don't add them yourself.)
Does your syntax work without the parameters? Are you sure your parameter
value looks like the code you test your query with?
Also, are you using the graphic query builder at all? In my opinion, the
query syntax that appears when using the query builder is very different
from the MDX queries used with AS 2000, and takes a while to get used to.
The filtering in the query works differently than before, especially when
you mess around with the parameters.
What is the error message?
Kaisa M. Lindahl Lervik
"zhouhuituan" <zhouhuituan@.discussions.microsoft.com> wrote in message
news:D231360E-7B36-41B2-A476-15FE606435BB@.microsoft.com...
> Hi:
> Thanks first.Did anyone know how to send the query statement for dynamic
> MDX Parameter query in sql server reporting service 2005.For example in
> 2000,we could send the statement like this:
> = " select {[Measures].[aaa]} on axis(0),
> {" + Parameters@.Parm_time.value + "} on axis(1)
> from [Cube] "
> ,but it failes in 2005.Anyone know why it failes?
> --
> zhouhuituan

Dynamic MDX Deployement in Reporting Services

I have created a Reporting Services report that uses MDX to retrieve data
from a cube. The MDX statements must use a parameter so they are placed into
text strings. The report will run with the Dynamic MDX in Visual Studio but
when I deploy it to the Web Server it locks up.
I believe that the dynamic MDX is causing the problem. The reports that
connect to the Cube that do not use parameters run fine on the web.
Any advice would be appreciated.
Thanks.I found this to be very helpful with parameterized MD
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
"AndyT" wrote:
> I have created a Reporting Services report that uses MDX to retrieve data
> from a cube. The MDX statements must use a parameter so they are placed into
> text strings. The report will run with the Dynamic MDX in Visual Studio but
> when I deploy it to the Web Server it locks up.
> I believe that the dynamic MDX is causing the problem. The reports that
> connect to the Cube that do not use parameters run fine on the web.
> Any advice would be appreciated.
> Thanks.

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")))

Dynamic grouping problem

Hi all,
I need a certain functionality in my Reporting service report.
I need a report with 4 levels of grouping above the detail level. The
initial visibility of all the lower levels should be hidden expect the top
most level. For all the levels toggle item is set in the group properties.
There should also be a boolean parameter called "expand all" which will
affect the initial visibility of the report.
This report should also have dynamic grouping facility with the help of 4
parameters one for each level. Thus if the value passed to one of these
groupby parameters is the word "none" then that group header row should not
be shown. In all cases the detail row should be visible either by drilling
down to that level or as a top level if all other levels are "none".
I am partially successful in the above scenario by making the top level a
mandatory one. i.e the top level can never be "none". However my problem is
that if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up.
Thus there are 3 boolean values to be controlled along with toggle in each
group and detail section. How to achieve this?
Please help me solve this issue.
Thanks.Hi,
Welcome to MSDN Managed NewsGroup. This is Justin from Microsoft.
I do not quite understand the following:
"if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row
should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up. "
Could you elaborate your issue?
Please aslo let me know the expression you set for the visiblity of the
group so that I could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Dynamic grouping problem
| thread-index: AcYmdoUqBM3uTevuTUye6qEq3EKGnQ==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| Subject: Dynamic grouping problem
| Date: Tue, 31 Jan 2006 06:56:30 -0800
| Lines: 29
| Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67863
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I need a certain functionality in my Reporting service report.
|
| I need a report with 4 levels of grouping above the detail level. The
| initial visibility of all the lower levels should be hidden expect the
top
| most level. For all the levels toggle item is set in the group
properties.
| There should also be a boolean parameter called "expand all" which will
| affect the initial visibility of the report.
|
| This report should also have dynamic grouping facility with the help of 4
| parameters one for each level. Thus if the value passed to one of these
| groupby parameters is the word "none" then that group header row should
not
| be shown. In all cases the detail row should be visible either by
drilling
| down to that level or as a top level if all other levels are "none".
|
| I am partially successful in the above scenario by making the top level a
| mandatory one. i.e the top level can never be "none". However my problem
is
| that if in the database a particular column supplied to report thorough
one
| of the groupby parameters has all nulls then also the group header row
should
| not show up. But when I try to put expression to control this using
| "isnothing" function, the detail row never shows up.
|
| Thus there are 3 boolean values to be controlled along with toggle in
each
| group and detail section. How to achieve this?
|
| Please help me solve this issue.
|
| Thanks.
||||Hi,
I have 3 parameters by name groupby2,groupby3,groupby4 which helps to create
3 dynamic groups. Also I have the top most non-dynamic group. Thus 4 group
headers above my details row and no group footers. I pass the word "NONE"
through one or many of the above parameters,if I dont want to see any or all
of the group header. I use a parameter called expand_all to set the initial
toggle state of all the groups to be expanded or collapsed.
Here are the values I set for each group:
1st group header:
(I reach visibility tab as follows: right click group header--edit
group-visibility tab)
There I set the initial visibility to be visible and no toggle item. close
dialog boxes.
Then I select the first group header row and in the properties window, I set
the visibility(hidden) to be visible.
2nd Group Header:
I reach visibility tab. Set initial visibility to be an expression ="(not
parameters!expand_all.value)" and toggle item to be the name of first textbox
in the 1st group header.
Then I select the 2nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby2.value="none",true,false)
3rd Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby2.value="none",false,true) and toggle item to be name
of the first text box in the 2nd group header row.
Then I select the 3nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby3.value="none",true,false)
4th Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby3.value="none",false,true) and the toggle item to be
name of the first textbox in the 3rd group header row.
Then I select the 4th group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby4.value="none",true,false)
Details row:
initial visibility: =(NOT Parameters!EXPAND_ALL.Value) AND
IIF(PARAMETERS!GROUPBY4.VALUE="NONE",FALSE,TRUE) and the toggle item to be
the name of the first text box in the 4th group header row.
Then I select the details row and in the properties window, I set the
visibility(hidden) to be =false.
With the above set up, the report works good. But my problem is say we pass
a particular database field name to groupby2 parameter. (These 4 groupby
parameters are attached in the select list of the dataset of report). Now in
the table if the sent column has no data just nulls, then the group header
first column has just the + toggle sign or - toggle sign based on the state
of toggle and no value. So I tried to suppress the group header when the
field passed for that group header has null values in the database using the
a condition like this in the group header row properties.
=iif(parameters!groupby2.value=none or
fields!groupby2databasefieldname.value is nothing,true,false)
now this trick does work good when the initial visibillity is expanded. But
when the initial visibility is collapsed, then the detials row always remains
hidden. Never shows up.
To put the problem precisely, I want to suppress the group header rows
conditionally; but details row should show up under all circumstances, even
if the initial state of details row is hidden.
Thanks|||Hi,
Is it possible for you to generate a sample rdl file using AdventureWorks
database to describe the issue more?
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
ONLINE before you click SEND), you may send the file to me directly and I
will keep secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Quick question Michael
One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
(interested in one of the features demonstrated).
How does one go about getting these (other than the chunking sprocs in the
default Report Server database)?
rob
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||all set (helped my developer...)
just 'edit' from the http interface and copy the .rdl into a VB.NET project.
thanks anyways
rob
"tutor" wrote:
> Quick question Michael
> One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
> (interested in one of the features demonstrated).
> How does one go about getting these (other than the chunking sprocs in the
> default Report Server database)?
> rob
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Is it possible for you to generate a sample rdl file using AdventureWorks
> > database to describe the issue more?
> >
> > I understand the information may be sensitive to you, my direct email
> > address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> > ONLINE before you click SEND), you may send the file to me directly and I
> > will keep secure.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> > ======================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Michael,
Do you have the rdl based on AdventureWorks for this reporting issue? I have
a similar need.
Thanks
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Did you resolve this problem? I have the exact same issue and would
appreciate any help.
"msdnuser" wrote:
> Hi all,
> I need a certain functionality in my Reporting service report.
> I need a report with 4 levels of grouping above the detail level. The
> initial visibility of all the lower levels should be hidden expect the top
> most level. For all the levels toggle item is set in the group properties.
> There should also be a boolean parameter called "expand all" which will
> affect the initial visibility of the report.
> This report should also have dynamic grouping facility with the help of 4
> parameters one for each level. Thus if the value passed to one of these
> groupby parameters is the word "none" then that group header row should not
> be shown. In all cases the detail row should be visible either by drilling
> down to that level or as a top level if all other levels are "none".
> I am partially successful in the above scenario by making the top level a
> mandatory one. i.e the top level can never be "none". However my problem is
> that if in the database a particular column supplied to report thorough one
> of the groupby parameters has all nulls then also the group header row should
> not show up. But when I try to put expression to control this using
> "isnothing" function, the detail row never shows up.
> Thus there are 3 boolean values to be controlled along with toggle in each
> group and detail section. How to achieve this?
> Please help me solve this issue.
> Thanks.