RE-Using passed parameters

Hi All

I link to a report passing parm a,b,c (3 parms) a (Hidden), B(Multi Selection), C(Single selection), the report display correctly and the parm selection boxes display with available options.

I would like to be able to choose from the selection boxes and replace the initial (passed parameters) B and C would have a new value, and display the same report using the selected criteria.

Thank you

Trentino

I can't understand what you are asking. You want to link to the same report with new parameter values? Or you want to just select different values? Maybe another example would help.|||

Hi Brian

I like to select different values.

report A link to report B using parm1 and parm2.

Parm1 passes value 'A', parm2 is spaces (space is interpreted as select all records

matching the other criteria, ex. if there are 5 records all of them will be retieved), the new report storedProc retrieve the data that I would like to use to initialize parm2.

I cannot initialize parm2 with the result value from the SP (5 records value), I get forward dependencies are not valid.

Currently I created a new parm3, initialize it with the SP values, I can select 1 or more of the 5 displayed records (multi value box) and that work ok.

Is there a way to re-use parm2 without adding parm3 so no changes need to be done to the SP?

Hope this help you understand, thank you for your time.

Trentino

|||You probably have based the Valid Values list query used for parm 2 dependent on parm2. This is a circular reference. You need to have the 3rd report parameter and you use the second one as a seed value.|||

Hello Brian,

I've read your previous reply about forward dependencies and I was hoping you'd hear my plee.

I am using Visual Studio 2005 and I'm passing a single parameter (a unique string called AuditGUID) to a report and this single parameter is part of a multivalue list (select box) of many AuditGUID's. This returns a lot of information related to that unique AuditGUID.

When I set the parameter to use a select box, the error says the following:

[rsInvalidReportParameterDependency] The report parameter ‘Audit_GUID_par’ has a DefaultValue or a ValidValue that depends on the report parameter “Audit_GUID_par”. Forward dependencies are not valid.

But, when I change the select box to a textbox, where the user would have to enter a Audit_GUID (which is a very long and complicated string), then the report compiles successfully and works...but I can't allow users to have to go through this, I'd rather let them select it from a list (selectbox). Could you help me with this please?

Thank you for your time and I look forward to hearing from you.

Jean

Re-using Identity achieved after INSERT query

Hi all,
I'm using an INSERT query to add records to a table with a defined
Identity column.
The query looks like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY();
The return value is Text_ID, and I'm using the it for another insert
query:
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Is there a way to combine the two queries into a single query ?
Something like this:
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT SCOPE_IDENTITY() = Text_ID;
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
SELECT SCOPE_IDENTITY();
Thanks to all.You can use variable for this.
Declare @.TextID int
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',9,1);
SELECT @.TextID = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.TextID ,1);
...
MC
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>|||I think you just need to use a variable:
DECLARE @.TextId INT
INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
9,1);
SELECT @.TextId = SCOPE_IDENTITY();
INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,@.Text_ID,1);
SELECT SCOPE_IDENTITY();
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Avital" <avital.chissick@.gmail.com> wrote in message
news:1193566518.353471.43280@.v3g2000hsg.googlegroups.com...
> Hi all,
> I'm using an INSERT query to add records to a table with a defined
> Identity column.
> The query looks like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY();
> The return value is Text_ID, and I'm using the it for another insert
> query:
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Is there a way to combine the two queries into a single query ?
> Something like this:
> INSERT INTO Table_A (Text,Size,CountOcur) VALUES ('copyright123',
> 9,1);
> SELECT SCOPE_IDENTITY() = Text_ID;
> INSERT INTO Table_B (FileID,TextID,TextLocaton) VALUES (1,Text_ID,1);
> SELECT SCOPE_IDENTITY();
> Thanks to all.
>

Re-using a calculated sub-select field in a view?

I've been running into more and more complexity with an application, because
as time goes on - we need more and more high-level, rolled-up information.
And so I've created views, and views that use other views.. and the queries
are getting slower and slower.

This morning, I'm working on something like this:

select
<some columns>,
"calculatedcolumn" = (select top 1 crap from stuff where
thingy='whatchamacallit')
from
someview

now, I realized that I need to really return "calculatedcolumn" in a couple
other places in the select like this - well, this is what I WANT to do:

select
<some columns>,
calculatedcolumn = (select top 1 crap from stuff where
thingy='whatchamacallit'),
otherfield = case SomeBit
when 1 then calculatedcolumn
else count(somefield)
end,
otherfield1 = case SomeotherBit
when 1 then calculatedcolumn
else sum(somefield)
end,
otherfield2 = case SomeBit2
when 1 then calculatedcolumn
else avg(somefield)
end,
otherfield3 = case SomeBit3
when 1 then calculatedcolumn
else count(somefield)
end,
from
someview

Point is, I CAN'T do that, so I have to re-run that sub-select for EACH of
these cases, and that is KILLING this stored procedure. It seems to me, that
if the database when and already got that field, for that row - I should be
able to re-use, rather than going back out additional times.

Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!RCS (rseder@.gmail.com) writes:
> Is there a way to so this? Put simpler:
> select
> x = (select top 1 user_id from users),
> bestUser=x,
> smartestUser=x
> from
> Users
> can I re-use "x" in that example. Thanks!

Maybe. It depends a little what is in that subquery. If it's uncorrelated
to the main query, you could to:

SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
FROM users
CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x

Here I'm using a derived table. A derived table is kind of a temp table
in the middle of the query. However, it is never materialized, and SQL
Server may not even compute the table as such, but can recast computation
order as result is the same.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the response!

