Get All Possible Values of a Report Parameter

Hi,
I need an application that displays reports, and prompts the user
for the parameter values. I have used the GetReportParameters() to retrieve
the list of parameters. However, i could not find a way to get the available
values when the parameter is of a list type. For example a countries
parameter would include a number of countries to choose from ( retrieved
from a dataset ). How could i retrieve the list of all the countries
dynamically? Can someone help me out.
Thanks,
Loui MerciecaHi Loui:
It sounds as if you are looking for the ValidValues property of a
parameter. See my article here for some tips:
Using GetReportParameters
http://odetocode.com/Articles/123.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 29 Sep 2004 12:20:06 +0200, "Loui Mercieca" <loui@.gfi.com>
wrote:
>Hi,
> I need an application that displays reports, and prompts the user
>for the parameter values. I have used the GetReportParameters() to retrieve
>the list of parameters. However, i could not find a way to get the available
>values when the parameter is of a list type. For example a countries
>parameter would include a number of countries to choose from ( retrieved
>from a dataset ). How could i retrieve the list of all the countries
>dynamically? Can someone help me out.
>Thanks,
>Loui Mercieca
>|||Make sure to pass in true for the forRendering parameter. Check here for
more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_6x4n.asp
Here is the relevant text from that page:
If ForRendering has a value of false, the parameter meta data returned
represents the parameter data that is currently associated with the
specified report. If any parameters values are based on a query and you are
interested in returning the query-based parameters valid values list, you
need to set ForRendering to true, In addition, for query based parameters,
you need to ensure that you have passed in all of the credential information
required to return the query parameters.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Loui Mercieca" <loui@.gfi.com> wrote in message
news:eeseq3gpEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need an application that displays reports, and prompts the user
> for the parameter values. I have used the GetReportParameters() to
retrieve
> the list of parameters. However, i could not find a way to get the
available
> values when the parameter is of a list type. For example a countries
> parameter would include a number of countries to choose from ( retrieved
> from a dataset ). How could i retrieve the list of all the countries
> dynamically? Can someone help me out.
> Thanks,
> Loui Mercieca
>|||THANK YOU! That ForRendering = true worked great for me. I was having
a similar problem. I wish they explained better what all of these
setting were. But thanks to you, I bypassed this problem.

Get 2 Decimal Places in Avg

I am running the query below but the results show only the integer portion.
I know the results should be values like "2.33" instead of "2". How do I get
the correct result format?
Wayne
========================================
SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
Count(judgeevalsbyunits.UnitID) As "Count of Evals",
Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
FROM judgeevalsbyunits
INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaptionIf the source columns are only integer values, you only get those back, if
you want to get a decimal value back, you have to convert the source columns
to the appropiate format, eg. AVG(CONVERT(decimal(8,2),YourColumn))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> schrieb im Newsbeitrag
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Try,
...
Avg(judgeevalsbyunits.RegScoring * 1.0) As "Reg Scoring Avg",
...
The average value of an integer column is an integer value. Cast the value
to decimal to get a decimal avg.
Example:
select
avg(c1 * 1.0)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
select
avg(c1)
from
(
select cast(1 as int)
union all
select cast(2 as int)
) t1(c1)
go
AMB
"Wayne Wengert" wrote:

> I am running the query below but the results show only the integer portion
.
> I know the results should be values like "2.33" instead of "2". How do I g
et
> the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>
>|||convert the fields into the decimal format like
AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
cheers
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>I am running the query below but the results show only the integer portion.
>I know the results should be values like "2.33" instead of "2". How do I
>get the correct result format?
> Wayne
> ========================================
> SELECT judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption,
> Count(judgeevalsbyunits.UnitID) As "Count of Evals",
> Avg(judgeevalsbyunits.RegScoring) As "Reg Scoring Avg",
> Avg(judgeevalsbyunits.WCScoring) As "WC Scoring Avg",
> Avg(judgeevalsbyunits.TapeDialog) As "Tape Dialog Avg",
> Avg(judgeevalsbyunits.Conduct) As "Conduct Avg"
> FROM judgeevalsbyunits
> INNER JOIN judges2eval ON judgeevalsbyunits.JudgeID=judges2eval.ID
> Group By judgeevalsbyunits.JudgeName, judgeevalsbyunits.JudgeCaption
>|||Thanks guys. That is exactly what I needed.
Wayne
"keber" <k@.c.com> wrote in message
news:O6fKoirZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> convert the fields into the decimal format like
> AVG(CAST(judgeevalsbyunits.RegScoring) AS FLOAT) or AS DECIMAL(x,x)
> cheers
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:%2354kWYrZFHA.3364@.TK2MSFTNGP09.phx.gbl...
>

