Easy way to find size of data returned in SQL 2005 ?

Id like to know when i have a query that returns say 12 million rows as an
example, is there an easy way to find out how much of data that is in MB or
GB ?
ThanksHassan
There's no automatic way. You could estimate the size of each row, and then
multiply by @.@.rowcount. Or capture the data in a temp table and use
sp_spaceused. Or find a tool that measures how much data is sent over the
network. There is a client statistics option in Query Analyzer and SSMS, but
I can't imagine you really want to send all 12 million rows to the client.
You've been asking lots of questions lately, and lots of people have been
providing answers. It would be nice to know if the answers you are getting
are useful to you.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Hello,
My suggestion will be:-
Populate a sample of 2 million row into a temp table and then use
SP_SPaceused. After that you could take an average of size per record and
multiply by total returned. There
is no direct method to know this...
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Id like to know when i have a query that returns say 12 million rows as an
> example, is there an easy way to find out how much of data that is in MB
> or GB ?
> Thanks
>|||Kalen,
I know at times it may appear that I am very thankless, but if not for the
newsgroup and the responses I get that have been very positive and
satisfactory, I wouldnt have been posting time and again out here.
Id love to thank all of the users for responding to not just my questions
but to others questions as well.
Keep up the good work..
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O0mQbYDGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Hassan
> There's no automatic way. You could estimate the size of each row, and
> then multiply by @.@.rowcount. Or capture the data in a temp table and use
> sp_spaceused. Or find a tool that measures how much data is sent over the
> network. There is a client statistics option in Query Analyzer and SSMS,
> but I can't imagine you really want to send all 12 million rows to the
> client.
> You've been asking lots of questions lately, and lots of people have been
> providing answers. It would be nice to know if the answers you are getting
> are useful to you.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:OhXtCTDGHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Id like to know when i have a query that returns say 12 million rows as
>> an example, is there an easy way to find out how much of data that is in
>> MB or GB ?
>> Thanks
>sql

Easy query problem

Hello Experts-
I would like a sproc to return a 1 row select with results based on the
results of what it has found. For example say the following table were
created by the sp:
ID Name Dept
23 A 4
38 B 4
117 C 4
if the sproc could tell me which of these columns contained unique values
that would be great:
ID Name Dept
null null 4
In other words if all values in a column are the same, return that value,
otherwise return null.Select (Select Case When Count(*) = 1
Then Min(ID) Else Null End
From Table T
Group By ID) As ID,
(Select Case When Count(*) = 1
Then Min(Name) Else Null End
From Table T
Group By Name) As Name,
(Select Case When Count(*) = 1
Then Min(Dept) Else Null End
From Table T
Group By Dept) As Dept
"Coffee guy" wrote:

> Hello Experts-
> I would like a sproc to return a 1 row select with results based on the
> results of what it has found. For example say the following table were
> created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique values
> that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that value,
> otherwise return null.|||Sorry - messed that u.. Here's the right one...
Select (Select Case When Count(Distinct ID) = 1
Then Min(ID) Else Null End
From Table T) As ID,
(Select Case When Count(Distinct Name) = 1
Then Min(Name) Else Null End
From Table T) As Name,
(Select Case When Count(Distinct Dept) = 1
Then Min(Dept) Else Null End
From Table T) As Dept
"CBretana" wrote:
> Select (Select Case When Count(*) = 1
> Then Min(ID) Else Null End
> From Table T
> Group By ID) As ID,
> (Select Case When Count(*) = 1
> Then Min(Name) Else Null End
> From Table T
> Group By Name) As Name,
> (Select Case When Count(*) = 1
> Then Min(Dept) Else Null End
> From Table T
> Group By Dept) As Dept
>
> "Coffee guy" wrote:
>|||Coffee guy wrote:
> Hello Experts-
> I would like a sproc to return a 1 row select with results based on
> the results of what it has found. For example say the following
> table were created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique
> values that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that
> value, otherwise return null.
<snort>
What makes you think this query is "Easy"?
Try this:
CREATE TABLE #temp (
ID int,
Name varchar(10),
Dept int)
insert into #temp
select 23,'A',4
union all select 38,'B',4
union all select 117,'C',4
SELECT
(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
ID
END FROM #temp) ID
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
Name
END FROM #temp) Name
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
Dept
END FROM #temp) Dept
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks to both, harder than I thought ;)
"Bob Barrows [MVP]" wrote:

