DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

<the rest of your current query>

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number

) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

distinct query & a union of some kind?

Hi all,

I need assistance (obviously) with two issues. I apologize for the length.

FIRST ISSUE
I wrote a distinct query (which depending on WHERE minimizes over 15700 rows to a few hundred or less depending on SELECT). I pasted an example of it below.

select distinct pr.host ||'|'|| PR.PRO_EN ||'|'|| PR.PRO_LNAME ||'|'|| PR.PRO_FNAME ||'|'|| PR.PRO_CLASS ||'|'|| PR.PRO_SPEC_DESC ||'|'|| PR.PRO_SPEC_CD||'|'|| PR.PRO_DEA ||'|'|| PR.PRO_LIC ||'|'|| PR.PRO_TAXON ||'|'||
substr(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) ||'|'||
substr(record_image,INSTR(record_image,'^',1,15)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,15) - 1)) ||'|'||
LE.error_code
FROM LOAD_ERRORS LE, I_PRO_TEMP PR
WHERE LE.FILE_TYPE = 'FILE' AND
LE.FILE_NAME LIKE 'test_1234_5678_FILE_2004%' AND
LE.ERROR_CODE = '200' AND
Pr.host='1234' and
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);

I do need for all of the SELECT criteria below to be DISTINCT. However, I also need to include PP.PRO_ID from another table called PP.PRO_PERM. However, if I include PP.PRO_ID in the DISTINCT query, instead of getting a few hunred rows, I would only get about 20 rows (which as you know, are the rows that have PRO_ID filled in/present).

Many of the few hundred rows (in the query below) wont have a PRO_ID (if a row has one, it means the data made it to the permanent table - PRO.PERM). Is there a way to get what I want 15700 rows reduced to a few hundred rows that include and exclue PRO_ID? I tried one or two other things in the WHERE but, obviously it didnt work for me.

==================================================

SECOND ISSUE
I need to expand the query above or just run a few separate queries with a slightly different SELECT AND WHERE. In looking at the query example above, the following line corresponds to person # 1:
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr.PRO_EN (+);
There could also be, if entered, a person #2 and a person #3.

What Ive been doing is running a query which includes the above (and not DISTINCT) for each person (of course for person #2 and #3 I would have to change the line above to correspond to person#2 and person#3). Also, within this particular query, Id ask for the people that person 1, 2, and 3 have serviced. I would then open up the text file for person #1, the file for person #2, and the file for person #3 in Excel. I would then copy data from person #2 andd #3 and insert it on the row next to person #1s data.

I think there has to be an easier way. Now, when I tried UNION (long before the modified query in issue one), I wouldnt get back the number of results that I shouldve gotten back. If I recall correctly, I only received info if person #1, person#2, and person #3 were available for within each row of data. However, I need to see all rows of data. Id like it to appear (all on one row) like:
person_serviced (by person 1, 2, 3) and person_serviced personal
data, person 1 and their data, person 2 and their data, and person 3
and their data.

Person 1, 2, and 3s personal data would be found in PRO_TEMP (if available) and hopefully PRO_PERM (if their data made it over to the permanent table). They all use PRO_EN for identification (other than their name).

What I need is a query that can give me what I all of this one one row. Is that possible?

and I apologize for the length of this.This looks like Oracle, right?

If so, I don't understand why you get less rows simply by including a column that may be null in the SELECT DISTINCT. Oracle considers NULL and the empty string '' to be equivalent, and concatenation of a NULL onto a string does not make the resulting string NULL:

SQL> select distinct ename||null from emp;

ENAME||NUL
----
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

Of course, if you are NOT using Oracle, then things probably will be different. If so, you need to use whatever function that DBMS provides to replace a NULL by a value (something like NVL or IFNULL or COALESCE):

.. || NVL(pp.pro_id,' ') || ...

Regarding your second issue, this is caused by poor database design - kludging several values (person 1, 2 and 3) into a single column. But anyway, can you not get all the required data at once by outer joining to PRO_TEMP 3 times like this:

SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr1.PRO_EN (+)
AND SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr2.PRO_EN (+)
AND SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr3.PRO_EN (+);

... making the necessary changes to the conditions for Pr2 and Pr3?|||Thanks for the reply. I apologize for not including that I have Oracle 81 and am using Sql*Plus.

REPLY TO FIRST ISSUE
I think I was a little burned out Friday. I looked at what I was doing and found that within my first issue, the PRO_ID wasnt really the problem as you figured. The problem is in the WHERE clause. To be exact its WHERE Pr.host='1234'. If I dont include this line but leave PRO_ID, it returns about 150 rows instead of 30 rows (if I include PR.HOST).

I need to include Pr.host='1234' so that I can make sure that the persons info is coming directly from 1234. Unfortunately, Jane Doe and Jonathan Doe can both have the same PRO_IEN of 8765 but each would have a different host. The same number can not appear under the same host number/ID. So, if I dont include host, the database can return a name and data from some other host number that is not 1234.

Do you know if there is a way to fix this? The (+) wont work if I put it next to Pr.host='1234'.

REPLY TO SECOND ISSUE
I tried the outer join option and that didn't work. It didn't return the data that I need (such as names and personal info from those on host 1234). Perhaps whats throwing it off is the fact that each persons (#1 and #2 and #3) identification number would be found in PRO_IEN. Perhaps I have to continue running separate queries (unless someone decides to redesign various tables and applications).

SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,18) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);|||1) Use: Pr.host='1234' (+)

