How do I generate a list of distinct values as a Data Set?

Question

How do I generate a list of distinct values as a Data Set?

mtassin

SSC-Insane

Points: 23099
More actions
September 10, 2010 at 10:14 am

#226193

I've got a report model out there for my users to use.
They want to populate the Parameters "Available Values" with the values from the model.
but when they add them, they get duplicates (because for instance, the same user probably created several records). They'd like the Parameter to only have the value once.
I can get around this by creating a data connection in the report and then specifying a SQL SELECT DISTINCT, but my users don't have the permssions, accounts, etc to do the same thing.
So when building a query using Query Designer for a dataset, how can they specify that they want a list of DISTINCT values, and not get duplicates in the list?

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

mtassin SSC-Insane Points: 23099 More actions · Answer 1

For example, here's a query built in Query designer

<SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder">

<Hierarchies>

<Hierarchy>

<BaseEntity>

<EntityID>G887eb75c-92fe-4e7d-9427-631ffd84e35f</EntityID>

</BaseEntity>

<Groupings>

<Grouping Name="Resource Name">

<Expression Name="Resource Name">

<Path>

<RolePathItem>

<RoleID>Gacaa5345-5cd1-452f-9a3f-74d1d80bcb98</RoleID>

</RolePathItem>

</Path>

<AttributeRef>

<AttributeID>G6800bae9-c9f7-467f-bdf5-e8c60647a5c1</AttributeID>

</AttributeRef>

</Expression>

</Grouping>

</Groupings>

<Filter>

<Expression Name="filter">

<Literal>

<DataType>Boolean</DataType>

<Value>true</Value>

</Literal>

<CustomProperties>

<CustomProperty Name="qd:Filter" />

<CustomProperty Name="qd:ContextEntityID">

<Value xsi:type="xsd:string">G887eb75c-92fe-4e7d-9427-631ffd84e35f</Value>

</CustomProperty>

<CustomProperty Name="qd:AutoChangeBaseEntity" />

<CustomProperty Name="qd:Design">

<Value xsi:type="xsd:string">expr1</Value>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

</SemanticQuery>

How can they specify a distinct Resource Name?

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]