Query problem splitting field into two comumns

Hi I'm new to reporting services. I'm desinging a query for a report.
I have one filed "ship_yw" that contains year and week numbers, for example,
200701, 200702. I want to display the year in one column and the week in
another. I'm connected to a Progress database via ODBC. I have accomplished
this with no problem in Access using the following code:
SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
This will not work in Query Builder or if I manually type it in the string
window.
I have also tried:
SELECT
LEFT(Ship__yw,4),
RIGHT(RTRIM(Ship__yw),2)
FROM
oe_head
SELECT
SUBSTRING(Ship__yw,1,4),
SUBSTRING(Ship__yw,5,2)
FROM
oe_head
Query Builder puts unwanted ' around the field name and causes the query to
return "ship" in every row in one column and "yw" in every row of the second.
Any suggestions?Try giving names for the fildes , Use AS
"Twaterman" wrote:
> Hi I'm new to reporting services. I'm desinging a query for a report.
> I have one filed "ship_yw" that contains year and week numbers, for example,
> 200701, 200702. I want to display the year in one column and the week in
> another. I'm connected to a Progress database via ODBC. I have accomplished
> this with no problem in Access using the following code:
> SELECT LEFT([Ship_yw],4), RIGHT([Ship_yw],2)
> This will not work in Query Builder or if I manually type it in the string
> window.
> I have also tried:
> SELECT
> LEFT(Ship__yw,4),
> RIGHT(RTRIM(Ship__yw),2)
> FROM
> oe_head
> SELECT
> SUBSTRING(Ship__yw,1,4),
> SUBSTRING(Ship__yw,5,2)
> FROM
> oe_head
> Query Builder puts unwanted ' around the field name and causes the query to
> return "ship" in every row in one column and "yw" in every row of the second.
> Any suggestions?
>
>

Query Performance diff

Hi @.all,

I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.

Publishing the report and running it from the report server. The query needs 40x the time as in report designer.

Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.

Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.

Thanks for any hints

T

The data tab will only retrieve data, so the report design may be the cause for bad performance. How much time does it take to preview the report in your report designer?

Shyam

|||

I'll try that,

but isn't this time which gets reported in ExecutionLog in Cols TimeProcessing and TimeRendering?

Query parameters not recognized by report designer

No matter how I try to enter an automatic query parameter it just isn't recognized as such. I get missing expression errors from the SQL syntax check and no report variables are generated. There must be something incredibly simple that I am missing....

The most recent query string I've entered is

SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = @.Facility

Thanks for anyone who can help..

I am not sure whether this will help but one thing that works is to create the query as a stored procedure in your database and use the Stored Proceudure option rather than Query Text. You simply type in the stored procedure name (no brackets or parameters) and hit the Execute Query (!) button. Reporting Services then creates the required parameters for you and prompts for their values. You have to run the stored procedure at least once in the dataset tab so that you have some columns to choose when you go to the layout tab. Reporting Services doesn't know what columns are returned until you run the query.

You miss out on the Generic Query Designer in Reporting Services but you don't have to remember or type in the parameter names. It's fast becoming my preferred method.

Dick Campbell

|||

I neglected to mention that we were using an Oracle database because I didn't see why it would make any difference. But coworkers now tell me that the automatic query parameter recognition is a SQL Server specific feature. I don't see why this should be, and if that is the case, it certainly should be documented as such.

For Oracle users, the only solution appears to be to enter the SQL query as an expression like...

="SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = '" & parameters.facility.value & "'"

Query Parameter question

Hello:

We're using RS2005 and I have the following question:

I have two report parameters that populated by a query within RS2005 and that works fine. Based on what I selected for the two report parameters I need to use these values to retrieve my Client's Name and paste on top of report in the header section of the report. I'm new at working with RS2005 but it appears to be hanging for some reason.

This is what I do to attempt to get the Client's Name. I created two individual datasets and populate with a query within RS2005. I created a third dataset and create parameters @.PCMR, @.PACCTNUM in the edit dataset section. I use parameters @.PCMR and @.PACCTNUM in WHERE clause of SQL statement in third dataset. I hope what I'm saying is making sense.

Best regards

You can have a filter condition for your third dataset. Go to Filters tab in Edit dataset and select "PCMR" under Expression and select "=" under Operator and select "Parameters!PCMR.Value" under Value section.

This should do the trick.

Shyam

|||

Shyam,

Thanks for the response however, I discovered my problem. It had to do with a 4th dataset I partially completed. I deleted the 4th dataset and the report runs fine.

Best regards

Query Parameter Problem

I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT * FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When I preview the report, no problem is found. But when I deploy it in report server, the following error is shown:
ORA-01036: illegal variable name/number
In fact, both report server and report designer are installed in same machine.Please try these steps:
* delete the published report from the report server (through the report
manager: http://localhost/reports)
* verify that your report really works in Report Designer Preview (click the
green "refresh" icon in the Preview toolbar)
* deploy the report again to report server
* try to run it again through report manager
BTW: is there any specific reason why you choose the MS OleDB provider for
ODBC and connect to an Oracle ODBC data source?
Why don't you use the managed Oracle provider (by selecting "Oracle" in the
data source dialog) which would support named parameters (example for
parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
can also use the MS OleDB provider for Oracle (which directly connects to
Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
Note: when designing Oracle queries you should always use the text-based
query designer (with 2 panes) rather than the graphical query designer (with
4 panes).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <May Liu@.discussions.microsoft.com> wrote in message
news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
I preview the report, no problem is found. But when I deploy it in report
server, the following error is shown:
> ORA-01036: illegal variable name/number
> In fact, both report server and report designer are installed in same
machine.|||thanks !!!
The error is gone if I use MS OleDB provider for Oracle.
"Robert Bruckner [MSFT]" wrote:
> Please try these steps:
> * delete the published report from the report server (through the report
> manager: http://localhost/reports)
> * verify that your report really works in Report Designer Preview (click the
> green "refresh" icon in the Preview toolbar)
> * deploy the report again to report server
> * try to run it again through report manager
> BTW: is there any specific reason why you choose the MS OleDB provider for
> ODBC and connect to an Oracle ODBC data source?
> Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> data source dialog) which would support named parameters (example for
> parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> can also use the MS OleDB provider for Oracle (which directly connects to
> Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> Note: when designing Oracle queries you should always use the text-based
> query designer (with 2 panes) rather than the graphical query designer (with
> 4 panes).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> I preview the report, no problem is found. But when I deploy it in report
> server, the following error is shown:
> > ORA-01036: illegal variable name/number
> >
> > In fact, both report server and report designer are installed in same
> machine.
>
>|||My SQL is base on two parameters. One is mandatroy and the other is optional.
e.g.
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO AND EMPL_ID = :EMPL_ID
:DEPT_NO is mandatory
:EMPL_ID is optional
When user input null/blank employee ID, no result is retrieved. How can I override the SQL so that if user don't put employee ID, the SQL become
SELECT DEPART_NO, EMPL_ID FROM EMPL_TABLE
WHERE DEPART_NO = :DEPT_NO
I am using MS OleDB provider for Oracle.
"May Liu" wrote:
> thanks !!!
> The error is gone if I use MS OleDB provider for Oracle.
> "Robert Bruckner [MSFT]" wrote:
> > Please try these steps:
> > * delete the published report from the report server (through the report
> > manager: http://localhost/reports)
> > * verify that your report really works in Report Designer Preview (click the
> > green "refresh" icon in the Preview toolbar)
> > * deploy the report again to report server
> > * try to run it again through report manager
> >
> > BTW: is there any specific reason why you choose the MS OleDB provider for
> > ODBC and connect to an Oracle ODBC data source?
> > Why don't you use the managed Oracle provider (by selecting "Oracle" in the
> > data source dialog) which would support named parameters (example for
> > parameter syntax: SELECT * FROM EMPLOYEE WHERE EMPL_ID = :employeeID )? You
> > can also use the MS OleDB provider for Oracle (which directly connects to
> > Oracle, rather than using the ODBC provider) or the Oracle OleDB provider.
> >
> > Note: when designing Oracle queries you should always use the text-based
> > query designer (with 2 panes) rather than the graphical query designer (with
> > 4 panes).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >
> > "May Liu" <May Liu@.discussions.microsoft.com> wrote in message
> > news:4FEB9C4F-DF19-451C-899D-37A63F53389D@.microsoft.com...
> > > I choose Microsoft OLE DB for ODBC driver provider and my SQL is "SELECT *
> > FROM EMPLOYEE WHERE EMPL_ID = ?. I have defined ? in report parameters. When
> > I preview the report, no problem is found. But when I deploy it in report
> > server, the following error is shown:
> > > ORA-01036: illegal variable name/number
> > >
> > > In fact, both report server and report designer are installed in same
> > machine.
> >
> >
> >

Query parameter drop-down not available once report is cached

Hi, I have a date parameter that is populated from a query to give me a
month drop-down on my report. When a user selects the month and runs
the report, it passes the parameter to a query to return a dataset.
This is all straight forward. Since the report runs quite slowly, I
wanted to cache the report witha dafault month, but allow the users to
then re-run the report with other months. I found that once I cached
the report on a shared schedule, the month drop-down is no longer
available for me to select a new query parameter to rerun the report.
Another filter parameter is available, so I am assuming it it because
it is a query parameter.
Is there a way to cache the reoprt, but still allow users to reselect
the query parameter and rerun the report?You can make the month parameter a filter parameter, bring back a all the
months data and then apply the month filter to the data regions in the
report..
Or if you are using Enterprise Edition you could preload the cache using a
data-driven subscription, see "Preloading the Cache"
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_processing_v1_2p89.asp
--
Douglas McDowell
douglas@.nospam.solidqualitylearning.com
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1109694129.517130.311640@.g14g2000cwa.googlegroups.com...
> Hi, I have a date parameter that is populated from a query to give me a
> month drop-down on my report. When a user selects the month and runs
> the report, it passes the parameter to a query to return a dataset.
> This is all straight forward. Since the report runs quite slowly, I
> wanted to cache the report witha dafault month, but allow the users to
> then re-run the report with other months. I found that once I cached
> the report on a shared schedule, the month drop-down is no longer
> available for me to select a new query parameter to rerun the report.
> Another filter parameter is available, so I am assuming it it because
> it is a query parameter.
> Is there a way to cache the reoprt, but still allow users to reselect
> the query parameter and rerun the report?
>

Query Optimization - Please Help

Hi,

Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.

DECLARE @.EmpID, @.DateFrom, @.Total1 ... // Declaring different variables

SELECT @.DateFrom = // Set to start of any month e.g. 2007-06-01 ..... 1st

Loop (condition -- Get all employees, working fine)

BEGIN

SELECT @.EmpID = // Get EmployeeID ..... 2nd

SELECT @.Total1 = SUM (Abences) ..... 3rd

FROM Attendance

WHERE employee_id_fk = @.EmpID (from 2nd step)

AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @.DateFrom)) (from 1st step)

SELECT @.Total2 ..................... same as above

SELECT @.Total3 ..................... same as above

INSERT IN @.TABLE (@.EmpID, @.Total1, ..... @.Total31) ..... 4th

Iterate (condition) to next employee ..... 5th

END

It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me...

What does the Attendance table look like? I have some ideas for you but I need to know how the attendance is stored. Can you give us the schema of that table please? Thanks!

|||

See this sample example-->

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

Declare @.fromDate datetime,
@.toDate datetime

Set @.fromDate = '1-Aug-2007'
Set @.toDate = '4-Aug-2007'

Select distinct a.dtAttendate,

'STATUS'=(select
(case
when lv_status='EL' then 'EL'
when lv_status='CL' then 'CL'
when lv_status='SL' then 'SL'
when lv_status='ML' then 'ML'
when (wk_status='S' AND log_status='P') then 'SB'
when (wk_status='S' AND log_status='A') then 'Absent (SB)'
when wk_status='N' then 'Weekend'
when bIsHoliday=1 then 'Holiday'
when log_status='A' then 'Absent'
--when mnyLateHrs>0 then 'Late'
when log_status='P' then 'Present'
end)
from tblLogAbsent
where dtAttendate = a.dtAttendate and intEmpCode=a.intEmpCode),

'TIMEIN'=(select dtEmpTimeIn from tblLogStatus where dtAttendate=a.dtAttendate and intEmpCode=a.intEmpCode),

'TIMEOUT'=(select dtEmpTimeOUT from tblLogStatus where dtAttendate=a.dtAttendate and intEmpCode=a.intEmpCode)

from tblLogStatus a
where a. dtAttendate between @.fromDate and @.toDate

group by a.dtAttendate, intEmpCode

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

here i use two different table "tbllogabsent" for his attendance status and "tbllogstatus" as a for additional information... this is not becoming problem... if u cant understand any line of code ask me again...hopefully this will be helpfull to u...

|||

Hmmm... I'm not sure how to use this information. In your initial code, you do a SELECT from a table called Attendance, which seems to have columns like Abences, employee_id_fk and Date_Absent. Can you give us more details on this table please? Thanks.

|||

Hi johram,

Thanks for replying. Yes you are right. The attendance table for employees has columns like EmpID_fk, Attendance_Date, ... , Attendance_Total. The Attendance_Total column is dependent on our business rules which include reason for signing in/out. e.g. If an employee has signed out for some official task, 1 is added to his Attendance_Total column. If he is going away for a business tour, 2 may be added to his Attendance_Total column. So in one day, an employee can have more than one record. The records for an employee may look like the following:

EmpID Attendance_Date ... Attendance_Total

1001 04/28/2006 1

1001 04/28/2006 2

1001 04/28/2006 1

So on 28th April, the total for Emp (1001) = 4. There are other columns in there but im only concerned with Attendance_Total. I need to display the SUM (Attendance_Total) for each day in a month for each employee.

If I further elaborate my report based on the above example, it may look something like:

EmpID EmpName D1 D2 D3 D4 D5 D6 ......

1001 ABC 1 0 4 4 1 0

I have tried a few techniques (under my experienceJ), but when it comes to computing the sum for each day, it takes almost 4-5 minutes which I am sure nobody wants. Also this report can be accessed anytime within a day and employees keep coming and going for business, so cant store the records and need to compute them everytime the report is accessed.

Thanks to you all for helping me...

|||

Hi patuary,

Thanks for your reply aswell. I have tried your technique and understand it - i bet :) Anyways, after applying your technique, there are the following two problems:

1. All records are being returned as rows e.g. The records for Emp 1001 in a month is not in a single row, rather seperate rows are returned for each day

2. Also, this query only returns data for days on which attendance may be marked. But if there is a weekend or the employee was absent, his attendance record for that day is not computed as there are no records. Whereas in my case, if he was absent or no record found on a given day, his attendance record must be marked as 0.

Thanks again for your time...

|||

Guys, please provide your valueable feedback...

|||

Is it important that you get a result with all the days, even if the sum is zero? Cause that will make it a bit more complicated in the SQL. You can have the SQL report back all days that actually have a total (greater than zero), and then in your GUI you can render the rest of the days as empty. In that case, I think we can work out a solution for you. At least that's what we'll start with ;-) I'll see what I can do!

