August 18, 2008 at 7:27 am
I have a report built in Reporting Services that references a stored procedure in SQL Server. I have this one parameter that works in SQL Server, works in the Data tab of Reporting Services but will not work on the Preview tab. It sets a sort order and no matter what I select on Preview tab, I get one sort order.
I have tried it as non-queried list (of two items) and I've tried it as a query parameter from a table that I populated with these two items. Regardless, it works in the first two places but not the Preview tab. Has anyone got any ideas on this? Thanks.
August 18, 2008 at 7:39 am
Can you post the code?
Are you doing any grouping or sorting on the report? Obviously sorting on the report will override any sorting in the dataset, and grouping can affect sorting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 8:03 am
I'm not doing any specific sorting but I am doing groupings. I have acct_unit and account and want the user to pick what is the top grouping level. The table defaulted to group 1 being acct_unit and group 2 being account and group 3 being subaccount (which is fine). So do I just remove the Grouping on the Properties for Levels 1 and 2 or is it more involved than that?
August 18, 2008 at 8:09 am
That will depend on what your desired sort order is. You will need to experiment with it as well. How are you implementing the sorting in the SQL Server procedure? Are you sure the parameter is being passed to it correctly? You may want to run Profiler when you run this report to see that everything is happening as you expect.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 8:13 am
I don't have permission to run Profiler. I built the procedure so that based on the grouping parameter it hits an if/else that builds all the code based on their choice. It works in SQL Server and the Data tab, just not Preview. So it has to be related to those grouping lines, but I don't know how to make them dynamic. Hmm....
August 18, 2008 at 8:33 am
diana.rhoades (8/18/2008)
I don't have permission to run Profiler. I built the procedure so that based on the grouping parameter it hits an if/else that builds all the code based on their choice. It works in SQL Server and the Data tab, just not Preview. So it has to be related to those grouping lines, but I don't know how to make them dynamic. Hmm....
The reason I recommended profiler is that in SQL Server and the Data tab you are directly supplying the parameter and not passing it from the report and I would want to verify that the parameter is being correctly passed from the report.
You can make grouping dynamic by using an expression in the groups. Something like:
IIF(Parameters!Sort.Value = "A", Fields!A.Value, Fields!B.Value
You would repeat that for each member of the group.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 8:41 am
Yep, I've been working on that. I put it Grouping/Sorting for rows 1 and 2.
=iif(Parameters!@GroupBy.Value="Account",Fields!acct_unit.Value,Fields!account.Value)
I put it in 1) Group on: Expression 2) Document map label (same screen), 3)Sorting Expression.
These are my current errors (I'm not an experienced VB coder):
[rsCompilerErrorInCode] There is an error on line 5 of custom code: [BC42021] Function without an 'As' clause; return type of Object assumed.
[rsCompilerErrorInExpression] The SortExpression expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
[rsCompilerErrorInExpression] The Label expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
[rsCompilerErrorInExpression] The GroupExpression expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
[rsCompilerErrorInExpression] The SortExpression expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
[rsCompilerErrorInExpression] The Label expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
[rsCompilerErrorInExpression] The GroupExpression expression for the table ‘table1’ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Build complete -- 6 errors, 2 warnings
August 18, 2008 at 8:54 am
[rsCompilerErrorInCode] There is an error on line 5 of custom code: [BC42021] Function without an 'As' clause; return type of Object assumed.
This error is in VB.NET Code that is embedded in the report using the custom code feature. What is that code doing? The error says that you need to tell the function what it is returning like this:
Public Function DoSomething(Paramter as Int) as String
=iif(Parameters!@GroupBy.Value="Account",Fields!acct_unit.Value,Fields!account.Value)
Is your RS parameter actually named with an "@" included in the name? Not that you can't, I just have never done that.
I'd just add the dynamics to one thing at a time to make sure it is working and then move on to the next one.
It also looks like you tried to write the IIF function in custom code and, if you did, you don't need to as it is a built-in function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 8:59 am
I edited my reply but you didn't see it. I realized that the @ needed to be removed and then it ran AND grouped correctly.
I'm interpreting what you're saying is that I could have left off the =iif--I'll have to experiment with that.
Thank you very much for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply