Creating a drill-through report

I'm building my report using the Report Designer in the SQL Server Business Intelligence Development Studio. At the moment I've got a single report which consists of 2 tables. The top table shows some summary data and the bottom table shows more detail.

What I'd like to do is design the report so that when the summary data (in the top table) appears, the user can click on a row in the table and this takes them to a new screen which shows more detail about that item, extracted from the bottom table.

Is this possible? I know it's possible to create a drill-through report if you have the high-level summary data in one report and the low-level detailed data in a separate report, but is it possible to create a drill-through report if all the data is in one single report (albeit in 2 separate tables)?

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

|||

??€?§Q? wrote:

If the data in the top table are directly associated (same number of rows and in the same position) as the data in the bottom table, then I would say it doesn't matter whether you are clicking the top table or the bottom one.

However, I doubt that is the case.

The bottom table doesn't contain the same number of rows as the top one. The top table has one row for each member of staff, which consists summary data about how many pieces of work they have completed. What I want is for the user to be able to click on the name of a member of staff, and then they will be taken to a separate screen showing the full details for that particular staff-member (this data should be taken from what is currently the bottom table in the report). The bottom table contains one row for each piece of work that a particular staff-member has completed, so the number of rows would vary depending on whose name has been selected.

I hope this makes sense; it's always hard to explain what you're trying to do on a forum.

|||

Ok, so why not just create a drilldown report and enable navigation on the top report to that drilldown?

Why does the bottom table play into this at all?

|||

You could have a hidden parameter in your report got StaffID. Setup Visibility on the bottom table to hide if staffid is null, and show when it isnt. Do the opposite with the top table, if is hidden if staffid is not null, and visible if it is null.

Setup the navigation on the top table to go to the same report, passing the StaffID that was clicked on.

Since StaffID will not be null, the report will hide the top and only show the bottom.

Now, all that being said, the better way to do that is to have 2 reports, one for the list of staff and one for the details.

HtH

BobP

|||

And to open the report in the new window, you will have to use javascript in the Navigation window.

Select Jump to URL and add this code:

="javascript:void window.open('http://server/reportserver/pages/reportviewer.aspx?%2fFOLDER%2fREPORT&StaffID=" & Fields!StqaffID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

BobP

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating .sql script file with data

Hi, how to create a single script file that contains db schema and data?
(Moving db to another machine with sql script file) . I have tried data
export wizard, but it does not export data, but only schema of db.> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
You might want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Create a backup and restore that on to the machine.
Simple and fast.
Mark Baekdal
www.dbghost.com
DB Ghost rocks!
>--Original Message--
>Hi, how to create a single script file that contains db
schema and data?
>(Moving db to another machine with sql script file) . I
have tried data
>export wizard, but it does not export data, but only
schema of db.
>
>.
>|||Hi,
There are 2 better options to perform this,
1. Detach and Attach
a. SP_detach_db <dbname>
b. Copy the MDF and LDF to destination
c. SP_ATTACH_DB <dbanme>,@.filename1='file.mdf',@.filename2='file.ldf'
Note: After detach please attach the original db again.
2. Backup and restore
1. Backup the database (Backup database dbname to
disk='c:\backup\dbname.bak' with init )
2. COpy the dbname.bak to destination server
3. Restore the database (Restore database dbname
disk='c:\backup\dbname.bak' )
Thanks
Hari
MCDBA
"Jessy" <admin@.zambak.com> wrote in message
news:O0rtNgF$DHA.2520@.TK2MSFTNGP11.phx.gbl...
> Hi, how to create a single script file that contains db schema and data?
> (Moving db to another machine with sql script file) . I have tried data
> export wizard, but it does not export data, but only schema of db.
>

Creating "columns" from transaction data

Hi,
I have a transaction table that basically has the following fields
RecId, PeriodId, Quantity (a single RecId can have multiple records, i.e.
quantities in multiple periods)
I need to convert an entire table of these records to one that looks like
this...
RedId , P1Qty, P2Qty, P3Qty, P4Qty etc...
Which has one row per RecId and places the quantity (quantities) in the
appropriate "period" column(s) based on the value(s) of "Period" in
the transaction file for each record.
I've done this before in Access, using the IIF function for each of P1...P4
columns (IIF(Period = 1, Quantity, 0), IIF(Period = 2, Quantity, 0) for
each of the columns of the derived table I was making. This doesn't seem to
work for SQL Server. IIF exists, but I can't get the computed columns to
work properly (Syntax error near "=").
So, I've thought about...
1) Use CreateTable to create my derived table with periods as columns,
2) Write a series of INSERT queries that reads the transaction file for
each possible individual value for "Period" and populates the appropriate
column in the derived table
3) Sum the derived table on every column by RecId
4) Run the whole batch as the SelectCommand of my DataAdapter. The last
command in the batch is Select * from DerivedTable and this is the table
that the DataSet gets.
There has to be a better way to do this?
Thanks.
BBMYou can use CASE expressions instead of IIF. But why would you ever create a
table like this? What you are asking for is a report not a table. Any
reporting tool will construct a cross tab report for you.
David Portas
SQL Server MVP
--|||Do you have any idea what First Normal Form is? You might want to
learn about RDBMS before you write any code.|||Thanks David, CASE was just what I'm looking for.
In this instance, this result set is used as one of the tables in
multi-table DataSet used on a fairly complex display.
Thanks again.
BBM
"David Portas" wrote:

> You can use CASE expressions instead of IIF. But why would you ever create
a
> table like this? What you are asking for is a report not a table. Any
> reporting tool will construct a cross tab report for you.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Yes, in fact I do. I simplified the underlying data structure in my questio
n
to hopefully make it easier to reply to. I was only using the "extra" table
,
because I couldn't figure out how to get the result set I wanted in one pass
.
Thanks for your response anyway.
"--CELKO--" wrote:

> Do you have any idea what First Normal Form is? You might want to
> learn about RDBMS before you write any code.
>

Create variables with same format in one single step?

I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below. Thanks a lot!

--Current Query--
SELECT cast(0.0 as money) as balance_1
,cast(0.0 as money) as balance_2
,cast(0.0 as money) as balance_3
,cast(0.0 as money) as balance_4
,cast(0.0 as money) as balance_5
,cast(0.0 as money) as balance_6
,cast(0.0 as money) as balance_7
,cast(0.0 as money) as balance_8
,account_no
,XXX
INTO table1
FROM account

Can I do something like this? This one didn't work.

SELECT balance_1 to balance_8 (cast 0.0 as money)
,account_no
,XXX
INTO table1
FROM accountIt's not a single step but this might work for you:

This might work for you:

declare @.balance_1 money,
@.balance_2 money,
@.balance_3 money,
@.balance_4 money,
@.balance_5 money,
@.balance_6 money,
@.balance_7 money,
@.balance_8 money

set @.balance_1 = 0
set @.balance_2 = 0
set @.balance_3 = 0
set @.balance_4 = 0
set @.balance_5 = 0
set @.balance_6 = 0
set @.balance_7 = 0
set @.balance_8 = 0

SELECT @.balance_1,
@.balance_2,
@.balance_3,
@.balance_4,
@.balance_5,
@.balance_6,
@.balance_7,
@.balance_8,
account_no,
XXX
INTO table1
FROM account|||I'm confused. There are no variables in the code you posted - you are
creating a table with eight columns. There is only one step in a query
- the whole query operates as one logical unit.

What you seem to be looking for is a syntax shortcut that saves you
typing out the column names. The best shortcut is probably to click and
drag a list of names from the Object Browser in Query Analyzer then
search and replace the rest. I expect you could do that in much less
time than it took to write out this question. :-)

Your query itself looks a little strange. Are you sure you can't
calculate the balances with a single query rather than SELECT followed
by an UPDATE / INSERT?

--
David Portas
SQL Server MVP
--|||(rong.guo@.gmail.com) writes:
> I would need to create multiple variables with the same format for
> future update, what I did is listing them separately, is there an easy
> way to combine them in one step? please see query below. Thanks a lot!
> --Current Query--
> SELECT cast(0.0 as money) as balance_1
> ,cast(0.0 as money) as balance_2
> ,cast(0.0 as money) as balance_3
> ,cast(0.0 as money) as balance_4
> ,cast(0.0 as money) as balance_5
> ,cast(0.0 as money) as balance_6
> ,cast(0.0 as money) as balance_7
> ,cast(0.0 as money) as balance_8
> ,account_no
> ,XXX
> INTO table1
> FROM account
> Can I do something like this? This one didn't work.
> SELECT balance_1 to balance_8 (cast 0.0 as money)
> ,account_no
> ,XXX
> INTO table1
> FROM account

You could build an string with the SQL statement by iterating from
1 to 8, and use EXEC() to execute that statement. However, this is
definnitely not recommendable.

I have no idea what you are up to, but columns are usually distinct
entities. If you find that you need balance_1 to balance_8, maybe
you should make them rows instead. That is how you work with array
data in SQL, rows with pairs (index, value).

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To answer the question I think you are trying to ask: No, there are not
arrays in SQL.

You create a table with a CREATE TABLE statement and you have to know
what it looks like before you type in the command.

You need to put the column names in the SELECT list. Using the SELECT
* option is a really bad idea for production code.

You need to give every column a meaningful name from your data model.
But fromthe look of this, you do not have a data model and are writing
code on the fly.|||Thank you ALL so much!!

I do have a bunch of other variables in the table (about 20), I only
listed account_no as an example of the variables.

Doing the calculation in a single step instead of using update is a
great idea, but for some reason, it created duplicates. I haven't found
out if it is due to the database duplicates or because of my query. I
will do further research... Thanks again...

--CELKO-- wrote:
> To answer the question I think you are trying to ask: No, there are
not
> arrays in SQL.
> You create a table with a CREATE TABLE statement and you have to know
> what it looks like before you type in the command.
> You need to put the column names in the SELECT list. Using the
SELECT
> * option is a really bad idea for production code.
> You need to give every column a meaningful name from your data model.
> But fromthe look of this, you do not have a data model and are
writing
> code on the fly.