MAX and DATEDIFF: record

I am trying to find out the last record in a table called Asgnmnt using HeatSeq. Trying the below code producess an error stating "A boolean is required here."

code:
{@.Date Convert} = {?Data Select} and
{CallLog.CallStatus} in ["Closed", "Solved", "Suspended", "Implemented"] and
maximum({Asgnmnt.HEATSeq})

Does anyone know how to solve this?code:
{@.Date Convert} = {?Data Select} and
{CallLog.CallStatus} in ["Closed", "Solved", "Suspended", "Implemented"] and
maximum({Asgnmnt.HEATSeq})

what to do with the maximum value. With which value or record you want ot compare it to get the result. curently it will return the maximum value but you need boolean so compare it with any value to get the boolean then your function will work.|||I want to create a report that displays the closed tickets of the dates selected. Right now I am getting all the closed tickets, but if the ticket has been reassigned to another person, I get mutliple ticket showing in the report. I only want the last ticket to be displayed.

what to do with the maximum value: I want to display only this record. The end result should be to display the last record in the array.

With which value or record you want to compare it to get the result: I want to compare each record and only display the last one.

How can I introduce a boolean to compare the maximum value to?|||First: Can you select the desired records by a simple SQL query ?

If you can then apply that in the record selection formula.

You can do this as e.g ticket_date=current_date AND ticket_status = 'Closed'

Then the report will select the the records accordingly then you can sort them of your choice.|||I don't think introducing SQL to the report is necessary. The field HEATSeq shows an number for every re-assignment. If I can figure out how to get the largest HEATSeq number this will resolve my problem.|||I have tried to entering the below SQL code into the report, but it does not display the last ticket. I have tried to run the same below code directly on the SQL database and it does display the last ticket. any ideas?

select * from dbo.Assgnmnt a
where a.heat = (select max (heatseq) from dbo.asgnmnt b where
b.callid=a.callid)
order by callid|||You can add the SQL Query directly in the report by clicking Add Command in the Database - > Set Datasource Location Dialoge.

And then paste your query there.|||I tried that, but that code that I used did not give me the last ticket. Should I take out the other code that I have in Selection Expert?|||Go ahead.

Create a new report only with this query to see how many records are fetched by Crystal Report from the database.

Then you can filter out the unwanted records using the Record Selection Formula or supressing on the report.|||The updated code with SQL looks like this:
{@.Date Convert} = {?Date Select} and
{CallLog.CallSource} = "Web/Chat Room" and
where a.heatseq = (select max(heatseq) from dbo.asgnmnt b where b.callid=a.callid )
order by Asgnmnt.GroupName

I get an error. Message: "A number, currency, amount boolean, date, time, or string is expected here." - system highlights the word "where"

I can easily get the max and min values of a dataset, but I want to get
another value in the record associated with that max or min. For example, I
am using a stored procedure to get average number of minutes per day of
week. I would want to get the min value, plus the day of week associated
with that min value. How would I go about that in reporting services?
Thanks.Using SQL:
SELECT ShipCity, 'Minimum Freight' = MIN(Freight)
FROM northwind..orders
GROUP BY ShipCity
ORDER BY 1
Using RS: See the rdl attached below that runs against the local Northwind
database.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Ship City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Minimum Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ShipCity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>ShipCity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ShipCity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Min(Fields!Freight.Value,
"table1_Group1")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ShipCity.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!ShipCity.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
</Table>
</ReportItems>
<Style />
<Height>0.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.875in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ShipCity">
<DataField>ShipCity</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT ShipCity, Freight
FROM Orders</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||The sample report at the end of this posting should be close to what you
need.
The key idea here is to test if the current field matches your minimum. If
that is true the display the associated field:
=iif(Fields!OrderTotal.Value = MIN( Fields!OrderTotal.Value),
Fields!OrderDate.Value, "")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
ShowAssociatedFieldBasedOnMin.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>If Min Order Show Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderTotal">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderTotal</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderTotal.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!OrderTotal.Value = MIN(
Fields!OrderTotal.Value), Fields!OrderDate.Value, "")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>3a58aeb0-8249-40c7-a202-4970a3e67640</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderTotal">
<DataField>OrderTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 Orders.OrderID, [Order
Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate,
[Order Details].UnitPrice * [Order Details].Quantity
AS OrderTotal
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY OrderTotal</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>663ad6a0-1d1d-4fd1-a456-dde00c15a5f1</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Monday, March 19, 2012

Maximum Record (Row) lenght?

Maximum Record

Friday, March 9, 2012

Maximum HEATSeq

Saturday, February 25, 2012

Max/Min

MAX and DATEDIFF

Blog Archive

About Me