Also, what's the datatype of you Attendance_Date column?

|||

If you are using SQL 2005, there should be a new statement called PIVOT, although it is nowhere to be found in the T-SQL reference manual on MSDN. Maybe you are luckier than me ;-) Pivot is the term for when you shift the layout of a table so that you look on it from a different perspective. In this case, you want to pivot the table on the date so that each date represents a column rather than a row.

Now, this can be done with a function calledCrosstable, which was developed by the legendary Rob Volk. The source code for this function can be foundhere. Note that you need to change the column "pivot" to "tpivot" or something, since "pivot" is a keyword in SQL 2005.

This is the modified version of Crosstable that will work in SQL 2005:

ALTER PROCEDURE crosstab @.select varchar(8000),@.sumfuncvarchar(100), @.pivotvarchar(100), @.table varchar(100)ASDECLARE @.sqlvarchar(8000), @.delimvarchar(1)SET NOCOUNT ONSET ANSI_WARNINGSOFFEXEC ('SELECT ' + @.pivot +' AS tpivot INTO ##pivot FROM ' + @.table +' WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot +' FROM ' + @.table +' WHERE ' + @.pivot +' Is Not Null')SELECT @.sql='', @.sumfunc=stuff(@.sumfunc,len(@.sumfunc), 1,' END)' )SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )WHEN 0THEN''ELSE''''END FROM tempdb.information_schema.columnsWHERE table_name='##pivot'AND column_name='tpivot'SELECT @.sql=@.sql +'''' +convert(varchar(100), tpivot) +''' = ' + stuff(@.sumfunc,charindex('(', @.sumfunc )+1, 0,' CASE ' + @.pivot +' WHEN ' + @.delim +convert(varchar(100), tpivot) + @.delim +' THEN ' ) +', 'FROM ##pivotDROP TABLE ##pivotSELECT @.sql=left(@.sql,len(@.sql)-1)SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0,', ' + @.sql +' ')EXEC (@.select)SET ANSI_WARNINGSON

Now, to demonstrate the power of this function I made a quick sample for you to push you in the right direction:

EXECUTE Crosstab'SELECT EmpId FROM Attendance GROUP BY EmpId','SUM(Attendance_total)','Attendance_date','attendance'

This will give you a matrix with all the employees vertically, and horizontally you will have all unique dates, with the respective attendance total for each employee on that day. As I said earlier, this will not give you all the days of the month, unless there are data for each day. So you might need to do some logic in your GUI to render "empty" days correctly. Good luck!

|||

Hi Johram,

Once again thanks for your time. I really appreciate all your help. Yes your rite, we do need to handle all days in a month. But as you mentioned this can be handled in my logic so im lesser concerned about the days without any data.

Anyways, im aware of the Pivot function. Its basically used to convert rows into columns. Indeed the functionality i have in my stored procedure does the same job. What i do is that i have a temporary table in which i insert 31 rows for each employee. so for 500 employee, i insert 500x31 rows. Later i convert the rows into columns and display it. Although i do not use the Pivot function but i did once give it a try and the Processing Time was similar to what i have in there right now.

Still, im not ready to backout and will definitely give a try to your solution. Let me see what can i get out of it. By the way Johram, if you have dealt with any monthly or annual report in the past, usually how much time does it take to display such a report? Do you think that im being over ambitious in displaying such a report or such reports do take their time...

Once again, thanks alot for every help you have provided...

|||

Sorry, haven't done exactly this kind of report before. But it will depend on the amount of data you are trying to cover. Is it relevant to show ALL employees in a list/report? Maybe you should restrict it to region, or last name or something. Try to do a selection out of the 500 if it possible.

Although I havent been able to compare this crosstable thing with your first query, I still think that it might be faster. Try implement it and see for yourself. Good luck!

|||

Hi Johram

Thanks alot for your time and patience. I really appreciate your efforts and the help you have provided.

keep up the good work...

Query only Dimension Data

We Have a case where we have to create a report only with the dimension data from SSAS.
Is there any way in doing it in Excel 2007.

I see a pivot table option which says " Display item labels when no fields are in the value area"
but this is disabled. Can anyone point me to a way of doin this or enabling this.

Thankyou
VidyaAre you creating the report through excel or programmatically? If programmatically $Dimension exposes the dimension as a cube and you can use it as you would any cube...

Query on crystal drilldown reports

I have a drilldown report that displays a few columns in each row. Is it possible to drilldown on each one of the columns separately. i.e., a separate subreport should be opened if I click on each of the columns in a row. Is this possible in Crystal reports. If so, how?Hi,

Its possible to drill down further, I think the column should be used in grouping.

Try grouping the filed & drill down.

Praveen

Query OK in QA, never finishes in report

I have a pretty involved query that takes about 5 minutes to run in query analyzer. I'm not too upset with that performance based on what it does. The problem I'm having is that it doesn't finish when run in reporting services as a report. I've had issues when a lot of rows and columns are returned, but the query returns 400 rows.

Is there a timeout setting somewhere in reporting services that causes it to hang? Is it a known problem for long-running queries? I can log into QA as the user I use for the report connection and run it fine. No apparent permission issues. It just never finishes.

Thanks for any insight.

Is this the property you are looking for?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_ir_9gds.asp
And a whole article on it:
http://www.databasejournal.com/features/mssql/article.php/3488466

Query not returning proper data (date related) in 2005 after upgrade from 2000...

I am sending out an SOS.

Here is the situation:

We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can’t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn’t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea’s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.

Thank you.

ScottIm very curious about this situation. Can you post the query that you are running?|||

It is very long but here it is...

select data.shipment,
po_line,
data.style,
p.prodcat1 as omnia_sbu,
Left(p.prodcat1, 3) as sbu_mstr,
isnull(CONVERT(CHAR(10), OrigCRD, 101),'') OrigCRD,
isnull(CONVERT(CHAR(10), CRD, 101),'') CRD,
isnull(OrigNDC, '') OrigNDC,
isnull(CONVERT(CHAR(10), NDC, 101),'') NDC,
isnull(OrigLogP, '') OrigLogP,
isnull(LogP,'') LogP,
isnull(po_mode, '') po_mode,
isnull(data.agent, '') agent,
isnull(rtrim(agent),'') + isnull(ship_mode,'') agent_key,
isnull(left(agent, 2),'') agent_mstr,
isnull(left(agent, 2),'') + isnull(ship_mode,'') agent_mstr_key,
isnull(data.factory_id, '') factory_id,
isnull(data.factory, '') factory,
isnull(rtrim(data.factory), '') + isnull(ship_mode,'') as fact_key,
isnull(data.broker, '') broker,
isnull(left(broker, 3), '') broker_mstr,
isnull(template, '') ship_cal,
isnull( ship_mode, '') ship_mode,
isnull(CONVERT(CHAR(10), brd_actual_date, 101),'') brd_actual_date,
isnull(CONVERT(CHAR(10), crd_actual_date, 101),'') crd_actual_date,
isnull(CONVERT(CHAR(10), cls_actual_date, 101),'') cls_actual_date,
isnull(CONVERT(CHAR(10), dep_actual_date, 101),'') dep_actual_date,
isnull(CONVERT(CHAR(10), arv_actual_date, 101),'') arv_actual_date,
isnull(CONVERT(CHAR(10), dlv_actual_date, 101),'') dlv_actual_date,
isnull(CONVERT(CHAR(10), rcv_actual_date, 101),'') rcv_actual_date,
isnull(CONVERT(CHAR(10), idc_actual_date, 101),'') idc_actual_date,
isnull(CONVERT(CHAR(10), bkd_actual_date, 101),'') bkd_actual_date,
isnull(CONVERT(CHAR(10), aci_actual_date, 101),'') aci_actual_date,
isnull(CONVERT(CHAR(10), dlt_actual_date, 101),'') dlt_actual_date,
isnull(CONVERT(CHAR(10), lot_actual_date, 101),'') lot_actual_date,
isnull(CONVERT(CHAR(10), dcr_actual_date, 101),'') dcr_actual_date,
isnull(CONVERT(CHAR(10), ccd_actual_date, 101),'') ccd_actual_date,
units ship_units
from
(select shipment.shipment, lot po_line, prbunhea.style, CASE ltrim(prbunhea.misc12)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc12) END OrigCRD,
CASE ltrim(prbunhea.misc4)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc4) END CRD,
ltrim(prbunhea.misc13) OrigNDC,
CASE ltrim(revdd)
WHEN '' THEN ''
ELSE revdd END NDC,
CASE ltrim(orig_shipcal)
WHEN '' THEN ''
ELSE ltrim(orig_shipcal) END OrigLogP,
CASE ltrim(prbunhea.defshipcal)
WHEN '' THEN ''
ELSE ltrim(prbunhea.defshipcal) END LogP,
CASE ltrim(prbunhea.misc14)
WHEN '' THEN ''
ELSE ltrim(prbunhea.misc14) END po_mode,
CASE ltrim(shipment.misc5)
WHEN '' THEN ''
ELSE ltrim(shipment.misc5) END ship_mode,
prbunhea.rdacode agent,
shipment.broker,
prbunhea.rfactory factory_id,
ship_to_1 factory,
sum(shipped.unitship) units
from prbunhea, shipped, shipment, shshipto where
prbunhea.lot = shipped.cut
and shipment.shipment = shipped.shipment
and shshipto.factory = prbunhea.rfactory
group by shipment.shipment, lot, prbunhea.style,prbunhea.misc12,
prbunhea.misc4, prbunhea.misc13, shipment.misc5, revdd,
orig_shipcal, prbunhea.DefShipCal, prbunhea.misc14,
prbunhea.rdacode, shipment.broker, prbunhea.rfactory, ship_to_1
) data,
(select CAST(tam.name AS CHAR(20)) AS name,
tam.id_ta,
tam.template,
brd_actual_date,
crd_actual_date,
cls_actual_date,
dep_actual_date,
arv_actual_date,
dlv_actual_date,
rcv_actual_date,
idc_actual_date,
bkd_actual_date,
aci_actual_date,
dlt_actual_date,
lot_actual_date,
dcr_actual_date,
ccd_actual_date
from
(select * from tamaster where calendarmodule = 'Shipment') as tam left outer join
(select id_ta,
max(brd_actual_date) as brd_actual_date,
max(crd_actual_date) as crd_actual_date,
max(cls_actual_date) as cls_actual_date,
max(dep_actual_date) as dep_actual_date,
max(arv_actual_date) as arv_actual_date,
max(dlv_actual_date) as dlv_actual_date,
max(rcv_actual_date) as rcv_actual_date,
max(idc_actual_date) as idc_actual_date,
max(bkd_actual_date) as bkd_actual_date,
max(aci_actual_date) as aci_actual_date,
max(dlt_actual_date) as dlt_actual_date,
max(lot_actual_date) as lot_actual_date,
max(dcr_actual_date) as dcr_actual_date,
max(ccd_actual_date) as ccd_actual_date
from
(
-- CRD start
select id_ta,
--name,
crd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as brd_actual_date ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CRD - CARGO READY DATE'
-- CRD end
union all
-- BRD start
select id_ta,
--name,
null as crd_actual_date,
brd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BRD - BOOKING REQUEST DATE'
-- BRD end
union all
-- CLS start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
cls_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CLS - SHIP CLOSE'
-- CLS end
union all
-- DEP start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
dep_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DEP - CONFIRMED ON BOARD'
-- DEP end
union all
-- ARV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
arv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ARV - ACTUAL DATE OF ARRIVAL'
-- ARV end
union all
-- DLV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
dlv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLV - CARGO RECEIVED AT DC (POD)'
-- DLV end
union all
-- RCV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
rcv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'RCV - RECEIVE DC'
-- RCV end
union all
-- IDC start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
idc_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'IDC - IN DISTRIBUTION CENTER'
-- IDC end
union all
-- BKD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
bkd_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BKD - BOOKING APPROVED'
-- BKD end
union all
-- ACI start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
aci_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ACI - DATE ENTRY IS FILED WITH CUSTOMS'
-- ACI end
union all
-- DLT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
dlt_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLT - ARRIVAL AT ATL RAIL / CY'
-- DLT end
union all
-- LOT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
lot_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'LOT - LOADED ON TRUCK OR TRAIN'
-- LOT end
union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end
union all
-- CCD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
ccd_actual_date =
CASE year(act_finish)
WHEN 1900 THEN null
WHEN 1899 THEN null
ELSE act_finish
END
from tacalendar where name = 'CCD - CUSTOMS CLEARED'
-- CCD end
) as cal group by id_ta ) as cal_temp
on tam.id_ta = cal_temp.id_ta) as cal, tmp_wcc_out_products p
where cast(data.shipment as char(20)) = cast(cal.name as char(20))
and data.style = p.prodcode
order by data.shipment, data.po_line

