Cannot modify "credentials stored securely in the report server" and always get the error - "the password is not valid"

Problem:

I have a data source using the "credentials stored securely in the report server" option. Initially it uses a windows login. Everything works fine, no problem.

Then I test this option with a SQL login. The login has a corresponding user in the msdb, report server, and AdventureWorks2008 databases in the db_datareader membership.

I have also changed the authentication mode to mixed, and tested the SQL Login working well.

However, in report manager, when I try to modify the "credentials stored securely in the report server" from the existing Windows login to the new SQL login for the data source, I always get an error: The password is not valid. Please retype the password. After I click the "Apply" button, it has no effect, the credential is back to the Windows login.


Solution (Workaround):

1. Create a new data source, mimicking the original data source except for using the SQL login
2. Make the report(s) using the new data source with the new login
3, (Optional) After all reports have been linked to the new data source, you can delete the original data source and rename the new one with the original name.



Use Expression and Custom Assembly in SSRS

1.    Extending Report Properties by using Expressions

·        Use expressions to calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.

·        You write expressions in Visual Basic.

·        You start an expression with an equal sign (=) and then use built-in collections, including dataset fields and parameters, constants, functions, and operators.

·        In a complex expression, you can reference multiple dataset fields, parameters, operators, constants, built-in report functions, Visual Basic runtime library functions, Microsoft .NET Framework common language runtime (CLR) classes, embedded custom code functions, and functions from a custom assembly.

·        Several common built-in expressions types:

o   Field

=Fields!LastName.Value

o   Control flow

=Choose(Fields!NameStyle.Value, “Mr”, “Mrs”, “Miss”)

=IIF(Fields!Amount.Value < 0, “Red”, “Black”)

=Switch(Fields!Amount.Value< 0.90*Fields!Goal.Value, “Red”,

Fields!Amount.Value<Fields!Goal.Value, “Yellow”

, Fields!Amount.Value>=Fields!Goal.Value, “Green”)

o   Built-in function

=Sum(Fields!Quantity.Value)

=(Rate(Fields!TotPmts.Value, - Fields!Payment.Value, Fields!PVal.Value)* 12) * 100

·        Using the <Code> Element in a Report

o   Steps

§  Create the code - Report menu |Report Properties | Code tab | Write the VB Code

§  Use the code

=Code.GetStockColor(Fields!Quantity.Value, Fields!ReorderPoint.Value)

o   Benefits

The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.

2.    Extending SSRS with Custom Assemblies - You can use any .NET languages

·        Create a custom assembly with a full blown Visual Studio and Build the .dll file

·        Deploy the assembly.

o   Copy the assembly file to the application folders of your SSRS server and the Report Designer.

o   Install your custom assembly in the global assembly cache (GAC).

·        Optionally, added the appropriate security policy.

·        Add a reference to the assembly in your report.

·        Use the assembly - using expressions to access the members of the classes in the assembly

o   You call a static method in your expression by using the format =Namespace.Class.Method, as this example shows:

=CustomColors.CustomColor.GetStockColorStatic(Fields!Quantity.Value,Fields!ReorderPoint.Value)

o   For calling instance-based members, you must instantiate an object of your class type. You add an instance name for a class by using the Add Or Remove Classes section of the Code tab in the Report Properties window. Provide a class name and an instance name. You can then refer to your instance-based members by using the Code keyword followed by the instance and member name, as in the following example:

=Code.MyCustomColor.GetStockColorInstance(Fields!StockLevel.Value, Fields!SafetyStockLevel.Value)

Dataset Filter, Dataset Parameter, and Report Parameter

1.  When to Use Filters at the Dataset and When to Use Query Parameters?

Filters at the dataset are very similar to parameters because they affect the data that is presented to all regions. However, filters always occur after data has been retrieved from the data source and might not perform as well as parameters because the query pulls all the data, not just the specific data that the user needs. Thus, from a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.

The main advantage of filters is that information can be shared between multiple users if you configure the report to use report execution snapshots. In report execution snapshots, the first execution of the report pulls the data from the server, and successive executions use the snapshot information stored in the report server. So the best practice is not to use filters at the dataset unless you plan to use report snapshots.  In other words, use query parameters instead to filter information before it gets to the report.

Filters at the dataset can be configured as follows:

·       Select the Report Data window.

·       In the Report Data window, right-click the dataset you want to filter, and then select Dataset Properties.

·       In the Dataset Properties dialog box, click the Filters tab.

·       In the Change Filters pane, click Add.

·       Configure the filter expression, data type, operator, and value. If your filter expression is simple, such as a single data field, the data type is assigned automatically from the field data type.