2) You have used the same alias "Pr" in all 3 conditions. I used "Pr1", "Pr2" and "Pr3" - you need that same table 3 times in the FROM clause with 3 different aliases.
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr1.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr2.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,18) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr3.PRO_EN (+);|||When I try PR.HOST='1234' (+), I get the following error:
ERROR at line 17:
ORA-00933: SQL command not properly ended
The lines below are an example of my FROM.

FROM LOAD_ERRORS LE, I_PRO_TEMP PR
WHERE LE.FILE_TYPE = 'FILE' AND
LE.FILE_NAME LIKE 'test_1234_5678_FILE_2004%' AND
LE.ERROR_CODE = '200' AND
Pr.host='1234' (+) and
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);|||Actually, I made a slip there - the (+) goes on the other side:

PR.HOST (+) = '1234'

However, I don't know if that is the cause of your syntax error. It could be, I think.|||Thanks! It worked. :Dsql

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.

Displaying the Top N records count

Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj

The report looks something like this:

Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------

U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:

Total 10 15 25

I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.

Please give me the solution as soon as possibleOriginally posted by rajdotme
Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj

The report looks something like this:

Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------

U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:

Total 10 15 25

I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.

Please give me the solution as soon as possible

Hi,

Validate in Detail Format Section using with "RecordNumber" value is <= 5 (variable value).

yours friendly,
K.Babu|||Hi,
Thanks for the Reply. Could u please tell me the exact location where to change. I still couldnt find it.

Thanks and Regards,
Raj|||Hi rajdotme,
Use the formulae given below

Formula1 {@.Reset}
whileprintingrecords;
NumberVar x:=0;
NumberVar y:=0;
NumberVar z:=0;

Formula2 {@.Accepted}
whileprintingrecords;
Numbervar x:=x+{Table.Accepted}

Formula3 {@.Rejected}
whileprintingrecords;
Numbervar y:=y+{Table.Rejected}

Formula4 {@.Total}
whileprintingrecords;
Numbervar z:=z+{Table.Total}

Now, Place Formula1 in the group header and suppress it
Place other formulae in the group footer

Madhivanan

Displaying Dynamically created columns

Hello,
I have a stored procedure that accepts input parameters and depending
on the input, returns multiple columns. For example depending on the month
range passed in, the stored procedure creates and populates temporary table
with columns for each month and returns the values in the temporary table.
How do I display these dynamically created columns using Reporting Services
(the number of coulmns returned by the stored proc varies between different
runs). Any help is appreciated.Sunil,
You would need to use the matrix control and then edit the
matrix_columngroup to use the data returned from your stored proc.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Sunil Oliver" <SunilOliver@.discussions.microsoft.com> wrote in message
news:D11DBA4E-C99F-4E37-8C0D-7F9D76192001@.microsoft.com...
> Hello,
> I have a stored procedure that accepts input parameters and
depending
> on the input, returns multiple columns. For example depending on the month
> range passed in, the stored procedure creates and populates temporary
table
> with columns for each month and returns the values in the temporary table.
> How do I display these dynamically created columns using Reporting
Services
> (the number of coulmns returned by the stored proc varies between
different
> runs). Any help is appreciated.

displaying different reports based on a parameter

Hi,

I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.

Thanks a bunch.

add a subreport control and then set the sub report name using an expression

display values depending on a rule

