How to display only distinct values/labels in a report parameter drop down?
The values are generated from the main query of the report. Thx. JLYou should have a dataset that is specifically for your report parameter. As
a matter of fact, you have it a little reversed. The report parameters
should be used to limit the query. If you are getting the data and then
using the report parameters to filter the report, you should re-evaluate. In
most cases you should limit the data coming over using query parameters
mapped to report parameters. If you filter the data and the data is of any
significant size you will have performance problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> How to display only distinct values/labels in a report parameter drop
> down?
> The values are generated from the main query of the report. Thx. JL|||It's very helpful. That really enlightens me. Now I think I have a lot of
changes to make. Thx. JL
"Bruce L-C [MVP]" wrote:
> You should have a dataset that is specifically for your report parameter. As
> a matter of fact, you have it a little reversed. The report parameters
> should be used to limit the query. If you are getting the data and then
> using the report parameters to filter the report, you should re-evaluate. In
> most cases you should limit the data coming over using query parameters
> mapped to report parameters. If you filter the data and the data is of any
> significant size you will have performance problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "JL" <JL@.discussions.microsoft.com> wrote in message
> news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> > How to display only distinct values/labels in a report parameter drop
> > down?
> > The values are generated from the main query of the report. Thx. JL
>
>
Showing posts with label display. Show all posts
Showing posts with label display. Show all posts
Sunday, March 25, 2012
Distinct Problem
I am currently trying to display a list of emergencies that have happened. So that the user can either send one up as an alert, unalert it or edit it. For legal purposes they cannot delete any of the emergencies they enter.
Here's my data:
isemgid - emergency - isemgevent
1000 - FALSE - Emergency1
1001 - FALSE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3
I have two pages. alert.jsp and home.jsp.
home.jsp looks at the first field (1000) in the table and displays isemgevent if emergency is TRUE. (it only reads the first field as I only want one emergency displaying at a time).
alert.jsp is a list of all the emergencies available to choose from. When the user clicks on say, emergency2, it takes all of emergency2's data and moves it into the first field (1000) and sets emergency to TRUE. This then alerts the homepage to display this emergency instead of whatever was in 1000 before.
My problem is that 1000 and whatever field was just sent as an alert are the same except for their isemgid number. I only want to display the distinct fields. I can't delete the duplicate because if the user was to make emergency3 the alert, emergency2's information would be lost forever, and they need to be able to switch back.
This is what I want to display:
isemgid - emergency - isemgevent
1000 - TRUE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3
See how 1001 is missing? Now say they alert 1002 (emergency2) I want it to look like this:
isemgid - emergency - isemgevent
1000 - TRUE - Emergency2
1001 - FALSE - Emergency1
1003 - FALSE - Emergency3
So that whatever is duplicate doesn't show.--Is this what you want?
select yt.isemgid, yt.emergency, yt.isemgevent
from YourTable yt
join (
select isemgid=max(isemgid)
from YourTable
group by isemgevent
) XXX on yt.isemgid=XXX.isemgid
order by yt.isemgevent
Here's my data:
isemgid - emergency - isemgevent
1000 - FALSE - Emergency1
1001 - FALSE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3
I have two pages. alert.jsp and home.jsp.
home.jsp looks at the first field (1000) in the table and displays isemgevent if emergency is TRUE. (it only reads the first field as I only want one emergency displaying at a time).
alert.jsp is a list of all the emergencies available to choose from. When the user clicks on say, emergency2, it takes all of emergency2's data and moves it into the first field (1000) and sets emergency to TRUE. This then alerts the homepage to display this emergency instead of whatever was in 1000 before.
My problem is that 1000 and whatever field was just sent as an alert are the same except for their isemgid number. I only want to display the distinct fields. I can't delete the duplicate because if the user was to make emergency3 the alert, emergency2's information would be lost forever, and they need to be able to switch back.
This is what I want to display:
isemgid - emergency - isemgevent
1000 - TRUE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3
See how 1001 is missing? Now say they alert 1002 (emergency2) I want it to look like this:
isemgid - emergency - isemgevent
1000 - TRUE - Emergency2
1001 - FALSE - Emergency1
1003 - FALSE - Emergency3
So that whatever is duplicate doesn't show.--Is this what you want?
select yt.isemgid, yt.emergency, yt.isemgevent
from YourTable yt
join (
select isemgid=max(isemgid)
from YourTable
group by isemgevent
) XXX on yt.isemgid=XXX.isemgid
order by yt.isemgevent
Distinct on single column?
Hi,
This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.
SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d
INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code
INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas
INNER JOIN EfasLive..part AS p ON p.code = sol.part
WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0
ORDER BY (p.code), datum DESC
The problem with this is that it returns multiple lines for every product (p.code). Like so:
code description price quantity total date
603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00
603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00
603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00
603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00
603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00
603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00
603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00
I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved
edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.
HelloJust a few ideas, do not have an MSSQL instace nearby to test:
1) use cursor, which I'd prefer to avoid
- declare cursor for "Select Distinct (code) From EfasLive..part"
2) do something like
Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)
The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)
3) try to use CTEs (Common Table Expressions)
Post the solution after you find one! Tnx
|||
Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me 
edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE 
I'll look into the pointer thing.
|||Here the query,
Code Snippet
;With CTE
as
(
SELECT DISTINCT
p.code
, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description
, sol.p_sales as price
, sol.q_ordered as quantity
, sol.p_sales * sol.q_ordered as total
, so.date_in as date
, max(so.date_in) over(partition by p.code) as maxdate
--, Row_Number() over(partition by p.code order by so.date_in desc) rid
FROM
EfasLive..debtor AS d
INNER JOIN Informatica..so AS so
ON so.deb_code = d.code
AND so.co_code = d.co_code
INNER JOIN Informatica..so_line AS sol
ON sol.code = so.code
AND sol.co_code = so.co_code
AND sol.acc_year = so.acc_year
AND sol.efas = so.efas
INNER JOIN EfasLive..part AS p
ON p.code = sol.part
WHERE
d.[grp{003}] = 'GROUP'
AND p.co_code = 1
AND p.code NOT LIKE '&%'
AND so.date_in > DATEADD(m,-3,GETDATE())
AND sol.q_ordered > 0
)
Select
code
, description
, price
, quantity
, total
, date
From
CTE
Where
date = maxdate
--rid=1
Wednesday, March 21, 2012
Displays commas in type FLOAT
I'm writing a SQL script. I'd like to display the value I have in a
column of type FLOAT so that it appears with the commas in the correct
place. It displays now as 10000000.0; I'd like it to display as
10,000,000. Thanks for any help."Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
Your front-end application should really be doing this type of work, not the
database itself.
Rick Sawtell
MCT, MCSD, MCDBA|||Formatting should be done client side, not in SQL.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.|||Do this in the reporting tool / client application, or cast the value to
money (not good) and then use function "convert" to cast it to varchar with
style 1.
Example:
use northwind
go
select
convert(varchar(25), cast(cast(orderid as float) as money), 1)
from
dbo.orders
AMB
"Rick Charnes" wrote:
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
>
column of type FLOAT so that it appears with the commas in the correct
place. It displays now as 10000000.0; I'd like it to display as
10,000,000. Thanks for any help."Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
Your front-end application should really be doing this type of work, not the
database itself.
Rick Sawtell
MCT, MCSD, MCDBA|||Formatting should be done client side, not in SQL.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.|||Do this in the reporting tool / client application, or cast the value to
money (not good) and then use function "convert" to cast it to varchar with
style 1.
Example:
use northwind
go
select
convert(varchar(25), cast(cast(orderid as float) as money), 1)
from
dbo.orders
AMB
"Rick Charnes" wrote:
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
>
displaying xml using reporting service web service
I'm using VS2005 web developer. I can't display XML results when using the
render method, although it renders fine.
I'm using the SQL Server 2000 reporting services web service render method
to get a byte array like this:
result = rs.Render(reportPath, format, historyID, devInfo,
parameters, _
credentials, showHideToggle, encoding, mimeType,
reportHistoryParameters, warnings, streamIDs)
This is from the sample at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
It works great if I assign HTML4.0 and EXCEL to the "format" variable.
Then I put
response.binarywrite(result)
and it is just fine.
When I try this with the XML format, it creates a file perfectly if I put
Dim stream As FileStream = File.Create("report.xml", result.Length)
but I don't know how to display the xml in a browser.
Response.binarywrite(result) and Response.writefile("report.xml") gives me
an error
The XML page cannot be displayed.
...
Cannot have a DOCTYPE declaration outside of a prolog
I guess because the XML is written inside the html.
I would appreciate any suggestions.
Thanks
BillCorrection - response.writefile(filename) works, but I don't want to have to
create files on the server and clean them up all the time.
Thanks
Bill
"bill" <belgie@.datamti.com> wrote in message
news:ubuEOCnMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> I'm using VS2005 web developer. I can't display XML results when using
> the render method, although it renders fine.
> I'm using the SQL Server 2000 reporting services web service render method
> to get a byte array like this:
> result = rs.Render(reportPath, format, historyID, devInfo,
> parameters, _
> credentials, showHideToggle, encoding, mimeType,
> reportHistoryParameters, warnings, streamIDs)
> This is from the sample at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
> It works great if I assign HTML4.0 and EXCEL to the "format" variable.
> Then I put
> response.binarywrite(result)
> and it is just fine.
> When I try this with the XML format, it creates a file perfectly if I put
> Dim stream As FileStream = File.Create("report.xml", result.Length)
> but I don't know how to display the xml in a browser.
> Response.binarywrite(result) and Response.writefile("report.xml") gives
> me an error
> The XML page cannot be displayed.
> ...
> Cannot have a DOCTYPE declaration outside of a prolog
> I guess because the XML is written inside the html.
> I would appreciate any suggestions.
> Thanks
> Bill
>
render method, although it renders fine.
I'm using the SQL Server 2000 reporting services web service render method
to get a byte array like this:
result = rs.Render(reportPath, format, historyID, devInfo,
parameters, _
credentials, showHideToggle, encoding, mimeType,
reportHistoryParameters, warnings, streamIDs)
This is from the sample at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
It works great if I assign HTML4.0 and EXCEL to the "format" variable.
Then I put
response.binarywrite(result)
and it is just fine.
When I try this with the XML format, it creates a file perfectly if I put
Dim stream As FileStream = File.Create("report.xml", result.Length)
but I don't know how to display the xml in a browser.
Response.binarywrite(result) and Response.writefile("report.xml") gives me
an error
The XML page cannot be displayed.
...
Cannot have a DOCTYPE declaration outside of a prolog
I guess because the XML is written inside the html.
I would appreciate any suggestions.
Thanks
BillCorrection - response.writefile(filename) works, but I don't want to have to
create files on the server and clean them up all the time.
Thanks
Bill
"bill" <belgie@.datamti.com> wrote in message
news:ubuEOCnMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> I'm using VS2005 web developer. I can't display XML results when using
> the render method, although it renders fine.
> I'm using the SQL Server 2000 reporting services web service render method
> to get a byte array like this:
> result = rs.Render(reportPath, format, historyID, devInfo,
> parameters, _
> credentials, showHideToggle, encoding, mimeType,
> reportHistoryParameters, warnings, streamIDs)
> This is from the sample at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
> It works great if I assign HTML4.0 and EXCEL to the "format" variable.
> Then I put
> response.binarywrite(result)
> and it is just fine.
> When I try this with the XML format, it creates a file perfectly if I put
> Dim stream As FileStream = File.Create("report.xml", result.Length)
> but I don't know how to display the xml in a browser.
> Response.binarywrite(result) and Response.writefile("report.xml") gives
> me an error
> The XML page cannot be displayed.
> ...
> Cannot have a DOCTYPE declaration outside of a prolog
> I guess because the XML is written inside the html.
> I would appreciate any suggestions.
> Thanks
> Bill
>
Displaying XML Data
There is a requirement that I need to display the XML data stored in
database in one of the report. How can I display this data in Reporting
Services with the color schema similar to IE (elements and attributes in
different colors etc..)?
Thanks,
Live_Love_LaughThe displaying the XML data itself is not an issue. You can pass it to a
custom function (preferebaly located in an external assembly) and apply a XSL
transformation when the report is processed. What makes your task difficult
is the color-coding. Unfortunately, version 1.0 doesn't support HTML markers,
e.g. <b> for bold, <font> etc. To make the task even more difficult textboxes
are rendered as table cells and don't have ids which makes it difficult to
reference them by DHTML.
One thing you can try is exporting the report as XML and associating an XSL
stylesheet in the DataOutput properties which will render the report the way
you want it.
"Live_Love_Laugh" wrote:
> There is a requirement that I need to display the XML data stored in
> database in one of the report. How can I display this data in Reporting
> Services with the color schema similar to IE (elements and attributes in
> different colors etc..)?
> Thanks,
> Live_Love_Laugh
>
>|||I have the same requirement
Did either of you succeed?
If so can you tell me what you did or post an example?
Thanks in advance
"Teo Lachev" wrote:
> The displaying the XML data itself is not an issue. etc
> "Live_Love_Laugh" wrote:
> > There is a requirement that I need to display the XML data stored in
> > database in one of the report. How can I display this data in Reporting
> > Services with the color schema similar to IE (elements and attributes in
> > different colors etc..)?
> >
> > Thanks,
> > Live_Love_Laughsql
database in one of the report. How can I display this data in Reporting
Services with the color schema similar to IE (elements and attributes in
different colors etc..)?
Thanks,
Live_Love_LaughThe displaying the XML data itself is not an issue. You can pass it to a
custom function (preferebaly located in an external assembly) and apply a XSL
transformation when the report is processed. What makes your task difficult
is the color-coding. Unfortunately, version 1.0 doesn't support HTML markers,
e.g. <b> for bold, <font> etc. To make the task even more difficult textboxes
are rendered as table cells and don't have ids which makes it difficult to
reference them by DHTML.
One thing you can try is exporting the report as XML and associating an XSL
stylesheet in the DataOutput properties which will render the report the way
you want it.
"Live_Love_Laugh" wrote:
> There is a requirement that I need to display the XML data stored in
> database in one of the report. How can I display this data in Reporting
> Services with the color schema similar to IE (elements and attributes in
> different colors etc..)?
> Thanks,
> Live_Love_Laugh
>
>|||I have the same requirement
Did either of you succeed?
If so can you tell me what you did or post an example?
Thanks in advance
"Teo Lachev" wrote:
> The displaying the XML data itself is not an issue. etc
> "Live_Love_Laugh" wrote:
> > There is a requirement that I need to display the XML data stored in
> > database in one of the report. How can I display this data in Reporting
> > Services with the color schema similar to IE (elements and attributes in
> > different colors etc..)?
> >
> > Thanks,
> > Live_Love_Laughsql
displaying vertical text in details section using sql reporting service
Nico,
U can change the "WritingMode" property of text box to set display
vertically.
I have another problem with the same, i want to display a text in the
details section spanning multiple rows.
below is example
S_NO NAME VERTICAL_TEXT
_______________________________
1 ABC S
2 CDE A
3 FGH M
4 IJK P
L
E
Appreciate any help!!..
VenkatCan anyone help on this?..
venkat.oar@.gmail.com wrote:
> Nico,
> U can change the "WritingMode" property of text box to set display
> vertically.
> I have another problem with the same, i want to display a text in the
> details section spanning multiple rows.
> below is example
> S_NO NAME VERTICAL_TEXT
> _______________________________
> 1 ABC S
> 2 CDE A
> 3 FGH M
> 4 IJK P
> L
> E
> Appreciate any help!!..
> Venkat
U can change the "WritingMode" property of text box to set display
vertically.
I have another problem with the same, i want to display a text in the
details section spanning multiple rows.
below is example
S_NO NAME VERTICAL_TEXT
_______________________________
1 ABC S
2 CDE A
3 FGH M
4 IJK P
L
E
Appreciate any help!!..
VenkatCan anyone help on this?..
venkat.oar@.gmail.com wrote:
> Nico,
> U can change the "WritingMode" property of text box to set display
> vertically.
> I have another problem with the same, i want to display a text in the
> details section spanning multiple rows.
> below is example
> S_NO NAME VERTICAL_TEXT
> _______________________________
> 1 ABC S
> 2 CDE A
> 3 FGH M
> 4 IJK P
> L
> E
> Appreciate any help!!..
> Venkat
Displaying various Time Intelligence calculations at the same time
I created a new calculation for a measure to display Year over Year growth % using the business intelligence wizard which created new calculated members in my time dimension. How can I display the new calculation and the original value at the same time without these two options being columns or rows? For example:
2005
Sales Sales Year over Year Growth %
Product A $23 1%
Product B $45 15%
I have tried to do this using perspectives but these are not included in the 'Standard Edition' SKU. Thanks in advance.
Here is an example that should help you (from Foodmart 2000)
with
member [Measures].[Prev Sales] as '([Measures].[Unit Sales],[Time].currentmember.lag(1))'
member [Measures].[% Increase] as 'iif([Measures].[Prev Sales]<>0,100*([Measures].[Unit Sales]-[Measures].[Prev Sales]) / [Measures].[Prev Sales],0)'
select crossjoin({[Time].[1997].[Q3],[Time].[1997].[Q4]},{[Measures].[Unit Sales],[Measures].[% Increase]}) on columns,
{[Product].[Product Name].members} on rows
from sales
Hope this helps,
Santi
Displaying various instances of the same report in one report
Here's my problem. I have a report that displays information for one
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql
Displaying values in list from Left-Right instead of Right-Left
Hi,
I want to display values in a listbox from left to right instead of top to bottom.
Instead of displaying values like this
D001
D002
D003
D004
..
..
..
I should display
D001 D002 D003 D004 D005 D001 D002 D003 D004 D005 D001 D002 D003 D004 D005
D001 D002 D003 D004 D005 D001 D002 D003 D004 D005 D001 D002 D003 D004 D005
D001 D002 D003 D004 D005 D001 D002 D003
I also should restrict the length of the listbox. Maximum of 20 values only should displayed per row.
Is there any way to acheive this ? Either using Table or Matrix dataregion ? Or concatinating string in a textbox ?
Regards,
I have done something similar for a mailing label report. i had to create a list box for each section LTR then when my data is returned i determine which list to put it into based off the record number.
Example: My report has 3 list controls placed next to each other.
Record Number Data
1 Blah 1
2 Blah 2
3 Blah 3
4 Blah 4
5 Blah 5
... ...
Record number 1 display in Fist list control, record number 2 displays in second list control, record number 3 displays in third list control, record number 4 display as second record in first list control, record number 5 displays as second record in second list control, and etc.
Eample Filter on List Control: =Code.SetColumn(CLng(Fields!ColumnFilter.Value),3) = 2
SetColumn Code:
Shared Function SetColumn(ByVal row As Integer, ByVal ColumnCount As Integer) As String
Dim RetVal As Integer = row
While RetVal > ColumnCount
If ColumnCount >= RetVal Then Exit While
RetVal = RetVal - ColumnCount
End While
Return RetVal
End Function
Also to return the sequential number with your data set look into SQL 2005 Ranking functions.
|||Hi,
I was able to acheive this as follows -
1. Add a Matrix control. Add required field in column grouping of matrix. (Dynamic columns)
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression =RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2 group expressions. (1 for Phone # and other for controling no. of columns).
Regards,
Chiro
I dont understand how you can have the expression in step 3 -
=RowNumber(Nothing) / 15
|||I think the technique is similar to the HorizontalTables example in Chric Hays Sleazy Hacks Blog. There's a working example you can download from http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspxDisplaying value of a variable during runtime
Greetings all,
Apologies if this question has been asked in the past but how I display the valuw of a variable during runtime?
Thanks for your help in advance.
Running in debugmode; you can set a break point and when the excution breaks; you can go to the locals window (Ctrl + Alt + V, L)to see the value of the variables at that point|||Dear Rafael, I am fallin in love with you 
dreameR.78 wrote:
Dear Rafael, I am fallin in love with you
Glad you got it!
Displaying used space in Q.A.
I would like to get information about percentage used space for data files and log files, like I can display it in Enterprise Mgr's TaskPad, through Query Analyzer. The 'sysfiles' system table only contains allocated space, not the % used.
Does anyone know if this info is available this way?To my knowledge the % of space used is not stored in any tables. As I am sure you know DBCC SQLPERF(LOGSPACE) will give you the answer BUT for ALL logs. You could always roll your own custom answer:
Code:
---------------------------------------
create table #Tmp(DB varchar(255), LogSize varchar(25), SpaceUSed varchar(25), Status tinyint)
declare @.DBLen int, @.LogSizeLen int, @.SpaceUsedLen int, @.StatusLen int, @.TSQL varchar(255)
insert into #Tmp exec('DBCC SQLPERF(LOGSPACE)')
select @.DBLen = max(datalength(DB)), @.LogSizeLen = max(datalength(LogSize)), @.SpaceUSedLen = max(datalength(SpaceUSed)), @.StatusLen = max(datalength(Status)) From #Tmp where DB = db_name()
set @.TSQL = 'select ' +
'cast(DB as varchar( ' + cast(@.DBLen as varchar(12)) + ')) as ''Database Name'', ' +
'cast(LogSize as varchar( ' + cast(@.LogSizeLen as varchar(12)) + ')) as ''Log Size (MB)'', ' +
'cast(SpaceUsed as varchar( ' + cast(@.SpaceUsedLen as varchar(12)) + ')) as ''Log Space Used (%)'', ' +
'cast(Status as varchar( ' + cast(@.StatusLen as varchar(12)) + ')) as ''Status'' ' +
'From #Tmp where DB = db_name()'
raiserror('',0,1)
exec(@.TSQL)
drop table #Tmp
---------------------------------------|||Off topic, but you can use the 'code' tag when posting code i.e
This is my code
with some indentation
and other stuff ;)
so the format
still looks nice :D|||Thank you very much. I was browsing through BOL yesterday but I missed DBCC SQLPERF for some reason.
I guess I can get the same info for database files through sp_spaceused, which I now stumbled over in BOL.
I tested running DBCC UPDATEUSAGE on some databases, which gave some corrections. Are these "problems" in sysindexes never corrected unless I explicitly run a DBCC UPDATEUSAGE manually or in a maintenance plan?
Sigh... when the guys you've outsourced the operation & maintenance to can't supervise things, you've got to do it yourself ;-)|||I run a maintaince plan every Saturday and Wednesday to re-calculate statistics, rebuild indexes, and force a recompile of all stored procedures. I am lucky to have a window twice a week where I can do these things and IMHO the 2 hours it takes to do this is time well spent.
Here is another tip. If you find something in EM that you like but don't know how it works try running the profiler and watch the commands issued.|||About sp_spaceusage, which is the most important detail to keep an eye on of Unallocated Space and Unused Space?
Further, for one database I got a negative value for Unallocated Space. What does this mean?
Does anyone know if this info is available this way?To my knowledge the % of space used is not stored in any tables. As I am sure you know DBCC SQLPERF(LOGSPACE) will give you the answer BUT for ALL logs. You could always roll your own custom answer:
Code:
---------------------------------------
create table #Tmp(DB varchar(255), LogSize varchar(25), SpaceUSed varchar(25), Status tinyint)
declare @.DBLen int, @.LogSizeLen int, @.SpaceUsedLen int, @.StatusLen int, @.TSQL varchar(255)
insert into #Tmp exec('DBCC SQLPERF(LOGSPACE)')
select @.DBLen = max(datalength(DB)), @.LogSizeLen = max(datalength(LogSize)), @.SpaceUSedLen = max(datalength(SpaceUSed)), @.StatusLen = max(datalength(Status)) From #Tmp where DB = db_name()
set @.TSQL = 'select ' +
'cast(DB as varchar( ' + cast(@.DBLen as varchar(12)) + ')) as ''Database Name'', ' +
'cast(LogSize as varchar( ' + cast(@.LogSizeLen as varchar(12)) + ')) as ''Log Size (MB)'', ' +
'cast(SpaceUsed as varchar( ' + cast(@.SpaceUsedLen as varchar(12)) + ')) as ''Log Space Used (%)'', ' +
'cast(Status as varchar( ' + cast(@.StatusLen as varchar(12)) + ')) as ''Status'' ' +
'From #Tmp where DB = db_name()'
raiserror('',0,1)
exec(@.TSQL)
drop table #Tmp
---------------------------------------|||Off topic, but you can use the 'code' tag when posting code i.e
This is my code
with some indentation
and other stuff ;)
so the format
still looks nice :D|||Thank you very much. I was browsing through BOL yesterday but I missed DBCC SQLPERF for some reason.
I guess I can get the same info for database files through sp_spaceused, which I now stumbled over in BOL.
I tested running DBCC UPDATEUSAGE on some databases, which gave some corrections. Are these "problems" in sysindexes never corrected unless I explicitly run a DBCC UPDATEUSAGE manually or in a maintenance plan?
Sigh... when the guys you've outsourced the operation & maintenance to can't supervise things, you've got to do it yourself ;-)|||I run a maintaince plan every Saturday and Wednesday to re-calculate statistics, rebuild indexes, and force a recompile of all stored procedures. I am lucky to have a window twice a week where I can do these things and IMHO the 2 hours it takes to do this is time well spent.
Here is another tip. If you find something in EM that you like but don't know how it works try running the profiler and watch the commands issued.|||About sp_spaceusage, which is the most important detail to keep an eye on of Unallocated Space and Unused Space?
Further, for one database I got a negative value for Unallocated Space. What does this mean?
Displaying traffic lighting images using MDX query
Hi All,
I want to display traffic light images in jpivot table depending upon the measures . I can be able to display color in the jpivot table. How to display images?
Thanks in advance.
Hi Rajha,
you have to create a KPI in your cube.
Edit your cube in BIDS and then go to KPI's tab. Create a new KPI and complete the form with MDX script you need for Value, Goal, Status and Trend. There you can also set Status indicator e Trend indicator (traffic light, gauge, standard arrow and so on).
|||Hi Francesco,
Thank you very much for your help. I'm a newbie to mdx. Actually I am using some open source database and cube designer which doesnt have KPI property. Can I manually add that code to my mdx query? If so, can u tell me where I can found related documents.
|||Hi Rajha,
I really don't know if there's a way to obtain the same in MDX but I'm thinking not.
What you can easily do is to color cell depending on the value in it like this:
WITH MEMBER MEASURES.DEMO AS
'[Measures].[Internet Sales Amount]' ,
FORE_COLOR = 'IIF ( MEASURES.DEMO < [Measures].[Reseller Sales Amount] * 0.7,
RGB(0,255,0),
RGB(255,0,0)
)'
SELECT
{MEASURES.DEMO} ON COLUMNS,
NONEMPTYCROSSJOIN([Date].[Calendar].[Month].Members,[Department].[Departments].[Department Level 02].MEMBERS) ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR
At the moment I'm not able to find documentation about this but you can check on the web
Let me know if it's what you're looking for and please check my answer if you think if it was helpful anyway.
P.S. what product are you using that supports MDX?
Francesco
|||
Hi Francesco
Thanks for your reply. As I said in my first thread I can be able to show color depending upon the conditions but i cant bring images in the table.
I'm evaluating Pentaho which uses mdx for drill down reports.
Monday, March 19, 2012
Displaying Total number of Rows in a Report in Page Header.
Hi,
I have requirement to display Total number of Rows in a Report in Page Header.
I have written the following code in Page header it shows RowCount for the Page only.
=Count(ReportItems!textboxInTableCell.Value)
Can anyone please help on this?
Regards
Raghav
By Total number of reports in the report do you mean the number of rows returned by the Dataset query? If so, add a textbox in your Report Body with the expression =CountRows("DataSet1") with the name of your Dataset in place of DataSet1.
Then refer to this textbox directly in the Page Header.
This should give you the total row count for your Dataset.
-Aayush
|||Thanks aayush,
I used =CountRows() in body header and set the RepeatWith property to "tableName" and it works as Page header.
Regards
Raghavendra
Displaying the time in hh:mm PM
Hi all
How display the time in requried format
example acutal time is 3:15:23PM
requried format is 3:15Pm how to display in that format please give the reply for our requrimentin formula field :
currenttime - second (currenttime)
How display the time in requried format
example acutal time is 3:15:23PM
requried format is 3:15Pm how to display in that format please give the reply for our requrimentin formula field :
currenttime - second (currenttime)
Displaying the table owner
Hi,
How can I display the table owner and the table name in
the form of 'tableowner.tablename' from the 'sysobjects'
table. Another words, I want to add the table
owner 'sqlapp' to the table names in my select query.
Thanks for help.You'd be better off using information_Schema views.. try this
select table_schema + '.' + table_name from information_Schema.tables
where table_type = 'base table'
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
> Hi,
> How can I display the table owner and the table name in
> the form of 'tableowner.tablename' from the 'sysobjects'
> table. Another words, I want to add the table
> owner 'sqlapp' to the table names in my select query.
> Thanks for help.|||I found it Thanks...This was what I needed...
select 'sqlapp.' + name from sysobjects
where xtype = 'U' and name <> 'dt_properties'
>--Original Message--
>You'd be better off using information_Schema views.. try
this
>select table_schema + '.' + table_name from
information_Schema.tables
>where table_type = 'base table'
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
>> Hi,
>> How can I display the table owner and the table name in
>> the form of 'tableowner.tablename' from the 'sysobjects'
>> table. Another words, I want to add the table
>> owner 'sqlapp' to the table names in my select query.
>> Thanks for help.
>
>.
>
How can I display the table owner and the table name in
the form of 'tableowner.tablename' from the 'sysobjects'
table. Another words, I want to add the table
owner 'sqlapp' to the table names in my select query.
Thanks for help.You'd be better off using information_Schema views.. try this
select table_schema + '.' + table_name from information_Schema.tables
where table_type = 'base table'
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
> Hi,
> How can I display the table owner and the table name in
> the form of 'tableowner.tablename' from the 'sysobjects'
> table. Another words, I want to add the table
> owner 'sqlapp' to the table names in my select query.
> Thanks for help.|||I found it Thanks...This was what I needed...
select 'sqlapp.' + name from sysobjects
where xtype = 'U' and name <> 'dt_properties'
>--Original Message--
>You'd be better off using information_Schema views.. try
this
>select table_schema + '.' + table_name from
information_Schema.tables
>where table_type = 'base table'
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
>> Hi,
>> How can I display the table owner and the table name in
>> the form of 'tableowner.tablename' from the 'sysobjects'
>> table. Another words, I want to add the table
>> owner 'sqlapp' to the table names in my select query.
>> Thanks for help.
>
>.
>
Displaying the table owner
Hi,
How can I display the table owner and the table name in
the form of 'tableowner.tablename' from the 'sysobjects'
table. Another words, I want to add the table
owner 'sqlapp' to the table names in my select query.
Thanks for help.You'd be better off using information_Schema views.. try this
select table_schema + '.' + table_name from information_Schema.tables
where table_type = 'base table'
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
select 'sqlapp.' + name from sysobjects
where xtype = 'U' and name <> 'dt_properties'
this
information_Schema.tables
message
How can I display the table owner and the table name in
the form of 'tableowner.tablename' from the 'sysobjects'
table. Another words, I want to add the table
owner 'sqlapp' to the table names in my select query.
Thanks for help.You'd be better off using information_Schema views.. try this
select table_schema + '.' + table_name from information_Schema.tables
where table_type = 'base table'
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
quote:|||I found it Thanks...This was what I needed...
> Hi,
> How can I display the table owner and the table name in
> the form of 'tableowner.tablename' from the 'sysobjects'
> table. Another words, I want to add the table
> owner 'sqlapp' to the table names in my select query.
> Thanks for help.
select 'sqlapp.' + name from sysobjects
where xtype = 'U' and name <> 'dt_properties'
quote:
>--Original Message--
>You'd be better off using information_Schema views.. try
this
quote:
>select table_schema + '.' + table_name from
information_Schema.tables
quote:
>where table_type = 'base table'
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
quote:
>news:89e501c3e9be$3a804a10$a001280a@.phx.gbl...
>
>.
>
Displaying the row number in a query
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.Is it not doable?
Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.
The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.
Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.
blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.Is it not doable?
Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.
The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.
Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.
blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.
Displaying sub report header when part of main report.
Does anyone know a way to display the header of a sub report when the sub report is part of a main report? Im able to get the main report and the sub report to display properly, but the header of the previously developed/tested sub report will not display when embedded in a main report.
Thanks,
MP
I'm afraid this is a feature. A report can only have one header/footer and the Main report takes precedence. To display, you'll need to move the components into the body of the subreport to have them displaying in the main report.displaying some predefined no. of rows
hi,
i wanted to know if there is any sql statement that enables the query to display say first '10' rows only.
Eg: suppose
SELECT * from Location;
returns 25 rows and i want only 10 rows in that to be displayed.
How can this be done? Help please.
You can use TOP statement like
SELECT TOP 10 * from Location
Is good to include order by for example table identity column if you would like to receive last inserted records
SELECT TOP 10 *
from Location
order by loc_ID desc
Subscribe to:
Posts (Atom)