Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts

Friday, March 23, 2012

Reset Report Parameters

Hi

I am creating a report which have 10 different parameter. User needs a button or any functionality using which he/she can reset all the parameter at once and view unfiltered report.

How can I achieve that?

Regards

You can add a textbox that has an action set to "Jump to Report" and set the report to itself.

This will reset the parameters back to their default values or you can specify specific parameters to reset by setting the parameter values that you want to persist to their current value in the Actions dialog.

HTH, Jason

|||

Thanks Jason.

That will do.

Regards

Monday, March 12, 2012

Request: Help creating a difficult view.

Hello all.

I have a table defined in sql server as follows:

ROW_ID (identity)
DEPTH_FROM Number (8,3)
DEPTH_TO Number (8,3)
COLOUR Char(10)

With typical data like:
ROW_ID DEPTH_FROM DEPTH_TO COLOUR
-------------------
1 0 5
BLUE
2 5 8
BLUE
3 8 10
RED
4 10 12
GREEN
5 12 16
GREEN

I want to create a view that will 'compress/roll up' the data so
it appears like:

DEPTH_FROM DEPTH_TO COLOUR
------------------
0 8 BLUE
8 10 RED
10 16 GREEN

I have been working on this for several days, with no luck,
any help would be appreciated. BTW: there are no overlaps
allowed in the depth_from, depth_to values.

Thanks in advance.I'll assume that the colours don't always occur at consecutive depths
otherwise you could just do this:

SELECT MIN(depth_from), MAX(depth_to), colour
FROM ColDepths
GROUP BY colour

Here's the (assumed) DDL and sample data. It helps if you include this with
posts.

CREATE TABLE ColDepths (row_id INTEGER NOT NULL UNIQUE, depth_from INTEGER
NOT NULL, depth_to INTEGER NOT NULL, colour CHAR(10) NOT NULL,
CHECK(depth_from<depth_to), PRIMARY KEY (depth_from,depth_to))

INSERT INTO ColDepths VALUES (1,0,5, 'BLUE')
INSERT INTO ColDepths VALUES (2,5,8, 'BLUE')
INSERT INTO ColDepths VALUES (3,8,10, 'RED')
INSERT INTO ColDepths VALUES (4,10,12, 'GREEN')
INSERT INTO ColDepths VALUES (5,12,16, 'GREEN')

Here's my query.

SELECT MIN(A.depth_from) AS depth_from,
MAX(A.depth_to) AS depth_to, A.colour
FROM ColDepths AS A
JOIN
(SELECT c1.row_id, MIN(C2.depth_to) AS next_depth
FROM ColDepths AS C1
LEFT JOIN ColDepths AS C2
ON C1.colour <> C2.colour
AND (C1.depth_from < C2.depth_from
OR (C1.depth_from = C2.depth_from)
AND C1.depth_to <= C2.depth_to)
GROUP BY c1.row_id) AS B
ON A.row_id = B.row_id
GROUP BY A.colour, B.next_depth

--
David Portas
----
Please reply only to the newsgroup
--|||Is this what you have in mind?

create table foo
(ROW_ID int, /* my datatypes vary from yours for my convenience
*/
DEPTH_FROM int,
DEPTH_TO int,
COLOUR Char(10))
go
insert foo values (1,0,5,'blue')
insert foo values (2,5,8,'blue')
insert foo values (3,8,10,'red')
insert foo values (4,10,12,'green')
insert foo values (5,12,16,'green')
go
select min(depth_from) as depth_from, max(depth_to) as depth_to, colour
from foo
group by colour

depth_from depth_to colour
---- ---- ----
0 8 blue
10 16 green
8 10 red

(3 row(s) affected)

You may want an ORDER BY clause, too. Order of rows returned with GROUP BY
is not guaranteed/predictable.

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote...

> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN

I don't use SqlServer but... wouldn't this work?

select min(depth_from), max(depth_to), colour from <tablename> group by
colour|||Testing these suggestions right now, thanks to all

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||Hi Dave,

If there is no overlap but there can be gaps between intervals, this is the
query you want:

select DEPTH_FROM,
DEPTH_TO = (select min(DEPTH_TO)
from T T3
where DEPTH_TO not in (select DEPTH_FROM
from T T4
where T3.COLOUR = T4.COLOUR
and T3.DEPTH_FROM <>
T4.DEPTH_FROM
)
and T3.DEPTH_TO > T1.DEPTH_FROM
),
COLOUR
from T T1
where DEPTH_FROM not in (select DEPTH_TO
from T T2
where T1.COLOUR = T2.COLOUR
and T1.DEPTH_FROM <> T2.DEPTH_FROM
)
order by DEPTH_FROM

Good Luck,
Shervin

"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.|||"Dave Pylatuk" <davep@.centurysystems.net> wrote in message
news:pZgcb.4184$1H3.311803@.news20.bellglobal.com.. .
> Hello all.
> I have a table defined in sql server as follows:
> ROW_ID (identity)
> DEPTH_FROM Number (8,3)
> DEPTH_TO Number (8,3)
> COLOUR Char(10)
> With typical data like:
> ROW_ID DEPTH_FROM DEPTH_TO COLOUR
> -------------------
> 1 0 5
> BLUE
> 2 5 8
> BLUE
> 3 8 10
> RED
> 4 10 12
> GREEN
> 5 12 16
> GREEN
> I want to create a view that will 'compress/roll up' the data so
> it appears like:
> DEPTH_FROM DEPTH_TO COLOUR
> ------------------
> 0 8 BLUE
> 8 10 RED
> 10 16 GREEN
> I have been working on this for several days, with no luck,
> any help would be appreciated. BTW: there are no overlaps
> allowed in the depth_from, depth_to values.
> Thanks in advance.

This will also handle gaps between consecutive depth intervals.

CREATE TABLE ColorDepths
(
depth_from INT NOT NULL PRIMARY KEY,
depth_to INT NOT NULL,
color CHAR(10) NOT NULL,
CHECK (depth_from <= depth_to)
)

