please help me fix this SQL query

SELECT * FROM

(SELECT row_number() OVER (ORDER BY custname) as resultNum,

t_enduser.id,substring(t_enduser.custcode,1,3), custname,city1,state1,

t_enduser.createdby,t_enduser.createdtime as ct, t_details.serialnumber,

t_details.package,startdate,endDate,endProduct

from t_enduser,t_master,t_details

where t_enduser.id=t_master.custcode

and t_master.t_ref=t_details.t_ref

and lOWER(package)<>'pdahardwareonly'

and lOWER(endproduct)<>'accessories' and status='valid'

and t_details.t_ref in

(SELECT rh.t_ref FROM t_details rh,

(SELECT max(endDate) as maxdate, serialNumber FROM t_details

where lOWER(package)<>'pdahardwareonly' and

lOWER(endproduct)<>'accessories' and status='valid'

GROUP BY serialNumber

)maxresults

WHERE rh.serialNumber = maxresults.serialNumber AND

rh.endDate= maxresults.maxdate

)

)as numberResults where resultNum '1' and '10'

It's difficult to say what's wrong seeing without seeing error messages or sample data.

One thing that stands out, however, is the WHERE clause:

where resultNum '1' and '10'

Should this be:

where resultNum IN('1', '10')

or if resultNum is of a numerical datatype:

where resultNum IN(1, 10)

?

Chris

|||

The first line says

Select * from

but there is nothing in the from clause.

There is a lot more wrong with this query.

|||

The query will be perfectly valid once the final WHERE clause has been sorted out.

Chris

|||

Firstly, I hope the code is better formatted on your end. I know it is pretty ugly to past directly into the forums, so consider pasting to text first. I would consider reformatting for our value if you post such amounts of code as youi have a very messy query. I have highlighted a few things that seem troublesome, but nothing overly horrible.

You are going to have to give more information...

SELECT *
--alias all names. We can't help you if you won't give all information.
FROM (SELECT row_number() OVER (ORDER BY custname) as resultNum,
t_enduser.id,
substring(t_enduser.custcode, 1, 3),
custname, city1, state1, t_enduser.createdby,
t_enduser.createdtime as ct,
t_details.serialnumber, t_details.package,
startdate, endDate, endProduct
from t_enduser
,t_master --is this table_master, or does t_ have other meaning. Master is a weak name
--master, what?
,t_details
--learn about the JOIN criteria instead of using the comma based cartesion product style join
--syntax, it is easier to follow, and certainly to debug
where t_enduser.id = t_master.custcode --Customers are also endusers? This seems odd
and t_master.t_ref = t_details.t_ref --t_ref? Is this the pkey of one of your tables? Or all?
--Why lower? Is your database case sensitive? If so, then use a constraint to make sure
--package and products forllow the rules.
and LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
and t_details.t_ref in (
SELECT rh.t_ref
FROM t_details rh
,(SELECT max(endDate) as maxdate,
serialNumber
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) maxresults
WHERE rh.serialNumber = maxresults.serialNumber
AND rh.endDate = maxresults.maxdate))
as numberResults
where resultNum between 1 and 10 --this is the code that needed fixed

--also you can redo the in clause using the rowNumber function to make it a little easier...
select getRows.tref
from (SELECT rh.t_ref, row_number() over (partition by serialNumber order by endDate) as rowNum
FROM t_details
WHERE LOWER(package) <> 'pdahardwareonly'
and LOWER(endproduct) <> 'accessories'
and status = 'valid') as getRows
where getRows.rowNum = 1

|||

Louis thanks for you tips. As what you suggested “between” is the problem. Basically
I want to combine three tables and those three tables cross-reference each other.
My task requirement in such I need to pull the transactions in one
stretch customer details and his last product purchased that is by expiry date.

1. Find the product and its expiry date (maximum end date is the exp date)
2. Get the customer details for that reference
3. Pagination
4. Get row number for each records

Customer Details TABLE NAMES (T_ENDUSER)
Transaction TABLE NAMES (T_MASTER,T_DETAILS)

