Replace report query

I design a report template which can serve for serveral reports. The
template and parameter are same, but the difference is 'select' statment. I
have another web application to retrieve different reports. Can I
dynamically change the report query by calling report web service ? I
can't find any example in RS books online. Is there any other example ?Hi May,
can you use a stored procedure and handle the logic internally (making the
results parameter driven)? That way your report is always pointing to the
same database object.
regards
Matt
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> I design a report template which can serve for serveral reports. The
> template and parameter are same, but the difference is 'select' statment.
I
> have another web application to retrieve different reports. Can I
> dynamically change the report query by calling report web service ? I
> can't find any example in RS books online. Is there any other example ?|||This is one way of doing it. Alternatively, you could:
1. Call SetReportDefinition SOAP API to update the report query.
2. Use an expression-based query. For example, you can check User!UserId and
if it gives you the web app identity change the report query respectively.
Or, you could use parameters to control your query statement.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> Hi May,
> can you use a stored procedure and handle the logic internally (making the
> results parameter driven)? That way your report is always pointing to the
> same database object.
> regards
> Matt
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > I design a report template which can serve for serveral reports. The
> > template and parameter are same, but the difference is 'select'
statment.
> I
> > have another web application to retrieve different reports. Can I
> > dynamically change the report query by calling report web service ? I
> > can't find any example in RS books online. Is there any other example
?
>|||Is there any example ?
Thanks !!!
"Teo Lachev" wrote:
> This is one way of doing it. Alternatively, you could:
> 1. Call SetReportDefinition SOAP API to update the report query.
> 2. Use an expression-based query. For example, you can check User!UserId and
> if it gives you the web app identity change the report query respectively.
> Or, you could use parameters to control your query statement.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > Hi May,
> >
> > can you use a stored procedure and handle the logic internally (making the
> > results parameter driven)? That way your report is always pointing to the
> > same database object.
> >
> > regards
> >
> > Matt
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > I design a report template which can serve for serveral reports. The
> > > template and parameter are same, but the difference is 'select'
> statment.
> > I
> > > have another web application to retrieve different reports. Can I
> > > dynamically change the report query by calling report web service ? I
> > > can't find any example in RS books online. Is there any other example
> ?
> >
> >
>
>|||Anyone can help me ? I search this topic in the internet but there is no
related result. This problem annoy me so much and I have so many reports
that will be implemented using this approach.
Thanks a lot !!!
"May Liu" wrote:
> Is there any example ?
> Thanks !!!
> "Teo Lachev" wrote:
> > This is one way of doing it. Alternatively, you could:
> >
> > 1. Call SetReportDefinition SOAP API to update the report query.
> > 2. Use an expression-based query. For example, you can check User!UserId and
> > if it gives you the web app identity change the report query respectively.
> > Or, you could use parameters to control your query statement.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > > Hi May,
> > >
> > > can you use a stored procedure and handle the logic internally (making the
> > > results parameter driven)? That way your report is always pointing to the
> > > same database object.
> > >
> > > regards
> > >
> > > Matt
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > > I design a report template which can serve for serveral reports. The
> > > > template and parameter are same, but the difference is 'select'
> > statment.
> > > I
> > > > have another web application to retrieve different reports. Can I
> > > > dynamically change the report query by calling report web service ? I
> > > > can't find any example in RS books online. Is there any other example
> > ?
> > >
> > >
> >
> >
> >

Replace Parameter Toolbar with a Custom Toolbar

Someone told me I could replace the parameter toolbar with my own custom
toolbar. How do you create/use a custom toolbar?Hi JrMcG,
Thank you for your posting!
Based on my scope, I don think you could replace the parameter toolbar
directly in reporting services.
The parameter toolbar is the default browser behavior and the report viewer
does not expose a way to override it.
Also, I found that you have submit another post about the conditionally
load drop down in parameter toolbar. I replied that issue in that thread. I
wonder does that issue the main concern from you. Please let me know the
result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Hi JrMcG,
Have you got any chance to check the suggestion I provided? Please feel
free to let me know if you need any help! Thank you!
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Yes I think I am OK. I went with loading all parameters with valid values,
forcing user to pick something valid thus not needing custom toolbar.
However, we are reading in other places that a custom tollbar can be done.
Thanks for your help.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:j38shYknGHA.2024@.TK2MSFTNGXA01.phx.gbl...
> Hi JrMcG,
> Have you got any chance to check the suggestion I provided? Please feel
> free to let me know if you need any help! Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hi JrMcg,
Glad to hear you resolved this issue. Based on my research, there is not
any Microsoft Offical document places that you could customize the
parameter toolbar. You could hide the report toolbar when you render, but I
don't find any document placed the customization of the parameter toolbar.
If you have any questions, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community 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.sql