-- Your sample data augmented to better exercise code
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (0,5, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (5,8, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (8,10, 'RED')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (11,12, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (12,15, 'GREEN')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (16, 18, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (18, 24, 'BLUE')
INSERT INTO ColorDepths (depth_from, depth_to, color)
VALUES (26, 30, 'BLUE')

-- Associate consecutive depth intervals with natural numbers
CREATE VIEW OrderedColorDepths (depth_from, depth_to, color, seq)
AS
SELECT D1.depth_from, D1.depth_to, D1.color, COUNT(*)
FROM ColorDepths AS D1
INNER JOIN
ColorDepths AS D2
ON D2.depth_from <= D1.depth_from
GROUP BY D1.depth_from, D1.depth_to, D1.color

-- Using above natural numbers, find endpoints
CREATE VIEW ColorDepthEnds (color, seq)
AS
SELECT OD1.color, OD1.seq
FROM OrderedColorDepths AS OD1
LEFT OUTER JOIN
OrderedColorDepths AS OD2
ON OD2.seq = OD1.seq + 1
WHERE OD2.color <> OD1.color OR -- consecutive depths w/ diff. colors
OD2.color IS NULL OR -- last (greatest) depth
OD2.depth_from > OD1.depth_to -- gap between consecutive depths

SELECT color,
MIN(depth_from) AS depth_from , MAX(depth_to) AS depth_to
FROM (SELECT OD.color, OD.depth_from, OD.depth_to,
MIN(DE.seq) AS seq
FROM OrderedColorDepths AS OD
INNER JOIN
ColorDepthEnds AS DE
ON DE.seq >= OD.seq AND
DE.color = OD.color
GROUP BY OD.depth_from, OD.depth_to, OD.color) AS R
GROUP BY seq, color
ORDER BY depth_from

color depth_from depth_to
BLUE 0 8
RED 8 10
GREEN 11 15
BLUE 16 24
BLUE 26 30

Regards,
jag

Wednesday, March 7, 2012

Representation of Relationships in 2005

We're currently developing on SQL Server 2005 and I have a question about ho
w
SQL Management Studio represents the relationships when creating a diagram.
First off, when creating the relationship, there's no place to designate
whether the relationship is a 1-1 or a 1-M relationship. Secondly, they all
end up being shown as 1-M, with a key on the 1 side and the infinity symbol
on the M side.
I can't find anything in the documentation about how to designate it as a
1-1 relationship instead of a 1-M.
Any ideas?
Thanks for the help.
DougHi Doug,
This is probably better asked in the SQL Server 2005 newsgroups
(http://communities.microsoft.com/ne...lcid=u
s).
Have you looked at the topic How to: Create Relationships Between Tables
(Visual Database Tools) in SQL Server Books Online? I just glanced at it,
but it looks like it might answer your question.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:180D9D97-5E55-42B6-A9BF-986B7FD62E4F@.microsoft.com...
> We're currently developing on SQL Server 2005 and I have a question about
> how
> SQL Management Studio represents the relationships when creating a
> diagram.
> First off, when creating the relationship, there's no place to designate
> whether the relationship is a 1-1 or a 1-M relationship. Secondly, they
> all
> end up being shown as 1-M, with a key on the 1 side and the infinity
> symbol
> on the M side.
> I can't find anything in the documentation about how to designate it as a
> 1-1 relationship instead of a 1-M.
> Any ideas?
> Thanks for the help.
> Doug

Representation of Relationships in 2005

We're currently developing on SQL Server 2005 and I have a question about how
SQL Management Studio represents the relationships when creating a diagram.
First off, when creating the relationship, there's no place to designate
whether the relationship is a 1-1 or a 1-M relationship. Secondly, they all
end up being shown as 1-M, with a key on the 1 side and the infinity symbol
on the M side.
I can't find anything in the documentation about how to designate it as a
1-1 relationship instead of a 1-M.
Any ideas?
Thanks for the help.
Doug
Hi Doug,
This is probably better asked in the SQL Server 2005 newsgroups
(http://communities.microsoft.com/new...r2005&slcid=us).
Have you looked at the topic How to: Create Relationships Between Tables
(Visual Database Tools) in SQL Server Books Online? I just glanced at it,
but it looks like it might answer your question.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:180D9D97-5E55-42B6-A9BF-986B7FD62E4F@.microsoft.com...
> We're currently developing on SQL Server 2005 and I have a question about
> how
> SQL Management Studio represents the relationships when creating a
> diagram.
> First off, when creating the relationship, there's no place to designate
> whether the relationship is a 1-1 or a 1-M relationship. Secondly, they
> all
> end up being shown as 1-M, with a key on the 1 side and the infinity
> symbol
> on the M side.
> I can't find anything in the documentation about how to designate it as a
> 1-1 relationship instead of a 1-M.
> Any ideas?
> Thanks for the help.
> Doug

Representation of Relationships in 2005

We're currently developing on SQL Server 2005 and I have a question about how
SQL Management Studio represents the relationships when creating a diagram.
First off, when creating the relationship, there's no place to designate
whether the relationship is a 1-1 or a 1-M relationship. Secondly, they all
end up being shown as 1-M, with a key on the 1 side and the infinity symbol
on the M side.
I can't find anything in the documentation about how to designate it as a
1-1 relationship instead of a 1-M.
Any ideas?
Thanks for the help.
DougHi Doug,
This is probably better asked in the SQL Server 2005 newsgroups
(http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us).
Have you looked at the topic How to: Create Relationships Between Tables
(Visual Database Tools) in SQL Server Books Online? I just glanced at it,
but it looks like it might answer your question.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:180D9D97-5E55-42B6-A9BF-986B7FD62E4F@.microsoft.com...
> We're currently developing on SQL Server 2005 and I have a question about
> how
> SQL Management Studio represents the relationships when creating a
> diagram.
> First off, when creating the relationship, there's no place to designate
> whether the relationship is a 1-1 or a 1-M relationship. Secondly, they
> all
> end up being shown as 1-M, with a key on the 1 side and the infinity
> symbol
> on the M side.
> I can't find anything in the documentation about how to designate it as a
> 1-1 relationship instead of a 1-M.
> Any ideas?
> Thanks for the help.
> Doug

Saturday, February 25, 2012

Repost: Exporting Information

Hello NG
We have a production database that we have moved the tables to SQLServer and
I have been creating stored procedures to replicate some of the processes
done on the access side - the server does processing extremely faster than
access - Here is my problem I am currently pulling info from SQL SERVER to
Access once a w for wly stats I want to create a stored procedure to
process the wly stats and push them to an access table upon request - we
have multiple warehouses and I would like to have the process coded once in
sql and dump to the access table based off a warehouse variable passed to
the stored procedure - Can SQLServer from a stored procedure export a
recordset into a varible defined Access Table?
TIAFAH
RandyHi Randy,
Yes this is possible...You can do it by using a linked server. You can
find info about linked servers in SQL Server Books Online. I've given
an outline of the general steps you need to do below.
Hope this helps.
=================
1) Open Query Analyzer and log in using an account with SysAdmin
privileges (e.g. sa). Make sure you are using the "master" database.
2) Type in the following:
exec sp_addlinkedserver '<Linked Server Name>', '<MS Access Version>',
'Microsoft.Jet.OLEDB.4.0', '<Full Path To Access DB>'
<Linked Server Name> can be whatever you want it to be, you will use
this to reference the Access DB.
<MS Access Version> is something like 'Access 97' or 'Access 2003' etc
<Full Path To Access DB> is, well, yeah...
3) Execute what you entered in (2) and then clear the Query Analyzer
window of the command
4) Type in the folliwng into Query Analyzer:
sp_addlinkedsrvlogin '<Linked Server Name>', false, '<SQL Server Login
Name>', 'Admin', NULL
<SQL Server Login Name> is the SQL Server login that will access the
Access DB. Make it something like "sa" if you will be executing the
stored proc under your account.
5) Execute the command entered in (4)
OK, after doing that you can now access you MS Access DB from SQL
Server. To reference a table in Access you have to write:
<Linked Server Name>...<Table Name>
You can do Inserts and other things to this table. In your case you
would probably be wanting to Insert values into the table.
Hope that helps.

