Data processing extension and parameters

hi, I have implemented a data processing extension successfully and I have my
own query syntax (even implemented a custom query designer).
However, if I want to make use of the report parameter(s), it seems that I
have to append Parameters!{ParameterName} to my query.Value in order to get
the parameter value entered by the user.
Is this correct?
Thanks
jennyHello Jenny,
When you reference parameter value in report, ou could use
Parameters!{ParameterName}.value. However, as for parameter for data
processing extension of data source, it depends on how you implement this.
For data processing extension s for SQL server, you could use @.varaiable
for parameter in SQL query. You may want to try this with your data
procssing extension to test.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| Subject: Data processing extension and parameters
| Date: Mon, 21 Nov 2005 16:29:01 -0800
| Lines: 11
| Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63712
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| hi, I have implemented a data processing extension successfully and I
have my
| own query syntax (even implemented a custom query designer).
|
| However, if I want to make use of the report parameter(s), it seems that
I
| have to append Parameters!{ParameterName} to my query.Value in order to
get
| the parameter value entered by the user.
|
| Is this correct?
| Thanks
| jenny
|
||||Thanks for your reply. Yes if I append the Parameters!{ParameterName}.value
to my query, it does work and I get the right value selected by users.
However, my main concern is that the query used in my data extension has its
own syntax and I'm thinking of not using the generic query designer but my
own custom query designer (contains some text boxes and combo boxes for
example to generate a query from the user inputs). That is, to accomodate
parameters in reports, the user will need to append
Parameters!{ParameterName}.value to the query command text as well. Do I
understand this correctly?
BTW, is there a limit on number of parameters used in a report? I don't
think so. Correct?
Thanks a lot!
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> When you reference parameter value in report, ou could use
> Parameters!{ParameterName}.value. However, as for parameter for data
> processing extension of data source, it depends on how you implement this.
> For data processing extension s for SQL server, you could use @.varaiable
> for parameter in SQL query. You may want to try this with your data
> procssing extension to test.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | Subject: Data processing extension and parameters
> | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | Lines: 11
> | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63712
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | hi, I have implemented a data processing extension successfully and I
> have my
> | own query syntax (even implemented a custom query designer).
> |
> | However, if I want to make use of the report parameter(s), it seems that
> I
> | have to append Parameters!{ParameterName} to my query.Value in order to
> get
> | the parameter value entered by the user.
> |
> | Is this correct?
> | Thanks
> | jenny
> |
> |
>|||Hello Jenny,
Report rdl contains dataset section that describes data fields and query.
Dataset section may contain one or more datasets depending on the report
layout. Each dataset entry has query section with command text entry and
query parameters. This is also true for data processing extension query.
For example:
<Query>
<DataSourceName>AdventureWorks</DataSourceName>
<CommandText>SELECT C.FirstName + ' ' + C.LastName AS
Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS Sales
FROM Sales.SalesOrderHeader SOH INNER JOIN
Sales.SalesOrderDetail SOD ON SOH.SalesOrderID =SOD.SalesOrderID INNER JOIN
Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID
INNER JOIN
HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
DATEPART(Year, SOH.OrderDate) = @.ReportYear AND DATEPART(Month,
SOH.OrderDate) <= @.ReportMonth) AND
(SOH.SalesPersonID = @.EmpID)
GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
DATEPART(Year, SOH.OrderDate),
DATEPART(Month, SOH.OrderDate), DATENAME(Month,
SOH.OrderDate)</CommandText>
<QueryParameters>
<QueryParameter Name="@.ReportYear">
<Value>=Parameters!ReportYear.Value</Value>
</QueryParameter>
<QueryParameter Name="@.ReportMonth">
<Value>=Parameters!ReportMonth.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EmpID">
<Value>=Parameters!EmpID.Value</Value>
</QueryParameter>
</QueryParameters>
<Timeout>30</Timeout>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
No matter how you implement your data extension or cutom query designer,
the result rdl shall include the commandtext and parameters you want.
I did not find any limiatation in number of parameters in a report and I
think it is limited by server performance though.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
<y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: Data processing extension and parameters
| Date: Tue, 22 Nov 2005 08:42:06 -0800
| Lines: 86
| Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63759
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Thanks for your reply. Yes if I append the
Parameters!{ParameterName}.value
| to my query, it does work and I get the right value selected by users.
|
| However, my main concern is that the query used in my data extension has
its
| own syntax and I'm thinking of not using the generic query designer but
my
| own custom query designer (contains some text boxes and combo boxes for
| example to generate a query from the user inputs). That is, to
accomodate
| parameters in reports, the user will need to append
| Parameters!{ParameterName}.value to the query command text as well. Do I
| understand this correctly?
|
| BTW, is there a limit on number of parameters used in a report? I don't
| think so. Correct?
|
| Thanks a lot!
| Jenny
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Jenny,
| >
| > When you reference parameter value in report, ou could use
| > Parameters!{ParameterName}.value. However, as for parameter for data
| > processing extension of data source, it depends on how you implement
this.
| > For data processing extension s for SQL server, you could use
@.varaiable
| > for parameter in SQL query. You may want to try this with your data
| > procssing extension to test.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --
| > | Thread-Topic: Data processing extension and parameters
| > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| > | X-WBNR-Posting-Host: 209.17.156.248
| > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > <yinjennytam@.newsgroup.nospam>
| > | Subject: Data processing extension and parameters
| > | Date: Mon, 21 Nov 2005 16:29:01 -0800
| > | Lines: 11
| > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:63712
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | hi, I have implemented a data processing extension successfully and I
| > have my
| > | own query syntax (even implemented a custom query designer).
| > |
| > | However, if I want to make use of the report parameter(s), it seems
that
| > I
| > | have to append Parameters!{ParameterName} to my query.Value in order
to
| > get
| > | the parameter value entered by the user.
| > |
| > | Is this correct?
| > | Thanks
| > | jenny
| > |
| > |
| >
| >
||||When I checked the rdl file that is generated, I couldn't find what you said
below, but I found the followings instead (where <ReportParameters> is at the
same level as <DataSources>):
<ReportParameters>
<ReportParameter Name="State">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>CA</Value>
</Values>
</DefaultValue>
<Prompt>State</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>CA</Value>
<Label>California</Label>
</ParameterValue>
<ParameterValue>
<Value>GA</Value>
<Label>Georgia </Label>
</ParameterValue>
<ParameterValue>
<Value>NY</Value>
<Label>New York</Label>
</ParameterValue>
<ParameterValue>
<Value>WA</Value>
<Label>Washington</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
Within the <DataSets> element, I could only find <Query> with <CommandText>
as follows (some details omitted):
<CommandText>=" ... State = " & Parameters!State.Value</CommandText>
Have I done anything wrong? It seems to work for me so far. I did get the
right selected parameter value when processing the report though.
Thanks again for your help.
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> Report rdl contains dataset section that describes data fields and query.
> Dataset section may contain one or more datasets depending on the report
> layout. Each dataset entry has query section with command text entry and
> query parameters. This is also true for data processing extension query.
> For example:
> <Query>
> <DataSourceName>AdventureWorks</DataSourceName>
> <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
> Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
> DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
> DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS Sales
> FROM Sales.SalesOrderHeader SOH INNER JOIN
> Sales.SalesOrderDetail SOD ON SOH.SalesOrderID => SOD.SalesOrderID INNER JOIN
> Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID
> INNER JOIN
> HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID
> INNER JOIN
> Person.Contact C ON E.ContactID = C.ContactID
> WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
> DATEPART(Year, SOH.OrderDate) = @.ReportYear AND DATEPART(Month,
> SOH.OrderDate) <= @.ReportMonth) AND
> (SOH.SalesPersonID = @.EmpID)
> GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
> DATEPART(Year, SOH.OrderDate),
> DATEPART(Month, SOH.OrderDate), DATENAME(Month,
> SOH.OrderDate)</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.ReportYear">
> <Value>=Parameters!ReportYear.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.ReportMonth">
> <Value>=Parameters!ReportMonth.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.EmpID">
> <Value>=Parameters!EmpID.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <Timeout>30</Timeout>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> No matter how you implement your data extension or cutom query designer,
> the result rdl shall include the commandtext and parameters you want.
> I did not find any limiatation in number of parameters in a report and I
> think it is limited by server performance though.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> | Subject: RE: Data processing extension and parameters
> | Date: Tue, 22 Nov 2005 08:42:06 -0800
> | Lines: 86
> | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63759
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Thanks for your reply. Yes if I append the
> Parameters!{ParameterName}.value
> | to my query, it does work and I get the right value selected by users.
> |
> | However, my main concern is that the query used in my data extension has
> its
> | own syntax and I'm thinking of not using the generic query designer but
> my
> | own custom query designer (contains some text boxes and combo boxes for
> | example to generate a query from the user inputs). That is, to
> accomodate
> | parameters in reports, the user will need to append
> | Parameters!{ParameterName}.value to the query command text as well. Do I
> | understand this correctly?
> |
> | BTW, is there a limit on number of parameters used in a report? I don't
> | think so. Correct?
> |
> | Thanks a lot!
> | Jenny
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Jenny,
> | >
> | > When you reference parameter value in report, ou could use
> | > Parameters!{ParameterName}.value. However, as for parameter for data
> | > processing extension of data source, it depends on how you implement
> this.
> | > For data processing extension s for SQL server, you could use
> @.varaiable
> | > for parameter in SQL query. You may want to try this with your data
> | > procssing extension to test.
> | >
> | > Best Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | > --
> | > | Thread-Topic: Data processing extension and parameters
> | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > <yinjennytam@.newsgroup.nospam>
> | > | Subject: Data processing extension and parameters
> | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | > | Lines: 11
> | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:63712
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | hi, I have implemented a data processing extension successfully and I
> | > have my
> | > | own query syntax (even implemented a custom query designer).
> | > |
> | > | However, if I want to make use of the report parameter(s), it seems
> that
> | > I
> | > | have to append Parameters!{ParameterName} to my query.Value in order
> to
> | > get
> | > | the parameter value entered by the user.
> | > |
> | > | Is this correct?
> | > | Thanks
> | > | jenny
> | > |
> | > |
> | >
> | >
> |
>|||Hello Jenny,
Thank you for your reply. ReportParameters is necessary for all reports
involving parameter. However, In SQL or OLEDB extenstion, parameters used
by query are "@.variable" which is mapped to reportparmeter via
<QueryParameters>.
As you have noticed, you could use Parameters!paramtername.Value directly
in commandtext anyway. Autually this is the method to build up dynamical
query from report parameters when using SQL/OLEDB extensions.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Data processing extension and parameters
| thread-index: AcXwUOY3bcJ4f+OHTTWQZWHBs9LJkA==| X-WBNR-Posting-Host: 209.17.156.248
| From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
<yinjennytam@.newsgroup.nospam>
| References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
<y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
<AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
<1gHNWo$7FHA.4000@.TK2MSFTNGXA02.phx.gbl>
| Subject: RE: Data processing extension and parameters
| Date: Wed, 23 Nov 2005 09:11:10 -0800
| Lines: 247
| Message-ID: <8252C18E-9A71-4E37-B59D-64FE1405FE65@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63867
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| When I checked the rdl file that is generated, I couldn't find what you
said
| below, but I found the followings instead (where <ReportParameters> is at
the
| same level as <DataSources>):
|
| <ReportParameters>
| <ReportParameter Name="State">
| <DataType>String</DataType>
| <DefaultValue>
| <Values>
| <Value>CA</Value>
| </Values>
| </DefaultValue>
| <Prompt>State</Prompt>
| <ValidValues>
| <ParameterValues>
| <ParameterValue>
| <Value>CA</Value>
| <Label>California</Label>
| </ParameterValue>
| <ParameterValue>
| <Value>GA</Value>
| <Label>Georgia </Label>
| </ParameterValue>
| <ParameterValue>
| <Value>NY</Value>
| <Label>New York</Label>
| </ParameterValue>
| <ParameterValue>
| <Value>WA</Value>
| <Label>Washington</Label>
| </ParameterValue>
| </ParameterValues>
| </ValidValues>
| </ReportParameter>
| </ReportParameters>
|
| Within the <DataSets> element, I could only find <Query> with
<CommandText>
| as follows (some details omitted):
|
| <CommandText>=" ... State = " & Parameters!State.Value</CommandText>
|
|
| Have I done anything wrong? It seems to work for me so far. I did get
the
| right selected parameter value when processing the report though.
|
| Thanks again for your help.
| Jenny
|
|
|
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Jenny,
| >
| > Report rdl contains dataset section that describes data fields and
query.
| > Dataset section may contain one or more datasets depending on the
report
| > layout. Each dataset entry has query section with command text entry
and
| > query parameters. This is also true for data processing extension
query.
| > For example:
| >
| > <Query>
| > <DataSourceName>AdventureWorks</DataSourceName>
| > <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
| > Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
| > DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
| > DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS
Sales
| > FROM Sales.SalesOrderHeader SOH INNER JOIN
| > Sales.SalesOrderDetail SOD ON SOH.SalesOrderID =| > SOD.SalesOrderID INNER JOIN
| > Sales.SalesPerson SP ON SOH.SalesPersonID =SP.SalesPersonID
| > INNER JOIN
| > HumanResources.Employee E ON SP.SalesPersonID =E.EmployeeID
| > INNER JOIN
| > Person.Contact C ON E.ContactID = C.ContactID
| > WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
| > DATEPART(Year, SOH.OrderDate) = @.ReportYear AND
DATEPART(Month,
| > SOH.OrderDate) <= @.ReportMonth) AND
| > (SOH.SalesPersonID = @.EmpID)
| > GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
| > DATEPART(Year, SOH.OrderDate),
| > DATEPART(Month, SOH.OrderDate), DATENAME(Month,
| > SOH.OrderDate)</CommandText>
| > <QueryParameters>
| > <QueryParameter Name="@.ReportYear">
| > <Value>=Parameters!ReportYear.Value</Value>
| > </QueryParameter>
| > <QueryParameter Name="@.ReportMonth">
| > <Value>=Parameters!ReportMonth.Value</Value>
| > </QueryParameter>
| > <QueryParameter Name="@.EmpID">
| > <Value>=Parameters!EmpID.Value</Value>
| > </QueryParameter>
| > </QueryParameters>
| > <Timeout>30</Timeout>
| > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
| > </Query>
| >
| > No matter how you implement your data extension or cutom query
designer,
| > the result rdl shall include the commandtext and parameters you want.
| >
| > I did not find any limiatation in number of parameters in a report and
I
| > think it is limited by server performance though.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --
| > | Thread-Topic: Data processing extension and parameters
| > | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==| > | X-WBNR-Posting-Host: 209.17.156.248
| > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > <yinjennytam@.newsgroup.nospam>
| > | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
| > | Subject: RE: Data processing extension and parameters
| > | Date: Tue, 22 Nov 2005 08:42:06 -0800
| > | Lines: 86
| > | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:63759
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Thanks for your reply. Yes if I append the
| > Parameters!{ParameterName}.value
| > | to my query, it does work and I get the right value selected by
users.
| > |
| > | However, my main concern is that the query used in my data extension
has
| > its
| > | own syntax and I'm thinking of not using the generic query designer
but
| > my
| > | own custom query designer (contains some text boxes and combo boxes
for
| > | example to generate a query from the user inputs). That is, to
| > accomodate
| > | parameters in reports, the user will need to append
| > | Parameters!{ParameterName}.value to the query command text as well.
Do I
| > | understand this correctly?
| > |
| > | BTW, is there a limit on number of parameters used in a report? I
don't
| > | think so. Correct?
| > |
| > | Thanks a lot!
| > | Jenny
| > |
| > |
| > | "Peter Yang [MSFT]" wrote:
| > |
| > | > Hello Jenny,
| > | >
| > | > When you reference parameter value in report, ou could use
| > | > Parameters!{ParameterName}.value. However, as for parameter for
data
| > | > processing extension of data source, it depends on how you
implement
| > this.
| > | > For data processing extension s for SQL server, you could use
| > @.varaiable
| > | > for parameter in SQL query. You may want to try this with your data
| > | > procssing extension to test.
| > | >
| > | > Best Regards,
| > | >
| > | > Peter Yang
| > | > MCSE2000/2003, MCSA, MCDBA
| > | > Microsoft Online Partner Support
| > | >
| > | > When responding to posts, please "Reply to Group" via your
newsreader
| > so
| > | > that others may learn and benefit from your issue.
| > | >
| > | > =====================================================| > | >
| > | >
| > | >
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > | >
| > | > --
| > | > | Thread-Topic: Data processing extension and parameters
| > | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==| > | > | X-WBNR-Posting-Host: 209.17.156.248
| > | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
| > | > <yinjennytam@.newsgroup.nospam>
| > | > | Subject: Data processing extension and parameters
| > | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
| > | > | Lines: 11
| > | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
| > | > | MIME-Version: 1.0
| > | > | Content-Type: text/plain;
| > | > | charset="Utf-8"
| > | > | Content-Transfer-Encoding: 7bit
| > | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | > | Content-Class: urn:content-classes:message
| > | > | Importance: normal
| > | > | Priority: normal
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | > | Path:
| > TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | > | Xref: TK2MSFTNGXA02.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:63712
| > | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > | > |
| > | > | hi, I have implemented a data processing extension successfully
and I
| > | > have my
| > | > | own query syntax (even implemented a custom query designer).
| > | > |
| > | > | However, if I want to make use of the report parameter(s), it
seems
| > that
| > | > I
| > | > | have to append Parameters!{ParameterName} to my query.Value in
order
| > to
| > | > get
| > | > | the parameter value entered by the user.
| > | > |
| > | > | Is this correct?
| > | > | Thanks
| > | > | jenny
| > | > |
| > | > |
| > | >
| > | >
| > |
| >
| >
||||Thank you for your help. I've noticed that when using the SQL extension, if
the query contains a @.variable, Report Designer automatically creates
corresponding report parameters in the report.
This does not happen in my data extension, and I believe this is because my
query parser does not do anything special when the query contains a @.variable.
Thank you
Jenny
"Peter Yang [MSFT]" wrote:
> Hello Jenny,
> Thank you for your reply. ReportParameters is necessary for all reports
> involving parameter. However, In SQL or OLEDB extenstion, parameters used
> by query are "@.variable" which is mapped to reportparmeter via
> <QueryParameters>.
> As you have noticed, you could use Parameters!paramtername.Value directly
> in commandtext anyway. Autually this is the method to build up dynamical
> query from report parameters when using SQL/OLEDB extensions.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Data processing extension and parameters
> | thread-index: AcXwUOY3bcJ4f+OHTTWQZWHBs9LJkA==> | X-WBNR-Posting-Host: 209.17.156.248
> | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> <yinjennytam@.newsgroup.nospam>
> | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> <1gHNWo$7FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> | Subject: RE: Data processing extension and parameters
> | Date: Wed, 23 Nov 2005 09:11:10 -0800
> | Lines: 247
> | Message-ID: <8252C18E-9A71-4E37-B59D-64FE1405FE65@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:63867
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | When I checked the rdl file that is generated, I couldn't find what you
> said
> | below, but I found the followings instead (where <ReportParameters> is at
> the
> | same level as <DataSources>):
> |
> | <ReportParameters>
> | <ReportParameter Name="State">
> | <DataType>String</DataType>
> | <DefaultValue>
> | <Values>
> | <Value>CA</Value>
> | </Values>
> | </DefaultValue>
> | <Prompt>State</Prompt>
> | <ValidValues>
> | <ParameterValues>
> | <ParameterValue>
> | <Value>CA</Value>
> | <Label>California</Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>GA</Value>
> | <Label>Georgia </Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>NY</Value>
> | <Label>New York</Label>
> | </ParameterValue>
> | <ParameterValue>
> | <Value>WA</Value>
> | <Label>Washington</Label>
> | </ParameterValue>
> | </ParameterValues>
> | </ValidValues>
> | </ReportParameter>
> | </ReportParameters>
> |
> | Within the <DataSets> element, I could only find <Query> with
> <CommandText>
> | as follows (some details omitted):
> |
> | <CommandText>=" ... State = " & Parameters!State.Value</CommandText>
> |
> |
> | Have I done anything wrong? It seems to work for me so far. I did get
> the
> | right selected parameter value when processing the report though.
> |
> | Thanks again for your help.
> | Jenny
> |
> |
> |
> |
> | "Peter Yang [MSFT]" wrote:
> |
> | > Hello Jenny,
> | >
> | > Report rdl contains dataset section that describes data fields and
> query.
> | > Dataset section may contain one or more datasets depending on the
> report
> | > layout. Each dataset entry has query section with command text entry
> and
> | > query parameters. This is also true for data processing extension
> query.
> | > For example:
> | >
> | > <Query>
> | > <DataSourceName>AdventureWorks</DataSourceName>
> | > <CommandText>SELECT C.FirstName + ' ' + C.LastName AS
> | > Employee, DATEPART(Year, SOH.OrderDate) AS OrderYear,
> | > DATEPART(Month, SOH.OrderDate) AS OrderMonthNum,
> | > DATENAME(Month, SOH.OrderDate) AS OrderMonth, SUM(SOD.LineTotal) AS
> Sales
> | > FROM Sales.SalesOrderHeader SOH INNER JOIN
> | > Sales.SalesOrderDetail SOD ON SOH.SalesOrderID => | > SOD.SalesOrderID INNER JOIN
> | > Sales.SalesPerson SP ON SOH.SalesPersonID => SP.SalesPersonID
> | > INNER JOIN
> | > HumanResources.Employee E ON SP.SalesPersonID => E.EmployeeID
> | > INNER JOIN
> | > Person.Contact C ON E.ContactID = C.ContactID
> | > WHERE (DATEPART(Year, SOH.OrderDate) <= @.ReportYear - 1 OR
> | > DATEPART(Year, SOH.OrderDate) = @.ReportYear AND
> DATEPART(Month,
> | > SOH.OrderDate) <= @.ReportMonth) AND
> | > (SOH.SalesPersonID = @.EmpID)
> | > GROUP BY C.FirstName + ' ' + C.LastName, SOH.SalesPersonID,
> | > DATEPART(Year, SOH.OrderDate),
> | > DATEPART(Month, SOH.OrderDate), DATENAME(Month,
> | > SOH.OrderDate)</CommandText>
> | > <QueryParameters>
> | > <QueryParameter Name="@.ReportYear">
> | > <Value>=Parameters!ReportYear.Value</Value>
> | > </QueryParameter>
> | > <QueryParameter Name="@.ReportMonth">
> | > <Value>=Parameters!ReportMonth.Value</Value>
> | > </QueryParameter>
> | > <QueryParameter Name="@.EmpID">
> | > <Value>=Parameters!EmpID.Value</Value>
> | > </QueryParameter>
> | > </QueryParameters>
> | > <Timeout>30</Timeout>
> | > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> | > </Query>
> | >
> | > No matter how you implement your data extension or cutom query
> designer,
> | > the result rdl shall include the commandtext and parameters you want.
> | >
> | > I did not find any limiatation in number of parameters in a report and
> I
> | > think it is limited by server performance though.
> | >
> | > Regards,
> | >
> | > Peter Yang
> | > MCSE2000/2003, MCSA, MCDBA
> | > Microsoft Online Partner Support
> | >
> | > When responding to posts, please "Reply to Group" via your newsreader
> so
> | > that others may learn and benefit from your issue.
> | >
> | > =====================================================> | >
> | >
> | >
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | >
> | > --
> | > | Thread-Topic: Data processing extension and parameters
> | > | thread-index: AcXvg6xl+JTGVcWuQlyb36CPBUDX8A==> | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > <yinjennytam@.newsgroup.nospam>
> | > | References: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > <y78iI7y7FHA.832@.TK2MSFTNGXA02.phx.gbl>
> | > | Subject: RE: Data processing extension and parameters
> | > | Date: Tue, 22 Nov 2005 08:42:06 -0800
> | > | Lines: 86
> | > | Message-ID: <AED34EDB-530C-4060-A3FA-3F82AFD91536@.microsoft.com>
> | > | MIME-Version: 1.0
> | > | Content-Type: text/plain;
> | > | charset="Utf-8"
> | > | Content-Transfer-Encoding: 7bit
> | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | Content-Class: urn:content-classes:message
> | > | Importance: normal
> | > | Priority: normal
> | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:63759
> | > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > |
> | > | Thanks for your reply. Yes if I append the
> | > Parameters!{ParameterName}.value
> | > | to my query, it does work and I get the right value selected by
> users.
> | > |
> | > | However, my main concern is that the query used in my data extension
> has
> | > its
> | > | own syntax and I'm thinking of not using the generic query designer
> but
> | > my
> | > | own custom query designer (contains some text boxes and combo boxes
> for
> | > | example to generate a query from the user inputs). That is, to
> | > accomodate
> | > | parameters in reports, the user will need to append
> | > | Parameters!{ParameterName}.value to the query command text as well.
> Do I
> | > | understand this correctly?
> | > |
> | > | BTW, is there a limit on number of parameters used in a report? I
> don't
> | > | think so. Correct?
> | > |
> | > | Thanks a lot!
> | > | Jenny
> | > |
> | > |
> | > | "Peter Yang [MSFT]" wrote:
> | > |
> | > | > Hello Jenny,
> | > | >
> | > | > When you reference parameter value in report, ou could use
> | > | > Parameters!{ParameterName}.value. However, as for parameter for
> data
> | > | > processing extension of data source, it depends on how you
> implement
> | > this.
> | > | > For data processing extension s for SQL server, you could use
> | > @.varaiable
> | > | > for parameter in SQL query. You may want to try this with your data
> | > | > procssing extension to test.
> | > | >
> | > | > Best Regards,
> | > | >
> | > | > Peter Yang
> | > | > MCSE2000/2003, MCSA, MCDBA
> | > | > Microsoft Online Partner Support
> | > | >
> | > | > When responding to posts, please "Reply to Group" via your
> newsreader
> | > so
> | > | > that others may learn and benefit from your issue.
> | > | >
> | > | > =====================================================> | > | >
> | > | >
> | > | >
> | > | > This posting is provided "AS IS" with no warranties, and confers no
> | > rights.
> | > | >
> | > | > --
> | > | > | Thread-Topic: Data processing extension and parameters
> | > | > | thread-index: AcXu+7x8ZtRJnUVcSr2mLOF0DypeJA==> | > | > | X-WBNR-Posting-Host: 209.17.156.248
> | > | > | From: "=?Utf-8?B?eWluamVubnl0YW1AbmV3c2dyb3VwLm5vc3BhbQ==?="
> | > | > <yinjennytam@.newsgroup.nospam>
> | > | > | Subject: Data processing extension and parameters
> | > | > | Date: Mon, 21 Nov 2005 16:29:01 -0800
> | > | > | Lines: 11
> | > | > | Message-ID: <007999F3-C081-419F-A02C-DDD114A18A42@.microsoft.com>
> | > | > | MIME-Version: 1.0
> | > | > | Content-Type: text/plain;
> | > | > | charset="Utf-8"
> | > | > | Content-Transfer-Encoding: 7bit
> | > | > | X-Newsreader: Microsoft CDO for Windows 2000
> | > | > | Content-Class: urn:content-classes:message
> | > | > | Importance: normal
> | > | > | Priority: normal
> | > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > | > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250

Data Modeling Question - Medical Lab Test Results

I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>I believe your "vertical" approach sounds like a normalized design. You
would have a table of possible tests, another of patients and then an
associative object (junction table) that links a patient to a test. You'd
have to allow for multiples of the same test on the same patient, though
likely this would not occur on the same day. That table would likely look
like:
PatientTests
PatientID -> FK to Patients
TestID -> FK to Tests
TestDate
Remarks
Hopefully, the table of tests is straightforward, though it is possible that
certain tests would have attributes that would not apply to other tests. In
that case, you could have exclusive subtypes, with a parent test table -
containing the common attributes - and child tables with the specialized
stuff.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frankie" <A@.B.COM> wrote in message
news:%23P%23ICzxkGHA.2272@.TK2MSFTNGP05.phx.gbl...
I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>|||>> My question is this: What would you recommend for how we go about the
Do you know the benefits of having a normalized schema? Do you understand
the drawbacks of "denormalized" structures? You have already stated that
your preference is to identify the logical groupings of lab results and
place those in their own table. Therefore, the answer should be obvious.
Anith