So, here is my expression that I use in a textbox on the site header. In the report it just returns error... any ides?
="Wareneingangsübersicht" + " " + today() + " / " +
iif(datepart("WeekdayName",dateadd("d",1,today()))= "Samstag", DateAdd("d",3,today()), DateAdd("d",1,today()))
Hi,I have an Address table which contains more than one addresses for aparticular member. I want to write a query that would only displaymost current address. All addresses have a unique ID (addID).Example:memberID addID address1-------- ------ --------------------------------------------------295 69 13 Auster St295 70 465 Lorre Ct295 71 P.O. Box 321722 171 10 Hannaford Rd722 172 Dubai, United Arab Emirates
Quote:
Check out this simple query
SELECT 0.172 * (365 / (90 + 91)) * 1.26 AS test1
, (0.172 * 365) / (90 + 91) * 1.26 AS test2
, 0.172 / ((90 + 91) / 365) * 1.26 AS test3
test1 and test2 should give the same result, but they don't?! test1 gives wrong result; test 2 gives correct result. Furthermore, test3 gives a Divide by Zero???
This makes no sense.
(365 / (90 + 91)) is a constant I use many times in my code; only way to procede is to replace it by a number, or have i misunderstood something?
I am running the query on SQL Server 8.0.2040
Regards,
Martin
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?
sprintf(publ_doc_id, "%010u", id)
Our server is 64 bits OS. After SQL instalation we try to load the Reports web site and the server return the following error, can anyone help us?:
Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.Server
Error in '/Reports' Application.
Attempted to load a 64-bit assembly on a 32-bit platform. Use
ReflectionOnlyLoad() instead if trying to load for reflection purposes.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code.
Exception Details: System.BadImageFormatException: Attempted to load a 64-bit
assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to
load for reflection purposes.
Source Error:
An unhandled exception was generated during the execution of the current
web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[BadImageFormatException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0
System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211
System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141
System.Reflection.Assembly.Load(String assemblyString) +25
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32
[ConfigurationErrorsException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596
System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479065
System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46
System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177
System.Web.Compilation.BuildProvidersCompiler..ctor(VirtualPath configPath, Boolean supportLocalization, String outputAssemblyName) +180
System.Web.Compilation.ApplicationBuildProvider.GetGlobalAsaxBuildResult(Boolean isPrecompiledApp) +3446645
System.Web.Compilation.BuildManager.CompileGlobalAsax() +51
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +462
[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Compilation.BuildManager.ReportTopLevelCompilationException() +57
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +612
System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) +456
[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +3426871
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +88
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +149
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET
Version:2.0.50727.42
When I run some code through an odbc driver onto a SQL server 6.5 database, from vb an error is being returned along the lines of
22005 Syntax error converting 'string' to INT4
if I copy this code, either from vb or from sql trace, and run it in isql the code runs without any errors.
The only thing I have found is about NULLs so I have removed these from the code that is passed through?
Any more suggestions?
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions.
Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake.
Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error.
==============================================================
Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal
Balance', which does not exist. (SemanticQuery '').
----------------------------
An error has occurred during report processing.
==============================================================
If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails.
This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all.
FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
I'm new to Report Model and trying to create a new expression field with IF condition on related Role's attribute when i do this it gives error "The arguments to the following function are not valid: = (Equal to)" but if put direct Entity's column it works fine.. heres the expression
IF(Activity Type List Value Display Name = "Sick Day", 1, 0)
"Activity Type" is the Role(1--*) in Employee Table, and i'm adding this expression in the Employee Table
are there any sample models that i can download with some complex filters/expressions etc?
regards
faraz
I have a matrix report that has two columns, and one of the colums has the following expression for background color:
=IIF( Fields!Percentile.Value >= .10, "Yellow", "White")
Basically if the percent is greater than 10 highlight the field, for some reason i have some fields that dont show up yellow, see below:
http://duhaas.googlepages.com/percent.Jpg
How do I change the report title via an expression? I have a parameter that can contain 1 of 3 values and want to change my Report Title accordingly. Can I nest the IIF statements somehow or how can I accomplish this?
View 4 Replies View RelatedI am trying to pad a fixed number of trailing spaces into a report expression, as follows:
Data:
CUSTOMER_NAME-------------MichaelPeterJohn
Result (e.g. with 10 spaces padded, assuming all names are below 10 characters long):
"Michael ""Peter ""John "
Is there an easy way to achieve this ?
Thanks.
Kenny
My update query updates about 50 columns and they all have the sameformat: (Im using ADP to interact with the SQL Server)UPDATE dbo.GIS_EVENTS_TEMPSET VEH1TYPE =(SELECT VEHICLETYPEFROM VEHICLE AS AWHERE(GIS_EVENTS_TEMP.CASEID = A.CRASHNUMBER) AND(A.UNITID = 1)),VEH2TYPE = (....... same as above with UNITID = 2)VEH1DAM = (Same format different tables...)....nThe problem is that it's part of an import process that is dependent ondata from an outside source which come once a month.I found an error in one of the tables (a duplicate ID) which should notbe in the table this is causeing the "Subqurey returns more than onevalue" error.How can I guard against this..? If possible I would like it to onlyreturn the first value..I tried "COALESCE" but I couldn't get it to work I also tried "SELECTDISTINCT"....Any Ideas would be great.....
View 7 Replies View RelatedHi,
I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.
I am trying to code :
IIF(Value_A = 0, <NULL>, Value_A)
It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.
I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.
Thanks.
regards,
Kenny
Hi All,
I got a situation that need to write a expression for doing if the value is negtive then display () around this value, and this expression should apply to 30 fields in my report. so i just wonder is that any way that i can create this expression as global variable , then i can use this expression in each field, instead of i write IIF function in every field expression area.
Any helps are appreciated.
Cheers
Nick
$exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
Is there some example of how i can add a return status into my stored procedure to have an extra field where I can verify if it runs successfully
1 if success
-1 if error (server, etc)
0 if empty record return
Thanks
As part of configuring my SQL Server 2005 box to be able to use the xp_cmdshell function, I ran sp_xp_cmdshell_proxy_account to set up the credentials:
sp_xp_cmdshell_proxy_account 'DomainDomainUser', 'password'
Unfortunately, it just returns:
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.
I am assuming that this is an "Access Denied" error message. Has anyone got any ideas on why this is happening or what I can do to narrow down what the problem is. I can't find any logs with further information.
I have used a similar setup on another SQL Server 2005 box before and have never had a problem running the sp_xp_cmdshell_proxy_account function. I can't see what I have done differently this time.
Further information:
The SQL Server and SQL Server Agent services are running under the DomainDomainUser account.
I have given the DomainDomainUser the following privileges:
Act as part of the operating system
Log on as a batch job
Log on as a service
Replace a process level token
Let me know if you need any further information.
Thanks in advance for any help.
Ben
Hi,
I have a simple report written in SQL and I only want to show the TOP (2) records by date for each branch...
When i try and use the TOP (2) command it gives me only the very first 2 records...
my code is as follows:
SELECT REPLACE(CONVERT(VARCHAR, FilteredNew_Visit.new_startutc, 3), '/', '/') AS new_startutc, REPLACE(CONVERT(VARCHAR,
FilteredNew_Visit.new_endutc, 3), '/', '/') AS new_endutc, FilteredAccount.accountid, FilteredAccount.new_branch, FilteredAccount.name,
FilteredAccount.new_versionmbaname, FilteredAccount.new_versionwename, FilteredAccount.new_primarytsmname, FilteredNew_Visit.new_typename,
FilteredNew_Visit.new_name, FilteredNew_Visit.new_endutc AS Expr2, FilteredNew_Visit.new_startutc AS Expr1,
FilteredNew_Visit.new_accountid
FROM FilteredNew_Visit INNER JOIN
FilteredAccount ON FilteredAccount.accountid = FilteredNew_Visit.new_accountid
ORDER BY FilteredAccount.name, Expr1 DESC
can anyone help...i dont think i have explained this very well..
sorry total newbie
I need to use a non-queried report parameter to filter a dataset for a report.
The dataset column I'm filtering is numeric. The dataset is not a sproc, it's a table in SQL Server that I am querying.
The Non-queried parameter values (Multi-value) are 1, 2, 3, 4, 5, >=6.
Selecting the >=6 throws the error: "Error converting data type nvarchar to numeric."
Which sucks.
Because...when I go straight into my dataset I can filter my numeric column with the exact same values (=1 or =3 or >=6) and everything works fine. The error is only raised when I use the @Parameter in the dataset.
I've tried eveything, researched everywhere online and I can't find any guidance anywhere.
How do I use an expression to position a tablix on the report?
View 4 Replies View RelatedHi everyone,
I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.
Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).
Report has three groups Org , Assessor and Unit.
Can someone please help me with this?
Appreciate help.
Thank you,
Ski
Org 1(Group1)
Unit Credits Units Assessed(# of Trainees) TotalCredits
Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4)
Assessor 2 Unit 2 1 2 2 (1*2)
Assessor 3 Unit 3 5 2 10 (5*2)
Unit 4 2 1 2
Assessor 4 Unit 5 10 2 20
--------------------------------------------------------------------------------------------------------
11 42 -----------------------------------------------------------------------------------------------------------
Org 2
Assessor 3 Unit 1 2 3 6
Assessor 4 Unit 6 1 10 10
--------------------------------------------------------------------------------------------------------
13 16
--------------------------------------------------------------------------------------------------------
Hi
Bear with me I am creating my first report using sql server 2005 report wizard.
I have a query that s runs OK and returns name/addresses of companies for me to write a statement report.
My question is , how can only the 'FILLED' address column get displayed onto report and empty address fields are rejected i.e.
Address 1: 12 WoodStock Road
Address 2:
Address 3: Oxford
Address 4:
Address 5: OX10 0AB
therefore the report (page) should only display Address 1, Address 3 and Address 5
I tried to solve by trying to write an expression in 'Edit Experssion' without any joy.
Thanks in advance
Product version : SQL Reporting Service 2005 with SQL SP2
It's a report with 3 sub-reports in it, i want to display only one of the 3 sub-reports at a time depending on 1 or 2 parameters received by the parent report. These parameters are verified by an expression into the visibility tab of each sub-report. The two parameters are 2 lists with possibles values between 1 and 2 (not query based). I have verified these values and they are correctly received by report depending on the selection of each list.
Ex.: SubReport1.expression = IIf(Parameters!Regroupement.Value = 1, True, False)
SubReport2.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 1, True, False)
SubReport3.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 2, True, False)
So here are the posibilities :
when Regroupement.Value = 1 --> SubReport1 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 1 --> SubReport2 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 2 --> SubReport3 will be shown
Now why that doesn't work ? I always see the same report and it's not the right one displayed even i change the selection of my 2 lists for the parameters value.
Hi All,
When I use the following I get an error because of duplicate records in my table.
Update person
Set username = (Select update_person
Hi All,
When I use the following I get an error. I think it is because of duplicate records in my table.
Update person
Set username = (Select username
From update_person
Where person.firsname = update_person.firstname
and person.lastname = update_person.lastname)
There are a few users that have the same first and last name. How can I ignore the duplicate records and continue to update the table?
Thanks in advance.
Guys,
could someone please tell me : am I supposed to use the OLE DB
Command in a dataflow to call a stored procedure to return a value? Or
is it just supposed to be used to call a straightforward insert
statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a
dataflow I would like to effect an insert of a record to this table and
retrieve the identity value of the inserted record... and I'd like to
store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by
clicking on Add Column) I just get an error dialog that says "the
component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ
I have the next code in my proyect:
If CurrentProject.IsConnected Then
MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly
If SQLConexion.State = adStateOpen Then SQLConexion.Close
SQLConexion.Close
End If
SQLConexion.Open SQLSentencia
AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
suggestions?
I googled on the latter error number and got nothing back
Error: 0xC0048006 at Set Destination File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x016F9EA4.
The former only returns one result which does not explain my problem
Hi peoples,
I have created a custom CLR user define aggregate function based on the example that i found at http://msdn2.microsoft.com/en-us/library/ms131056(SQL.90).aspx.
It works great until i discovered that it will failed if i try to do either one of the following:
query a large records e.g: more than 4k records
has IS NULL in my where clause e.g: WHERE myConcatenatedFld IS NULL
Other than these two, it works perfectly fine.
Here is the error that i got:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have attached the Concatenate class that i used in my UDAGG.
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined, //use custom serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
private const int MAX_STRING_LEN = 4000;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
//Dont do anything if the input is null or empty
if (value.IsNull || value.Value.Length == 0)
{
return;
}
//Skip concatenation if the output exceed 4000 chars
if (this.intermediateResult.Length + value.Value.Length > MAX_STRING_LEN)
{
return;
}
//Concatenate output
if (!this.intermediateResult.ToString().Contains(value.Value.Trim()))
this.intermediateResult.Append(value.Value.Trim()).Append(", ");
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
//Skip concatenation if the output exceed 4000 chars
if (this.intermediateResult.Length + other.intermediateResult.Length > MAX_STRING_LEN)
{
return;
}
//Concatenate computed output
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 2);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Anyone has any idea on how to solve this issue?. Thanks
Hi AllWondered if you could help.Basically I have 2 tables that contain all the data I want for my report,but I need to put it in a particular way and I need to display it in an ASPpage so my queries got to be manual rather than an MS Excel/Query 'munge'.To be honest, the report itself is very basic, so hopefully my ramble willmake sense. I need to report that shows 6 columns at best, 4 columns ifit's easier. Report looks like the following:Account Code - standard text fieldAccount Name - standard text fieldTurnover (Year to Date) 2002 - standard decimal number (single??) fieldTurnover (Year to Date) 2003 - standard decimal number (single??) field(not vital, but would be nice)Deviation as a straight figure between what the 2003 turnover figure isagainst the 2002 figure - ad hoc calc fieldDeviation as a % between what the 2003 turnover figure is against the 2002figure - ad hoc calc fieldMy table structure is as follows:TABLE 1 - ACCOUNTSThis contains all of the account codes (PK) and names as ACCOUNTID and NAMErespectively. It also contains a field called LEDGER which I would need touse in a Where clause to set this to 1 (1 = sales and 2 = purchases).TABLE 2 - ACCOUNTBUDGETSThis contains the Turnover details and is linked to the ACCOUNTS table by anACCOUNTID (FK??). This table contains a TURNOVER field for the actualturnover amount, a PERIOD field for the months of a year (eg, 1 - 12 - thatbeing Jan to Dec) and a YEAR field for the relevant years.In this ACCOUNTBUDGETS table it basically keeps a line-by-line record ofeach turnover for each period and year for each account code. As youprobably would with any transaction line storing table.An important part of this is that I want to show all of the Account codesand names that relate to LEDGER=1 even if they have no entries in theACCOUNTSBUDGETS table. The reason being is that I want to be able to showthat an account might not have traded for 2002 and therefore their 2003 Yearto date might be really good cos they've sold loads in 2003.So the final part is that I need summarise the Year to date figures next toeach account code and name. To do this I need to take what the month numberis now (eg, Dec = 12) and summarise all the turnover figures for 2002 upto12 and then all the turnover figures for 2003 upto 12. If you can imaginewhen this report is run in March the report would summarize from 1 to 3rather than 1 - 12 in the last examples.The nice bits on the end would be that they would give 2 different calcsagainst what the 2 turnover figures stated.The result should be something like the following:December Turnover YTDDeviationAcc No: Account Name 2003 2002 £%A001 Alpha Motor 9,250 5,398 3,85271%A003 A J V Secure 094 -1,294 -100%A004 Aye Gee 039 -4,239 -100%A007 P H Allin 2,23508 -1,973 -47%Any ideas??RgdsRobbie
View 3 Replies View RelatedIf you look at the General Properties for a report in Report Manager, there are 4 properties listed at the top:
Modified Date:
2/26/2007 6:37 PM
Modified By:
DOMAINusername1
Creation Date:
2/14/2006 5:19 PM
Created By:
DOMAINusername2
What I'm looking for is if there's a way to retrieve these properties via an expression from a textbox within a report. For instance, there's already an expression that is:
=Globals!ReportName
I know it won't be that easy for the CreatedBy, CreationDate, ModifiedBy, and ModifiedDate properties, but I thought I'd ask if there was a workaround or hack.
We're wanting to put that info into the footer of all our reports. (Actually this is for Report Builder reports, but you can still use expressions in Report Builder reports, so it should apply as it would to normal reports.)
I have a pretty complicated report (lots of subreports, tables, etc) that can be well over a few hundred pages long. I can generate the report just fine, but I cannot seem to export it to anything other than an html archive. I need to export to PDF. Every time I try to export it get an error that says:
An internal error occurred on the report server. See the error log for more details.
I don't get any more information than that. Is there a way that I can figure out what the actual error is, or any other way I can go about troubleshooting this? Thanks.
Jeff