> Coffee guy wrote:
> <snort>
> What makes you think this query is "Easy"?
> Try this:
> CREATE TABLE #temp (
> ID int,
> Name varchar(10),
> Dept int)
> insert into #temp
> select 23,'A',4
> union all select 38,'B',4
> union all select 117,'C',4
> SELECT
> (SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
> ID
> END FROM #temp) ID
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
> Name
> END FROM #temp) Name
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
> Dept
> END FROM #temp) Dept
> drop table #temp
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||I think this is a bit simpler than what's been posted so far. Assuming
no NULLs in any of the columns,
select
case when min(ID) = max(ID) then min(ID) else null end as ID,
case when min(Name) = max(Name) then min(Name) else null end as Name,
case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
from #temp
Steve Kass
Drew University
Coffee guy wrote:
>Thanks to both, harder than I thought ;)
>"Bob Barrows [MVP]" wrote:
>
>|||Duh! I definitely did not give this enough thought.
Thanks,
Bob
Steve Kass wrote:
> I think this is a bit simpler than what's been posted so far. Assuming no
> NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Steve,
Yes, Elegant !
"Steve Kass" wrote:

> I think this is a bit simpler than what's been posted so far. Assuming
> no NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
>|||And if your fingers are tired, these are a tiny bit shorter,
but they're basically the same thing:
select
nullif(min(ID),nullif(min(ID), max(ID))) as ID,
nullif(min(Name),nullif(min(Name), max(Name))) as Name,
nullif(min(Dept),nullif(min(Dept), max(Dept))) as Dept
from #temp
select
case min(ID) when max(ID) then min(ID) end as ID,
case min(Name) when max(Name) then min(Name) end as Name,
case min(Dept) when max(Dept) then min(Dept) end as Dept
from #temp
SK
CBretana wrote:
>Steve,
>Yes, Elegant !
>
>"Steve Kass" wrote:
>
>sql

Easy Newbie reporting services question

I'd like to learn more about what I can put in expressions.
For example: Fields!<FieldName>.Value.Length
Is very useful. I found it on this user group but could not find it in
the documentation (online books). The expression painter does not seem
to be much help.
Where can I find more information on what I can put into expressions?
Thanks in advance :)Hi,
Search for "Using Expressions in Reporting Services" and
"Expression Examples in Reporting Services" this on gives you how to use it
with examples.
Hope you might have seen this. but this is very useful. Moreover you can get
the help from "Edit Expression" form. click on any function. you can get the
help.
Amarnath
"mzwilli" wrote:
> I'd like to learn more about what I can put in expressions.
> For example: Fields!<FieldName>.Value.Length
> Is very useful. I found it on this user group but could not find it in
> the documentation (online books). The expression painter does not seem
> to be much help.
> Where can I find more information on what I can put into expressions?
> Thanks in advance :)
>

Dynamicly create report

Hi All,
I would like to create report like below:
For example: I have a stored procedure spDeptEmp, which has a Dept ID
as input parameter, Once the Dept ID has been passed in, I will get
all the employees on that dept. The question is: I would like to
generate the report, which will display each employee's detail
information, one person per page.
Could you let me know how can I do that using reporting services?
Thanks in advance!
--BillWhat do you mean by dynamic? I don't see the report being dynamic (i.e. that
you show different columns at different times or some such thing). It seems
to me that you are just returning different data depending on the parameter
but the format/layout etc of the report is unchanged. Everything you
describe here is very vanilla report generation for RS. You can easily add
page breaks, you can easily have a query based on a parameter.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408250950.723ae518@.posting.google.com...
> Hi All,
> I would like to create report like below:
> For example: I have a stored procedure spDeptEmp, which has a Dept ID
> as input parameter, Once the Dept ID has been passed in, I will get
> all the employees on that dept. The question is: I would like to
> generate the report, which will display each employee's detail
> information, one person per page.
> Could you let me know how can I do that using reporting services?
> Thanks in advance!
> --Bill|||"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> What do you mean by dynamic? I don't see the report being dynamic (i.e. that
> you show different columns at different times or some such thing). It seems
> to me that you are just returning different data depending on the parameter
> but the format/layout etc of the report is unchanged. Everything you
> describe here is very vanilla report generation for RS. You can easily add
> page breaks, you can easily have a query based on a parameter.
> Bruce L-C
> "bill" <bli2001@.hotmail.com> wrote in message
> news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > Hi All,
> >
> > I would like to create report like below:
> > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > as input parameter, Once the Dept ID has been passed in, I will get
> > all the employees on that dept. The question is: I would like to
> > generate the report, which will display each employee's detail
> > information, one person per page.
> >
> > Could you let me know how can I do that using reporting services?
> >
> > Thanks in advance!
> >
> > --Bill
The dynamic means you don't know how many employees inside one dept.
until you get the input parameter(dept ID). Different dept. will have
different number of employees. i.e. the report will be different.
Also, for one employee's information, it will come from different
dataset.
Thanks,
--Bill|||What you are wanting to do is exactly what RS is designed to do quite
easily. If a simple matter of here is a dept, list all employee's
information with page breaks between them. That would be a single
parameterized query with appropriate grouping and page breaks. If it is more
a master detail type report then subreports will do what you want.
Bruce L-C
"bill" <bli2001@.hotmail.com> wrote in message
news:2a3a3975.0408251442.232899ab@.posting.google.com...
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uUeQVAtiEHA.3612@.TK2MSFTNGP12.phx.gbl>...
> > What do you mean by dynamic? I don't see the report being dynamic (i.e.
that
> > you show different columns at different times or some such thing). It
seems
> > to me that you are just returning different data depending on the
parameter
> > but the format/layout etc of the report is unchanged. Everything you
> > describe here is very vanilla report generation for RS. You can easily
add
> > page breaks, you can easily have a query based on a parameter.
> >
> > Bruce L-C
> >
> > "bill" <bli2001@.hotmail.com> wrote in message
> > news:2a3a3975.0408250950.723ae518@.posting.google.com...
> > > Hi All,
> > >
> > > I would like to create report like below:
> > > For example: I have a stored procedure spDeptEmp, which has a Dept ID
> > > as input parameter, Once the Dept ID has been passed in, I will get
> > > all the employees on that dept. The question is: I would like to
> > > generate the report, which will display each employee's detail
> > > information, one person per page.
> > >
> > > Could you let me know how can I do that using reporting services?
> > >
> > > Thanks in advance!
> > >
> > > --Bill
> The dynamic means you don't know how many employees inside one dept.
> until you get the input parameter(dept ID). Different dept. will have
> different number of employees. i.e. the report will be different.
> Also, for one employee's information, it will come from different
> dataset.
> Thanks,
> --Bill

