"Lisa Slater Nicholls" <lisa@spacefold.com> wrote in message
news:Ok4UumunHHA.4196@TK2MSFTNGP06.phx.gbl...
> Sorry, R, didn't mean to mislead you. I am not opening and reading RDLs
> for your particular reason, it's something I do for other reasons. Which
> I will be glad to discuss when it's done!
>
>>L<
>
> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
> news:8B03BE92-A3EC-447E-8020-72EBC05F8FF7@microsoft.com...
>> Good morning Lisa
>>
>> Thanks for your note, it sounds very interesting, would be quite
>> interested
>> in how you get on, would save lots of time, as opposed to opening each
>> report
>> individually, not sure how much you have progressed, but if you would
>> like
>> some collaboration, then I am happy to help.
>>
>> Kind regards
>>
>> R
>>
>> "Lisa Slater Nicholls" wrote:
>>
>>> Absolutely one can search the code, in fact I am in the middle of
>>> writing a
>>> utility that does... just that... with RDLs <g>.
>>>
>>> But when one of the reporting team says to do it a certain way (as he
>>> does
>>> in that blog item)... he usually has a good reason.
>>>
>>> Look at it this way: a lot depends on how the Report Builder actually
>>> stores
>>> its information. You can see "the definition of the query" there, true,
>>> but that does not mean, by definition, that you actually see any SQL!
>>>
>>> It's my understanding that the Report Builder stores information about
>>> how
>>> the report uses a report model, and the SQL is only extrapolated at
>>> runtime.
>>> Below I'll put an abbreviated example of how it is stored in the RDL as
>>> a
>>> "query". It's not a SQL query. So what Bob was saying is look in the
>>> execution log, where the SQL query is built based on what you see here.
>>> I
>>> hope this helps... I might be muddying the waters more...
>>>
>>> >L<
>>>
>>>
>>> <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:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder"
>>> xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign">
>>> <Hierarchies>
>>> <Hierarchy>
>>> <BaseEntity>
>>> <!--Recipient-->
>>> <EntityID>G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</EntityID>
>>> </BaseEntity>
>>> <Groupings>
>>> <Grouping Name="Recipient">
>>> <Expression Name="Recipient">
>>> <EntityRef>
>>> <!--Recipient-->
>>> <EntityID>G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</EntityID>
>>> </EntityRef>
>>> </Expression>
>>> <Details>
>>> <Expression Name="Recipient1">
>>> <AttributeRef>
>>> <!--Recipient-->
>>>
>>> <AttributeID>G0f0d9d15-d088-4848-96de-340981db107c</AttributeID>
>>> </AttributeRef>
>>> </Expression>
>>> <Expression Name="Locale">
>>> <AttributeRef>
>>> <!--Locale-->
>>>
>>> <AttributeID>G8f1ef406-aaa6-47e8-83f3-d6ab55c1530f</AttributeID>
>>> </AttributeRef>
>>> </Expression>
>>> </Details>
>>> </Grouping>
>>> </Groupings>
>>> <Filter>
>>> <Expression Name="expr2">
>>> <Function>
>>> <FunctionName>Equals</FunctionName>
>>> <Arguments>
>>> <Expression>
>>> <AttributeRef>
>>> <!--Locale-->
>>>
>>> <AttributeID>G8f1ef406-aaa6-47e8-83f3-d6ab55c1530f</AttributeID>
>>> </AttributeRef>
>>> </Expression>
>>> <Expression>
>>> <Literal>
>>> <DataType>String</DataType>
>>> <Value>MO Jefferson City</Value>
>>> </Literal>
>>> </Expression>
>>> </Arguments>
>>> </Function>
>>> <CustomProperties>
>>> <CustomProperty Name="qd:FilterCondition" />
>>> <CustomProperty Name="qd:Filter" />
>>> <CustomProperty Name="qd:ContextEntityID">
>>> <Value
>>> xsi:type="xsd:string">G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</Value>
>>> </CustomProperty>
>>> <CustomProperty Name="qd:AutoChangeBaseEntity" />
>>> <CustomProperty Name="qd:Design">
>>> <Value xsi:type="xsd:string">expr3</Value>
>>> </CustomProperty>
>>> </CustomProperties>
>>> </Expression>
>>> </Filter>
>>> </Hierarchy>
>>> </Hierarchies>
>>> <!-- more here -->
>>> </SemanticQuery>
>>>
>>>
>>> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
>>> news:FD2322A1-A133-4ECA-9EAF-AFBB3E53AC92@microsoft.com...
>>> > Hi Lisa
>>> >
>>> > Thank you for the posting, seems like a logical idea. Although I was
>>> > under
>>> > the assumption that I could have somehow interrogate the Report and
>>> > extract
>>> > the SQL, perhaps using somesort of API, but then the report is
>>> > essentially
>>> > a
>>> > mark Up Language, so maybe one can search the code?
>>> >
>>> > Kind regards
>>> >
>>> > R
>>> >
>>> > "Lisa Slater Nicholls" wrote:
>>> >
>>> >> You will find the directions here (post is titled: How to get the SQL
>>> >> for
>>> >> a
>>> >> Report Builder report):
>>> >>
>>> >> http://blogs.msdn.com/bobmeyers/archive/2006/07/05/657125.aspx
>>> >>
>>> >> Caveat: I have not tested or even tried this.
>>> >>
>>> >> >L<
>>> >>
>>> >>
>>> >> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
>>> >> news:E0A92485-ACCB-49B1-B973-2DC9A7F0EA59@microsoft.com...
>>> >> > Hi
>>> >> >
>>> >> > We have RS2005/SS2005 running as part of our company MI system. We
>>> >> > have
>>> >> > many users creating reports using Report Builder, I was wondering
>>> >> > is
>>> >> > there
>>> >> > anyway, in which we can work out what data is utilised in the
>>> >> > reports,
>>> >> > since
>>> >> > we are getting many duplicate reports and would like to perform a
>>> >> > cleansing
>>> >> > exercise.
>>> >> >
>>> >> > Kind regards
>>> >> >
>>> >> > R
>>> >> > (SS2005/RS2005)
>>> >>
>>> >>
>>>
>>>
>

