Parameter in Datasource Connection

Achilie · 2017-01-24 11:07:18

Hi,

i am new to ReportServer.
I have searched the Documentation and the forum but could not find a solution.
I have an Oracle-Datasource configured and can connect and run a CrystalReport with
this simple select: select sysdate, user from dual.
What i want to do is to parametrize the login of the datasource because
we have a virtual private database set up.
The user has the same login for ReportServer and the Database.
I want to use this 2 parameters for user and password.

Can someone point me in the right direction?

Thanks in advance
Joachim

karolina · 2017-01-24 18:10:09

Hi Joachim,

Does it work in a way that the query result depends on which user connects to the database?

If so, I think that the database bundle feature could be the option. Usually the database bundle is used when you want to execute the same report on different databases, but actually it is executing the same report using different datasource definitions (which can point to the same database, but with different connection parameters).

Cheers,
Karolina

Achilie · 2017-01-25 10:01:22

Hi Karolina,

Yes, the result depends on the connected user. There are Databaserules which are applied to the current user.
I was hoping the datasource can be parametrized using the login information for the ReportServer.

As i understand there will be a dropdown when using the database bundle.
So user A can not only connect as user A but also as user B?

I will give it a try using Report Parameter Provider and Static Mapping.

Thanks
Joachim

karolina · 2017-01-25 15:20:11

Hi Joachim,

Thanks for the information. This is quite complex task to implement (i.e. it involves many ReportServer features), so I'd like to understand your requirements well and we will have to split it into a few steps.

Concerning the dropdown list - I'll check if it is possible to limit it in a way that user A can connect as user A only and B as user B (it should be possible). I'll get back to you when I know more.

Karolina

wonky · 2017-01-25 16:17:40

Joachim, You seem to be working on some of the same issues that my team and I are working on. We are using datasource bundles and parameters successfully against Crystal Reports as well. I'll be interested to follow your progress. The query I posted today, for retrieving Teamspaces by user may help you in selecting the datasource.

karolina · 2017-01-26 18:56:00

Hi Joachim,

To follow up:

I assume that:
- you have created several database datasources with user names same as RS user names,
- there is only one datasource with a particular user name same as a particular RS user name,
- all the users have granted read and execute rights to the datasources (this can be done at datasource folder level)
- all these datasources are placed in one folder that will be used for the bundle

(if you need additional datasource definition for the database, just use different user name that any RS user name and place this datasource definition in another folder)

A. If you use static mapping the steps are:

1. create a datasource bundle, choose Parameter as Key provider, 'DS' as Parameter Key and static mapping. As keys in the static mapping please put user names.
2. create a report with the bundle as datasource
3. create a datasource parameter with a key 'DS'
in the parameter config details:
- choose the internal ReportServer database as datasource
- in the query field put

SELECT ${_RS_USER.getUsername()} AS username

- choose java.lang.String as datatype
- choose 'Single selection' mode and the dropdown list as the selection style

When a user logs in and chooses this report, he/she will see a dropdown list with one option to choose from, being his or her username.

Comments:
1. If there will be a RS user without a datasource user and he/she tries to execute the report, the dropdown list will be empty and the report execution attempt will result in error - make sure that such users are not allowed to execute the report.
2. The _RS_USER.getUsername() parameter is a special parameter. You will find more information in the chapter 6.3.13 of the Administrator Guide.

B. If you choose auto mapping (here please use only the 'DS-node (by ID)'):

1. First choose the static mapping, but leave the key empty. As a datasource choose folder that contains the datasources that should be in the bundle; save the configuration.
2. Switch to auto-mapping, 'DS-node (by ID)' option
3. create a report with the bundle as datasource
4. create a datasource parameter with a key 'DS'
in the parameter config details:
- choose the internal ReportServer database as datasource
- in the query field put

SELECT ds.id, df.name_field
FROM rs_database_datasource ds
INNER JOIN rs_datasource_definition df ON (ds.id = df.id)
WHERE ds.username = ${_RS_USER.getUsername()}

- choose java.lang.Long as datatype
- choose 'Single selection' mode and the dropdown list as the selection style
When a user logs in and chooses this report, he/she will see a dropdown list with one option to choose from.
The label in the dropdown list will be the datasource name.

Comments:

1. if there are more datasources with a particular user name, a user will see all of them in the dropdown list - make sure that the report can be executed on all such datasources without errors
2. if there are datasources that have user names the same as RS user names and such datasources are outside the folder used in the data bundle config, in some cases this will lead to errors, as users will
see them in the list, but these datasources are not included in the bundle

Hope it helps.
If anything is unclear or you need more information, please don't hesitate to ask.
Please let me know if it works in your case - I don't have posibility to test it with Oracle database and Crystal reports (I used PostgreSQL & dynamic list for testing)

Karolina

Achilie · 2017-01-30 07:10:14

Hi Karolina,

Thanks for this detailed description. I will try it as soon as possible and give a feedback.

Joachim

Achilie · 2017-02-03 13:50:05

Hi Karolina,

i have tried both solutions without success.
I come to Parameterkonfiguration and the DropDown only shows one (my) DataSource as described.
When i go to Vorschau, i get the error:
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: Der Bericht konnte nicht ausgeführt werden: Could not open connection to: null with user: null. null
Seems there is an assignment missing?