|||

We solved the issue by making the following change. I am wondering if SS 2005 handles null values differently.

Before:

union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end

After:

union all
-- DCR start
select id_ta,
--name,
convert(datetime,'12/30/1899',101) crd_actual_date,
convert(datetime,'12/30/1899',101) brd_actual_date,
convert(datetime,'12/30/1899',101) cls_actual_date,
convert(datetime,'12/30/1899',101) dep_actual_date,
convert(datetime,'12/30/1899',101) arv_actual_date,
convert(datetime,'12/30/1899',101) dlv_actual_date,
convert(datetime,'12/30/1899',101) rcv_actual_date,
convert(datetime,'12/30/1899',101) idc_actual_date,
convert(datetime,'12/30/1899',101) bkd_actual_date,
convert(datetime,'12/30/1899',101) aci_actual_date,
convert(datetime,'12/30/1899',101) dlt_actual_date,
convert(datetime,'12/30/1899',101) lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN convert(datetime,'12/30/1899',101) WHEN 1900 THEN convert(datetime,'12/30/1899',101)
ELSE act_finish
END,
convert(datetime,'12/30/1899',101) ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end|||

I can't see where exactly the problem is by looking at the work workaround that you implemented (at least with a first glance). Is it possible to reduce the query to a small statement that shows that NULLs are treated differently between the two versions of SQL Server ? That will help us investigate the problem.

