Report Viewer control ignores parameters

I have a Report Viewer control on an asp.net page. The report shows the
parameters. When I choose a value from the drop down and then click on "View
report", the screen gets refreshed and the parameters are reset. It's like
no parameters have been selected from the drop down and no data is shown. If
I run the same report from report manager, or from VS, everything works. Has
anybody had this issue?
Thanks,
GilgameshOn Aug 28, 4:27 pm, "Gilgamesh" <gilgamesh4e...@.aol.com> wrote:
> I have a Report Viewer control on an asp.net page. The report shows the
> parameters. When I choose a value from the drop down and then click on "View
> report", the screen gets refreshed and the parameters are reset. It's like
> no parameters have been selected from the drop down and no data is shown. If
> I run the same report from report manager, or from VS, everything works. Has
> anybody had this issue?
> Thanks,
> Gilgamesh
You might want to check your parameter mappings to the main
datasource. It sounds like there's a breakdown there somewhere. Also,
make sure that you are not receiving an error in the database
connection string, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Report Title in Report manager Subscription

Hi,

Is there a way of passing a default parameter value into the subject title on a report subscription?

Currently there are std global variables i.e. @.ReportName was executed at @.ExecutionTime, but what other variables can be passed here to make it more relevant to the recipient?

Thx

so....could do with some help on this, but no replies as yet...

What I have found out is that only some of the global variables can be used such as the ones above, but not all of them.

What other options are there?

Has anyone tried modifying this or is it restricted to @.ReportName and @.ExecutionTime?

any help appreciated.

Thx

Report Server 2005 - How to SUM value by condition

hi, i need some help on this.

I create Matrix that show the sales and cancels of the sales department.

The TOTAL in the Matrix are fine, but i need to show also how many Sales

(param is bigger then 0) and how many cancels (param is negative, smaller the 0) each

sales man have in the Matrix report. i try to use IIF statement but it only return absolute number:
--
="Sales :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Total :" & IIF(sum(Fields!SalesCommission.Value)<0, "(" & Format(ABS(sum(Fields!SalesCommission.Value)),"F1") & ")" ,sum(Fields!SalesCommission.Value))
--

I think i will need to use Costum Code on this one.

anny advice ?

Thanks

It looks like your IF statements are backwards from what you want. Your output for your "Sales:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is less then 0. Your output for your "Cancels:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is greater then 0. This is because IIF( [EXPRESSION], [TRUE PART], [FALSE PART]). So it should look like:

="Sales :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &

See what that does for you. Maybe I misunderstood/misread what you wanted for that part. Try and see what that does for you.

|||

Thanks, you are right, i replace the Sales and Cancels places.

I try your sample but again, the Matrix row have both Sales and Cancels in the Matrix TOTAL boxes and the EXPRESSION return the Total of the Sales O.K but the Total of the Cancels is 0 (zero).

What i need is to show in the MATRIX table the totals per salesman (Sales - (Minus) Cancels) and the clculatet Braek of

For exsaple:

Salse: 12.5

Cancels: -6.5

Total: 6

Anny Advice ?

|||So a "Cancels" is commision under 0 dollars. And a "Sales" is commission over 0 dollars. So you want to find how much commission total each sales person made? If I understand you correctly, all you need to do is sum(Fields!SalesCommission.Value) for your total. That should take care of everything. I hope this helps!

Report Server 2005 - How to SUM value by condition

hi, i need some help on this.

I create Matrix that show the sales and cancels of the sales department.

The TOTAL in the Matrix are fine, but i need to show also how many Sales

(param is bigger then 0) and how many cancels (param is negative, smaller the 0) each

sales man have in the Matrix report. i try to use IIF statement but it only return absolute number:
--
="Sales :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Total :" & IIF(sum(Fields!SalesCommission.Value)<0, "(" & Format(ABS(sum(Fields!SalesCommission.Value)),"F1") & ")" ,sum(Fields!SalesCommission.Value))
--

I think i will need to use Costum Code on this one.

anny advice ?

Thanks

It looks like your IF statements are backwards from what you want. Your output for your "Sales:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is less then 0. Your output for your "Cancels:" will return sum(Fields!SalesCommission.Value) when Fields!SalesCommission.Value is greater then 0. This is because IIF( [EXPRESSION], [TRUE PART], [FALSE PART]). So it should look like:

="Sales :" & (IIF(Fields!SalesCommission.Value>0, sum(Fields!SalesCommission.Value), 0)) &
"Cancels :" & (IIF(Fields!SalesCommission.Value<0, sum(Fields!SalesCommission.Value), 0)) &

See what that does for you. Maybe I misunderstood/misread what you wanted for that part. Try and see what that does for you.

|||

Thanks, you are right, i replace the Sales and Cancels places.

I try your sample but again, the Matrix row have both Sales and Cancels in the Matrix TOTAL boxes and the EXPRESSION return the Total of the Sales O.K but the Total of the Cancels is 0 (zero).

What i need is to show in the MATRIX table the totals per salesman (Sales - (Minus) Cancels) and the clculatet Braek of

For exsaple:

Salse: 12.5

Cancels: -6.5

Total: 6

Anny Advice ?

|||So a "Cancels" is commision under 0 dollars. And a "Sales" is commission over 0 dollars. So you want to find how much commission total each sales person made? If I understand you correctly, all you need to do is sum(Fields!SalesCommission.Value) for your total. That should take care of everything. I hope this helps!

Report running really really slowly!!

Hi, I have a report with 18 cascading report parameters. Each report parameter has a unique dataset which passes the value of the previous parameter into the sql string. As I am selecting the report parameters it is taking longer to query the further down I go. I think this is because of the number of where conditions that are being passed through the sql query. The last report parameter is passing 17 where conditions.

In access when I have done this the parameters near the bottom were being refreshed quicker than the top ones - why is it the opposite way round in reporting services? Any ideas of how to speed this process up?

probobaly becuase in access the data is cached locally.

|||How do you have your parameters structured? Are you building the sql string dynamically? If you give a few more details/examples, I might be able to point you in a direction with less overhead...|||

I wont list all my report parameters, but to show you what I am doing I will list the first few. I have parameters:

Publication, Division, Product Manager.

Parameter Publication is just bringing back a list of publications, divison is bringing back a list of divisions where publication is in paramter publication, i.e

