I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kritisql
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Thursday, March 29, 2012
Dynamic selecting a table
I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
Dynamic selecting a table
I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti
Tuesday, March 27, 2012
Dynamic report
Hi all,
I want to create a dynamic report by using code, for ex a report with dynamic column , but dont know where to start and which method is best.
Could you show me some examples about this ?
ThanksThere's a dll that I use called p2smon.dll. This dll has 2 functions:
From scr8_ttxado.pdf which can be found by searching http://support.businessobjects.com/search/advsearch.asp
Using Active Data driver functions
The Active Data driver P2smon.dll (Pdsmon.dll for 16-bit) has two functions that create a TTX file based on a recordset that exists in the VB project. This is the preferred method to create a Data Definition file, as the TTX file will always match the recordset (or vice versa). The two functions available in the DLL are:
CreateFieldDefFile() - Creates a TTX file at runtime based on a recordset.
CreateReportOnRuntimeDS() - Creates a TTX file at runtime based on a recordset. A report (RPT) file is also created off the new TTX file, and there is an option to open the RPT file in the Crystal Reports designer.
These functions are purely for development purposes (they are not required at runtime).
NOTE CreateReportOnRuntimeDS( ) does not place any fields on the report. A blank report is created instead.
The function declarations for CreateFieldDefFile() and CreateReportOnRuntimeDS() are as follows:
CreateFieldDefFile()
Declare Function CreateFieldDefFile Lib "p2smon.dll"(lpUnk As Object, ByVal fileName As String, ByVal bOverWriteExistingFile As Long) As Long
Parameter Description
- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an IUnknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.
- Filename The path and file name of the field definition file to be created.
- bOverWriteExistingFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.
CreateReportOnRuntimeDS()
Declare Function CreateReportOnRuntimeDS Lib "p2smon.dll" ( lpUnk As Object, ByVal reportFile As String, ByVal fieldDefFile As String, ByVal bOverWriteFile As Long, ByVal bLaunchDesigner As Long) As Long
Parameter Description
- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an Iunknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.
- ReportFile The path and file name of the report file to be created.
- FieldDefFile The path and file name of the field definition file to be created.
- BoverWriteFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.
- BlaunchDesigner If True (1), Crystal Reports is launched with the newly created report file opened. Crystal Reports must be installed on the system.
NOTE Since a TTX file is a tab-separated text file, it can be manually created or edited using Microsoft Notepad or any other text editor. This method is not recommended for creating TTX files due to possible typing errors.sql
I want to create a dynamic report by using code, for ex a report with dynamic column , but dont know where to start and which method is best.
Could you show me some examples about this ?
ThanksThere's a dll that I use called p2smon.dll. This dll has 2 functions:
From scr8_ttxado.pdf which can be found by searching http://support.businessobjects.com/search/advsearch.asp
Using Active Data driver functions
The Active Data driver P2smon.dll (Pdsmon.dll for 16-bit) has two functions that create a TTX file based on a recordset that exists in the VB project. This is the preferred method to create a Data Definition file, as the TTX file will always match the recordset (or vice versa). The two functions available in the DLL are:
CreateFieldDefFile() - Creates a TTX file at runtime based on a recordset.
CreateReportOnRuntimeDS() - Creates a TTX file at runtime based on a recordset. A report (RPT) file is also created off the new TTX file, and there is an option to open the RPT file in the Crystal Reports designer.
These functions are purely for development purposes (they are not required at runtime).
NOTE CreateReportOnRuntimeDS( ) does not place any fields on the report. A blank report is created instead.
The function declarations for CreateFieldDefFile() and CreateReportOnRuntimeDS() are as follows:
CreateFieldDefFile()
Declare Function CreateFieldDefFile Lib "p2smon.dll"(lpUnk As Object, ByVal fileName As String, ByVal bOverWriteExistingFile As Long) As Long
Parameter Description
- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an IUnknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.
- Filename The path and file name of the field definition file to be created.
- bOverWriteExistingFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.
CreateReportOnRuntimeDS()
Declare Function CreateReportOnRuntimeDS Lib "p2smon.dll" ( lpUnk As Object, ByVal reportFile As String, ByVal fieldDefFile As String, ByVal bOverWriteFile As Long, ByVal bLaunchDesigner As Long) As Long
Parameter Description
- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an Iunknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.
- ReportFile The path and file name of the report file to be created.
- FieldDefFile The path and file name of the field definition file to be created.
- BoverWriteFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.
- BlaunchDesigner If True (1), Crystal Reports is launched with the newly created report file opened. Crystal Reports must be installed on the system.
NOTE Since a TTX file is a tab-separated text file, it can be manually created or edited using Microsoft Notepad or any other text editor. This method is not recommended for creating TTX files due to possible typing errors.sql
Dynamic query in a function
Greetings:
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad
and produce a poor
execution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad and produce a poor
> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>
I am trying to write a view that includes a column derived from a function
call.
The function is written thus:
CREATE FUNCTION dbo.fnc_CapArrayValue
(@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
RETURNS REAL
AS
BEGIN
Declare @.Result REAL
SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id =
@.measurementid) and (tracking_id = @.trackingid))
RETURN @.Result
END
GO
What am I doing wrong here? When I run the view this function resides in, I
get a timeout error.Does the same query executed against the view return results in a resonable
time?
If not then posting the view definition and the definitions and indexes of
the underlying tables would give a few clues.
Is this an extract of somethimg much more complicated - if not I cannot see
much reason for using the function - unless you need it in a constraint or
similar.
Re-reading your post - is the function being used in a view definition, and
itself retriving data from a view (assuming vw_CapArrayData is a view?)
BTW you may get a deluge saying prefixing vw on the front of view names is a
bad idea, which I happen to think it is - be warned.
I suspect that the combination of a view based on a function, based on a
view has caused the optimiser to get a tad
and produce a poorexecution plan.
Mike John
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:y2ikg.3677$Oh1.923@.news01.roc.ny...
> Greetings:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL
> AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData where (measurement_id
> =
> @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
> What am I doing wrong here? When I run the view this function resides in,
> I
> get a timeout error.
>|||You cannot use Dynamic SQL inside functions. The workaround is to use a CASE
expression like:
SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
WHEN 'value_2' THEN col_2
..
WHEN 'value_n' THEN col_n
END
FROM dbo.vw_CapArrayData
WHERE measurement_id = @.m_id
AND tracking_id = @.t_id ) ;
Alternatively you can create a procedure to get this information, however
you'll have to make careful considerations for security and performance.
Anith|||Thomas Mick (nospam@.nospam.com) writes:
> I am trying to write a view that includes a column derived from a function
> call.
> The function is written thus:
> CREATE FUNCTION dbo.fnc_CapArrayValue
> (@.MEASUREMENTID INT,@.TRACKINGID BIGINT,@.JET INT)
> RETURNS REAL AS
> BEGIN
> Declare @.Result REAL
> SET @.RESULT = (select @.JET from dbo.vw_CapArrayData
> where (measurement_id = @.measurementid) and (tracking_id = @.trackingid))
> RETURN @.Result
> END
> GO
This function appears funny to me. It will return the value in @.JET if
there is a matching row, else it will return NULL. This could be expressed
more clearly.
Avoid scalar UDFs that performs data access. This can cause quite severe
performance degradation, in queries that calls the UDF, as the query
more or less gets converted to a cursor behind the scenes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I am trying to write a view that includes a column derived from a functio
n call. <<
Take a freshman course in BASIC Software Engineering. Try to be sober
on the days that that coupling and cohesion are discussed. It will
keep you from public embarassment in the future. You might also want
to Google over to Larry O'Brien's current column this month for a
reminder.
You even even put prefixes like "fnc_" on names! You never read
ISO-11179. Measurement_id? There is no name possible in a data model.
So very wrong! BIGINTs because you have more data than there are atoms
in the Universe!!
Almost everything. Forget the crashes; your whole mindset is wrong.
Post specs and we can try to get you something that is correct.
--CELKO--|||Are you always this condescending? I am relatively new to the world of
databases, not a seasoned veteran; hence the reason I am asking a question.
I appreciate what you have inputted, but not the attitude used to deliver
it.
BTW, I am in no way embarrassed as I am working on curing my ignorance and
appreciate all the help I can get.
Thomas
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
> Take a freshman course in BASIC Software Engineering. Try to be sober
> on the days that that coupling and cohesion are discussed. It will
> keep you from public embarassment in the future. You might also want
> to Google over to Larry O'Brien's current column this month for a
> reminder.
> You even even put prefixes like "fnc_" on names! You never read
> ISO-11179. Measurement_id? There is no name possible in a data model.
> So very wrong! BIGINTs because you have more data than there are atoms
> in the Universe!!
>
> Almost everything. Forget the crashes; your whole mindset is wrong.
> Post specs and we can try to get you something that is correct.
> --CELKO--
>|||Thank you for your response, Mike,
Allow me to better describe what I am attempting to do without divulging
confidential information.
I have two views that I am joining together with related data. On the one
side is yield data related to particular failures. On the other side is data
from a related process generated during the manufacturing processes prior to
generating the yield data; this data is related by three of their fields.
The two views link together predictably and the data returned is valid.
However, I must also get a related value from an array table to insert into
the view I am creating; hence the reason I was exploring the use of a
function.
I hope this is more understandable, if not I'm screwed; this problem has me
at my wits end right now.
Thomas Mick
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:uyz7m8KkGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Does the same query executed against the view return results in a
> resonable time?
> If not then posting the view definition and the definitions and indexes of
> the underlying tables would give a few clues.
> Is this an extract of somethimg much more complicated - if not I cannot
> see much reason for using the function - unless you need it in a
> constraint or similar.
> Re-reading your post - is the function being used in a view definition,
> and itself retriving data from a view (assuming vw_CapArrayData is a
> view?) BTW you may get a deluge saying prefixing vw on the front of view
> names is a bad idea, which I happen to think it is - be warned.
> I suspect that the combination of a view based on a function, based on a
> view has caused the optimiser to get a tad
and produce a poor> execution plan.
> Mike John
> "Thomas Mick" <nospam@.nospam.com> wrote in message
> news:y2ikg.3677$Oh1.923@.news01.roc.ny...
>|||Erland,
Thank you for your constructive response. I truly appreciate it and will
learn accordingly.
Thomas Mick
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E429816C9FYazorman@.127.0.0.1...
> Thomas Mick (nospam@.nospam.com) writes:
> This function appears funny to me. It will return the value in @.JET if
> there is a matching row, else it will return NULL. This could be expressed
> more clearly.
> Avoid scalar UDFs that performs data access. This can cause quite severe
> performance degradation, in queries that calls the UDF, as the query
> more or less gets converted to a cursor behind the scenes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Just ignore him Thomas, most be do - he's an ignorant, condesending idiot
who has a high opinion of himself, oh and watch out for his stuck in the
80's model thinking.
Don't let this single fool put you off posting here again!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Thomas Mick" <nospam@.nospam.com> wrote in message
news:Hdnkg.3705$Oh1.2508@.news01.roc.ny...
> Are you always this condescending? I am relatively new to the world of
> databases, not a seasoned veteran; hence the reason I am asking a
> question. I appreciate what you have inputted, but not the attitude used
> to deliver it.
> BTW, I am in no way embarrassed as I am working on curing my ignorance and
> appreciate all the help I can get.
> Thomas
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1150410406.196985.12150@.y41g2000cwy.googlegroups.com...
>|||I have a feeling I already know the answer to this, but I have to ask
anyway; can a stored procedure be run from within a function? Or
alternatively, can I run a stored procedure from within a view?
You see, I need to insert this value into a column of a view I am creating
for an engineer; a stored procedure would most likely be the best method of
retrieving what I need.
Thomas Mick
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eWGUFjMkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> You cannot use Dynamic SQL inside functions. The workaround is to use a
> CASE expression like:
> SET @.r = ( SELECT CASE @.p WHEN 'value_1' THEN col_1
> WHEN 'value_2' THEN col_2
> ...
> WHEN 'value_n' THEN col_n
> END
> FROM dbo.vw_CapArrayData
> WHERE measurement_id = @.m_id
> AND tracking_id = @.t_id ) ;
> Alternatively you can create a procedure to get this information, however
> you'll have to make careful considerations for security and performance.
> --
> Anith
>
Monday, March 26, 2012
Dynamic Query
Hi there
I am trying to create a report with a proc that has dynamic column headers.
eg TonsHarvested1998, TonsHarvested2000, etc.
The problem I am experiencing is that these columns are not displayed in the
fields editor box and I have no idea as to how I can get them displayed in
my textboxes.
Can anyone give me any ideas as to how I can sort this out.
Thanks
KershniFirst run the static version of the query which will populate the fields
list and then replace the query with the dynamic version. You can also
populate the fields list manually (Dataset dialog box -> Fields tab.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kershni Chetty" <support@.sqrsoftware.com> wrote in message
news:%23xwvy6GeEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Hi there
> I am trying to create a report with a proc that has dynamic column
headers.
> eg TonsHarvested1998, TonsHarvested2000, etc.
> The problem I am experiencing is that these columns are not displayed in
the
> fields editor box and I have no idea as to how I can get them displayed in
> my textboxes.
> Can anyone give me any ideas as to how I can sort this out.
> Thanks
> Kershni
>sql
I am trying to create a report with a proc that has dynamic column headers.
eg TonsHarvested1998, TonsHarvested2000, etc.
The problem I am experiencing is that these columns are not displayed in the
fields editor box and I have no idea as to how I can get them displayed in
my textboxes.
Can anyone give me any ideas as to how I can sort this out.
Thanks
KershniFirst run the static version of the query which will populate the fields
list and then replace the query with the dynamic version. You can also
populate the fields list manually (Dataset dialog box -> Fields tab.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kershni Chetty" <support@.sqrsoftware.com> wrote in message
news:%23xwvy6GeEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Hi there
> I am trying to create a report with a proc that has dynamic column
headers.
> eg TonsHarvested1998, TonsHarvested2000, etc.
> The problem I am experiencing is that these columns are not displayed in
the
> fields editor box and I have no idea as to how I can get them displayed in
> my textboxes.
> Can anyone give me any ideas as to how I can sort this out.
> Thanks
> Kershni
>sql
Dynamic pivot of resultset
Hi,
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,
> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>
I need to pivot and sum a resultset (based on a particular column) but I
don't know how many cols the pivot would result in.
Say you got resultset:
[C0] [C1] [C2]
1 100 OO
1 200 KK
2 245 OO
2 244 OO
3 188 KK
4 124 AR
- would result in:
[C0] [OO] [KK] [AR]
1 100 200 0
2 489
3 188
4 124
- only, I don't know how many distinct [C1]'s there are in the resultset
Unfortunately this has to be executed on SQL2000 (with new functions in
SQL2005 it would be much easier)
Thanks in advance
Kr. SorenCheck this out:
http://www.sqlservercentral.com/scr...butions/931.asp|||Sorry,
> - only, I don't know how many distinct [C1]'s there are in the resultset
Just noticed an error in above line - [C1] should be [C2] :)
Kr. Soren|||If you don't know the number of columns to be pivoted, then even SQL 2005
won't be much help.
This solution by Itzik Ben-Gan might help you:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML
http://milambda.blogspot.com/|||Sure it will, I could then do it in the CLR!
- for what i'm doing this would also be far more efficiant :)
Soren
"ML" <ML@.discussions.microsoft.com> skrev i en meddelelse
news:5F8E3B65-B229-45D7-8EDA-D628797D852E@.microsoft.com...
> If you don't know the number of columns to be pivoted, then even SQL 2005
> won't be much help.
> This solution by Itzik Ben-Gan might help you:
> http://www.windowsitpro.com/Article...15608.html?Ad=1
>
> ML
> --
> http://milambda.blogspot.com/|||Ever hear of RAC? :)
www.rac4sql.net
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:uLsbnqkAGHA.2620@.tk2msftngp13.phx.gbl...
> Hi,
> I need to pivot and sum a resultset (based on a particular column) but I
> don't know how many cols the pivot would result in.
> Say you got resultset:
> [C0] [C1] [C2]
> 1 100 OO
> 1 200 KK
> 2 245 OO
> 2 244 OO
> 3 188 KK
> 4 124 AR
> - would result in:
> [C0] [OO] [KK] [AR]
> 1 100 200 0
> 2 489
> 3 188
> 4 124
> - only, I don't know how many distinct [C1]'s there are in the resultset
> Unfortunately this has to be executed on SQL2000 (with new functions in
> SQL2005 it would be much easier)
> Thanks in advance
> Kr. Soren
>
Wednesday, March 21, 2012
Dynamic name of result column in s-proc?
How can I make this stored procedure concept work:
---------------
Create Procedure MyProc @.MyValue varchar(5)
As
Declare @.ColumnName as varchar(11)
Set @.ColumnName = 'Price_' + @.MyValue
Select Sum(Price) As @.ColumnName --??
From MyTable where Item = @.MyValue
Return
---------------
Using @.MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
BjornUSE Northwind
GO
DECLARE @.ColumnName sysname, @.sql varchar(8000), @.myValue varchar(5)
SELECT @.myValue = '23'
SET @.ColumnName = 'Price_' + @.MyValue
SELECT @.sql = 'SELECT Sum(UnitPrice) As ' + @.ColumnName
+ ' FROM [Order Details] '
+ ' WHERE ProductID = ' + @.MyValue
SELECT @.sql
EXEC(@.sql)|||Thank you thank you!
Bjornsql
---------------
Create Procedure MyProc @.MyValue varchar(5)
As
Declare @.ColumnName as varchar(11)
Set @.ColumnName = 'Price_' + @.MyValue
Select Sum(Price) As @.ColumnName --??
From MyTable where Item = @.MyValue
Return
---------------
Using @.MyValue = 23 should make the Sum(Price) column name 'Price_23'. It's not working. What am I doing wrong?
BjornUSE Northwind
GO
DECLARE @.ColumnName sysname, @.sql varchar(8000), @.myValue varchar(5)
SELECT @.myValue = '23'
SET @.ColumnName = 'Price_' + @.MyValue
SELECT @.sql = 'SELECT Sum(UnitPrice) As ' + @.ColumnName
+ ' FROM [Order Details] '
+ ' WHERE ProductID = ' + @.MyValue
SELECT @.sql
EXEC(@.sql)|||Thank you thank you!
Bjornsql
Dynamic measures
Hi,
I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.
The parameter values has, Actual, Budget, Target
For the one field, base on the above parameter, will select,
if the value for the parameter is Actual, then we will have only a column of Actual values
if it's Budget, then we will have only a column of Budget values
if it's Target, then we will have only a column of Target values
How should I write the mdx query for this?
I'm really desperate for the answer.
Thanks a lot for your help.
The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.sqlDynamic measures
Hi,
I am new to mdx. Based on the requirement, I need to dynamically loaded up a column of measures depending on the selection of a parameter.
The parameter values has, Actual, Budget, Target
For the one field, base on the above parameter, will select,
if the value for the parameter is Actual, then we will have only a column of Actual values
if it's Budget, then we will have only a column of Budget values
if it's Target, then we will have only a column of Target values
How should I write the mdx query for this?
I'm really desperate for the answer.
Thanks a lot for your help.
The recommended way to do this is to create a seperate scenario dimension that jsut contains the members Actual, Budget, and Target. Then depending on which of theses members you have selected in your query, you will see the appropriate values in your measures.Monday, March 19, 2012
dynamic join based on column value?
Hi,
I have three tables.
The "Master table has a recordid, a masterID, a "IsSubField" and other stuff.
I need to do a join to a second table based on the MasterID...
However for each record, if the "IsSubField" has a True value then it has to use table A to JOIN to, where as if it's False, then it uses table B.
Make sense?
Anyone got any pointers?
Thanks in advance
James
Something like this might work (untested):
Code Snippet
SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM MasterTable m
JOIN TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
JOIN TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)
I am not sure that I get this; maybe something like this?
|||--drop table dbo.masterTable
--drop table dbo.TableA
--drop table dbo.tableB
gocreate table dbo.masterTable
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)
go
insert into dbo.masterTable
select 1, 11, 21, 'True' union all
select 2, 12, 22, 'False' union all
select 3, 13, 23, 'True'
gocreate table dbo.TableA
( MasterID integer,
Col2 integer
)
goinsert into tableA
select 1, 101 union all
select 2, 102 union all
select 3, 103
gocreate table dbo.TableB
( MasterID integer,
Col2 integer
)
goinsert into tableB
select 1, 201 union all
select 2, 202 union all
select 3, 203
go
SELECT
m.MasterId,
m.Col1 as m_col1,
m.Col2 as m_col2,
b.Col2 as [subfield?]
FROM MasterTable m
JOIN
( select masterID,
'True' as isSubField,
Col2
from TableA
union all
select masterId,
'False' as isSubField,
Col2
from TableB
) b
on m.MasterId = b.masterId
and m.isSubfield = b.isSubfield/*
MasterId m_col1 m_col2 subfield?
-- -- -- --
1 11 21 101
2 12 22 202
3 13 23 103
*/
Hi,
thanks for that - I haven't tested it yet, but the snytax is working.. so just need to get a few test records in now..
|||Hi,
I have this as my query - but don't get any results:
SELECT m.MaterialID, m.MasterID, a.ProductName, b.MaterialName, m.IsSubMaterial
FROM redrose1.Materials_Listings m INNER JOIN
dbo.Products a ON m.MasterID = a.ProductID AND (m.IsSubMaterial = 'No' OR
m.IsSubMaterial IS NULL) INNER JOIN
redrose1.Materials b ON m.MasterID = b.MaterialID AND m.IsSubMaterial = 'Yes'
WHERE (m.MaterialID = @.MaterialID)
any ideas?
|||Both JOINs should be LEFT JOIN.
Code Snippet
SET NOCOUNT ON
DECLARE @.MasterTable table
( MasterID integer,
Col1 integer,
col2 integer,
isSubField varchar(5)
)
INSERT INTO @.MasterTable VALUES ( 1, 11, 21, 'True' )
INSERT INTO @.MasterTable VALUES ( 2, 12, 22, 'False' )
INSERT INTO @.MasterTable VALUES ( 3, 13, 23, 'True' )
DECLARE @.TableA table
( MasterID integer,
Col2 integer
)
INSERT INTO @.TableA VALUES ( 1, 101 )
INSERT INTO @.TableA VALUES ( 2, 102 )
INSERT INTO @.TableA VALUES ( 3, 103 )
DECLARE @.TableB table
( MasterID integer,
Col2 integer
)
INSERT INTO @.TableB VALUES ( 1, 201 )
INSERT INTO @.TableB VALUES ( 2, 202 )
INSERT INTO @.TableB VALUES ( 3, 203 )
SELECT
m.Col1,
m.Col2,
a.Col2,
b.Col2
FROM @.MasterTable m
LEFT JOIN @.TableA a
ON ( m.MasterID = a.MasterID
AND m.IsSubField = 'TRUE'
)
LEFT JOIN @.TableB b
ON ( m.MasterID = b.MasterID
AND m.IsSubField = 'False'
)
Col1 Col2 Col2 Col2
-- -- -- --
11 21 101 NULL
12 22 NULL 202
13 23 103 NULL
Dynamic If Update() in Trigger - Urgent!
Hi All
I have a question about generating dynmamicly If Update() statement in a
trigger..
in My db, there is a table that holds some column names of an another table.
for example;
Columns Table-A: Col1, Col2, Col3, Col4,Col5
Table-B: Col2, Col5 (The selected columns of Table A)
Then, in the Trigger of Table-A I use;
Select name from syscolumns where id=object_id('Table-A')
fetch next from TableA_Cursor into @.strColName
then, I used a statement like this..
if UPDATE(' + @.strColName + ')
But it gives "incorrect syntax" error..
How can I write this line?
Thanks alot in advance...
--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.
If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.
If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.
If you need more help, please post a fuller description of your problem
including DDL and sample data.
--
David Portas
SQL Server MVP
--
Dynamic header column names in Excel
Folks,
I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.
With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.
When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).
How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...
Thank you.
I am afraid that is something that is not that easy in SSIS. Jamie, has a serie of posts that talk about some differences with DTS:
http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx
In SSIS the data flow needs to know the column metadata at design time. One option could be to generate packages programatically. Books on line has a chapter about that.
|||Hi:
I have a related problem. I have multiple packages, but only a single table structure, and so I worked around by making a template XLS file, and using a filesystem task to make copies. The template has the required col headers.
Now the problem is that when a data structure changes, I have to re-do all my packages to incorporate the changed metadata.
Your problem description gave me an idea, and I am planning to try out the following:
1. Execute SQL task to call a stored procedure that drops my temp_table, and then inserts my resultset into temp_table (which now has all the columns required).
2. Have a script task that makes a ADO.net connection, gets a dataset, saves my data from temp_table to an XML file, creates a new XLS file, and calls the OpenXML method to open my data in the excel.
This will probably need VSTO installed on the same machine as BIDS. Since the Script Task only allows reference to managed assemblies from a particular path(s), I would need to create a custom .net DLL and store in those paths. The .net DLL will internally use Office Interop COM if I have office 2003 or lower.
Else I could simply save XML files and associate XML files with Excel by default.
I had done something similar in VB.net, and the only problem I see is that the XLS files thus created are bloated. Opening them and saving them again can compress the file to around half its size.
Does this approach look feasible? Maybe the gurus on this forum can point out flaws in this?
HTH
Kar
|||Rafael and Karfast -
I really appreciate your replies. I took a look at Jamie's post mentioned by Rafael and I am just speechless. My concern in this migration from DTS to SSIS is all what we had in DTS and we are not having in SSIS. I truly understand some losses are needed towards a better final result but this case for an example (dynamic header column names) really seems not to be possible to be implemented in SSIS, or at least will require a lot of code intelligence versus something that was really simple in DTS.
I work for EDS and DBAs here are saying we will migrate soon for the next SQL Server 2005 version (which does not support DTS) and I am just deadly scared of what is gonna happen when we truly have to migrate all our packages.
I wonder why Microsoft still has these surprises for us and maybe this is something that they were willing to change in SSIS? As Karfast said, I guess I will wait for our SSIS gurus to discuss this a little bit more.
Thanks to all.
|||
Gabriel,
Perhaps few functionalities have been changed from DTS, but personally SSIS has a lot more potential to become an Enterprise ETL platform. How familiar are you with SSIS at this point? perhaps you should look a little bit into the all other features it offers. I never used DTS, but I cerntarly understand how surprising is to find out that something like that is missing.
Dynamic Grouping
Hi all
I have a table which has employee information like
empno, empname, empsalary, empmgr. The empmgr column points to empno.
I want to create a report which will dynamically recognise the employees
under a employee and show his salary. The other requirement is that i want to
enable drill down, ie. at first one employee and his salary will appear who
is the head. On drilling down all the employee directly reporting to him
should appear and so on and so forth. Is this possible in reporting services.RS has drilldown and drillthrough. With drill down you have all the info
returned in a single dataset. You set up your grouping and then hide the
detail rows based on the previous field. The downside to this can be the
amount of information you are bringing back. You do not want to be bringing
back more than a thousand or so records (definitely not 50,000+ records). So
it depends on your situation. Drillthrough allows the user to click on a
field and automatically jump to another report, filling in the report
parameters and pulling up the report. I find that this works very well and
is intuitive for the user. They are very used to this from using the web. I
make the text in the field they should click on blue and underlined. In
books online search on drilldown and drillthrough.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> Hi all
> I have a table which has employee information like
> empno, empname, empsalary, empmgr. The empmgr column points to empno.
> I want to create a report which will dynamically recognise the employees
> under a employee and show his salary. The other requirement is that i want
to
> enable drill down, ie. at first one employee and his salary will appear
who
> is the head. On drilling down all the employee directly reporting to him
> should appear and so on and so forth. Is this possible in reporting
services.|||Hi Bruce
I am able to acheive what i was looking for. But i am also facing a problem.
The first time i run the report it showed like this
empid empsalary
(+)1 2000
on drilling down on the 1 i see like this.
empid empsalary
(-)1 2000
(+)2 1000
(+)3 3000
4 4000
on drill down on 2 i get some thing like this.
empid empsalary
(-)1 2000
(-)2 1000
5 500
6 350
(+)3 3000
4 4000
Now at this point when i want to drill up to the top most item by clicking
on 1, i expect everything to be collapsed. But i am getting something like
this
empid empsalary
(+)1 2000
5 500
6 350
Which is wrong it should show only the first record. Can anybody help in
resolving this. I hope i have given you the correct picture of my problem.
Please help me with this.
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>|||You can set the visibility of a row or of a field. So you set the visibility
of the row based on the field that you will have the +/- with. I have two
levels and it works as you expect it to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> Hi Bruce
> I am able to acheive what i was looking for. But i am also facing a
problem.
> The first time i run the report it showed like this
> empid empsalary
> (+)1 2000
> on drilling down on the 1 i see like this.
> empid empsalary
> (-)1 2000
> (+)2 1000
> (+)3 3000
> 4 4000
> on drill down on 2 i get some thing like this.
> empid empsalary
> (-)1 2000
> (-)2 1000
> 5 500
> 6 350
> (+)3 3000
> 4 4000
> Now at this point when i want to drill up to the top most item by clicking
> on 1, i expect everything to be collapsed. But i am getting something like
> this
> empid empsalary
> (+)1 2000
> 5 500
> 6 350
> Which is wrong it should show only the first record. Can anybody help in
> resolving this. I hope i have given you the correct picture of my problem.
> Please help me with this.
> "Bruce L-C [MVP]" wrote:
> > RS has drilldown and drillthrough. With drill down you have all the info
> > returned in a single dataset. You set up your grouping and then hide the
> > detail rows based on the previous field. The downside to this can be the
> > amount of information you are bringing back. You do not want to be
bringing
> > back more than a thousand or so records (definitely not 50,000+
records). So
> > it depends on your situation. Drillthrough allows the user to click on a
> > field and automatically jump to another report, filling in the report
> > parameters and pulling up the report. I find that this works very well
and
> > is intuitive for the user. They are very used to this from using the
web. I
> > make the text in the field they should click on blue and underlined. In
> > books online search on drilldown and drillthrough.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > Hi all
> > >
> > > I have a table which has employee information like
> > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > >
> > > I want to create a report which will dynamically recognise the
employees
> > > under a employee and show his salary. The other requirement is that i
want
> > to
> > > enable drill down, ie. at first one employee and his salary will
appear
> > who
> > > is the head. On drilling down all the employee directly reporting to
him
> > > should appear and so on and so forth. Is this possible in reporting
> > services.
> >
> >
> >|||I have set the visibility to hidden and visibility can be toggeled by the
field that has +/- with , i.e. the employe id field. But its still not
working
"Bruce L-C [MVP]" wrote:
> You can set the visibility of a row or of a field. So you set the visibility
> of the row based on the field that you will have the +/- with. I have two
> levels and it works as you expect it to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> > Hi Bruce
> >
> > I am able to acheive what i was looking for. But i am also facing a
> problem.
> >
> > The first time i run the report it showed like this
> >
> > empid empsalary
> > (+)1 2000
> >
> > on drilling down on the 1 i see like this.
> >
> > empid empsalary
> > (-)1 2000
> > (+)2 1000
> > (+)3 3000
> > 4 4000
> >
> > on drill down on 2 i get some thing like this.
> >
> > empid empsalary
> > (-)1 2000
> > (-)2 1000
> > 5 500
> > 6 350
> > (+)3 3000
> > 4 4000
> >
> > Now at this point when i want to drill up to the top most item by clicking
> > on 1, i expect everything to be collapsed. But i am getting something like
> > this
> >
> > empid empsalary
> > (+)1 2000
> > 5 500
> > 6 350
> > Which is wrong it should show only the first record. Can anybody help in
> > resolving this. I hope i have given you the correct picture of my problem.
> > Please help me with this.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > RS has drilldown and drillthrough. With drill down you have all the info
> > > returned in a single dataset. You set up your grouping and then hide the
> > > detail rows based on the previous field. The downside to this can be the
> > > amount of information you are bringing back. You do not want to be
> bringing
> > > back more than a thousand or so records (definitely not 50,000+
> records). So
> > > it depends on your situation. Drillthrough allows the user to click on a
> > > field and automatically jump to another report, filling in the report
> > > parameters and pulling up the report. I find that this works very well
> and
> > > is intuitive for the user. They are very used to this from using the
> web. I
> > > make the text in the field they should click on blue and underlined. In
> > > books online search on drilldown and drillthrough.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > > Hi all
> > > >
> > > > I have a table which has employee information like
> > > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > > >
> > > > I want to create a report which will dynamically recognise the
> employees
> > > > under a employee and show his salary. The other requirement is that i
> want
> > > to
> > > > enable drill down, ie. at first one employee and his salary will
> appear
> > > who
> > > > is the head. On drilling down all the employee directly reporting to
> him
> > > > should appear and so on and so forth. Is this possible in reporting
> > > services.
> > >
> > >
> > >
>
>|||I don't know what to tell you. It works for me (I only go two levels deep
however, although that was the same as the example you gave). Do you have
groups too. This works in tandem with grouping.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:CB32D4AF-F7AE-4795-8E9C-CCA429565B63@.microsoft.com...
>I have set the visibility to hidden and visibility can be toggeled by the
> field that has +/- with , i.e. the employe id field. But its still not
> working
> "Bruce L-C [MVP]" wrote:
>> You can set the visibility of a row or of a field. So you set the
>> visibility
>> of the row based on the field that you will have the +/- with. I have
>> two
>> levels and it works as you expect it to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
>> > Hi Bruce
>> >
>> > I am able to acheive what i was looking for. But i am also facing a
>> problem.
>> >
>> > The first time i run the report it showed like this
>> >
>> > empid empsalary
>> > (+)1 2000
>> >
>> > on drilling down on the 1 i see like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (+)2 1000
>> > (+)3 3000
>> > 4 4000
>> >
>> > on drill down on 2 i get some thing like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (-)2 1000
>> > 5 500
>> > 6 350
>> > (+)3 3000
>> > 4 4000
>> >
>> > Now at this point when i want to drill up to the top most item by
>> > clicking
>> > on 1, i expect everything to be collapsed. But i am getting something
>> > like
>> > this
>> >
>> > empid empsalary
>> > (+)1 2000
>> > 5 500
>> > 6 350
>> > Which is wrong it should show only the first record. Can anybody help
>> > in
>> > resolving this. I hope i have given you the correct picture of my
>> > problem.
>> > Please help me with this.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > RS has drilldown and drillthrough. With drill down you have all the
>> > > info
>> > > returned in a single dataset. You set up your grouping and then hide
>> > > the
>> > > detail rows based on the previous field. The downside to this can be
>> > > the
>> > > amount of information you are bringing back. You do not want to be
>> bringing
>> > > back more than a thousand or so records (definitely not 50,000+
>> records). So
>> > > it depends on your situation. Drillthrough allows the user to click
>> > > on a
>> > > field and automatically jump to another report, filling in the report
>> > > parameters and pulling up the report. I find that this works very
>> > > well
>> and
>> > > is intuitive for the user. They are very used to this from using the
>> web. I
>> > > make the text in the field they should click on blue and underlined.
>> > > In
>> > > books online search on drilldown and drillthrough.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
>> > > > Hi all
>> > > >
>> > > > I have a table which has employee information like
>> > > > empno, empname, empsalary, empmgr. The empmgr column points to
>> > > > empno.
>> > > >
>> > > > I want to create a report which will dynamically recognise the
>> employees
>> > > > under a employee and show his salary. The other requirement is that
>> > > > i
>> want
>> > > to
>> > > > enable drill down, ie. at first one employee and his salary will
>> appear
>> > > who
>> > > > is the head. On drilling down all the employee directly reporting
>> > > > to
>> him
>> > > > should appear and so on and so forth. Is this possible in reporting
>> > > services.
>> > >
>> > >
>> > >
>>|||Bruce,
I use the drill thru however we have discovered an issue when the report
is deployed to a portal. User has a portal screen with content for selection
on the left and display area on the right. User selects Report A for display
and Report A comes up and renders in the right hand side of the page (content
area). Then user selects item on Report A for drill thru to Report B.
Report B is replacing the entire browser window Content Selection on left and
Display area on right where Report A was. When the user hits the BACK button
on the browser they are not returned to the previous report display - they
are returned to the screen as it appeared before they selected Report A for
display. I want to be able to return to the report they drilled from (that
report takes LOTS of parameters and I dont want to have to carry them all
forward and have to code another "jump to report" just to return back again
... that seems rather lame). What am I missing here' thanks!
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>
I have a table which has employee information like
empno, empname, empsalary, empmgr. The empmgr column points to empno.
I want to create a report which will dynamically recognise the employees
under a employee and show his salary. The other requirement is that i want to
enable drill down, ie. at first one employee and his salary will appear who
is the head. On drilling down all the employee directly reporting to him
should appear and so on and so forth. Is this possible in reporting services.RS has drilldown and drillthrough. With drill down you have all the info
returned in a single dataset. You set up your grouping and then hide the
detail rows based on the previous field. The downside to this can be the
amount of information you are bringing back. You do not want to be bringing
back more than a thousand or so records (definitely not 50,000+ records). So
it depends on your situation. Drillthrough allows the user to click on a
field and automatically jump to another report, filling in the report
parameters and pulling up the report. I find that this works very well and
is intuitive for the user. They are very used to this from using the web. I
make the text in the field they should click on blue and underlined. In
books online search on drilldown and drillthrough.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> Hi all
> I have a table which has employee information like
> empno, empname, empsalary, empmgr. The empmgr column points to empno.
> I want to create a report which will dynamically recognise the employees
> under a employee and show his salary. The other requirement is that i want
to
> enable drill down, ie. at first one employee and his salary will appear
who
> is the head. On drilling down all the employee directly reporting to him
> should appear and so on and so forth. Is this possible in reporting
services.|||Hi Bruce
I am able to acheive what i was looking for. But i am also facing a problem.
The first time i run the report it showed like this
empid empsalary
(+)1 2000
on drilling down on the 1 i see like this.
empid empsalary
(-)1 2000
(+)2 1000
(+)3 3000
4 4000
on drill down on 2 i get some thing like this.
empid empsalary
(-)1 2000
(-)2 1000
5 500
6 350
(+)3 3000
4 4000
Now at this point when i want to drill up to the top most item by clicking
on 1, i expect everything to be collapsed. But i am getting something like
this
empid empsalary
(+)1 2000
5 500
6 350
Which is wrong it should show only the first record. Can anybody help in
resolving this. I hope i have given you the correct picture of my problem.
Please help me with this.
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>|||You can set the visibility of a row or of a field. So you set the visibility
of the row based on the field that you will have the +/- with. I have two
levels and it works as you expect it to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> Hi Bruce
> I am able to acheive what i was looking for. But i am also facing a
problem.
> The first time i run the report it showed like this
> empid empsalary
> (+)1 2000
> on drilling down on the 1 i see like this.
> empid empsalary
> (-)1 2000
> (+)2 1000
> (+)3 3000
> 4 4000
> on drill down on 2 i get some thing like this.
> empid empsalary
> (-)1 2000
> (-)2 1000
> 5 500
> 6 350
> (+)3 3000
> 4 4000
> Now at this point when i want to drill up to the top most item by clicking
> on 1, i expect everything to be collapsed. But i am getting something like
> this
> empid empsalary
> (+)1 2000
> 5 500
> 6 350
> Which is wrong it should show only the first record. Can anybody help in
> resolving this. I hope i have given you the correct picture of my problem.
> Please help me with this.
> "Bruce L-C [MVP]" wrote:
> > RS has drilldown and drillthrough. With drill down you have all the info
> > returned in a single dataset. You set up your grouping and then hide the
> > detail rows based on the previous field. The downside to this can be the
> > amount of information you are bringing back. You do not want to be
bringing
> > back more than a thousand or so records (definitely not 50,000+
records). So
> > it depends on your situation. Drillthrough allows the user to click on a
> > field and automatically jump to another report, filling in the report
> > parameters and pulling up the report. I find that this works very well
and
> > is intuitive for the user. They are very used to this from using the
web. I
> > make the text in the field they should click on blue and underlined. In
> > books online search on drilldown and drillthrough.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > Hi all
> > >
> > > I have a table which has employee information like
> > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > >
> > > I want to create a report which will dynamically recognise the
employees
> > > under a employee and show his salary. The other requirement is that i
want
> > to
> > > enable drill down, ie. at first one employee and his salary will
appear
> > who
> > > is the head. On drilling down all the employee directly reporting to
him
> > > should appear and so on and so forth. Is this possible in reporting
> > services.
> >
> >
> >|||I have set the visibility to hidden and visibility can be toggeled by the
field that has +/- with , i.e. the employe id field. But its still not
working
"Bruce L-C [MVP]" wrote:
> You can set the visibility of a row or of a field. So you set the visibility
> of the row based on the field that you will have the +/- with. I have two
> levels and it works as you expect it to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> > Hi Bruce
> >
> > I am able to acheive what i was looking for. But i am also facing a
> problem.
> >
> > The first time i run the report it showed like this
> >
> > empid empsalary
> > (+)1 2000
> >
> > on drilling down on the 1 i see like this.
> >
> > empid empsalary
> > (-)1 2000
> > (+)2 1000
> > (+)3 3000
> > 4 4000
> >
> > on drill down on 2 i get some thing like this.
> >
> > empid empsalary
> > (-)1 2000
> > (-)2 1000
> > 5 500
> > 6 350
> > (+)3 3000
> > 4 4000
> >
> > Now at this point when i want to drill up to the top most item by clicking
> > on 1, i expect everything to be collapsed. But i am getting something like
> > this
> >
> > empid empsalary
> > (+)1 2000
> > 5 500
> > 6 350
> > Which is wrong it should show only the first record. Can anybody help in
> > resolving this. I hope i have given you the correct picture of my problem.
> > Please help me with this.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > RS has drilldown and drillthrough. With drill down you have all the info
> > > returned in a single dataset. You set up your grouping and then hide the
> > > detail rows based on the previous field. The downside to this can be the
> > > amount of information you are bringing back. You do not want to be
> bringing
> > > back more than a thousand or so records (definitely not 50,000+
> records). So
> > > it depends on your situation. Drillthrough allows the user to click on a
> > > field and automatically jump to another report, filling in the report
> > > parameters and pulling up the report. I find that this works very well
> and
> > > is intuitive for the user. They are very used to this from using the
> web. I
> > > make the text in the field they should click on blue and underlined. In
> > > books online search on drilldown and drillthrough.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > > Hi all
> > > >
> > > > I have a table which has employee information like
> > > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > > >
> > > > I want to create a report which will dynamically recognise the
> employees
> > > > under a employee and show his salary. The other requirement is that i
> want
> > > to
> > > > enable drill down, ie. at first one employee and his salary will
> appear
> > > who
> > > > is the head. On drilling down all the employee directly reporting to
> him
> > > > should appear and so on and so forth. Is this possible in reporting
> > > services.
> > >
> > >
> > >
>
>|||I don't know what to tell you. It works for me (I only go two levels deep
however, although that was the same as the example you gave). Do you have
groups too. This works in tandem with grouping.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:CB32D4AF-F7AE-4795-8E9C-CCA429565B63@.microsoft.com...
>I have set the visibility to hidden and visibility can be toggeled by the
> field that has +/- with , i.e. the employe id field. But its still not
> working
> "Bruce L-C [MVP]" wrote:
>> You can set the visibility of a row or of a field. So you set the
>> visibility
>> of the row based on the field that you will have the +/- with. I have
>> two
>> levels and it works as you expect it to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
>> > Hi Bruce
>> >
>> > I am able to acheive what i was looking for. But i am also facing a
>> problem.
>> >
>> > The first time i run the report it showed like this
>> >
>> > empid empsalary
>> > (+)1 2000
>> >
>> > on drilling down on the 1 i see like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (+)2 1000
>> > (+)3 3000
>> > 4 4000
>> >
>> > on drill down on 2 i get some thing like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (-)2 1000
>> > 5 500
>> > 6 350
>> > (+)3 3000
>> > 4 4000
>> >
>> > Now at this point when i want to drill up to the top most item by
>> > clicking
>> > on 1, i expect everything to be collapsed. But i am getting something
>> > like
>> > this
>> >
>> > empid empsalary
>> > (+)1 2000
>> > 5 500
>> > 6 350
>> > Which is wrong it should show only the first record. Can anybody help
>> > in
>> > resolving this. I hope i have given you the correct picture of my
>> > problem.
>> > Please help me with this.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > RS has drilldown and drillthrough. With drill down you have all the
>> > > info
>> > > returned in a single dataset. You set up your grouping and then hide
>> > > the
>> > > detail rows based on the previous field. The downside to this can be
>> > > the
>> > > amount of information you are bringing back. You do not want to be
>> bringing
>> > > back more than a thousand or so records (definitely not 50,000+
>> records). So
>> > > it depends on your situation. Drillthrough allows the user to click
>> > > on a
>> > > field and automatically jump to another report, filling in the report
>> > > parameters and pulling up the report. I find that this works very
>> > > well
>> and
>> > > is intuitive for the user. They are very used to this from using the
>> web. I
>> > > make the text in the field they should click on blue and underlined.
>> > > In
>> > > books online search on drilldown and drillthrough.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
>> > > > Hi all
>> > > >
>> > > > I have a table which has employee information like
>> > > > empno, empname, empsalary, empmgr. The empmgr column points to
>> > > > empno.
>> > > >
>> > > > I want to create a report which will dynamically recognise the
>> employees
>> > > > under a employee and show his salary. The other requirement is that
>> > > > i
>> want
>> > > to
>> > > > enable drill down, ie. at first one employee and his salary will
>> appear
>> > > who
>> > > > is the head. On drilling down all the employee directly reporting
>> > > > to
>> him
>> > > > should appear and so on and so forth. Is this possible in reporting
>> > > services.
>> > >
>> > >
>> > >
>>|||Bruce,
I use the drill thru however we have discovered an issue when the report
is deployed to a portal. User has a portal screen with content for selection
on the left and display area on the right. User selects Report A for display
and Report A comes up and renders in the right hand side of the page (content
area). Then user selects item on Report A for drill thru to Report B.
Report B is replacing the entire browser window Content Selection on left and
Display area on right where Report A was. When the user hits the BACK button
on the browser they are not returned to the previous report display - they
are returned to the screen as it appeared before they selected Report A for
display. I want to be able to return to the report they drilled from (that
report takes LOTS of parameters and I dont want to have to carry them all
forward and have to code another "jump to report" just to return back again
... that seems rather lame). What am I missing here' thanks!
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>
Dynamic Graphs
I want let the end user decide on which type of graphic(via a report
parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
couldn't find any option to define the chart type e.g. with the aid of an
expression. Do I have to place any possible chart type in my report layout
and control the appearance by defining an expression for the 'visibility'
property?Niklas,
You can't do it with an expression. Instead, the option I would go for is
building a custom app to get the user selection and update the report RDL.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> I want let the end user decide on which type of graphic(via a report
> parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> couldn't find any option to define the chart type e.g. with the aid of an
> expression. Do I have to place any possible chart type in my report layout
> and control the appearance by defining an expression for the 'visibility'
> property?|||Thanks Teo,
I often see answers, dealing with generating the RDL dynamically. Please
correct me, if I'm wrong, but I don't think this is a solution in a
multi-user environment. I need fixed RDL files, guaranteeing the same
behaviour. So, I cannot change the RDL, every time a user wants to access the
report?!?!
"Teo Lachev [MVP]" wrote:
> Niklas,
> You can't do it with an expression. Instead, the option I would go for is
> building a custom app to get the user selection and update the report RDL.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > I want let the end user decide on which type of graphic(via a report
> > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> > couldn't find any option to define the chart type e.g. with the aid of an
> > expression. Do I have to place any possible chart type in my report layout
> > and control the appearance by defining an expression for the 'visibility'
> > property?
>
>|||Niklas,
Not really. You basically need to generated the report RDL and upload it to
the report catalog under an unique name, e.g. the user logon id. The way I
addressed this on a similar occasion in the past was to have the report
template RDL file included in my web application. My web app will load the
template, change it as needed and upload it to the report catalog. Then, it
will render the report. Finally, when the user logs out, the web app will
purge the report.
Does this make sense?
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:E1B5C271-5C8A-4C15-9C75-615ADFFF1678@.microsoft.com...
> Thanks Teo,
> I often see answers, dealing with generating the RDL dynamically. Please
> correct me, if I'm wrong, but I don't think this is a solution in a
> multi-user environment. I need fixed RDL files, guaranteeing the same
> behaviour. So, I cannot change the RDL, every time a user wants to access
the
> report?!?!
> "Teo Lachev [MVP]" wrote:
> > Niklas,
> >
> > You can't do it with an expression. Instead, the option I would go for
is
> > building a custom app to get the user selection and update the report
RDL.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> > news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > > I want let the end user decide on which type of graphic(via a report
> > > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.)
I
> > > couldn't find any option to define the chart type e.g. with the aid of
an
> > > expression. Do I have to place any possible chart type in my report
layout
> > > and control the appearance by defining an expression for the
'visibility'
> > > property?
> >
> >
> >
parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
couldn't find any option to define the chart type e.g. with the aid of an
expression. Do I have to place any possible chart type in my report layout
and control the appearance by defining an expression for the 'visibility'
property?Niklas,
You can't do it with an expression. Instead, the option I would go for is
building a custom app to get the user selection and update the report RDL.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> I want let the end user decide on which type of graphic(via a report
> parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> couldn't find any option to define the chart type e.g. with the aid of an
> expression. Do I have to place any possible chart type in my report layout
> and control the appearance by defining an expression for the 'visibility'
> property?|||Thanks Teo,
I often see answers, dealing with generating the RDL dynamically. Please
correct me, if I'm wrong, but I don't think this is a solution in a
multi-user environment. I need fixed RDL files, guaranteeing the same
behaviour. So, I cannot change the RDL, every time a user wants to access the
report?!?!
"Teo Lachev [MVP]" wrote:
> Niklas,
> You can't do it with an expression. Instead, the option I would go for is
> building a custom app to get the user selection and update the report RDL.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > I want let the end user decide on which type of graphic(via a report
> > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.) I
> > couldn't find any option to define the chart type e.g. with the aid of an
> > expression. Do I have to place any possible chart type in my report layout
> > and control the appearance by defining an expression for the 'visibility'
> > property?
>
>|||Niklas,
Not really. You basically need to generated the report RDL and upload it to
the report catalog under an unique name, e.g. the user logon id. The way I
addressed this on a similar occasion in the past was to have the report
template RDL file included in my web application. My web app will load the
template, change it as needed and upload it to the report catalog. Then, it
will render the report. Finally, when the user logs out, the web app will
purge the report.
Does this make sense?
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:E1B5C271-5C8A-4C15-9C75-615ADFFF1678@.microsoft.com...
> Thanks Teo,
> I often see answers, dealing with generating the RDL dynamically. Please
> correct me, if I'm wrong, but I don't think this is a solution in a
> multi-user environment. I need fixed RDL files, guaranteeing the same
> behaviour. So, I cannot change the RDL, every time a user wants to access
the
> report?!?!
> "Teo Lachev [MVP]" wrote:
> > Niklas,
> >
> > You can't do it with an expression. Instead, the option I would go for
is
> > building a custom app to get the user selection and update the report
RDL.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> > news:AAF771C4-37F6-4D94-8C77-21B8792FA6EC@.microsoft.com...
> > > I want let the end user decide on which type of graphic(via a report
> > > parameter) is used for displaying the data (Bar, Column, 2D, 3D, etc.)
I
> > > couldn't find any option to define the chart type e.g. with the aid of
an
> > > expression. Do I have to place any possible chart type in my report
layout
> > > and control the appearance by defining an expression for the
'visibility'
> > > property?
> >
> >
> >
Sunday, March 11, 2012
Dynamic formatting question
Hello,
I have a dataset that returns one column of data from which some fields are supposed to appear horizontally in the report, while the rest appear line by line vertically. Is it possible to take, for example, three consecutive fields from the dataset and merge the corresponding table rows that hold these fields in the report? Perhaps using an expression or custom code?
Thanks in advance for any help. I'd be happy to provide examples of what I need if it can help clarify the question.
RLGow
Not exactly sure what you are trying to do, but you can write expressions that concatenate the value of multiple fields, such as:
=Fields!ColumnA.Value & Fields!ColumnB.Value & Fields!ColumnC.Value
-- Robert
Friday, March 9, 2012
dynamic excel destination depend on dataflow data
I created a data flow with complaicated SQL. There is "type" field in the output column.
I would like to created excel files for each "type" value
E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.
Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?
The conditional split has fixed conditions, it is not suitable for by dynamic number of value
For Loop condition is not a good choice because I need to run the complicated SQL for many time.
Thanks.
Put all data in one place, but with the additional type column. Then drive a loop to extract each type of data into a file. This assumes that each types file has the same format, and would require two data flows, the latter of which would be inside the loop.
I would store the list of unique types in advance, perhaps by multicasting the preparatory flow just prior to writing the full file, and then getting distinct type values, use the Sort component.
Your second data flow would just be source, conditional split acting as a filter then the destination. The split filter would be based on typecolumn == @.typevariable. @.typevariable would be set from the loop enumerator, it is type value for that iteration. I would also use that variable in an expression on the destination connection to give a type specific filename.
Dynamic derivation of Heading - is it possible
Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently
Extract of Current SP
CREATE PROC dbo.FairValeSummaryPivot
@.BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @.BatchRunID
GO
what I would like would be along the lines of
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,
Hope this is clear
CheersDynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.
-PatP|||Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.|||Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.
Regards,
hmscott
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently
Extract of Current SP
CREATE PROC dbo.FairValeSummaryPivot
@.BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @.BatchRunID
GO
what I would like would be along the lines of
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,
Hope this is clear
CheersDynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.
-PatP|||Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.|||Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.
Regards,
hmscott
Wednesday, March 7, 2012
Dynamic Dates in Columns on Matrix Report?
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.
For example a report that would run on today's MM, yy (5/2007) would look something like this:
MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08
(Col 1 - Col 12 separated by commas)
How do I do this in a matrix report column?
1- Filter your datasource to return only those rows that meet the date criteria you describe
If SQL Server...
Code Snippet
Select Item, SalesDate, Sales from YourTable Where SalesDate >= DateAdd(dd, -3, GetDate()) AND SalesDate <= DateAdd(dd, 9, GetDate())For example:
Item Date Sales
Bicycle Mar-07 $5000
Bicycle Apr-07 $4500
etc...
Then set your date column as the Column Group in the matrix report
Then select that column header in SSRS Layout view, and change the format property to the MMM-yy
(If the granularity of the dates in your source data is not month, if it's by day instead for instance, then you'll need to aggregate that data in your query with the appropriate Group By calls)
Hope this helps,
Geof
Subscribe to:
Posts (Atom)