Thanks a lot,

Leo

query needed for path navigation through a web site

hi experts,
i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
is this possible and if not, what are the other approches that can i make to get the report i want.
also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
thanksHow do you determine the order of the pages visited? Are you storing timestamps or incrementally increasing IDs?|||hi,
thanks for the answer, actually i'm storing also the timestamp, and i think you have a point there, a timestamp is a good way to know the order, but i think a lot of information will be stored in the database if i decided to store the timestamp, do you have any other approach because at this time we can not afford tos tore a lot of information?
thanks|||A datetime column is not going to take up a lot of disk space. I can't think of any solution that would be cheaper in terms of bytes except an auto-incrementing identity surrogate key, and this is not the intended use of surrogate keys.|||there are plenty of 3rd party apps that do all this - you are reinventing the wheel if you code it yourself.

have you looked at google analytics?
http://www.google.com/analytics/|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.

what about the report for showing the returning visitors?
thanks|||I use google analytics to analyze traffic on my website. I find it very good, and it's free. You can do all sorts of stuff, like see where people are referred from, most common navigation paths they take through your site, etc.

Did you look at the link I provided?

also there are a ton of 3rd party apps for parsing web logs, many are free and the ones that aren't almost always have trial versions. all you need to do is look for them:

http://www.google.com/search?q=web+log+analysis|||ok.
blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
jezemine, can you explain more.

