I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done
From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comIf you are using 2005 why do you need a ctrl key for selection, instead you
can use the multiple check box to pick multiple values.
Amarnath
"Duvon Harper" wrote:
> I have a report that I would like to allow the user to use the Ctrl key to select mutiple cost centers in a report parameter field. Does anyone know how this would be done?
> From http://www.developmentnow.com/g/115_2004_11_0_4_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>
Showing posts with label cost. Show all posts
Showing posts with label cost. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
MULTIPLE TABLE QUERY!
I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJ
Anthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>
|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =
category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =
category_group.category_id
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJ
Anthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>
|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =
category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =
category_group.category_id
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Friday, March 9, 2012
MULTIPLE TABLE QUERY!
I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Wednesday, March 7, 2012
Multiple Sorting
Hello All!
My report has columns Date, Year, Model, Cost, Color
I have users that will require the abililty to sort by multiple criteria.
Is it possible to configure a primary AND secondary AND maybe a THIRD sort
in Reporting Services?
For example: Sort the above columns as: Date, Model and Cost at the same
time?
This sorting criteri will change based on the user and the info sought. So
another user may want to
sort on Cost and Year, while another person wants Color,Cost, Year etc..
Currently, as far as I know, Reporting Services limits one to one column
sort at a time in a report.
Thanks in advance !
ScottOn Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
wrote:
> Currently, as far as I know, Reporting Services limits one to one column
> sort at a time in a report.
By default, all of your objects will be populated in the order of the
data in the DataSet. You can define this with a ORDER BY clause in
your SQL.
But, you can create Sort rules by object in your Report. Go to your
Table Properties, then the Sort tab, and you can add multiple sorting
rules to your table. Each rule is applied in sequence, with an
Ascending or Descending. Note that the sorting value is actually an
Expression, which means you can make it dynamic with a little code.
For your problem, what I would do is create a Parameter called
SortRules, make it a Integer, then define Available values as:
"Date, Model, Cost", 1
"Cost, Year", 2
"Color, Cost, Year", 3
etc
Then, in your table, add three Sort rules, each Ascending
Rule 1:
= IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
Nothing ) ) )
Rule 2:
= IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
Nothing ) ) )
Rule 3:
= IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 2, Nothing,
IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
Nothing ) ) )
Now, when the user views the report, they will get a Parameter Prompt
at the top to select a sort method, then after choosing one and
hitting the View Report, the table will sort based on that method.
-- Scott|||Thanks so much for your idea! have a great weekend!
Scott
"Orne" wrote:
> On Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
> wrote:
> > Currently, as far as I know, Reporting Services limits one to one column
> > sort at a time in a report.
> By default, all of your objects will be populated in the order of the
> data in the DataSet. You can define this with a ORDER BY clause in
> your SQL.
> But, you can create Sort rules by object in your Report. Go to your
> Table Properties, then the Sort tab, and you can add multiple sorting
> rules to your table. Each rule is applied in sequence, with an
> Ascending or Descending. Note that the sorting value is actually an
> Expression, which means you can make it dynamic with a little code.
> For your problem, what I would do is create a Parameter called
> SortRules, make it a Integer, then define Available values as:
> "Date, Model, Cost", 1
> "Cost, Year", 2
> "Color, Cost, Year", 3
> etc
> Then, in your table, add three Sort rules, each Ascending
> Rule 1:
> = IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
> Nothing ) ) )
> Rule 2:
> = IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
> Nothing ) ) )
> Rule 3:
> = IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 2, Nothing,
> IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
> Nothing ) ) )
> Now, when the user views the report, they will get a Parameter Prompt
> at the top to select a sort method, then after choosing one and
> hitting the View Report, the table will sort based on that method.
> -- Scott
>
My report has columns Date, Year, Model, Cost, Color
I have users that will require the abililty to sort by multiple criteria.
Is it possible to configure a primary AND secondary AND maybe a THIRD sort
in Reporting Services?
For example: Sort the above columns as: Date, Model and Cost at the same
time?
This sorting criteri will change based on the user and the info sought. So
another user may want to
sort on Cost and Year, while another person wants Color,Cost, Year etc..
Currently, as far as I know, Reporting Services limits one to one column
sort at a time in a report.
Thanks in advance !
ScottOn Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
wrote:
> Currently, as far as I know, Reporting Services limits one to one column
> sort at a time in a report.
By default, all of your objects will be populated in the order of the
data in the DataSet. You can define this with a ORDER BY clause in
your SQL.
But, you can create Sort rules by object in your Report. Go to your
Table Properties, then the Sort tab, and you can add multiple sorting
rules to your table. Each rule is applied in sequence, with an
Ascending or Descending. Note that the sorting value is actually an
Expression, which means you can make it dynamic with a little code.
For your problem, what I would do is create a Parameter called
SortRules, make it a Integer, then define Available values as:
"Date, Model, Cost", 1
"Cost, Year", 2
"Color, Cost, Year", 3
etc
Then, in your table, add three Sort rules, each Ascending
Rule 1:
= IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
Nothing ) ) )
Rule 2:
= IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
Nothing ) ) )
Rule 3:
= IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
IIF( Parameters!SortRules.Value = 2, Nothing,
IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
Nothing ) ) )
Now, when the user views the report, they will get a Parameter Prompt
at the top to select a sort method, then after choosing one and
hitting the View Report, the table will sort based on that method.
-- Scott|||Thanks so much for your idea! have a great weekend!
Scott
"Orne" wrote:
> On Nov 16, 9:53 am, iamscott <iamsc...@.discussions.microsoft.com>
> wrote:
> > Currently, as far as I know, Reporting Services limits one to one column
> > sort at a time in a report.
> By default, all of your objects will be populated in the order of the
> data in the DataSet. You can define this with a ORDER BY clause in
> your SQL.
> But, you can create Sort rules by object in your Report. Go to your
> Table Properties, then the Sort tab, and you can add multiple sorting
> rules to your table. Each rule is applied in sequence, with an
> Ascending or Descending. Note that the sorting value is actually an
> Expression, which means you can make it dynamic with a little code.
> For your problem, what I would do is create a Parameter called
> SortRules, make it a Integer, then define Available values as:
> "Date, Model, Cost", 1
> "Cost, Year", 2
> "Color, Cost, Year", 3
> etc
> Then, in your table, add three Sort rules, each Ascending
> Rule 1:
> = IIF( Parameters!SortRules.Value = 1, Fields!Date.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Color.Value,
> Nothing ) ) )
> Rule 2:
> = IIF( Parameters!SortRules.Value = 1, Fields!Model.Value,
> IIF( Parameters!SortRules.Value = 2, Fields!Year.Value,
> IIF( Parameters!SortRules.Value = 3, Fields!Cost.Value,
> Nothing ) ) )
> Rule 3:
> = IIF( Parameters!SortRules.Value = 1, Fields!Cost.Value,
> IIF( Parameters!SortRules.Value = 2, Nothing,
> IIF( Parameters!SortRules.Value = 3, Fields!Year.Value,
> Nothing ) ) )
> Now, when the user views the report, they will get a Parameter Prompt
> at the top to select a sort method, then after choosing one and
> hitting the View Report, the table will sort based on that method.
> -- Scott
>
Subscribe to:
Posts (Atom)