Dynamically selected table via prompt

[psten]

Hi,
In Framework Manager, I have modeled a dimension to dynamically switch between two tables, based on a prompt value. The fields in the dimension are modelled something like this :
[Database].#sb( prompt( 'parTable','token','DMD_DEPARTMENT_TAB') )#.[DEPID]

DMD_DEPARTMENT_TAB is the default table/prompt value so that self-service users dont have to relate to this.

This solution works fine.

My problem now, however, is that i want to create a report with one query that uses the first table, and a second query that uses the other table. But i have not been able to get the two queries to use two different prompt values.

Any creative thoughts on this?

Thanks,
Paul...

[BigChris]

Not my area of expertise, but I would guess that the Framework Manager is taking the value of the prompt when it makes the connection. Once it's made that connection it won't ask for that prompt again...it's already connected.

[Lynn]

Not my area of expertise, but I would guess that the Framework Manager is taking the value of the prompt when it makes the connection. Once it's made that connection it won't ask for that prompt again...it's already connected.

I believe your belief is the correct thing to believe  :D

There is only one parameter (parTable) and it can have only one value within the report. I suppose you did this to present a single model query subject over either table rather than two identical query subjects over two different sources, but the only options I can think of involve some changes on the database and model side rather than the reporting side.

You can model both tables or model the same thing again with a different parameter but this defeats the purpose of what you've done I suspect. I'd go further back to the source and ask why there are two different tables for the same thing and see if perhaps they could be merged. An additional attribute that could be used to filter results would allow report authors to do whatever is required with whatever sub-set or complete set of information they need.

[psten]

Thanks guys.
The reason why it is modeled like this, is that the default table (DMD_DEPARTMENT_TAB) has security based on user, while the other table has no security. Typically a user can only see one department, but sometimes we want to create reports that shows numbers for other departments. For instance, the users department compared to chain or country average.

What complicates this, is that the model and packages are also used in self-service. So it has to be modeled in a way that makes sense, and works, for an ordinary user. I am trying to avoid creating models that are tailored workarounds that can only be used by specialist report developers. We have had solutions like this in the past, with two department dimensions (one secured, one unsecured), but this cannot be used in self-service.

P..

[Lynn]

Thanks guys.
The reason why it is modeled like this, is that the default table (DMD_DEPARTMENT_TAB) has security based on user, while the other table has no security. Typically a user can only see one department, but sometimes we want to create reports that shows numbers for other departments. For instance, the users department compared to chain or country average.

What complicates this, is that the model and packages are also used in self-service. So it has to be modeled in a way that makes sense, and works, for an ordinary user. I am trying to avoid creating models that are tailored workarounds that can only be used by specialist report developers. We have had solutions like this in the past, with two department dimensions (one secured, one unsecured), but this cannot be used in self-service.

P..

I have a similar scenario. We have a single "department" table containing all departments. A separate table with columns for user ID and department ID is populated to include a row for every allowed combination. This security table is joined to the department dimension table in the physical layer on department ID.

In the business layer there is a filter on the department dimension that restricts the user ID column on the security table to the logged in user, thus enforcing the join between the tables *only* when an element from the department dimension is included in the query.

This means that any user can get any metric in total, but can only see the break down of metrics by departments they are authorized to view. As a report author, I can create a query including a metric broken down by department and another query for the metric in total (no reference to department). These can be displayed in different layouts and/or joined at the query level in order to compute a % of total.

Not sure if any of this is suitable for your environment but thought I'd explain in case there are elements you find helpful.

Code Select Expand


lower( [XYZ Physical].[USER_DEPT_SECURITY].[COGNOS_USER_ID] )
=
lower ( #sq($account.personalInfo.userName)# )


[psten]

Thanks Lynn.
This looks very similar to how we have modeled our solution. A user who do not use the department dimension, will see the metrics for all departments. However, what we are typically interested in, is the Chain or Country metrics (dimension is Country > Chain > Department), not all Depts. And as Country and Chain are in the same department table, we need to access this :/

P..