what about the report for showing the returning visitors?
thanks
Okay, assuming you have the timestamp that will give you the order in which the pages were visited. Then you just need a way to differentiate between the different people/logins/connections that visit each page. What is your method of accomplishing this?|||thanks for your answers and sorry for being late to answer back.
i have all th information stored about the user, i have sessionid,siteid, i did not think of it yet, but in your point of view which one is the best?
any ideas?!!!
i will take a look also to the link you give me.
thanks|||So can't you just sort your data by sessionid and timestamp to get the order of the pages visited?|||hi,
can you give me an example or a query how to do it?
thanks|||select * from YourData order by SessionID, TimeStamp

What is the problem here?|||it seems too easy but i think you are right.
i will work on the report and i will post back if there is a problem, thaks for your help.
what about the report to store the IP's any idea about that.
thanks again and have a nice week end

Query Limit?

Is there a limit to the size of the query for a report? I can create simple reports but when I paste in a larger query (say about 4500 characters and we will need larger queries) and try to execute the query, the report hangs in the designer. The source is a SQL Server 2000 database and this particular query only returns about 400 rows.

Thanks in advance.

Scott Lezberg
DeltekI've worked with RS2000 and the query limit is 32KB of plain text. For if I need a bigger query I create a view (or an SP, whatever you like most) and then I call it from RS.
Since I haven't used RS2005 I couldn't tell if the query is limited to 32KB.

I hope it helps.

Query Limit?

Is there a limit to the size of the query for a report? I can create simple reports but when I paste in a larger query (say about 4500 characters and we will need larger queries) and try to execute the query, the report hangs in the designer. The source is a SQL Server 2000 database and this particular query only returns about 400 rows.

Thanks in advance.

Scott Lezberg
DeltekI've worked with RS2000 and the query limit is 32KB of plain text. For if I need a bigger query I create a view (or an SP, whatever you like most) and then I call it from RS.
Since I haven't used RS2005 I couldn't tell if the query is limited to 32KB.

I hope it helps.