ReportServer Forum

DISSTeam

Member

Registered: 2020-09-11

Multi Company User

Hello

We have a multi-tenancy user who we want to be able to run a report for one or all of their sub-companies within the database.
This is defined by a variable $CompanyID

We can set the CompanyID to be a variable for every user and enter the default (0) - but if we have a user with 3 companies, say 1,2,3 - how can we specify this and then ensure it will run the query based on WHERE company_id = $CompanyID if the result is 1,2,3 (as this currently throws an error)

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

So I've worked this out but I wonder if it's possible to allow the user to define?

So I have set a user variable as CompanyID

Most users are 0 which is default
Some users are multi-company so list is 0,1,2,3

Then the query is
SELECT * FROM `customer_table` WHERE `company_ids` IN (${CompanyID})

This then searches for all companies comma separated.

Can I however, allow the user to select their company ID so if they want to run the report just for companyID 2 they can, or 2 and 3 they can, or 0 they can...etc.

They would however only be able to select from a pre-defined list in their profile, set up by an administrator?

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

you are correct, user variables are the way to achieve what you need

In order to allow the user to select their company ID, you can use a multivalue datasource parameter: https://reportserver.net/en/guides/admi … atasource/
In the query of this datasource, you can control which company IDs your users are able to see and thus, select.

For accessing this datasource parameter, instead of this:
SELECT * FROM `customer_table` WHERE `company_ids` IN (${CompanyID})

you can use the $X{IN, column, parameterkey} construct: https://reportserver.net/en/guides/admi … arameters/

"$X{IN, column, parameterkey}: If all parameter values are other than NULL, an expression in the form <column> IN (?, ?, .., ?) will be generated. If the list of values includes NULL values as well as values other than NULL the generated expression is:
(<column> IS NULL OR <column> IN (?, ?, .., ?))
If all delivered values are NULL, the expression generated becomes <column> IS NULL."

So your query would be something like:
SELECT * FROM `customer_table` WHERE $X{IN, company_ids, CompanyID}  using your CompanyID datasource parameter and your company_ids column.

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

I am unable to see how you link a multivalue datasource parameter to a list specified within the user variables? Is that possible?

Each user has access to only some company ID's and they are different.

User A - Access to select Company 1, 2, 3 ,4
User B - Access to select Company 5, 6, 7, 8
User C - Access to select Comapny 2, 6, 8

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

yes, you can create a parameter of type user variable, link it with the corresponding user variable, and then you can access this parameter as all other parameters:
${CompanyID}

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

THanks Eduardo however that then doesn't allow me to set up the multi select option, is that correct?

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

multiselect would work, as well:

an example scenario would be:

-assume there is a table companyinformation with attribute (column company)

- create a user variable "uv_company":

User A's "uv_company" contains: "1, 2, 3 ,4"
User B's "uv_company" contains: "5, 6, 7, 8"
User C's "uv_company" contains "2, 6, 8"

- create a parameter of type user variable "p_company", linking it to the user variable "uv_company",

- create a multi-select datasource parameter "companyDataSource", defined by

select distinct id, company from companyinformation where $X{IN, company, p_company}

then, according to this documentation:
https://reportserver.net/en/guides/admi … arameters/

the query of this datasource parameter will be changed to:
select distinct id, company from companyinformation where company IN (1, 2, 3 ,4)

which would filter on values only allowed for the user (according to the user variable).

The main query of your dynamic list could then be

select * from data where $X{IN, company, companyDataSource}

which will filter data on the values selected by the user in the datasource parameter, which is in turn filtered by the allowed values defined in the user variable.

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Thank you I'll give this a try!

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Thanks.
This isn't ideally the solution I was hoping to achieve.

I want the user to be able to select from the list we provide in their user variable list.
So effectively multi select from uv_company list we've entered.

They could then run the report, complete their variables including selecting their uv_company options as per their user profile variables.
They can either select 1 or all and the report will run (as it is set up to do) by only looking at the company ID's the user has selected from their own list.

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Do you think you guys could add this as an option at all?  A type of user variable which we can pre-define in the user profile, but they can then multi-select from or radio select from depending on the settings? This would then enable this scenario to become a possibility.

So I say
User 1 - CompanyID - Value = 1,2,3,4,5 (Multi selection)
User 2 - CompanyID - Value = 1,5,8,9 (Radio selection)

When User 1 runs a report, they get the parameters to set - they can multi select any option from 1,2,3,4,5
When user 2 runs a report, they get a parameter option to set - they can radio select one option from 1,5,8,9

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Do you think you guys could add this as an option at all?  A type of user variable which we can pre-define in the user profile, but they can then multi-select from or radio select from depending on the settings? This would then enable this scenario to become a possibility.

So I say
User 1 - CompanyID - Value = 1,2,3,4,5 (Multi selection)
User 2 - CompanyID - Value = 1,5,8,9 (Radio selection)

When User 1 runs a report, they get the parameters to set - they can multi select any option from 1,2,3,4,5
When user 2 runs a report, they get a parameter option to set - they can radio select one option from 1,5,8,9

Hi DISSTeam,

if I understand this correctly, this is exactly what I described here: https://forum.reportserver.net/viewtopi … 7669#p7669

Pls take a look and double check. If this does not work, pls send screenshots what is happening and what you would expect to happen.

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Ok let me revisit that. I think I was getting confused around
- create a multi-select datasource parameter "companyDataSource", defined by

and where to enter
select distinct id, company from companyinformation where $X{IN, company, p_company}

I also already run a datasource parameter using a database bundle with a key defined by a user variable.

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Could you screenshot how you think this should be set up so I can replicate?
- create a multi-select datasource parameter "companyDataSource", defined by

select distinct id, company from companyinformation where $X{IN, company, p_company}

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

pls check here how to create datasource parameters: https://reportserver.net/en/guides/admi … atasource/

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Yeah I can do that - but I am confused.

Does the datasource need to pull data from a database? If so, that won't work - our reports are set up to run based on a datasource bundle, with a static key that is passed from a user variable. Therefore, they run on different databases depending on the user.

I want the list of "Companies" to pull from a list provided by the user variable "CompanyID" (or in your example uv_company)

I don't understand:
, defined by

select distinct id, company from companyinformation where $X{IN, company, p_company}

I have done: create a multi-select datasource parameter "companyDataSource"

But I don't know what settings are appropriate within that window.

Last edited by DISSTeam (2020-09-30 14:03:46)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

Does the datasource need to pull data from a database? If so, that won't work - our reports are set up to run based on a datasource bundle, with a static key that is passed from a user variable. Therefore, they run on different databases depending on the user.

your datasource parameter can use any datasource defined in ReportServer, so it may use your database bundle as well, which is a datasource

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

select distinct id, company from companyinformation where $X{IN, company, p_company}

I have done: create a multi-select datasource parameter "companyDataSource"

But I don't know what settings are appropriate within that window.

Hi DISSTeam,

just copy paste the query into the "specific properties" of the parameter you created, and select the database bundle you need for this parameter.

Regards,
Eduardo

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Thank you for all your help. I do appreciate this is difficult and not straight forward.
I have set up a Multi-select datasource parameter.

SPecific Properties:
Datasource: MyDataBundle
Query: select distinct id, company from customers where $X{IN, company, p_company}
Post Processing: <blank>
Selection Mode: Multi select
Selection Style: Checkboxes

Error: DatasourceBundleMessages$$EnhancerByCGLIB$$3dc3ac07.exceptionNoDatabaseFound

How do I link in the values from the uv_company user parameter for them to multi-select from?

DISSTeam

Member

Registered: 2020-09-11

Re: Multi Company User