After I made the correction, The following query can work for me. But as what you suggested my join criteria,
I feel like dump. Can u please tune up for me?

SELECT * FROM (
SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id,T_MASTER.t_ref
from T_ENDUSER,T_MASTER,T_DETAILS
where T_ENDUSER.id=T_MASTER.custcode
and T_MASTER.t_ref=T_DETAILS.t_ref
and lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid'
and T_DETAILS.t_ref in
(SELECT rh.t_ref FROM T_DETAILS rh,
(SELECT max(endDate) as maxdate, serialNumber FROM T_DETAILS where lOWER(package)<>'pdahardwareonly'
and lOWER(endproduct)<>'accessories'
and status='valid' GROUP BY serialNumber
) as EXPDATE
WHERE rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate= EXPDATE.maxdate)
)as NUMRESULTS where RESULTNUM between '1' and '20'

|||

I was just suggesting that you use the new style joins instead:

SELECT *
FROM (SELECT row_number() OVER (ORDER BY custname) as RESULTNUM,
T_ENDUSER.id, T_MASTER.t_ref
from T_ENDUSER
JOIN T_MASTER
on T_ENDUSER.id = T_MASTER.custcode
JOIN T_DETAILS
on T_MASTER.t_ref = T_DETAILS.t_ref
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
and T_DETAILS.t_ref in (
SELECT rh.t_ref
FROM T_DETAILS rh
join (SELECT max(endDate) as maxdate,
serialNumber
FROM T_DETAILS
where lOWER(package) <> 'pdahardwareonly'
and lOWER(endproduct) <> 'accessories'
and status = 'valid'
GROUP BY serialNumber) as EXPDATE
ON rh.serialNumber = EXPDATE.serialNumber
AND rh.endDate = EXPDATE.maxdate))
as NUMRESULTS
where RESULTNUM between 1 and 20 --Also, these should be numbers, not characters

The version you did will work just fine, but this is a lot easier to follow, and is far better for outer joins...

please help fix this statement

im using an access project file now and no longer a mdb file.

i used to prompt a user for the first and last name of the patient they were looking for using this statement like [forms]![main]![text0] & "*"
and like [forms]![main]![text2] & "*" then it would promt them to enter in a last name and a first name but if they only entered a last name and left the first name prompt empty it would give them all the last name they had entered .

now im on sql as my backend and i got my query working except
if i dont type anything in on the second prompt i get no records.

how can i fix this because the user doesnt always no the first name and would like to sort through the records by just the last.

my current statement that works as long as you fill in both prompts is a follows.

SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE @.LastName + '%') AND (fname LIKE @.firstname + '%')
ORDER BY chartnumberSELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber|||Originally posted by Enigma
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber

thank you very much worked great , im just having a hard time with the new syntax at least the vb codes i wrote still work.sql

Please help - problem with simple SQL - SUM ?

Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

I'm so stressed out, please help, need to find the problem

this is the code:

1. REPORT CARD

CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

2. CLASS LIST

CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;

3.GRADE VERIFICATION REPORT

CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;

4.TIME SCHEDULE

CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

6.STUDENT SCHEDULE

CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;

7.FULL STUDENT INFORMATION

CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;

8. FACULTY INFORMATION REPORT

CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

9.WORK VERSION OF THE TIME SCHEDULE

CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;

10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;

11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT

SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END

12.POST GRADES: EXAMPLE

UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';

13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;can u post the ddl for the base tables ?
and which view is causing the problem?

Originally posted by Mari9922
Hello All, after writing my simple SQL i don't know how to fix this problem, SUM problem, won't run because of it.

I'm so stressed out, please help, need to find the problem

this is the code:

1. REPORT CARD