hello,
i have a table with 3 fields
CustNr (int)
artikleNr (int)
pieces (int)
simple example - all customer have bueyed the article with the nr 11
101 11 8
102 11 3
101 11 4
102 11 20
103 11 3
104 11 15
104 11 25
i want to display a information in the following way
if customer has < 10 pieces display 0
if customer has 10 -20 display real value 1...20
if customer has more then 20 display 20
101 8 + 4 =12 display 12
102 3 + 20=23 display 20
103 3 display 0
104 15+25=40 display 20
thanksSomething like this?
SELECT CustNr,
'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
WHEN SUM(pieces) > 20 THEN 20
ELSE NULL END
FROM YourTable
WHERE artikleNr = 11 /* I don't know if this is a param that would limit
the data returned, or if you want to group by this column as well (in
addition to CustNr) */
Keith Kratochvil
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
> hello,
> i have a table with 3 fields
> CustNr (int)
> artikleNr (int)
> pieces (int)
> simple example - all customer have bueyed the article with the nr 11
> 101 11 8
> 102 11 3
> 101 11 4
> 102 11 20
> 103 11 3
> 104 11 15
> 104 11 25
> i want to display a information in the following way
> if customer has < 10 pieces display 0
> if customer has 10 -20 display real value 1...20
> if customer has more then 20 display 20
>
> 101 8 + 4 =12 display 12
> 102 3 + 20=23 display 20
> 103 3 display 0
> 104 15+25=40 display 20
> thanks|||thanks Keith, it works perfect.
best regards
"Keith Kratochvil" wrote:

> Something like this?
> SELECT CustNr,
> 'SomeColumn' = CASE WHEN SUM(pieces) < 10 THEN 0
> WHEN SUM(pieces) BETWEEN 10 AND 20 THEN SUM(pieces)
> WHEN SUM(pieces) > 20 THEN 20
> ELSE NULL END
> FROM YourTable
> WHERE artikleNr = 11 /* I don't know if this is a param that would limit
> the data returned, or if you want to group by this column as well (in
> addition to CustNr) */
> --
> Keith Kratochvil
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:6A10DB32-0D1E-4C48-B657-C5C83A78DDF7@.microsoft.com...
>
>

Display textboxes depending on rendering format

I would like to display some textboxes depending on the rendering format. I
would like to write an expression like this in the Visibility.Hidden
=renderingFormat="Excel"
Is this possible?In case you are calling Reporting Services from your
Application then just pass an extra parameter from the UI
which has the Rendering Format value and then use the
value of this Textbox in the Report to determine Rendering
Format.
>--Original Message--
>I would like to display some textboxes depending on the
rendering format. I
>would like to write an expression like this in the
Visibility.Hidden
>=renderingFormat="Excel"
>Is this possible?
>.
>|||Eric:
I don't see a solution posted, but I have a similar need. Did you ever come
up with an answer to this?
Vince P
"Eric Quist" wrote:
> Thanks for your suggestion, but I have to support it even if the user uses
> the HtmlViewer and chooses to export the report from there.
>
> "Ravi" wrote:
> > In case you are calling Reporting Services from your
> > Application then just pass an extra parameter from the UI
> > which has the Rendering Format value and then use the
> > value of this Textbox in the Report to determine Rendering
> > Format.
> >
> > >--Original Message--
> > >I would like to display some textboxes depending on the
> > rendering format. I
> > >would like to write an expression like this in the
> > Visibility.Hidden
> > >=renderingFormat="Excel"
> > >
> > >Is this possible?
> > >.
> > >
> >|||I haven't found any good solution to this problem. I guess it must be added
to RS.
/Eric
"vmp_pdx" wrote:
> Eric:
> I don't see a solution posted, but I have a similar need. Did you ever come
> up with an answer to this?
> Vince P
> "Eric Quist" wrote:
> > Thanks for your suggestion, but I have to support it even if the user uses
> > the HtmlViewer and chooses to export the report from there.
> >
> >
> > "Ravi" wrote:
> >
> > > In case you are calling Reporting Services from your
> > > Application then just pass an extra parameter from the UI
> > > which has the Rendering Format value and then use the
> > > value of this Textbox in the Report to determine Rendering
> > > Format.
> > >
> > > >--Original Message--
> > > >I would like to display some textboxes depending on the
> > > rendering format. I
> > > >would like to write an expression like this in the
> > > Visibility.Hidden
> > > >=renderingFormat="Excel"
> > > >
> > > >Is this possible?
> > > >.
> > > >
> > >