Replace parameter doesnt work

I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable:

Dim cmd As New SqlCommand("SELECT'+@.LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode", MyConnection)
cmd.Parameters.Add(New SqlParameter("@.UserCode", UserCode))
cmd.Parameters.Add(New SqlParameter("@.LanguageColumnName", LanguageColumnName))

I have tried'+@.LanguageColumnName+' and also just @.LanguageColumnName but this variable isnt replaced for some reason.

The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:

SELECTde FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode

What am I doing wrong?

Take a look at your SqlCommand, it looks like:

"SELECT'de' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode"

So you will get a set of 'de' strings. You're on the right direction, but not yet the proper way of using dynamic SQL. You can use EXECUTE command to execute dynamic built SQL statement, for example:

Dim cmd As New SqlCommand("EXEC('SELECT'+@.LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
"WHEREb.UserCode=@.UserCode')", MyConnection)
cmd.Parameters.Add(New SqlParameter("@.UserCode", UserCode))
cmd.Parameters.Add(New SqlParameter("@.LanguageColumnName", LanguageColumnName))

Rendering without parameter values supplied give error

Hi

I hope someone can help me out here. I'm quite new to RS so perhaps this is just me not knowing something obvious.

I have a report with several parameters, most of which have values the user can select from a list, supplied by a query. Some of these lists are filtered depending on the value of other parameters. (Eg One list has all customers, one list has all orders. If I select a customer, the order list changes to only contain orders for the selected customer.) Some of the parameters are just float or date values that do not rely on any other parameters being selected first (eg OrderDate). Some parameters have default values and some do not.

When I first go into the report in Report Manager it is fine and does not automatically render - as not all parameters have values supplied.

The problem is that as soon as I select a parameter value from the list when that value is part of the query for another parameters values, the report renders automatically - without some of the other parameters being supplied yet - and consequently errors with the rsReportParameterTypeMismatch error. (Eg I select a customer and the report renders before I can enter the OrderDate parameter.)

How can I stop the report automatically rendering before I have entered/selected values for all the parameters?

Thanks, Phaedra

I am having a very similar issue with default values creating a large report that takes a long time to render. I would like to prevent the report from firing on load and for it to wait until the "view report" button is actually pressed. Any suggestions would be welcome.

Rendering without parameter values supplied give error

Hi

I hope someone can help me out here. I'm quite new to RS so perhaps this is just me not knowing something obvious.

I have a report with several parameters, most of which have values the user can select from a list, supplied by a query. Some of these lists are filtered depending on the value of other parameters. (Eg One list has all customers, one list has all orders. If I select a customer, the order list changes to only contain orders for the selected customer.) Some of the parameters are just float or date values that do not rely on any other parameters being selected first (eg OrderDate). Some parameters have default values and some do not.

When I first go into the report in Report Manager it is fine and does not automatically render - as not all parameters have values supplied.

The problem is that as soon as I select a parameter value from the list when that value is part of the query for another parameters values, the report renders automatically - without some of the other parameters being supplied yet - and consequently errors with the rsReportParameterTypeMismatch error. (Eg I select a customer and the report renders before I can enter the OrderDate parameter.)

How can I stop the report automatically rendering before I have entered/selected values for all the parameters?

Thanks, Phaedra

I am having a very similar issue with default values creating a large report that takes a long time to render. I would like to prevent the report from firing on load and for it to wait until the "view report" button is actually pressed. Any suggestions would be welcome.

Rendering report

I request a report from a web service to be rendered in html 4.0 format.
In the request i also set this parameter:
<DeviceInfo><HTMLFragment>true</HTMLFragment>
The rendering is ok, but the problem is that the images in the report are
not displaied.
Any ideas?
Thanks!
--
OSVALDO COLITTIOsvaldo,
With SOAP you need to download the images explicitly. The most flexible
approach I could have come up with that works accross XP and Windows 2003 is
explained in chapter 11 of my book. It involves writing an ASP.NET-based
image handler page and setting StreamRoot to it. If you don't feel like
buying my book, you can download the source code from the publisher website.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:09B91460-6F93-48F2-B04D-36B2129617A5@.microsoft.com...
> I request a report from a web service to be rendered in html 4.0 format.
> In the request i also set this parameter:
> <DeviceInfo><HTMLFragment>true</HTMLFragment>
> The rendering is ok, but the problem is that the images in the report are
> not displaied.
> Any ideas?
> Thanks!
> --
> OSVALDO COLITTI
>