Generating values as part of a compound key

BEGINNER QUESTION

I have a table which has a compound primary key consisting of two columns.

One of these columns is a foreign key which is generated in another table by
an identity.

I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.

There will be potentially more than one user accessing this table so I want
to avoid generating the key on the client side.

How can I do this? Will it require some hardcore T-SQL?

I hope this is clear (I suspect it isn't) I'd be happy to supply more info.
I would be extremely grateful for any help!

Mark.Mark wrote:
> BEGINNER QUESTION
> I have a table which has a compound primary key consisting of two
> columns.
> One of these columns is a foreign key which is generated in another
> table by an identity.

But with "generated" you don't mean it's also inserted into the table with
the compound key at the same time, do you?

> I want to be able to generate the other primary key column value
> automatically when an insert occurs but assume that I cannot use an
> identity because it would have to be unique for this table.

I don't see a problem here because identity *is* unique to your compound
key table.

> There will be potentially more than one user accessing this table so
> I want to avoid generating the key on the client side.

Yes, of course.

> How can I do this? Will it require some hardcore T-SQL?
> I hope this is clear (I suspect it isn't) I'd be happy to supply more
> info. I would be extremely grateful for any help!

Not fully to be honest. Maybe you post some DDL so we can see the table
layout. Also, it's not 100% clear to me when inserts in your main table
occur.

Kind regards

robert|||I hope it may be clearer if I outline what the tables are for:

I'm basically writing an application that stores information about
'behaviour incidents' at a school. The table in question is the 'incidents'
table which is used to record information about individual incidents of
negative behaviour (ok - let's call it being naughty).

The primary key for the 'incidents' table is made up of an 'incidentID' and
'pupilID'. The pupilID indicates the pupil(s) who were involved in the
incident and is itself a foreign key into a 'pupils' table.

This is to reflect the possibility that more than one pupil can be involved
in the same incident. In this case, there may be for example three rows with
the same 'incidentID' - each having a unique pupilID to reflect one incident
in which three different pupils were involved.

My question really revolves around how to generate the 'incidentID' that is
unique at the time of insertion but allows duplicates if more than one pupil
is involved.

Can I insert the first row and retrieve the identity with a scope_identity
and then just insert the rest of the rows with the same incidentID? Wouldn't
that return an error as the identity column wouldn't contain all unique
values.

I hope this is clearer.

Thanks for your time!

Mark.

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3nj2krF1nuhtU1@.individual.net...
> Mark wrote:
>> BEGINNER QUESTION
>>
>> I have a table which has a compound primary key consisting of two
>> columns.
>>
>> One of these columns is a foreign key which is generated in another
>> table by an identity.
> But with "generated" you don't mean it's also inserted into the table with
> the compound key at the same time, do you?
>> I want to be able to generate the other primary key column value
>> automatically when an insert occurs but assume that I cannot use an
>> identity because it would have to be unique for this table.
> I don't see a problem here because identity *is* unique to your compound
> key table.
>> There will be potentially more than one user accessing this table so
>> I want to avoid generating the key on the client side.
> Yes, of course.
>> How can I do this? Will it require some hardcore T-SQL?
>>
>> I hope this is clear (I suspect it isn't) I'd be happy to supply more
>> info. I would be extremely grateful for any help!
> Not fully to be honest. Maybe you post some DDL so we can see the table
> layout. Also, it's not 100% clear to me when inserts in your main table
> occur.
> Kind regards
> robert|||Mark wrote:
> I hope it may be clearer if I outline what the tables are for:
> I'm basically writing an application that stores information about
> 'behaviour incidents' at a school. The table in question is the
> 'incidents' table which is used to record information about
> individual incidents of negative behaviour (ok - let's call it being
> naughty).
> The primary key for the 'incidents' table is made up of an
> 'incidentID' and 'pupilID'. The pupilID indicates the pupil(s) who
> were involved in the incident and is itself a foreign key into a
> 'pupils' table.
> This is to reflect the possibility that more than one pupil can be
> involved in the same incident. In this case, there may be for example
> three rows with the same 'incidentID' - each having a unique pupilID
> to reflect one incident in which three different pupils were involved.
> My question really revolves around how to generate the 'incidentID'
> that is unique at the time of insertion but allows duplicates if more
> than one pupil is involved.
> Can I insert the first row and retrieve the identity with a
> scope_identity and then just insert the rest of the rows with the
> same incidentID? Wouldn't that return an error as the identity column
> wouldn't contain all unique values.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert|||> You are right, this table layout would not work with identity. However, I
> figure your table layout may not be optimal because you really have a n-m
> relationship between incidents and pupils. And as far as I can see
> there's no place to store information where there is just one piece per
> incident (for example date and time). With all that I know ATM I would
> have it as follows:
> table incidents with date, time, location whatever and incidentid
> (identity)
> table pupils with pupilid (identity), name, day of birth - whatever
> table pupils_in_incidend with incidentid, pupilid (both foreign keys)
> This seems the most normalized approach here.
> Kind regards
> robert

OF COURSE! I should have seen that it would be silly to duplicate all of the
incident information for every pupil involved in a given incident.

Thank you immensely for your help!

Mark.

Generate values between a starting and stopping point (time values).

[RS 2005]

Given the starting and stopping points (time values), how do I generate values between these points.

For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.

From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.

So how do I accomplish this? In SQL or in the RS?

The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).

//H?kan

Use custom code in the report to achieve this. Take the start and end points as input parameters as you might be already doing. Then pass them to the function in custom code and write VB code inside that function to generate all intermediate values. Then store that array in a global variable in the custom code itself which you could refer from anywhere in your report.

Shyam

|||I would either use a function or a stored procedure or a plain query for this.

CREATE FUNCTION dbo.NumberRange

(

@.Starting INT,

@.End INT

)

RETURNS @.Numbers TABLE

(

Number INT

)

AS

BEGIN

WHILE @.Starting <= @.End

BEGIN

INSERT INTO @.Numbers VALUES (@.Starting)

SET @.Starting = @.Starting + 1

END

RETURN

END

SELECT * FROM dbo.NumberRange(1,10)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hakan is not keen on using SQL and WHILE loops and tables.|||I know, thats why I write the function for him.|||

H?kan,

do you need the data to be included in the dataset so that it can be rendered on the report? If so, you will have to generate it in the SQL as mentioned by Jens. If all you need to do is refer to the intermediate values from other expressions then a global array is fine, but make sure you have null and bounds checks in your custom code before trying to access the array.

|||

I guess the function also has the WHILE loop and tables.

Instead he can have a master table with just one column and values from 00 to 100 and just select the necessary numbers from that table between the given start and end.

Shyam

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

go

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

as

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

go

)

update cte

set pol_seq_id = rn;

AMB

Generate report automaically and email them : Please help

Please help me if possible.
My report takes a parameter say "name".
I want reports to be generated automatically for various values of
"name" and each of the generated report should be sent as email to the
person every month or so.
How do I go about achieving this? I am very new to reporting services
and I am stuck with very little documentation.
A detailed explanation or even a hint in the right direction would be
greatly appreciated.Use a data driven subscription. This will require setting up an additional
table to hold the values, but then there is a wizard to walk through in the
Report Manager to set this up. Take a look at the SQL-RS Books On Line to
get the details as it takes several steps to get it going.
Brian
"Aravind" <r.aravind@.gmail.com> wrote in message
news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> Please help me if possible.
> My report takes a parameter say "name".
> I want reports to be generated automatically for various values of
> "name" and each of the generated report should be sent as email to the
> person every month or so.
> How do I go about achieving this? I am very new to reporting services
> and I am stuck with very little documentation.
> A detailed explanation or even a hint in the right direction would be
> greatly appreciated.
>|||Yea, just make sure you have the enterprise edition of SQL server before you
start as it won't work in standard. The EE version is 10x the cost of
standard.
"goodman93" wrote:
> Use a data driven subscription. This will require setting up an additional
> table to hold the values, but then there is a wizard to walk through in the
> Report Manager to set this up. Take a look at the SQL-RS Books On Line to
> get the details as it takes several steps to get it going.
> Brian
> "Aravind" <r.aravind@.gmail.com> wrote in message
> news:1121867840.826151.118570@.o13g2000cwo.googlegroups.com...
> > Please help me if possible.
> >
> > My report takes a parameter say "name".
> > I want reports to be generated automatically for various values of
> > "name" and each of the generated report should be sent as email to the
> > person every month or so.
> >
> > How do I go about achieving this? I am very new to reporting services
> > and I am stuck with very little documentation.
> >
> > A detailed explanation or even a hint in the right direction would be
> > greatly appreciated.
> >
>
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury822.34602709630
Standard433.34602709630
Custom397.34602709630
Modified285.34602709630
Other222.34602709630
More...676.34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury822.10032954961
Standard433.05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
Hi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury822.34602709630
> Standard433.34602709630
> Custom397.34602709630
> Modified285.34602709630
> Other222.34602709630
> More...676.34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury822.10032954961
> Standard433.05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>
|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com
|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunct
ion
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comHi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for eac
h
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFun
ction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass
,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunctio
n
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

generate all possible subsets from master set?

Hi.

I have a master set of values, lets say

(1,2,3)

for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.

(1)
(2)
(3)
(1,2)
(1,3)
(2,3)
(1,2,3)

thx.Can I ask why?

In a table or a string?

I'm thinking CROSS JOIN...|||Back up a sec', manster.

Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.

But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.

blindman|||thanks, all.

Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."

if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.

thx.|||That sounds bizzare!

Can you post the DDL of the table?

And some sample data?

And are you're higher ups high?|||yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.

bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.

table1

F1 F2 F3 etc.

output is all possible subsets of these fields:

F1
F2
F3
F1 F2
F2 F3
F1 F3
F1 F2 F3

thx!|||Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow

You'll also need
CREATE TABLE Numbers(n int)
GO
DECLARE @.x int
SET NOCOUNT ON
SELECT @.x = 1
WHILE @.x < 101 BEGIN
INSERT INTO Numbers (n) SELECT @.x
SELECT @.x = @.x + 1
END
SET NOCOUNT OFF

To play with it...vary cool though...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752|||What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman|||I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx|||Originally posted by blindman
What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman

LOL...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646

Some Calculations here...

Say we have a table of 10,000 measly rows...

You want to join col1 to each of the other 14 columns to represent permutations

OK Col1 + Col2 = 100,000

So i't like Col1 , Row 1 + Col2, Every row

Do that 14 more times for all the other columns...1.4 million rows

And we haven't yet begun! That's still just 2 dimensions of the data

Now lets see, Three dimensions

I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
Then Col1, Row1, Col2, Row2, Col3 All the rows of data

So what that would be 10,000 * 10,000 * 10,000

Blindman help me out here, sound right?

The for all other cols * 14, or 14 million?

4 Dimensions...

140,000,000? Just a guess...

That about right?|||here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...

mine is n=15, k=15

and I'm looking for lex order, list of elements.

http://www.theory.cs.uvic.ca/~cos/gen/comb.html|||Originally posted by manster
I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx

Was busy doing calcs when you posted...

Are you trying to build a "pick list" of what fields you want to select for an end user..

Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..

DECLARE @.x BIGINT
SELECT @.x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
SELECT @.x

I get an arithmetic overflow at 13...|||I guess you could call it a pick list as long as all subsets are represented.

I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.

thx.|||But that's only bit data...is that what you're looking for?|||yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.|||You are still talking about over 4 million permutations.

blindman