>L<

"Ricky" <r@msn.com> wrote in message
news:egR9%23vxnHHA.668@TK2MSFTNGP05.phx.gbl...
> Hey Lisa
>
> That's really okay, you didn't mislead me, I was jst offering any
> assitance if you required any :-).
>
> Look forward to hearing from you.
>
> Kind regards
> R
>
> "Lisa Slater Nicholls" <lisa@spacefold.com> wrote in message
> news:Ok4UumunHHA.4196@TK2MSFTNGP06.phx.gbl...
>> Sorry, R, didn't mean to mislead you. I am not opening and reading RDLs
>> for your particular reason, it's something I do for other reasons. Which
>> I will be glad to discuss when it's done!
>>
>>>L<
>>
>> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
>> news:8B03BE92-A3EC-447E-8020-72EBC05F8FF7@microsoft.com...
>>> Good morning Lisa
>>>
>>> Thanks for your note, it sounds very interesting, would be quite
>>> interested
>>> in how you get on, would save lots of time, as opposed to opening each
>>> report
>>> individually, not sure how much you have progressed, but if you would
>>> like
>>> some collaboration, then I am happy to help.
>>>
>>> Kind regards
>>>
>>> R
>>>
>>> "Lisa Slater Nicholls" wrote:
>>>
>>>> Absolutely one can search the code, in fact I am in the middle of
>>>> writing a
>>>> utility that does... just that... with RDLs <g>.
>>>>
>>>> But when one of the reporting team says to do it a certain way (as he
>>>> does
>>>> in that blog item)... he usually has a good reason.
>>>>
>>>> Look at it this way: a lot depends on how the Report Builder actually
>>>> stores
>>>> its information. You can see "the definition of the query" there,
>>>> true,
>>>> but that does not mean, by definition, that you actually see any SQL!
>>>>
>>>> It's my understanding that the Report Builder stores information about
>>>> how
>>>> the report uses a report model, and the SQL is only extrapolated at
>>>> runtime.
>>>> Below I'll put an abbreviated example of how it is stored in the RDL as
>>>> a
>>>> "query". It's not a SQL query. So what Bob was saying is look in the
>>>> execution log, where the SQL query is built based on what you see here.
>>>> I
>>>> hope this helps... I might be muddying the waters more...
>>>>
>>>> >L<
>>>>
>>>>
>>>> <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:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder"
>>>> xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign">
>>>> <Hierarchies>
>>>> <Hierarchy>
>>>> <BaseEntity>
>>>> <!--Recipient-->
>>>> <EntityID>G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</EntityID>
>>>> </BaseEntity>
>>>> <Groupings>
>>>> <Grouping Name="Recipient">
>>>> <Expression Name="Recipient">
>>>> <EntityRef>
>>>> <!--Recipient-->
>>>>
>>>> <EntityID>G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</EntityID>
>>>> </EntityRef>
>>>> </Expression>
>>>> <Details>
>>>> <Expression Name="Recipient1">
>>>> <AttributeRef>
>>>> <!--Recipient-->
>>>>
>>>> <AttributeID>G0f0d9d15-d088-4848-96de-340981db107c</AttributeID>
>>>> </AttributeRef>
>>>> </Expression>
>>>> <Expression Name="Locale">
>>>> <AttributeRef>
>>>> <!--Locale-->
>>>>
>>>> <AttributeID>G8f1ef406-aaa6-47e8-83f3-d6ab55c1530f</AttributeID>
>>>> </AttributeRef>
>>>> </Expression>
>>>> </Details>
>>>> </Grouping>
>>>> </Groupings>
>>>> <Filter>
>>>> <Expression Name="expr2">
>>>> <Function>
>>>> <FunctionName>Equals</FunctionName>
>>>> <Arguments>
>>>> <Expression>
>>>> <AttributeRef>
>>>> <!--Locale-->
>>>>
>>>> <AttributeID>G8f1ef406-aaa6-47e8-83f3-d6ab55c1530f</AttributeID>
>>>> </AttributeRef>
>>>> </Expression>
>>>> <Expression>
>>>> <Literal>
>>>> <DataType>String</DataType>
>>>> <Value>MO Jefferson City</Value>
>>>> </Literal>
>>>> </Expression>
>>>> </Arguments>
>>>> </Function>
>>>> <CustomProperties>
>>>> <CustomProperty Name="qd:FilterCondition" />
>>>> <CustomProperty Name="qd:Filter" />
>>>> <CustomProperty Name="qd:ContextEntityID">
>>>> <Value
>>>> xsi:type="xsd:string">G3d75ac77-244c-4ff2-b3e1-32d98a3a4105</Value>
>>>> </CustomProperty>
>>>> <CustomProperty Name="qd:AutoChangeBaseEntity" />
>>>> <CustomProperty Name="qd:Design">
>>>> <Value xsi:type="xsd:string">expr3</Value>
>>>> </CustomProperty>
>>>> </CustomProperties>
>>>> </Expression>
>>>> </Filter>
>>>> </Hierarchy>
>>>> </Hierarchies>
>>>> <!-- more here -->
>>>> </SemanticQuery>
>>>>
>>>>
>>>> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
>>>> news:FD2322A1-A133-4ECA-9EAF-AFBB3E53AC92@microsoft.com...
>>>> > Hi Lisa
>>>> >
>>>> > Thank you for the posting, seems like a logical idea. Although I was
>>>> > under
>>>> > the assumption that I could have somehow interrogate the Report and
>>>> > extract
>>>> > the SQL, perhaps using somesort of API, but then the report is
>>>> > essentially
>>>> > a
>>>> > mark Up Language, so maybe one can search the code?
>>>> >
>>>> > Kind regards
>>>> >
>>>> > R
>>>> >
>>>> > "Lisa Slater Nicholls" wrote:
>>>> >
>>>> >> You will find the directions here (post is titled: How to get the
>>>> >> SQL for
>>>> >> a
>>>> >> Report Builder report):
>>>> >>
>>>> >> http://blogs.msdn.com/bobmeyers/archive/2006/07/05/657125.aspx
>>>> >>
>>>> >> Caveat: I have not tested or even tried this.
>>>> >>
>>>> >> >L<
>>>> >>
>>>> >>
>>>> >> "Rikesh" <Rikesh@discussions.microsoft.com> wrote in message
>>>> >> news:E0A92485-ACCB-49B1-B973-2DC9A7F0EA59@microsoft.com...
>>>> >> > Hi
>>>> >> >
>>>> >> > We have RS2005/SS2005 running as part of our company MI system.
>>>> >> > We
>>>> >> > have