In this particular case, the general gist is that this is for tracking
scores for tests.. the outer select is for general information (module name,
etc) and the subselects are tallying up total users, how many passed, how
many failed.. so in psuedo sql, it's something like this (this is very, very
over-simplied - but hopefully is clear):

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
from module as m

but it gets more complicated, I have to add some logic in for several fields
that say "if the module is a training module, then return back 'totalusers'
becuase even just finishing a practice module, should be considered
'passing'". For example.. So what I am doing now (using the same example
above) is:

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistraining
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers" above
else (select count(distinct userid) from modulescores where
moduleid=m.moduleid and didpass=1)
end
from module as m

But that "(select count(*) from moduleusers where moduleid = m.moduleid)"
(again, as a simple example) is in real life, a complex join, to a couple of
views and is slow - and I have to keep re-using it in other select fields.

I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns962CE7FDF29B2Yazorman@.127.0.0.1...
> RCS (rseder@.gmail.com) writes:
>> Is there a way to so this? Put simpler:
>>
>> select
>> x = (select top 1 user_id from users),
>> bestUser=x,
>> smartestUser=x
>> from
>> Users
>>
>> can I re-use "x" in that example. Thanks!
> Maybe. It depends a little what is in that subquery. If it's uncorrelated
> to the main query, you could to:
> SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
> FROM users
> CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x
> Here I'm using a derived table. A derived table is kind of a temp table
> in the middle of the query. However, it is never materialized, and SQL
> Server may not even compute the table as such, but can recast computation
> order as result is the same.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||RCS (rseder@.gmail.com) writes:
> select
> modulename,
> totalusers = (select count(*) from moduleusers where moduleid =
> m.moduleid),
> passed = case moduleistraining
> when 1 then (select count(*) from moduleusers where moduleid =
> m.moduleid) -- this is the same as "totalusers" above
> else (select count(distinct userid) from modulescores where
> moduleid=m.moduleid and didpass=1)
> end
> from module as m

SELECT m.modulname, mu.totalusres,
passed = CASE m.moduleistraing
WHEN 1 THEN mu.totalusrers
ELSE ms.didpass
END
FROM modules m
JOIN (SELECT moduleid, totalusers = COUNT(*)
FROM moduleusers
GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
FROM modulescores
WHERE didpass = 1
GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid

See my preivious post for the explanation of the derived tables.

> I've never had a use for, or maybe never understood the "cross join"
> statment - and I'm not clear on how you mean to use it below. Could you
> elaborate?? Thanks again!!

A cross join gives all possible combinations; this is the famous
"carteisan product". I used a cross join this case because that was
how I could translate your very loose example.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ah-HA!!! Very nice - thanks very much!!!

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns962DE5AEDCD0AYazorman@.127.0.0.1...
> RCS (rseder@.gmail.com) writes:
>> select
>> modulename,
>> totalusers = (select count(*) from moduleusers where moduleid =
>> m.moduleid),
>> passed = case moduleistraining
>> when 1 then (select count(*) from moduleusers where moduleid =
>> m.moduleid) -- this is the same as "totalusers" above
>> else (select count(distinct userid) from modulescores where
>> moduleid=m.moduleid and didpass=1)
>> end
>> from module as m
>
> SELECT m.modulname, mu.totalusres,
> passed = CASE m.moduleistraing
> WHEN 1 THEN mu.totalusrers
> ELSE ms.didpass
> END
> FROM modules m
> JOIN (SELECT moduleid, totalusers = COUNT(*)
> FROM moduleusers
> GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
> JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
> FROM modulescores
> WHERE didpass = 1
> GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid
> See my preivious post for the explanation of the derived tables.
>> I've never had a use for, or maybe never understood the "cross join"
>> statment - and I'm not clear on how you mean to use it below. Could you
>> elaborate?? Thanks again!!
> A cross join gives all possible combinations; this is the famous
> "carteisan product". I used a cross join this case because that was
> how I could translate your very loose example.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||It's difficult to understand what you are really trying to do, but if
calculatedcolumn is going to be the same all the way through, DECLARE
and SET it upfront, i.e.
DECLARE @.CalculatedColumn int <or whatever>
SET @.CalculatedColumn = (select top 1 crap from stuff where
thingy='whatchamacallit')

then every time you need it again, just put @.CalculatedColumn instead
of the whole statement that figures it out.|||A variable could be used if you know the result is going to be static.

I've also run into a problem similar to this when updating a table
with calculations, but when the calculations are based off the current
row it's updating the use of a variable gets thrown out the window :(
I had to keep reselecting the result, a correlated sub-query really,
but with my update statement I used it a hell of a lot and a simple
update statement grew to over 300 lines :)

But hey, with a cursor it took 40 mins with 1.5 million rows, now it
takes 5 mins :)

I'm going to take a look into that derived table example, might be
able to speed my statements up! Cheers!

Ian

"Ellen K" <ekaye2002@.yahoo.com> wrote in message news:<1112595864.008752.272740@.f14g2000cwb.googlegroups. com>...
> It's difficult to understand what you are really trying to do, but if
> calculatedcolumn is going to be the same all the way through, DECLARE
> and SET it upfront, i.e.
> DECLARE @.CalculatedColumn int <or whatever>
> SET @.CalculatedColumn = (select top 1 crap from stuff where
> thingy='whatchamacallit')
> then every time you need it again, just put @.CalculatedColumn instead
> of the whole statement that figures it out.