Report Conditions 1

[charbrad]

I have a report that gives total pounds produced in a day. My question is if there is a way to add a condition to my report so that if totallbs = 0 the report will not generate? This is ASP code and it is a report file being written to the page as HTML.
Below is the SQL Query used in the report

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT Item) ItemsProduced, COUNT(MO) JobsClosed FROM ReportsMOVariance (NOLOCK) WHERE CloseDate = '" & ReportDate & "'"
rs.Open SQLQuery, MyConn 
p=rs.GetRows()
rs.close

 

[cLFlaVA]

you can do a test for EOF...

If Not rs.EOF Then
    p=rs.GetRows()
    ' other code to produce your report...
End If

*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[monksnake]

Are you asking if all your select columns that have SUM on them add up to 0??

I'm not sure what all fields constitute the total pounds.




<.

 

[cLFlaVA]

ahh, i may have misunderstood..



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[charbrad]

Sorry for not being clear.....I am very, very new to ASP. What I can see from the code, yes if all select columns that have SUM on them add up to 0.

 

[monksnake]

Put that in your SQL statement. It'll look like a long mess, but it's the most efficient way and should be fast.

When messing with ASP and SQL, the best way is to try to get your SQL to do as much of the work ar it can for you.

SQLQuery = "SELECT ISNULL(SUM(LaborVariance),0) LaborVariance, ISNULL(SUM(MaterialVariance),0) MaterialVariance, ISNULL(SUM(LBSProduced),0) LBSProduced, CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END Yield, COUNT(DISTINCT Item) ItemsProduced, COUNT(MO) JobsClosed, [!]ISNULL(SUM(LaborVariance),0) +  ISNULL(SUM(MaterialVariance),0) + ISNULL(SUM(LBSProduced),0) + CASE WHEN ISNULL(SUM(ConsumedQTY),0) = 0 THEN 0 ELSE ISNULL(SUM(LBSProduced),0)/SUM(ConsumedQTY)*100 END totalPounds[/!] FROM ReportsMOVariance (NOLOCK) WHERE CloseDate = '" & ReportDate & "'"

Then open your recordset and only create a report if totalPounds > 0.

rs.Open SQLQuery, MyConn 
if (rs.RecordCount && rs("totalPounds")) then
   p=rs.GetRows()
   rs.Close
   //Build report here
else
   rs.Close
end If

<.

 

[charbrad]

I am getting this error....

"Operation is not allowed when the object is closed

 

[monksnake]

Somewhere in your code, you are trying to pull a value from the recordset AFTER you do an rs.Close.

I'm assuming you are pulling the data "on-the-fly" to create the report. So in that case, you won't use the statement

p = rs.GetRows()

Change your code like so

rs.Open SQLQuery, MyConn 
if (rs.RecordCount && rs("totalPounds")) then
   p=rs.GetRows()
   //Build report here
   [!]rs.Close[/!]
   put the close AFTER you build the report
else
   rs.Close
end If

This change is all based on assumption. I'm assuming you are pulling data from the recordset as you are writing the report.

After you get this worked out, you may want to check out the .GetRows() function.




<.

 

[charbrad]

Thank you Monksnake...have a star for your brilliance.

 

[monksnake]

<.