Could I get an email for you Eduardo by any chance? I'd like to either screen record what is happening - or send a load of screenshots through to you?

ange

Member

Registered: 2020-07-02

Re: Multi Company User

Hi DISSTeam,

here is an example of Multi-Tenant System and user variable. I hope this will answer to your questions.

0. Assume that we have an Organizational Units namely MyCompany where you have 4 sub-companies A, B, C, and D.
See bellow pictures:

1. We create a user variable that we call client and we assign a user variable to each sub-company accordingly. To Group A we assign A, to Group B we assign B and so on. See the pictures bellow:

2. Assume that we also have a DemoData setup with a variant

 

We create a parameter that we call P_CLIENT_ID and we assign the user variable client to it. See bellow pictures

  (you can hide the parameter or leave it open. But it is better to hide)

** The last thing to do here is to write the query

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID}

to enable each group to see only its data

3. We configure the TeamSpace and also give permission to MyCompany to access the report.

We also add the report to the TeamSpace of each Group. See bellow pictures

4. Now, a user is able to see only data corresponding to his user variable. The group A will only see A, group B will see B, and so on. See bellow pictures:

5. Here is a special example for you where user c1 can see data from A and B.
** You need to write the query

SELECT * FROM ClientData WHERE 

${P_CLIENT_ID}  like concat('%',ClientID,'%')

to enable each group to see all its data. Note that this is a mysql specific query. You may have to adapt it to your database.

Now, the user c1 can see data from A and B. See pictures

I hope that this answered to your preoccupation.

Regards,
Ange

Last edited by ange (2020-10-19 12:18:35)

ange

Member

Registered: 2020-07-02

Re: Multi Company User

Hi DISSTeam,

In order to answer your question with datasource parameter, here is an example how to perform it:

Consider our Organizational Units 'MyCompany' where we had 4 sub-companies A, B, C, and D.
Suppose that all the 5 points mentionned in my previous comment are satisfied on our DemoData.

Then, to allow each group to see only its datasource, we do the following:

(a) create a Datasource parameter that we call D_CLIENT_ID.
Most important here is in Depends on > Add, where you have to add the user variable parameter P_CLIENT_ID. 
Also, in Specific Properties, we have to select the Datasource and then put the query

SELECT id,ClientId FROM ClientData 
WHERE ${P_CLIENT_ID}  like concat('%',ClientID,'%')

See picture.

(b) Now, each user can see only the data from its group. See bellow pictures.

*** For our special case c1:

Regards,
Ange

Last edited by ange (2020-10-20 08:22:14)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Multi Company User

Hi DISSTeam,

pls check above for an example of this requirement:

"
User variable: company
Value: 1,2,3,4

When they run a report, they can then select from any of the above.
DO you think we can get a situation where a user can multi select from a list generated by a user variable?
"

In the examples, we have user variable: client, with values: A,B, so basically the same, as this also works with A,B,C,D

For this, as stated above, you need a query like this (this one is for mysql):

SELECT * FROM ClientData WHERE

${P_CLIENT_ID}  like concat('%',ClientID,'%')

for using the D_CLIENT_ID datasource parameter in a multiselect way, you can of course write in your main query the following:

Select * from ClientData where $X{IN, id, D_CLIENT_ID}

which will create a statement like this:

Select * from ClientData where id IN (1, 2, .., 3)

which will of course select everything you selected in the D_CLIENT_ID datasource parameter (in this case 1, 2, 3..)

More details can be found here:

https://reportserver.net/en/guides/admi … arameters/

    $X{IN, column, parameterkey}: If all parameter values are other than NULL, an expression in the form <column> IN (?, ?, .., ?) will be generated. If the list of values includes NULL values as well as values other than NULL the generated expression is:

    (<column> IS NULL OR <column> IN (?, ?, .., ?))

    If all delivered values are NULL, the expression generated becomes <column> IS NULL.

Regards,
Eduardo