Tuesday, February 21, 2012

ReportViewer: creating PDF on the fly

Hi all,

I'm writing a offer web page using ReportViewer control.
This offer page will be shown in PDF format.

I understood that the ReportViewer control can't work without any DataSource, but for my needs I use report parameters and not DataSet (or somesimilar object). So, I defined the unreal DataSet just for deceive the ReportViewer (I don't know if this solution is wise, but probably it's what I ableSmile).

<rsweb:ReportViewer runat="server" ID="ReportViewer1" Font-Names="Verdana" Font-Size="8pt" Height="400px" ShowToolBar="False" Width="685px"> <LocalReport ReportPath="Offer.rdlc" > <DataSources> <rsweb:ReportDataSource DataSourceId="objectDataSource" Name="DataSet1" /> </DataSources> </LocalReport></rsweb:ReportViewer><asp:ObjectDataSource runat="server" ID="objectDataSource" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="DataSet1TableAdapters.MyTableAdapter"></asp:ObjectDataSource>
So far so good. When I activate the offer page, it shown properly, but when I add the following code for create the PFD document from the page, I get an error:
A data source instance has not been supplied for the data source "DataSet1"
protected void Page_Load(object sender, EventArgs e){ Warning[] warnings;string[] streamids;string mimeType;string encoding;string extension;byte[] myBytes =this.ReportViewer1.LocalReport.Render("PDF",null,out mimeType,out encoding,out extension,out streamids,out warnings); Response.Buffer =true; Response.Clear(); Response.ContentType = mimeType; Response.BinaryWrite(myBytes); Response.Flush(); Response.End();}

Please help!

Thanks in advance!

Hi,

Take a look over here :

http://blogs.msdn.com/bimusings/archive/2005/07/01/434659.aspx

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Hi Suprotim Agarwal,

Thank you for the quick response.
I already saw this short article, but it not exactly answers to my case - the error appears only when I create a PDF document from my predefined ReportViewer. If I delete an aforementioned code, the Offer.aspx page work properly.

|||

Hi,

Based on the code you provided, you are going to render the report in the code behind, right?

I think you should declare and add the report datasource explicitly in your code-behind file before you are invoking the render method, see the following code snippet:

protected void Page_Load(object sender, EventArgs e)
{
this.ReportViewer1.ProcessingMode = ProcessingMode.Local;
this.ReportViewer1.LocalReport.ReportPath =
@."c:\Reports\Report1.rdl";
ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("Sales", LoadSalesData())); declare and add the datasource explicitly.

}
protected void Button1_Click(object sender, EventArgs e)
{
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;

byte[] bytes = ReportViewer1.LocalReport.Render(
"Excel", null, out mimeType, out encoding,
out extension,
out streamids, out warnings);

FileStream fs = new FileStream(@."c:\output.xls",
FileMode.Create);
fs.Write(bytes, 0, bytes.Length);
fs.Close();

Label1.Text = "Report exported to output.xls";

}
}

Thanks.

|||

The problem solved when I moved a code that creates a PDF document from Page_Load to Page_SaveStateComplete event.

Thanks to all of you!