Showing posts with label troubles. Show all posts
Showing posts with label troubles. Show all posts

Friday, March 30, 2012

problems with sum()over(order by field) syntax

Hello

I'm experimenting with window functions and I'm having troubles with the sum()over() syntax.

I've followed the syntax from many examples and i'm still getting this error. I can get it work with sum(field1) over (partition by field2) but every time i add order by i get an error

I have the following query, this is from the adventureworks sample

use adventureworksdw

select *, sum(scenariokey)over(order by amount) as test1

from FactFinance

and I get the following error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'order'.

Any suggestions.

Thanks in Advance

Daniel

When you use aggregation you have to use Partition By instead of Order BY.

select *, sum(scenariokey)over(Partition By amount) as test1

When you use Ranking functions then you have to use Order By & you can use Partition By if nessasary..

select *, Rank() over (Partition By OrderDate Order By amount) as test1

select *, Row_Number() over (Partition By OrderDate Order By amount) as test1

|||SQL Server 2005 only supports partition clause for aggregate window functions. The ranking window functions support partition and order by. So we implement only a small subset of the OVER clause as specified in the ANSI SQL standards.

Friday, March 9, 2012

problems with Exporting and Viewer

Hi, i'm having troubles using the ReportViewer to export to a pdf. Parameters are set by the user and (depending on the report) programmatically.
When i try to export in my code, i get a "Missing Parameter Values" error.
However, if i try to open a report with no parameters - the export works fine.

Can someone please help me out?

Here's my code:

this is the Export code:

<code>
private void Export4()
{
string ExportPath;
ExportPath = Request.PhysicalApplicationPath + "Exported\\";

if (Directory.Exists(ExportPath) == false)
Directory.CreateDirectory(Request.PhysicalApplicationPath + "Exported\\");

crDiskFileDestinationOptions = new DiskFileDestinationOptions();
crExportOptions = reportDocument1.ExportOptions;
crDiskFileDestinationOptions.DiskFileName =ExportPath + "PortableDoc.pdf";
crExportOptions.DestinationOptions = crDiskFileDestinationOptions;
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;

try
{
reportDocument1.Export();
}
catch (Exception err)
{
Response.Write("<BR>");
Response.Write(err.Message.ToString());
}
}
</code>

And this is the code that sets the parameters, report and Viewer:

<code>

public void LoadReport_Param(ref CrystalReportViewer crViewer, ref ReportDocument crReport)
{
TableLogOnInfo crTableLogOnInfo = new TableLogOnInfo();
ConnectionInfo crConnInfo = new ConnectionInfo();
CrystalDecisions.CrystalReports.Engine.Database crDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crTables;
ParameterDiscreteValue crPDValue;
ParameterFieldDefinitions crPFDefs;
ParameterFieldDefinition crPFDef;
ParameterValues crPValues;
try
{
crConnInfo.ServerName = _servername;
crConnInfo.DatabaseName = _dbname;
crConnInfo.UserID = _username;
crConnInfo.Password = _password;
crDatabase = crReport.Database;
crTables = crDatabase.Tables;

foreach(CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
{
crTableLogOnInfo = crTable.LogOnInfo;
crTableLogOnInfo.ConnectionInfo = crConnInfo;
crTable.ApplyLogOnInfo(crTableLogOnInfo);
}
crPFDefs = crReport.DataDefinition.ParameterFields;
for (int intParamCounter = 0; intParamCounter <= crPFDefs.Count-1; intParamCounter++)
{
try
{
crPFDef = crPFDefs[intParamCounter];
crPValues = crPFDef.CurrentValues;
if ((_GroupABBR != "") || (crPFDef.ParameterFieldName == "Branch") ||
(crPFDef.ParameterFieldName == "@.Branch")|| (crPFDef.ParameterFieldName == "?Branch") || (crPFDef.ParameterFieldName.ToLower() == "branch"))
{
crPDValue = null;
crPDValue = new ParameterDiscreteValue();
crPDValue.Value = _GroupABBR;
crPValues.Add(crPDValue); crPFDef.ApplyCurrentValues(crPValues);
}
}

catch (EngineException engEx)
{
throw new Exception(engEx.Message);
}
}
crViewer.ReportSource= crReport;
}
catch (LogOnException engEx)
{
throw new Exception("Incorrect Logon Parameters. Check your user name and password.");
}
catch (DataSourceException engEx)
{ throw new Exception("An error has occurred while connecting to the database.");
}
catch (EngineException engEx)
{
throw new Exception(engEx.Message);
}
}
</code>Did you ever get a result with this? I am having the same problem. I have watched every single ParameterField in my ParameterFields collection and where there isnt a value, I set one - then as soon as I do ReportDocument.Export I get this "Missing Parameter Values" exception.|||See if you are able to find solution here
www.businessobjects.com