CREATE VIEW REPORT_CARD AS
SELECT DEPT_NAME, COURSE_NUM, COURSE_DESC, GRADE_L, COURSE_CREDIT as
CREDITS_TAKEN, COURSE_CREDIT AS COURSE_EARNED, GRADE_N*COURSE_CREDIT AS
GRADE_POINTS,
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
SUM(COURSE_CREDIT) AS SEMESTER_CREDITS_TAKEN, SUM(COURSE_CREDIT) AS
SEMESTER_CREDITS_TAKEN, SUM(GRADE_N*COURSE_CREDIT) AS TOTAL_POINTS,
TOTAL_POINTS/SEMESTER_CREDITS_TAKEN AS GPA
CLASS.SEM_ID,
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE,
S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP
FROM STUDENT, DEPARTMENT, COURSE, ENROLLMENT, CLASS, SEMESTER
WHERE
STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

2. CLASS LIST

CREATE VIEW CLASS_LIST AS
SELECT COURSE.DEPT_CODE "DEPARTMENT", COURSE_NUM "COURSE", SEC_NUM
"SECTION', CLASS.SCHED_CODE, DEPT_NAME, COURSE_DESC, COURSE_CREDIT,
SEM_ID,
TIME, DAY, ROOM_NUM, BLDG_CODE,
CLASS.F_ID, F_FNAME, F_LNAME
ENROLLMENT.S_ID, S_FNAME, S_MI, S_LNAME, GRADE
FROM DEPARTMENT, CLASS, COURSE, ROOM, STUDENT, ENROLLMENT, FACULTY
WHERE
ROOM.ROOM_ID=CLASS.ROOM_ID
AND COURSE.DEPT_CODE=DEPARTMENT.DEPT_CODE
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND ENROLLMENT S_ID=STUDENT.S_ID;

3.GRADE VERIFICATION REPORT

CREATE VIEW GRADE_VERIFICATION AS
SELECT * FROM CLASS_LIST;

4.TIME SCHEDULE

CREATE VIEW TIME_SCHEDULE AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT, PREREQ
FROM CLASS, COURSE, FACULTY, ROOM
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID;

6.STUDENT SCHEDULE

CREATE VIEW STUDENT_SCHEDULE AS
SELECT STUDENT.S_ID, SEM_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY,
S_L_STATE, S_L_ZIP, S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
CLASS.SCHED_CODE, CLASS_CLASS_ID, COURSE_DESC, SEC_NUM,
COURSE_CREDIT, TIME, DAY, ROOM_NUM, BLDG_CODE, SUM(COURSE_CREDIT)
FROM STUDENT, CLASS, COURSE, ROOM, ENROLLMENT
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND CLASS.SCHED_CODE=ENROLLMENT.SCHED_CODE
AND CLASS.CLASS_ID=COURSE.COURSE_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID;

7.FULL STUDENT INFORMATION

CREATE VIEW FULL_STUDENT_INFO AS
SELECT STUDENT.*, SEM_ID,
COURSE_NUM, COURSE_DES, COURSE_CREDIT,
GRADE_L, GRADE_N*COURSE_CREDIT AS "GRADE POINTS",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(COURSE_CREDIT) AS "CREDITS ATTEMPTED",
SUM(GRADE_N*COURSE_CREDIT) AS "TOTAL GRADE POINTS",
S_GPA
FROM STUDENT, ENROLLMENT, COURSE, CLASS
WHERE STUDENT.S_ID=ENROLLMENT.S_ID
AND ENROLLMENT.SCHED_ID=CLASS.SCHED_ID
AND CLASS.CLASS_ID=COURSE_COURSE_ID;

8. FACULTY INFORMATION REPORT

CREATE VIEW FACULTY_INFO AS
SELECT FACULTY.*,
S_ID, S_FNAME, S_MI, S_LNAME, S_L_ADD, S_L_CITY, S_L_STATE, S_L_ZIP,
S_P_ADD, S_P_CITY, S_P_STATE, S_P_ZIP,
DEPT_CODE, MAJOR_NAME
FROM FACULTY, STUDENT, MAJOR
WHERE
FACULTY.F_ID=STUDENT.F_ID
AND STUDENT.MAJOR_ID=MAJOR.MAJOR_ID;

9.WORK VERSION OF THE TIME SCHEDULE