Best regards
Joachim

karolina · 2017-02-03 14:57:35

Hi Joachim,

:-/

Looks like you have the same issue as described here (i.e. there is a bug with Crystal Reports & bundle; it will be fixed in the new RS release)

Karolina

Achilie · 2017-02-06 08:09:27

Hi Karolina,

i have tried it with a BIRT-Report and it worked as described. Thank you.
So the question is, when will be the next RS release?

Joachim

karolina · 2017-02-06 08:19:12

:-)
When we kill all known bugs and ones that will show up on the way ;-)
No idea - Eduardo has mentioned the first half of 2017, so ask him directly for updates.

Achilie · 2017-07-17 08:12:25

Hi,

now, with ReportServer 3.0.3 (beta) Datasource Bundles worked as described.
My next problem is, that datasource parameter should work the same way, i.e. a datasource parameter needs a datasource bundle for the connection.

Parameter p_myValues (with a SQL like select * from myValues):
Mandant Value
1 A
1 B
1 C
2 D
2 E
2 F
3 G

Mandant 1 is only allwed to see A, B and C and so on.
Normally this comes from Oracles virtual private database without any additional work (once the rules are defined).

Best Regards
Joachim

jalbrecht · 2017-07-18 11:46:41

Hi Joachim,
i'm not quite sure whether i do understand your datasource parameter problem in detail.

- data source parameter can use data source bundels as data source. That way aequivalent tables containing different data rows in different databases can be accessed by different users using the same report (and therefore parameter).
- is the data for mandant 1 stored in the same table of the same database as for mandant 2 ?

Can you describe the problem in a little more detail ?

wbr jan

Achilie · 2017-07-18 12:22:43

Hi Jan,

we use a virtual private database with oracle.
The data is stored in one table and a user sees only the rows depending
on one column (i.e. mandant).
The datasource bundle works with the report as describes by Karolina.

Now mandant A would like to filter only 'A'-Values
so we create a filter as a datasource parameter with the sql 'select * from myValues'.
Because i cannot parameterize the datasource of this filter
(with the same technic as for the report) mandant A sees the whole table myValue and can
choose 'F' for the filter which results in 0 rows.
I think the username and password of a datasource parameter is fixed and cannot be changed at runtime.

Best Regards
Joachim

eduardo · 2017-07-18 12:53:19

Hi Joachim,

a little question, maybe I am still not understanding the whole problem:
but why don't you filter this table with a where statement? 'select * from myValues where value='A''

This 'A' can be a parameter, for example, a user variable as described here: https://forum.reportserver.net/viewtopic.php?pid=3079, #4.

Would this help?

Best regards,
Eduardo

eduardo · 2017-07-18 13:17:43

Hi Joachim,

also check here: you can use a user variable for this parameter. Together with the bundle, it should work.
https://forum.reportserver.net/viewtopic.php?id=618

Best regards,
Eduardo

eduardo · 2017-07-19 07:13:13

Hi Joachim,

Achilie wrote:

Because i cannot parameterize the datasource of this filter
(with the same technic as for the report)

You can parametrize datasource parameters by using cascading parameters: https://reportserver.net/en/guides/admi … cascading/
"By using cascading parameters you can use parameters within parameters and thus nest parameters. Source parameters can here be any parameters. At present, only the data source parameter supports the configuration by means of depending parameters. To do this, on the general configuration page of the data source parameter set the parameters on which the data source parameter will depend. In the following you can use them in the data source configuration as describe below."

I recreated this and I have in my datasource parameter the following:
select distinct * from myValues where Value = $P{myValue}

myValue is in my case a parameter of type text parameter, but it may be any parameter type you need.
Don't forget to add myValue as a dependent parameter: "Parameter properties" -> "Depends on"

Best regards,
Eduardo

ReportServer Forum

#1 2017-01-24 11:07:18

Parameter in Datasource Connection

#2 2017-01-24 18:10:09

Re: Parameter in Datasource Connection

#3 2017-01-25 10:01:22

Re: Parameter in Datasource Connection

#4 2017-01-25 15:20:11

Re: Parameter in Datasource Connection

#5 2017-01-25 16:17:40

Re: Parameter in Datasource Connection

#6 2017-01-26 18:56:00

Re: Parameter in Datasource Connection

#7 2017-01-30 07:10:14

Re: Parameter in Datasource Connection

#8 2017-02-03 13:50:05

Re: Parameter in Datasource Connection

#9 2017-02-03 14:57:35

Re: Parameter in Datasource Connection

#10 2017-02-06 08:09:27

Re: Parameter in Datasource Connection

#11 2017-02-06 08:19:12

Re: Parameter in Datasource Connection

#12 2017-07-17 08:12:25

Re: Parameter in Datasource Connection

#13 2017-07-18 11:46:41

Re: Parameter in Datasource Connection

#14 2017-07-18 12:22:43

Re: Parameter in Datasource Connection

#15 2017-07-18 12:53:19

Re: Parameter in Datasource Connection

#16 2017-07-18 13:17:43

Re: Parameter in Datasource Connection

#17 2017-07-19 07:13:13

Re: Parameter in Datasource Connection

Board footer