Guys, I need your help here. I'd deployed a report(SQL 2005, VS2005)
it is working on my local server, but when I publish it in my
production server, all the hyperlinks aren't working anymore, what
could be the reason? (Both SQL 2005 already installed SP2)
I've checked the tags in local server which having the following
hyperlinks for example:
<a tabindex="1" href="http://links.10026.com/?link=javascript:void(window.open('../something.aspx?
vid=1&Language=en-
US','PopUp','width=520,height=300,scrollbars=yes,toolbar=no,status=no,menubar=no'))"
style="color:Blue" TARGET="_top">some values</a>
But those tags aren't exist in my production page, and the table only
shows values that can't be click(not a link, but blue colored and
underlined).
What happen?What you should do is just set the jump to URL to the expression:
="javascript:void(window.open('URL here')"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<heislyc@.gmail.com> wrote in message
news:1187245762.711978.161950@.i13g2000prf.googlegroups.com...
> Guys, I need your help here. I'd deployed a report(SQL 2005, VS2005)
> it is working on my local server, but when I publish it in my
> production server, all the hyperlinks aren't working anymore, what
> could be the reason? (Both SQL 2005 already installed SP2)
> I've checked the tags in local server which having the following
> hyperlinks for example:
> <a tabindex="1" href="http://links.10026.com/?link=javascript:void(window.open('../something.aspx?
> vid=1&Language=en-
> US','PopUp','width=520,height=300,scrollbars=yes,toolbar=no,status=no,menubar=no'))"
> style="color:Blue" TARGET="_top">some values</a>
> But those tags aren't exist in my production page, and the table only
> shows values that can't be click(not a link, but blue colored and
> underlined).
> What happen?
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Jump to Report - misdirection
I have created a baseline folder with several reports, which I use as a "link to" from production folders for a variety of departments. Moreover, I have used a "Jump To Report" configuration as a drill-through to another report. The problem is that when I click on the link it executes the report in the baseline folder versus the report in the production department folder. How do I direct the execution to the appropriate production department folder(s)? Thanks in advance for any assistance.
The link to the drillthrough report can either be relative to the current report, for ex:
detailsreport.rdl - which means we will look for it in the current folder, or
..\OtherFolder\detailsreport.rdl - relative to the path of the current report
or absolute:
/folder1/folder2/detailsreport.rdl - this path is relative to the report server root.
Either one of these can probably be made to work for your scenario.
Thanks
Tudor
|||I tried each option that you presented; however, I still don't get the results that I think should occur. I do, however, think we may be on the right track.
Let me restate the requirements a little differently. Let's say that I use the "Baseline" as the central storage area for all of my reports. Then, let's say I have two departments: Dept A and Dept B. Each department has its own separate folder with links to the same reports in Baseline; but, the parameters passed from each Department is somewhat different. For example, each would have a different department Id to distinquish itself.
Using this scenario and with the drill-through set as 'detailsreport.rdl', I would think that when executing the rdl from Dept A folder would use the detailsreport.rdl in Dept A folder. So far, this has not been the case for me. It executes the detailsreport.rdl in Baseline.
|||That is correct. If you use linked reports, the relative links will be relative to the reports that contain the definition, not the link.
Friday, March 9, 2012
Join with a Having clause -- having problems too
Could someone please help me with a query that I am trying to create or suggest a better way?
What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."
SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType
FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.
The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.
Brett is asking for more information on your table design.
blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.
The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.
Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.
I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).
Here is the table structure.
tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8
tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:
SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)
DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.
Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?
blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]
I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.
Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.
That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.
I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.
I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!
I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.
Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.
Thanks to all for your help!
You are the best!
What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."
SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType
FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.
The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.
Brett is asking for more information on your table design.
blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.
The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.
Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.
I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).
Here is the table structure.
tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8
tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:
SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)
DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.
Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?
blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]
I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.
Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.
That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.
I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.
I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!
I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.
Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.
Thanks to all for your help!
You are the best!
Subscribe to:
Posts (Atom)