CREATE VIEW TIME_SCHED_INTERNAL AS
SELECT SEMESTER.*, COURSE_NUM, COURSE_DESC, CLASS.SCHED_CODE, SEC_NUM,
DAY, TIME, ROOM_NUM, BLDG_CODE, CLASS.F_ID, F_LNAME, COURSE_CREDIT,
PREREQ,
COUNT(ENROLL_ID), MAX_ENROLL
FROM CLASS, COURSE, FACULTY, ROOM, ENROLLMENT
WHERE CLASS.CLASS_ID = COURSE_COURSE_ID
AND CLASS.F_ID=FACULTY.F_ID
AND CLASS.ROOM_ID=ROOM.ROOM_ID
AND SEMESTER.SEM_ID=CLASS.SEM_ID
AND ENROLLMENT.SCHED_CODE=CLASS.SCHED_CODE;

10.CREATE VIEW COURSE_REPORT AS
SELECT COURSE_NUM, COURSE_DESC, COURSE_CREDIT,
DEPT_DESC, COURSE.DEPT_CODE
FROM COURSE, DEPARTMENT
WHERE COURSE.DEPT_CODE=DEPARTMENT.DEPT.CODE;

11.UPDATE ENROLLMENT-NEED TO CREATE A TRIGGER TO CHECK UPON INSERT INTO
THE ENROLLMENT TABLE WHERE COURSE.PREREQ NOT NULL AND ENROLLMENT.GRADE NOT
NULL FOR THAT STUDENT

SYNTAX WILL BE SIMILAR TO:
CREATE OR REPLACE TRIGGER VERIFY_ENROLLMENT
BEFORE INSERT ON ENROLLMENT
BEGIN
******
END

12.POST GRADES: EXAMPLE

UPDATE ENROLLMENT
SET GRADE='A'
WHERE S_ID='123';

13. PURGE:EXAMPLE
DELETE FROM CLASS
WHERE CLASS < 1010;|||Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?|||Thank you very much, I'll post it after work, if that's not too much of a problem.

Originally posted by harshal_in
can u post the ddl for the base tables ?
and which view is causing the problem?

Please fix the post-SP1 excel export issue