Filters at data regions affect only the report item they are filtering, and different data regions can apply different filters to the same dataset. Additionally, filters can also be for a data grouping.

2.  When to Use Query parameters and When to Use Report parameters?

A query parameter, also called a dataset parameter, is a variable defined at the dataset level that affects the way SSRS queries the data source. The main purpose of a query parameter is to filter data in the data source.

A report parameter is a variable defined at the report level that allows the personalization of a report at run time. The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select. Report parameters are often mapped to query parameters. For example, the user can select a department and use the report parameter to assign the query parameter value and filter expenses by departments. In addition, if a data source does not support query parameters, you can use report parameters to give users the ability to filter data after it has been retrieved for the report.

Filtering datasets after the data has been retrieved is also useful if you are using report snapshots. With report snapshots, SSRS caches the data and does not retrieve it from the source again to satisfy a user rendering request. If you want to present only a subset of data to the user, you need to use report parameters and filter the data in the report dataset or in any report data region.

The Differences between Query and Report Parameters

Query parameters

Report parameters

Parameter runs on

Database server

Report server

Primary purpose

of parameter

To filter data

To interact with user

Parameter properties

Name and value (can be an expression)

Name, type, value, prompt, available values, default values, and so on

Parameter is managed through

Dataset

Report Parameters dialog box

3.  How to Create a Query Parameter or Dataset Parameter?

a.   Option 1 via Dataset Properties – Your Dataset (right-click)| Dataset Properties | Query or Parameter.

·       In BIDS, open a project and then open a report.

·       In the Report Designer, open the Report Data window by using the tab displayed on the left side of BIDS (where you also access the Toolbox window). If the Report Data tab is not there, use the BIDS View menu and select Report Data to open this window.

·       Change the query syntax to add the parameters. Right-click a dataset to which you want to add parameters, and select Dataset Properties. On the Query tab in the Dataset Properties dialog box, edit the query to add the parameters. The SQL Server client uses the at symbol (@) as the first character of the name to define the parameters in the query. You can use parameters in the WHERE clause of the T-SQL query.

·       In the Dataset Properties dialog box, click the Parameters tab.

·       On the Parameters tab, assign a value to each query parameter.

b.   Option 2 via Graphical Query Designer (works for MDX as well) – Your Dataset (right-click)| Query.

·       In the Report Designer, open the Report Data window.

·       Right-click the dataset, and then select Query.

4.  How to Create a Report Parameter?

a.   Option 1 – via the Parameter folder in the Report Data window

·       In the Report Designer, click the Design tab, if necessary.

·       In the Report Data window, right-click the Parameters folder, and then select Add Parameter.

·       If you expand the Parameters folder, you can right-click an already defined report parameter and delete it or edit its properties.

b.   Option 2 – via Query Parameters

By default, a report parameter with an equivalent name is created for each query parameter, and query parameter values are assigned to corresponding report parameters. End users can change report parameters when they run the report, and SSRS replaces query parameters with report parameter values when executing the query. This is usually what you need; nevertheless, you could assign a constant or an expression to a value of a query parameter.

5.  Working with Report Parameter

a.   How to Assign a List of Values That Users Can Select From - Binding a Dataset to a Parameter?

Your Report Parameter (right-click)| Report Parameter Properties | Available Values.

b.   How to Use Multivalued Parameters

Your Report Parameter (right-click)| Report Parameter Properties | General | Allow Multiple Values.

c.   How to Set Parameter Defaults?

Your Report Parameter (right-click)| Report Parameter Properties | Default Values.

d.   How to supply the parameter values through the URL used to run parameterized reports?

http://localhost/ReportServer?%2fLogistics%2fTracking+Numbers+by+Month&rs:Command=Render&Department=12

·       Report server URL: http://localhost/ReportServer? The report server name and protocol will reflect your specific installation. The question mark begins the parameter section.

·       Report path: %2fLogistics%2fTracking+Numbers+by+Month& The path includes the folder and report name; for your reports, replace the names appropriately. Use %2f to replace the slash mark (/) and %20 to replace spaces.

·       Command section: &rs:Command=Render The command section configures the action of the report. The & symbol at the beginning separates the command section from the report path.

·       Parameters section: &Department=1 Use the & symbol to separate parameters, and use a name and value pair to configure the parameter. If you have multivalued parameters, you need to repeat the name and value pair once for each parameter.

·       If you want to hide parameters in the report, add &rc:parameters=false to the URL. You can also hide the toolbar completely by adding &rc:toolbar=false.