Display of message on page footer depending on field value

Hi
My requirement is like this, i need to display message on footer(table
footer ) depending upon the value of one of field column (LegendInd ,the
value will be 1 for legend and null for no legend)which varies for rows.I
want message at footer.I tried like this. I have table in which i display
field data.In that i added table footer and in table footer i added following
expression. =iif((Fields!LegendInd.Value =1),"my message","").Despite of
having the value 1 for this field , the message never gets printed.
Any suggestions?try putting =Fields!LegendInd.Value in the footer and see what it prints.
Also you could try =First(Fields!LegendInd.Value)
If that prints fine then your message should print
"Aniruddha" wrote:
> Hi
> My requirement is like this, i need to display message on footer(table
> footer ) depending upon the value of one of field column (LegendInd ,the
> value will be 1 for legend and null for no legend)which varies for rows.I
> want message at footer.I tried like this. I have table in which i display
> field data.In that i added table footer and in table footer i added following
> expression. =iif((Fields!LegendInd.Value =1),"my message","").Despite of
> having the value 1 for this field , the message never gets printed.
> Any suggestions?|||Thanks for reply Ram
i tried both approaches,but still it is not displaying the value.
I checked from database side,it does bring LegendInd=1 value.
"vRam" wrote:
> try putting =Fields!LegendInd.Value in the footer and see what it prints.
> Also you could try =First(Fields!LegendInd.Value)
> If that prints fine then your message should print
> "Aniruddha" wrote:
> > Hi
> > My requirement is like this, i need to display message on footer(table
> > footer ) depending upon the value of one of field column (LegendInd ,the
> > value will be 1 for legend and null for no legend)which varies for rows.I
> > want message at footer.I tried like this. I have table in which i display
> > field data.In that i added table footer and in table footer i added following
> > expression. =iif((Fields!LegendInd.Value =1),"my message","").Despite of
> > having the value 1 for this field , the message never gets printed.
> > Any suggestions?

Display DB Records In Label?

A SQL Server 2005 stored procedure expects a parameterUserID depending upon which it retrieves the no. of records & OrderIDs corresponding to theUserID from a DB table (note thatOrderID &UserID are two of the columns in the DB table). So for e.g. consider a user whoseUserID=6 & the DB table has 3 records whereUserID=6. In other words, there are 3OrderID records of the user whoseUserID=6, say,OrderID=8,OrderID=17 &OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 forUserID=6) & the OrderID (which will be 8, 17 & 29 forUserID=6). This is the stored procedure:

ALTER PROCEDURE dbo.OrderCount
@.UserID int
AS
DECLARE
@.OrderCount int

SET @.OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @.UserID)

SELECT @.OrderCount AS OrderCount, OrderID
FROM
NETOrders
WHERE
UserID = @.UserID

In a VB class file, I am invoking the stored procedure in a function namedGetOrderCount which returns aSqlDataReader back to the calling ASPX page. I want the ASPX page to display the 3 OrderIDs ofUserID=6 in aLabel control. Unlike the DataBinding controls likeDataList,DataGrid,Repeater controls, theLabel control will not automatically loop through the recordset.

So I tried to accomplish this using aFor...Next loop

Dim i As Integer
Dim sqlReader As SqlDataReader

iUserID = Request.Cookies("UserID").Value
'ShopCart is the name of the class in the VB class file
boShopCart = New ShopCart
sqlReader = boShopCart.GetOrderCount(iUserID)

While (sqlReader.Read)
If (sqlReader.GetValue(0) > 1) Then
pnlLinks.Visible = True
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i
Else
pnlLinks.Visible = False
End If
End While

But this generates the following error:

No default member found for type 'Integer'.

pointing to the line

lblLinks.Text = sqlReader.GetValue(1)(i)

in the above shown ASPX code. Can someone please correct me & suggest how do I loop through the recordset so that I can display the records in aLabel control?

Hi,

Replace these lines:
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i

with this line where your column id is 1:
lblLinks.Text &= sqlReader.GetValue(1)

|||

RN5A:

A SQL Server 2005 stored procedure expects a parameterUserID depending upon which it retrieves the no. of records & OrderIDs corresponding to theUserID from a DB table (note thatOrderID &UserID are two of the columns in the DB table). So for e.g. consider a user whoseUserID=6 & the DB table has 3 records whereUserID=6. In other words, there are 3OrderID records of the user whoseUserID=6, say,OrderID=8,OrderID=17 &OrderID=29. The stored procedure will finally return 2 columns - the OrderCount (which is 3 forUserID=6) & the OrderID (which will be 8, 17 & 29 forUserID=6). This is the stored procedure:

ALTER PROCEDURE dbo.OrderCount
@.UserID int
AS
DECLARE
@.OrderCount int

SET @.OrderCount = (SELECT COUNT(OrderID) FROM NETOrders WHERE UserID= @.UserID)

SELECT @.OrderCount AS OrderCount, OrderID
FROM
NETOrders
WHERE
UserID = @.UserID

Dim i As Integer
Dim sqlReader As SqlDataReader

iUserID = Request.Cookies("UserID").Value
'ShopCart is the name of the class in the VB class file
boShopCart = New ShopCart
sqlReader = boShopCart.GetOrderCount(iUserID)

While (sqlReader.Read)
If (sqlReader.GetValue(0) > 1) Then
pnlLinks.Visible = True
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text = sqlReader.GetValue(1)(i)
Next i
Else
pnlLinks.Visible = False
End If
End While

If I understand your issue correctly, the stored procedure will return several rows with 2 columns--the first column is identical for all rows, and the number of returned rows (let's say N) is determined by the qualified records, so there are N OrderIDs. Then when using SqlDataReader, you only need a single loop to go through all records, no matter what's the value of OrderCount; and you can just use SqlDataReader.GetValue(1) to access the OrderID field in each returned row, not SqlDataReader.GetValue(1)(i). So the OrderCount column seems to be redundant in this case.

|||Well mates......I am sorry to say that I forgot to mention one very important point in post #1. I want to display the 3OrderIDs ofUserID=6 in theLabel control but theText of theLabel should not be theOrderIDs themselves. In other words, theLabel should not display

8 17 29

which are the 3OrderIDs ofUserID=6. Rather theText of theLabel, which will reflect how many orders a user has placed, should start from 1 & increment by 1 till the total number of orders a user has placed. For e.g. sinceUserID=6 has 3OrderIDs, it means thatUserID=6 has placed 3 orders - theOrderID of the first order being 8, theOrderID of the second order being 17 & theOrderID of the third order being 29. Hence theLabel control should display

1 2 3

toUserID=6 since he has placed 3 orders. Similarly, if a user has placed, say, 8 orders, theLabel should display

1 2 3 4 5 6 7 8

& not theOrderIDs of the 8 orders. That's precisely the reason why I used theFor..Next loop in the code shown in post #1 but the problem is since theFor..Next loop has to be within aWhile loop to read theSqlDataReader something like this:

While (sqlReader.Read)
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text += i & " "
Next
End While

theLabel control displays the value ofi more than once since its looping inside a loop. So forUserID=6, theLabel control displays the followingText:

1 2 3 1 2 3 1 2 3

Any idea how do I resolve this problem or any other suggestiona?|||

Hi,

You can try doing this:
Declare a counter variable before your while loop
dim counter as integer = 1

Replace these lines:
For i = 1 To sqlReader.GetValue(0)
lblLinks.Text += i & " "
Next

with these line
lblLinks.Text &= counter & " "
counter++

|||Thanks a lot, Anjin....that's exactly what I was looking out for. It was really stupid of me to ask the follow-up question which you answered. I should have done that myself but the day I was working on it, it was one of those days when the brain just refuses to work!!|||

hi,

not to argue with, though your code will work fine but still better would be to user stringbuilder and append text to it rather using string i.e. lbl.text +="";

like

StringBuilder sb = new StringBuilder();

sb.append(whatever you like);

appending to stringbuilder has performance advantage.

regards,

satish

|||Thanks, Satish, for the alternative but simply using

sb.Append(i)

won't display anything on the ASPX page for the users to view. To display the output produced by using theAppend method of theStringBuilderclass, something like

lblLinks.Text += sb.Append(i)

has to be used, isn't it? So why unnecessarily useStringBuilder as shown in the preceding line when the following line will generate exactly the same output?

lblLinks.Text += i

So I doubt whether using StringBuilder (atleast in my case) will indeed have any performance advantage. Correct me if I am wrong.|||

errrr

sorry mate

first you keep appending the text/values etc in stringbuilder in loop or whatever, then after loop finishes use

lable.text = sb.ToString();

regards,

satish