Though it has been well-documented in the community since SP1 was released, I'd like to reiterate that many of us are having critical issues related to element hiding and excel export.
I, for one, have built a highly dynamic reporting system that relies heavily on the use visibility for showing/hiding subreports. My users require export to excel.
The question to Microsoft is: Can you fix this issue that was introduced in SP1? Please provide us with an SP1a update that will allow elements to be hidden & thus NOT show up in the excel export...just as it works in the HTML & PDF renderings.
Regards,
Todd SmartIt may be too much work to "re-tool", but have you considered DTS services?
However, I too, am waiting for a fix on Excel exporting!
-KB
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> Though it has been well-documented in the community since SP1 was
released, I'd like to reiterate that many of us are having critical issues
related to element hiding and excel export.
> I, for one, have built a highly dynamic reporting system that relies
heavily on the use visibility for showing/hiding subreports. My users
require export to excel.
> The question to Microsoft is: Can you fix this issue that was introduced
in SP1? Please provide us with an SP1a update that will allow elements to
be hidden & thus NOT show up in the excel export...just as it works in the
HTML & PDF renderings.
>
> Regards,
> Todd Smart|||I'm not sure what you want us to do. You would rather have items that are
hidden and can never be toggled?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> Though it has been well-documented in the community since SP1 was
> released, I'd like to reiterate that many of us are having critical issues
> related to element hiding and excel export.
> I, for one, have built a highly dynamic reporting system that relies
> heavily on the use visibility for showing/hiding subreports. My users
> require export to excel.
> The question to Microsoft is: Can you fix this issue that was introduced
> in SP1? Please provide us with an SP1a update that will allow elements to
> be hidden & thus NOT show up in the excel export...just as it works in the
> HTML & PDF renderings.
>
> Regards,
> Todd Smart|||Hi Brian,
If I have a subreport (or any other element for that matter) that is hidden due to an expression result of [True], that subreport should not be rendered in the Excel export. It should not even have an Excel cell associated with it.
In other words, I would want the render of Excel to work just like the HTML & PDF render works...and how it worked prior to SP1.
Regards,
Todd
"Brian Welcker [MSFT]" wrote:
> I'm not sure what you want us to do. You would rather have items that are
> hidden and can never be toggled?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> > Though it has been well-documented in the community since SP1 was
> > released, I'd like to reiterate that many of us are having critical issues
> > related to element hiding and excel export.
> >
> > I, for one, have built a highly dynamic reporting system that relies
> > heavily on the use visibility for showing/hiding subreports. My users
> > require export to excel.
> >
> > The question to Microsoft is: Can you fix this issue that was introduced
> > in SP1? Please provide us with an SP1a update that will allow elements to
> > be hidden & thus NOT show up in the excel export...just as it works in the
> > HTML & PDF renderings.
> >
> >
> > Regards,
> >
> > Todd Smart
>
>|||OK, we'll take a look at this and if it is a regression, we will fix it in
SP2.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:423C4612-1C70-4178-9DCA-321019F859E0@.microsoft.com...
> Hi Brian,
> If I have a subreport (or any other element for that matter) that is
> hidden due to an expression result of [True], that subreport should not be
> rendered in the Excel export. It should not even have an Excel cell
> associated with it.
> In other words, I would want the render of Excel to work just like the
> HTML & PDF render works...and how it worked prior to SP1.
>
> Regards,
> Todd
> "Brian Welcker [MSFT]" wrote:
>> I'm not sure what you want us to do. You would rather have items that are
>> hidden and can never be toggled?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
>> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
>> > Though it has been well-documented in the community since SP1 was
>> > released, I'd like to reiterate that many of us are having critical
>> > issues
>> > related to element hiding and excel export.
>> >
>> > I, for one, have built a highly dynamic reporting system that relies
>> > heavily on the use visibility for showing/hiding subreports. My users
>> > require export to excel.
>> >
>> > The question to Microsoft is: Can you fix this issue that was
>> > introduced
>> > in SP1? Please provide us with an SP1a update that will allow elements
>> > to
>> > be hidden & thus NOT show up in the excel export...just as it works in
>> > the
>> > HTML & PDF renderings.
>> >
>> >
>> > Regards,
>> >
>> > Todd Smart
>>|||Brian, are there any work arounds ? I am also finding that visible tables are
not being displayed in the export to excel, and the start of the table data
appears several rows down (80 rows) .
Regards
Stuart
"Brian Welcker [MSFT]" wrote:
> OK, we'll take a look at this and if it is a regression, we will fix it in
> SP2.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> news:423C4612-1C70-4178-9DCA-321019F859E0@.microsoft.com...
> > Hi Brian,
> >
> > If I have a subreport (or any other element for that matter) that is
> > hidden due to an expression result of [True], that subreport should not be
> > rendered in the Excel export. It should not even have an Excel cell
> > associated with it.
> >
> > In other words, I would want the render of Excel to work just like the
> > HTML & PDF render works...and how it worked prior to SP1.
> >
> >
> > Regards,
> >
> > Todd
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> I'm not sure what you want us to do. You would rather have items that are
> >> hidden and can never be toggled?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> >> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> >> > Though it has been well-documented in the community since SP1 was
> >> > released, I'd like to reiterate that many of us are having critical
> >> > issues
> >> > related to element hiding and excel export.
> >> >
> >> > I, for one, have built a highly dynamic reporting system that relies
> >> > heavily on the use visibility for showing/hiding subreports. My users
> >> > require export to excel.
> >> >
> >> > The question to Microsoft is: Can you fix this issue that was
> >> > introduced
> >> > in SP1? Please provide us with an SP1a update that will allow elements
> >> > to
> >> > be hidden & thus NOT show up in the excel export...just as it works in
> >> > the
> >> > HTML & PDF renderings.
> >> >
> >> >
> >> > Regards,
> >> >
> >> > Todd Smart
> >>
> >>
> >>
>
>