="SELECT
division_code
FROM base_table
WHERE publication_code in ('
" & Parameters!par_pub.Value & "
') " &
"
GROUP BY 1
ORDER BY 1"

Product Manager is bringing back a list of product managers, where publication is in parameter publication and where division is in parameter division, ie

="SELECT
product_range_manager_code,
trim(product_range_manager_desc)as product_range_manager_desc
FROM base_table WHERE division_code IN ('
" & join(Parameters!par_div.Value," ','
") & "
') " &
"

AND publication_code in ('

" & Parameters!par_pub.Value & "

') " &

"
GROUP BY 1,2
ORDER BY 1,2"

Like I said this goes on, so the 4th parameter will contain 3 WHERE conditions until the 18th parameter contains 17 where conditions. My main dataset which populates my report table looks at each of the parameters and filters based on these values. Oh also as you probably have guessed all the processing is done on the server. Hope you can help.

|||First of all, I think that you will see some speedup if you make stored procedures. Depending on the database structure, and where the bottle-neck is that speedup could be considerable or not. It seemed that you were pulling all of your data from one base table? If that is the case, you could write one stored procedure that would populate each dropdown, with a where clause of the form:

Code Snippet

WHERE foo IN (@.param1) AND (@.param2 IS NULL OR bar IN (@.param2)) AND ...

You should also verify whether there are any dependencies in your parameters that you can exploit to reduce calls to the database. In other words check if selecting a particular parameter is the same as selecting another one downstream (1 to 1), in which case that second filter (and clause) does not need to be applied at all. Similarly, verify that you need the previous filters applied once you know the downstream selections. For example, if all publications are produced by only one division, then in the second query you supplied you do not need to filter for the divison code at all.

Since you are not aggregating any columns, the group by clause is unnecessary. The SQL compiler should have caught that, so eliminating it probably won't save you anything, but it never hurts to remove unnecessary bits.

Finally, if these steps still result in a report that takes too long to refresh, you have a couple of options. You can create a drill-through report, wherein you actually navigate to a child report with fewer parameters, and you can look at improving your database structure to optimize this sort of activity, e.g. via normalization and adding indexes.

I hope this helps.

Report Renders w/out parameters being selected

I have a report with a parameter list. The parameter has no default value.
When I open the report it renders without waiting for the parameter to be
selected and the parameter list is empty.
I ran SQL Profiler and the sproc that is used to populate the parameter list
is being run and I verified in Query Analyzer that the sproc is returning 4
values.
This same report runs ok on other environments (I have double checked an
uploaded the same RDL to multiple servers).
Any ideas? Is there a server setting that would control this?Try to delete report from server and re-deploy from report designer.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <noemail@.noemail.com> wrote in message
news:esAZuAcnFHA.1996@.TK2MSFTNGP10.phx.gbl...
>I have a report with a parameter list. The parameter has no default value.
> When I open the report it renders without waiting for the parameter to be
> selected and the parameter list is empty.
> I ran SQL Profiler and the sproc that is used to populate the parameter
> list is being run and I verified in Query Analyzer that the sproc is
> returning 4 values.
> This same report runs ok on other environments (I have double checked an
> uploaded the same RDL to multiple servers).
> Any ideas? Is there a server setting that would control this?
>

Report Refresh Problem

Hi,
I had a report wherein it was taking a single parameter value and outputting
the data. In addition I pass other parameters to this report. Now the
functionality is that it can take multiple values in addition to the existing
params. The multiple value list is not passed into the report but the way I
have implemented this is:
1. Load all the values into a listbox and then load it into a table.
2. The stored procedure looks for the entries in the table and builds the
sql statement and outputs the data.
What's happening is this for the first time when the report runs it is fine
and I get the data that I want. When I add another value into the report or
remove the data, the report doesn't get refreshed unless I chnage one of the
parameters(dropdown or date).
Any clue why this is happeing.. I really appreciate your inputs.
Thanks
Suresh
Whn i first select the report to runThe dataset for those parameters is already created for the first set of
parameters. Changing the parameters is how you change the data.
--
| Thread-Topic: Report Refresh Problem
| thread-index: AcWCTExfmZtzyhPaRcKY0HoFtDt+Rw==| X-WBNR-Posting-Host: 161.88.255.139
| From: =?Utf-8?B?U3VyZXNo?= <Suresh@.discussions.microsoft.com>
| Subject: Report Refresh Problem
| Date: Wed, 6 Jul 2005 10:01:06 -0700
| Lines: 23
| Message-ID: <65901F85-6FE9-497F-A0D5-0D9DD1C7BAC9@.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: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47502
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
| I had a report wherein it was taking a single parameter value and
outputting
| the data. In addition I pass other parameters to this report. Now the
| functionality is that it can take multiple values in addition to the
existing
| params. The multiple value list is not passed into the report but the way
I
| have implemented this is:
|
| 1. Load all the values into a listbox and then load it into a table.
| 2. The stored procedure looks for the entries in the table and builds the
| sql statement and outputs the data.
|
| What's happening is this for the first time when the report runs it is
fine
| and I get the data that I want. When I add another value into the report
or
| remove the data, the report doesn't get refreshed unless I chnage one of
the
| parameters(dropdown or date).
|
| Any clue why this is happeing.. I really appreciate your inputs.
|
| Thanks
| Suresh
|
| Whn i first select the report to run
|