Dynamically Writing & Rendering Report

Is there a way to Dynamically Writing & Rendering Report for SQL
Server Reporting Services? If so an example would be good.Until there is a render control that does not require the server this
difficult to do. When you publish the report it is there for everyone, so
they can easily step on each other. MS has a document that totally specifies
the xml syntax for RDL. Also, you can open up the report.rdl into an editor
to see what it looks like.
Bruce L-C
"Bila" <bakpan@.teckit.com> wrote in message
news:d26cf8a6.0407291113.6103048@.posting.google.com...
> Is there a way to Dynamically Writing & Rendering Report for SQL
> Server Reporting Services? If so an example would be good.|||A method may be do infer an XSD document from the document MSFT provides and
this will create an object model which you can access just like any other
object model. You can then create your report / content using the object
model, serialize back into XML, and then deploy the report to a server and
invoke the render command.
I'm pretty sure you can pull the RDL specification into a XSD document
(which will create a .vb or .c# file for you), but I haven't tried this
befoe.
-Joel
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Until there is a render control that does not require the server this
> difficult to do. When you publish the report it is there for everyone, so
> they can easily step on each other. MS has a document that totally
specifies
> the xml syntax for RDL. Also, you can open up the report.rdl into an
editor
> to see what it looks like.
> Bruce L-C
> "Bila" <bakpan@.teckit.com> wrote in message
> news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > Is there a way to Dynamically Writing & Rendering Report for SQL
> > Server Reporting Services? If so an example would be good.
>|||Still, when you deploy that report is there for everyone. I guess you could
deploy with a unique name just for that particular user but still, not easy,
not fast.
Bruce L-C
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:u15I6lcdEHA.720@.TK2MSFTNGP11.phx.gbl...
> A method may be do infer an XSD document from the document MSFT provides
and
> this will create an object model which you can access just like any other
> object model. You can then create your report / content using the object
> model, serialize back into XML, and then deploy the report to a server and
> invoke the render command.
> I'm pretty sure you can pull the RDL specification into a XSD document
> (which will create a .vb or .c# file for you), but I haven't tried this
> befoe.
> -Joel
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OqsarIadEHA.3420@.TK2MSFTNGP12.phx.gbl...
> > Until there is a render control that does not require the server this
> > difficult to do. When you publish the report it is there for everyone,
so
> > they can easily step on each other. MS has a document that totally
> specifies
> > the xml syntax for RDL. Also, you can open up the report.rdl into an
> editor
> > to see what it looks like.
> >
> > Bruce L-C
> >
> > "Bila" <bakpan@.teckit.com> wrote in message
> > news:d26cf8a6.0407291113.6103048@.posting.google.com...
> > > Is there a way to Dynamically Writing & Rendering Report for SQL
> > > Server Reporting Services? If so an example would be good.
> >
> >
>

Dynamically Referencing a Field Name

Is there any way to reference a Data Set field name dynamically in an expression?
For example, I have 5 reports that have the same layout but reference different columns.
For example, TextBox1 would be one of the following names depending on the report:
=Fields!A_Total.Value
=Fields!B_Total.Value
=Fields!C_Total.Value
=Fields!D_Total.Value
=Fields!E_Total.Value
I would like to build the field name dynamically instead (e.g., "Fields!" & Parameters.XXX.Label & "_Total.Value").
I know we could use IFF statements but that gets messy.
I have used dynamic sql in stored procedures to solve this issue for other reports, but I was wondering if there was an easy way to do this within the report (I have limited ability to modify this particular stored proc).
Thanks,
Mike=Fields(Parameters.WhichField.Value & "_Total").Value
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mike Lyncheski" <MikeLyncheski@.discussions.microsoft.com> wrote in message
news:CCFFB904-03B0-41CF-87C9-ED43CC027355@.microsoft.com...
> Is there any way to reference a Data Set field name dynamically in an
expression?
> For example, I have 5 reports that have the same layout but reference
different columns.
> For example, TextBox1 would be one of the following names depending on the
report:
> =Fields!A_Total.Value
> =Fields!B_Total.Value
> =Fields!C_Total.Value
> =Fields!D_Total.Value
> =Fields!E_Total.Value
> I would like to build the field name dynamically instead (e.g., "Fields!"
& Parameters.XXX.Label & "_Total.Value").
>
> I know we could use IFF statements but that gets messy.
> I have used dynamic sql in stored procedures to solve this issue for other
reports, but I was wondering if there was an easy way to do this within the
report (I have limited ability to modify this particular stored proc).
> Thanks,
> Mike
>

Dynamically Populated Columns

Hi everyone,
I have to create report that has dynamically populated columns. The data
example is below:
Zone Mar-06 Apr-06 May-06
---
Med 1841.096666 1955.371600 2359.752608
Sub 0.000000 0.000000 0.000000
Work 1353.824214 945.390865 1542.416949
West 1022.000000 0.000000 0.000000
Nat'l 0.000000 0.000000 0.000000
And month columns are added dynamically. Like so:
Zone Mar-06 Apr-06 May-06
Jun-06 Jul-06 Aug-06
------
Med 1841.096666 1955.371600 2359.752608 1098.842727
1827.094062 1381.066666
Sub 0.000000 0.000000 0.000000
0.000000 0.000000 0.000000
Work 1353.824214 945.390865 1542.416949 1176.967816
1192.468396 621.960000
West 1022.000000 0.000000 0.000000
0.000000 1353.905000 0.000000
Nat'l 0.000000 0.000000 0.000000
0.000000 0.000000 0.000000
How can I design a report so that it grows with however many columns are
returned from the SP.
Thanks everybody for your input.Check out matrix control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CI" <ci@.ci.com> wrote in message
news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> I have to create report that has dynamically populated columns. The data
> example is below:
> Zone Mar-06 Apr-06 May-06
> ---
> Med 1841.096666 1955.371600 2359.752608
> Sub 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949
> West 1022.000000 0.000000 0.000000
> Nat'l 0.000000 0.000000 0.000000
> And month columns are added dynamically. Like so:
> Zone Mar-06 Apr-06 May-06
> Jun-06 Jul-06 Aug-06
> ------
> Med 1841.096666 1955.371600 2359.752608 1098.842727
> 1827.094062 1381.066666
> Sub 0.000000 0.000000 0.000000
> 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949 1176.967816
> 1192.468396 621.960000
> West 1022.000000 0.000000 0.000000 0.000000
> 1353.905000 0.000000
> Nat'l 0.000000 0.000000 0.000000 0.000000
> 0.000000 0.000000
> How can I design a report so that it grows with however many columns are
> returned from the SP.
> Thanks everybody for your input.
>|||I did,
However I am not sure how to map the top columns to it. In the dataset that
I get back I do not have the reference to the Mar-06 Apr-06
May-06 columns.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
> Check out matrix control.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "CI" <ci@.ci.com> wrote in message
> news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> I have to create report that has dynamically populated columns. The data
>> example is below:
>> Zone Mar-06 Apr-06 May-06
>> ---
>> Med 1841.096666 1955.371600 2359.752608
>> Sub 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> West 1022.000000 0.000000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> And month columns are added dynamically. Like so:
>> Zone Mar-06 Apr-06 May-06
>> Jun-06 Jul-06 Aug-06
>> ------
>> Med 1841.096666 1955.371600 2359.752608 1098.842727
>> 1827.094062 1381.066666
>> Sub 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> 1176.967816 1192.468396 621.960000
>> West 1022.000000 0.000000 0.000000 0.000000
>> 1353.905000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> How can I design a report so that it grows with however many columns are
>> returned from the SP.
>> Thanks everybody for your input.
>>
>|||i am new to this RS2005. i have same fucntionality with interactive
sorting... any steps to implement this functionality...,, please send
me link or code
Thanks
vinod
CI wrote:
> I did,
> However I am not sure how to map the top columns to it. In the dataset that
> I get back I do not have the reference to the Mar-06 Apr-06
> May-06 columns.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
> > Check out matrix control.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "CI" <ci@.ci.com> wrote in message
> > news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> >> Hi everyone,
> >> I have to create report that has dynamically populated columns. The data
> >> example is below:
> >>
> >> Zone Mar-06 Apr-06 May-06
> >> ---
> >> Med 1841.096666 1955.371600 2359.752608
> >> Sub 0.000000 0.000000 0.000000
> >> Work 1353.824214 945.390865 1542.416949
> >> West 1022.000000 0.000000 0.000000
> >> Nat'l 0.000000 0.000000 0.000000
> >>
> >> And month columns are added dynamically. Like so:
> >>
> >> Zone Mar-06 Apr-06 May-06
> >> Jun-06 Jul-06 Aug-06
> >> ------
> >> Med 1841.096666 1955.371600 2359.752608 1098.842727
> >> 1827.094062 1381.066666
> >> Sub 0.000000 0.000000 0.000000
> >> 0.000000 0.000000 0.000000
> >> Work 1353.824214 945.390865 1542.416949
> >> 1176.967816 1192.468396 621.960000
> >> West 1022.000000 0.000000 0.000000 0.000000
> >> 1353.905000 0.000000
> >> Nat'l 0.000000 0.000000 0.000000
> >> 0.000000 0.000000 0.000000
> >>
> >> How can I design a report so that it grows with however many columns are
> >> returned from the SP.
> >>
> >> Thanks everybody for your input.
> >>
> >>
> >
> >|||Oh, so you are getting back a varying number of columns? I thought you
wanted to take your data and get variable columns. RS cannot deal with
multiple columns. However, you could probably write your SP to not create
the variable columns and let RS do it with the matrix control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"CI" <ci@.ci.com> wrote in message
news:e%23fdm$c1GHA.2036@.TK2MSFTNGP05.phx.gbl...
>I did,
> However I am not sure how to map the top columns to it. In the dataset
> that I get back I do not have the reference to the Mar-06
> Apr-06 May-06 columns.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23ySQzoc1GHA.1304@.TK2MSFTNGP05.phx.gbl...
>> Check out matrix control.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "CI" <ci@.ci.com> wrote in message
>> news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
>> Hi everyone,
>> I have to create report that has dynamically populated columns. The data
>> example is below:
>> Zone Mar-06 Apr-06 May-06
>> ---
>> Med 1841.096666 1955.371600 2359.752608
>> Sub 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> West 1022.000000 0.000000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> And month columns are added dynamically. Like so:
>> Zone Mar-06 Apr-06 May-06
>> Jun-06 Jul-06 Aug-06
>> ------
>> Med 1841.096666 1955.371600 2359.752608
>> 1098.842727 1827.094062 1381.066666
>> Sub 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> Work 1353.824214 945.390865 1542.416949
>> 1176.967816 1192.468396 621.960000
>> West 1022.000000 0.000000 0.000000 0.000000
>> 1353.905000 0.000000
>> Nat'l 0.000000 0.000000 0.000000
>> 0.000000 0.000000 0.000000
>> How can I design a report so that it grows with however many columns are
>> returned from the SP.
>> Thanks everybody for your input.
>>
>>
>|||Everybody,
Thanks for your help. What i ended up doing is I unpivot the result set and
now i can use the Matrix to do it all for me
"CI" <ci@.ci.com> wrote in message
news:u1BzUcc1GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Hi everyone,
> I have to create report that has dynamically populated columns. The data
> example is below:
> Zone Mar-06 Apr-06 May-06
> ---
> Med 1841.096666 1955.371600 2359.752608
> Sub 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949
> West 1022.000000 0.000000 0.000000
> Nat'l 0.000000 0.000000 0.000000
> And month columns are added dynamically. Like so:
> Zone Mar-06 Apr-06 May-06
> Jun-06 Jul-06 Aug-06
> ------
> Med 1841.096666 1955.371600 2359.752608 1098.842727
> 1827.094062 1381.066666
> Sub 0.000000 0.000000 0.000000
> 0.000000 0.000000 0.000000
> Work 1353.824214 945.390865 1542.416949 1176.967816
> 1192.468396 621.960000
> West 1022.000000 0.000000 0.000000 0.000000
> 1353.905000 0.000000
> Nat'l 0.000000 0.000000 0.000000 0.000000
> 0.000000 0.000000
> How can I design a report so that it grows with however many columns are
> returned from the SP.
> Thanks everybody for your input.
>

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically naming a report

Is there a way to dynamically rename a report? For example if I schedule a report to run every day and I want the report names to be like Report_Dec01_2006, Report_Dec02_2006, etc. This way the users can see which days data is in each of the report.

Any way to handle this?

Thx

Does anyone else have a need for this? Anyone from Microsoft , plz respond!

Dynamically hide parameters

Is there a way to dynamically show/hide the parameter selection for a report? For example, when my report is viewed in Report Manager I want the parameters to be visible, but in our custom app with an embedded report I want them hidden so that the user can use our UI to enter them.
Is this possible?

The ReportViewer control has a ShowParameterPrompts property that can be set to False if you do not want to show the parameter prompts in your custom app. There is also a ShowToolBar property which you could use to hide the rest of the toolbar, so that all the user sees is the actual report.

If you need to leave some parameters visible and hide others, I am not sure, someone else will have to answer that.

Good luck,

Dave

Dynamically execute stored procedure

Hello,
I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:
insert into Table1
(
id, name
)
select id, name
from Table2
Can you do something like:
exec spProc @.id = id, @.name = name
from Table1
Or something like that? I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.
Thanks.

You can create a SQL cursor base on Table 1. Then iterate over the cursor and build your SQL statements dynamically using that syntax of the Execute statement. You can find syntax in SQL Books Online.

HTH.

Dynamically Changing the Picture at the Record Level.

Hi,
Appreciate your help on the following.
I need to display images according to the status of the record. For example,
I am displaying product list where the margin is less than 15% then display
image1, when margin is between 16% and 25% display image2 etc. I am currently
using a table to store the path of the images.
Thank you again,
KG@.SF
Highly appreciate your helpI also have to develop similar concept but Matrix report where I have to
again display image indicators when a category of product margin falls betwen
a certain range. Appreciate your help,
KG
"KG@.SFC" wrote:
> Hi,
> Appreciate your help on the following.
> I need to display images according to the status of the record. For example,
> I am displaying product list where the margin is less than 15% then display
> image1, when margin is between 16% and 25% display image2 etc. I am currently
> using a table to store the path of the images.
> Thank you again,
> KG@.SF
> Highly appreciate your help

Dynamically Change SSIS For Each Loop container

Hello,
I would like to modify "Files" attribute of the Foreach Loop of type File 
Enumerator. This attribute is used to set the mask (for example *.txt) to 
specify which files to include in the selection. I need to be able to change 
this mask dynamically depending on package global variable. Is this possible?
Thank you!
Michael

Use the Expressions property, and create an expression for FileSpec property that references the global variable.|||

More details on How To get to the Extressions Property -

Open the ForEach Loop Editor by double clicking ForEach Loop Container.
Select Collection on left.
Click on the + sign on Expressions
Select FileSpec for Property and On Expression select the Global Variable Name. (which holds the file property such as *.txt)

Thanks,
Loonysan

Dynamic Where clause in Stored Procedure

Hi, I have several parameters that I need to pass to stored procedure but sometimes some of them might be null. For example I might pass @.Path, @.Status, @.Role etc. depending on the user. Now I wonder if I should use dynamic Where clause or should I use some kind of switch, maybe case and hardcode my where clause. I first created several stored procedures like Documents_GetByRole, Documents_GetByRoleByStatus ... and now I want to combine them into one SP. Which approach is better. Thanks for your help.dynamic where clauses would eliminate most of the benefits of the stored procedure.

For every ad-hoc query that's executed, a new execution plan and compiliation takes place. Furthermore, your ram goes up.

A lot of databases will slow down with usage due to this. Therefore, hardcoding your where clauses is best. (like field = @.value)

Dynamic Where clause

What is the best way to dynamically choose the where clause based on a
variable.
In the following test example, depending on @.i value, WHERE clause could
compare against being null or not null.
Another way to do would be writing ugly sql string like @.select + @.where
Please let me know.
TIA...
set nocount on
go
create table z_test_del
(
c1 int,
c2 int
)
go
insert z_test_del values(1,null)
insert z_test_del values(2,333)
insert z_test_del values(3,null)
insert z_test_del values(4,5555)
go
declare @.i int
set @.i = 0
if (@.i = 0)
select * from z_test_del where c2 is null
else
select * from z_test_del where c2 is not null
go
drop table z_test_del
go>> What is the best way to dynamically choose the where clause based on a
variable. <<
Dynamic is poor choice of words in SQL -- it implies that you are
writing code on the fly.
SELECT *c1, c2 -- never use * in production code!!
FROM Foobar
WHERE (c2 IS NULL AND @.flag = 0)
OR (c2 IS NOT NULL AND @.flag <> 0);|||You can use a CASE or use OR-ed predicates or write two separate statements.
For some alternatives refer to: http://www.sommarskog.se/dyn-search.html
Anith|||Thanks Joe.
"--CELKO--" wrote:

> variable. <<
> Dynamic is poor choice of words in SQL -- it implies that you are
> writing code on the fly.
> SELECT *c1, c2 -- never use * in production code!!
> FROM Foobar
> WHERE (c2 IS NULL AND @.flag = 0)
> OR (c2 IS NOT NULL AND @.flag <> 0);
>

Dynamic Tool Configuration

Newbie here wanting to get information, links, opinions on how to configure a simple data flow from code. Here's my example. I have a CSV input tool, a SORT tool, and a CSV output tool. I want to call this package from an application running C#. Can I access this package like DataPack("filename","sort","output filename") from the object model? Or does XML have to get involved? Can I dynamically swap out the input or output tools? Any links to the UML mockup of the object model? Just getting started here.

Thanks in advance,
Mitch

Start here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/60fe148b-a7c4-4289-ae3e-2e949fc1886c.htm

dynamic text

Hi.
In Reporting Services, is there a chance to make the text dynamic?
For example, sometimes I want it to show 10 lines and sometimes 20.
Is it possible?Can you be a little more specific? Nearly everything is expression based and hence can be made dynamic.|||I have a report about SaleContracts.
In the SaleContract Text area, the customer (company) definition will be shown and for example one company's definition is 20 character length and another is 50 character length.I use this definition more than once in the contract text.So the text changes from contract to contract.And so this text may be 20 line or 30 line length.
If I keep the textbox's size small, some of the text couldn't be shown when the company definition string contains 30 characters(for example).And if I keep the textbox's size large, there is a blank space and this is not a good view.Therefore I want the text to be dynamic.

Thanks in advance!|||Text boxes have 2 properties CanGrow and CanShrink that can be used to automatically size the textbox based on it's content. Bear in mind that these will only grow or shrink vertically i.e. height, the width is fixed.

Dynamic Stored Procedures

Hi everyone,

My question is how can i set a var with a table name to use it in a SELECT statement for example.

EX:

Declare @.table varchar
Set @.table = 'mytable'

Select * From @.table

I've got 1 stored procedure wich i want to use to get and update 2 tables. For that reason i want to know how to do this because having 2 stored procedures when the only difference are table names its not a good solution.

Thanks :)

Declare @.sql varchar
Declare @.table varchar
Set @.table = 'mytable'

set @.sql = 'Select * From '+ @.table

execute(@.sql)

visit this link for more info

http://www.sommarskog.se/dynamic_sql.html

|||

Tiago:

You can do what you ask with something like:

exec ( 'select * from ' + @.table )

A safer method is something that does not potentially incur problems from hacking is something like:

if @.table = 'A'
select * from A
else
select * from B

The other comment I have has to do with the "select *" syntax; is this what you are really planning on doing? Because this syntax leaves "land mines." What I mean is that if you include this syntax in a stored procedure and later make column changes to either table A or table B these "select *" statements are likely to surprise you.

The reason is that what "select *" means in a stored procedure has to do with what "select *" meant at COMPILE time and NOT what "select *" should mean at RUN time! If in fact what you are trying to do is to write a generic stored procedure that returns all columns for an unspecified table you need to realize that this is a potentially dangerous stored procedure in a number of different ways. Also, you should tend to explicitly list all columns instead of using "select *" syntax when dealing with a permanent table. This is not so bad with temp tables, but I am assuming here that your target tables are not temp tables.

If you are wanting a stored procedure to list all columns of permanent tables that are not isomorphic -- that is, the forms of the tables differernt -- in my opinion you are better off writing separate stored procedures.

Dave

|||

Thanks for quick answers.

I've already put my query correctly, but i've got another problem.

The "dynamic query" its used in a CURSOR and i've getting error with that because Exec(@.query)

My Code:

set @.query = 'SELECT CodigoConta,AnoOrcamento FROM ' + @.t +
' WHERE AnoOrcamento = ' + CAST(@.Ano as varchar) +
' AND CodigoConta like ''' + cast(@.Classe as varchar) +
'%'' ORDER BY CAST(CodigoConta as varchar)'

DECLARE Orcamento_Cursor CURSOR FOR
execute(@.query).

when i try to save the SP, i'm getting this error:

Incorrect syntax near the keyword 'execute'.

Can you help me with this problem ?

|||

Tiago:

Would you mind posting the rest of your process that is using this cursor? (I just marched my army right off a cliff; trying not to repeat it.)

Dave

|||

Mugambo,

I've clear the code because i don't have more time now to get arround with this, so i'm using something like this:

IF @.Classe = '6'
BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoCustosPerdas
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

ELSE

BEGIN
DECLARE Orcamento_Cursor CURSOR FOR
SELECT CodigoConta
FROM OrcamentoProveitosGanhos
WHERE AnoOrcamento = @.Ano
AND CodigoConta like @.Classe + '%'
ORDER BY CAST(CodigoConta AS VARCHAR)
END

Maybe in future i change this code to a dynamic one. Btw, thanks a lot for u'r help. :)

|||

Tiago:

I think this adjustment will work; you are welcome.

Dave

|||create a temp table,...store the result of execute in that...then in cursor..use select * from #temp, ..though better options may exist...|||( HELP! I am really concerned that I have badly screwed this up. )

Dynamic SQL in UDF

Does anyone know how to execute dynamic SQL in a user defined function? Here's a quick example of what I would like to do. I get an error that only functions and extended stored procedures may be called in a function. Is there any other way to execute dynamic SQL in a UDF?

CREATE function dbo.test(@.table char(40), @.value char(40))
RETURNS int
AS
BEGIN
DECLARE @.return char(3)
DECLARE @.sqlstring nvarchar(500)

SET @.sqlstring = 'Select count(*) From @.table Where id = @.value'

Execute sp_executesql @.sqlstring

RETURN(@.return)
ENDHow would you use this function?

SELECT dbo.Test('a','b')

?

Why not just do

EXEC @.rc = Test 'a','b'|||I'm not sure I follow your reply. I want to call the UDF from a table constraint.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.

UDF's do not support dynamic sql. You would have to go with a stored procedure in order to gain that flexibility. I think they have that somewhere on msdn too..|||I can't find the msdn outline... here's a limitation rundown from informit (http://www.informit.com/isapi/product_id~{0D83BA18-CDB2-4D74-9C2A-AA44581B27B9}/element_id~{43287535-9508-43D6-BB36-4852A7A9F91B}/st~{340C91CD-6221-4982-8F32-4A0A9A8CF080}/session_id~{65960459-451E-4EF6-9D3B-7E7CF0E4CB0B}/content/articlex.asp) that I found to be pretty comprehensive.|||Originally posted by peterlemonjello
I'm not sure I follow your reply. I want to call the UDF from a table constraint.

A CONSTRAINT? What would that do?

what are you trying to restrict?|||I'm trying to restrict date ranges from overlapping in several tables. I was hoping to use dynamic sql in a udf so that the udf can be reused by multiple tables.

Here's an example: A salesman can be licensed in a particular state to sell widgets. His licensed can be terminated and re-instated in a state. However, he can't hold two licenses in the same state at the same time. Our system must track each instance of a license the salesman has had in every state. Our developers didn't think validating overlapping start and end dates was important so no validation exists in the java code. Hence we have bad data with overlapping date ranges for a salesman in a state.
**Table Structure (not syntactically correct):
id int(pk identity)
salesman_id int
state_id int
start_date datetime
end_date datetime

I would like the table constraint to call the udf which would return if there were any date ranges overlapping the inserted or updated data. If so prevent the insert or update. Oh yeah, this would prevent me from having to code a trigger on each table this logic will be used.

Hope this helps!|||Why not just create a unique index on saleman id, State?

use an update trigger to move to current row to a history table where it's not unique?

The create a view if the need to see all of the data.

If someone tries to add another salesman that's already in the same state, they'll get an exception...

What they probably should be doing is an update not an insert anyway

MOO|||Yeah, that would work but I would have to do that for every table where this occurs. I was looking for an 'easier' solution that may end up being just as complex. I would prefer not to have to maintain seperate history tables nor triggers on each table but thanks for the ideas.

Dynamic SQL as a DataSet

How do I create a dynamic SQL DataSet?
For Example: 'Select * From MyTable Where MyField In (' + @.S + ')'
(I'm using Oracle as a Database, and thus the variable is :S instead of @.S)Fortunately I found the answer here:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx|||On Jun 3, 9:22 am, "=E2=F8=E9 =F8=F9=F3" <GeriRes...@.GMail.com> wrote:
> Fortunately I found the answer here:http://solidqualitylearning.com/blogs=
/dejan/archive/2004/10/22/200.aspx
Glad you found your solution. For future assistance, feel free to
repost on this group.
Regards,
Enrique Martinez
Sr. Software Consultant