sql server reporting services: Session ID in Data Extension -- sql server reporting services

You're in the

sql server reporting services

group:

Session ID in Data Extension

shankar
8/5/2004 4:07:01 PM

sql server reporting services:

Is it possible to get the session id in Data Extension or Report Designer
thanks

Re: Session ID in Data Extension

Lukasz Pawlowski [MSFT]
8/6/2004 10:54:56 AM

Nope. What are you trying to do?

-Lukasz

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Session ID in Data Extension

shankar
8/9/2004 11:52:40 AM

Lukasz,
As you know, a data extension cannot return multiple resultsets. I have
a report(infact, many reports) with two data region. one region displays
details and other region displays summary(it's not just summing up the data.
the summary data has complex business calculation done at the server). One
of the workaround suggested in this new group was to execute the stored proc
twice one with "detail' parameter and other with "summary ". I don't like
this approach because of performance reasons. So, I was researching about
other options. My idea is to create a intermediate request manager.
Here's how i think it shoud work.
Design the report with two data region
Set the first dataregion's quertstring property EXEC stored_proc1 0
, other parameters(0 means return first resultset)
Set the second dataregion's quertstring property EXEC stored_proc1
1, other parameters( 1 means return second resultset)
Create custom data extension which hands over the request to a
RequestManager.
Based on the session id, the request manager executes the stored
proc and returns the first table to first data region and
caches the dataset
For the second data region, If the stored proc is executing , then
it queues the request and returns the second table when the results become
available or from cache.

I know this sounds complicated or may be i'm doing things in round about
way. For me, executing the stored proc twice is not an option. please
suggest me a workaround

thanks

shankar

[quoted text, click to view]

Re: Session ID in Data Extension

Lukasz Pawlowski [MSFT]
8/9/2004 2:22:35 PM

That sounds complicated :-).

First, can you try using temp tables in SQL to solve your problem? For
example:

Create a SP that does the following (really really pseudo code :-))

SP_GetData(QueryOrdinal)
{
If (temp table ! exists)
create temp table containing desired results

if (QueryOrdinal==1)
run query/SP 1 on temp table
else if (QueryOrdinal==2)
run query/SP 2 on temp table
else
send back some error or empty result set
}

One issue you're done with that, there's an option for data sources to
execute all querys on that data source in a single transaction - the RDL
element is Transaction. Set this to execute all queries in the same
transaction.

Finally, author your report to use the SP_GetData as the source of data and
pass the right parameter. You might need to enter field names manually if
report designer isn't able to sniff the names because of the temp table.

However, this should be much simpler than wirting all that code :-).

-Lukasz

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Session ID in Data Extension

shankar
8/10/2004 1:18:43 PM

Thanks, Lukasz. In your reply, you mentioned about IDBTransaction. Can you
shed light on how it's used. Who(ReportServer) begins the transaction and
who commits or rollback the transaction

shankar

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn