Simple Sql Report
Jan 3, 2008
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
View 14 Replies
ADVERTISEMENT
Jul 20, 2005
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 Related
Apr 13, 2008
I have a report with a table and three columns. Whenever the data in one of the columns cannot fit on a single page it continues on the next page BUT, there is no header on the next page and the data in the other two columns repeats itself on the next page. (This behavior happens when I export to PDF)
Is this a known issue? I have tried every setting I could think of.
Thanks
View 7 Replies
View Related
Apr 2, 2007
Hi all, I've been building a set of traffic based reports on our website and I've run into a strange problem.
The reports are pretty basic, and up till now I've been really impressed with RS overall.
Recently I've added a StartDate and EndDate and since then the performance has gone from ~10 secs to ~10 minutes.
I've taken a really simple query from my reports. Running this query in Management Studio on the same data returns in less than a second. When its run from a test report with nothing else in it it takes ~1 minute. Even stranger when I run the same query with the same values for parameters inside of RS in the data view it takes less than 1 second. ARG!
SELECT COUNT(DISTINCT SessionID) as Occurences
FROM WebAppSummary
JOIN WebAppLocalizations
ON WebAppSummary.ClientIp = WebAppLocalizations.ClientIp
where FirstTime BETWEEN @StartDate AND @EndDate
The last line that was just added is this part:
where FirstTime BETWEEN @StartDate AND @EndDate
So whats going on here? Is this a really poorly performing query that management studio is optimizing but RS isnt? Is RS messing up the databind and getting a bunch of DSs instead of just one?
View 1 Replies
View Related
Jun 18, 2007
Experienced memebers,
I have what should be simple question about related tables in a dataset and using those fields in a report. Here is my simple design.
Order Header -> Line Item -> Line Discount -> Discount Table
Now when designing this report I cannot seem to access the related discount records for the line item. (as more than one can be applied) Every time I try to drag an field from one of the tables in the dataset to my report it automatically adds "First()" onto the field value expression. This seems to work fine for the order header, but doen't even come close to getting the right discount on the line item? I basically need to pass through the Line Discount table to get to the discount name field to put on the same line as my line item data on the report.
I have looked and googled for for an example on this, but have come up short. Can anyone point me in the right direction?
Thanks,
Chris
View 5 Replies
View Related
Apr 22, 2008
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()))
View 5 Replies
View Related
Feb 1, 2007
I've been running around in circles all afternoon trying to create one simple report using Reporting Services (with latest SP2 installed) and SharePoint 2007. To the best of my knowledge, I have everything configured correctly:
When I access http://<server>/ReportServer, I see the server name of my SharePoint site.
When I click on the name of my SharePoint site, it shows me the directory structure I have created within my SharePoint Site
When I drill down in the directory, I can ultimately see the forms I created in my forms library (created via InfoPath 2007).
The next step is to create one simple report from the data in one of these forms libraries and a report on all the items within a form library. I'm stuck at the first step of creating a report, namely what to enter as the Data Source and the connection string. With a SQL database this isn't an issue.
How does one create a data source that will allow reporting over SharePoint content with the setup described above? And, if you have information that is found in the SQL Books Online, please be kind and post links so others know where to find this information.
View 5 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Jun 16, 2006
SQL server 2005 express reporting problem.
error message:
This feature "remote access to report data sources and/or the report server database" is not supported in this edition of reporting service
I got this error message when I try to connect to database hosted in another PC running SQL server 2000.
Is it true that SQlL server Express can only use Local Database Engine to host the database?
View 5 Replies
View Related
Sep 9, 2015
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
View 5 Replies
View Related
Jun 30, 2006
After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
Any suggestions would be appreciated. Thanks
View 3 Replies
View Related
May 3, 2007
How do I jump to another report based on a value in my current report. The report that I am jumping from has no parameters, just values.
View 7 Replies
View Related
Feb 6, 2008
Hi,
When i select datasource in Report Builder, i am able to see all the available DataSources.
Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource.
when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3)
Is there any property or relationship do i need to maintain?
Thanks,
SR.
View 5 Replies
View Related
Sep 6, 2007
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
View 3 Replies
View Related
Oct 9, 2015
I have done the following and a domain user would not access report created a login to the SQL server to the user (this SQL Server is where data source DB is)went to site setting in Report Manager and made this use a system userright clicked on report folder and made this user in the browser roleeven checked that in the report in question, the user is already in the browser role Still the user would not access the report! "User .......... does not have required permission" is the error message I am getting.Â
View 6 Replies
View Related
Apr 30, 2007
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
View 1 Replies
View Related
Jul 5, 2012
I have written a report visual studio. Â The report has 10 multi-value parameters that pull data from their individual data sets within the reports. Â When running the report from within Visual Studio it renders fine. Â There are no errors reported, only a warning related to a pathname I have use to retrieve image data and display on the report.Deployment of the report is error free.When I view the report I briefly get the "Loading" splash window, but then nothing. Â None of the headers or static text is displayed. Â The results window is blank.
The report defaults all the parameter values. Â In trying to debug the issue I have found by reducing the number of parameter values the report will render. Â Once I have all the values added, the report does nothing.Below is the query being used for the report. Â The where clause in the query shows the parameters being used and the syntax.We are running on Windows Server 2008 R2 and SQL Server 2008 R2.
Ralph
SELECT RTRIM(a.ITEMNMBR) AS Style, RTRIM(a.ITEMDESC) AS Description, a.ITMSHNAM AS UPC, c.LOCNCODE AS Store, d.LISTPRCE AS ListPrice, a.CURRCOST AS Cost,
a.USCATVLS_1 AS [Main Category], a.USCATVLS_2 AS Market, a.USCATVLS_3 AS [Alternate Retail], a.USCATVLS_4 AS Country, b.ITEMXTRAS_1_Type AS Type,
b.ITEMXTRAS_2_Finish AS Finish, b.ITEMXTRAS_3_SubCategory AS SubCategory, b.ITEMXTRAS_4_Department AS Department,
[code]...
View 7 Replies
View Related
Aug 20, 2007
Here's tricky one.
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
Essentially, I want to create a for each loop.
Any ideas?
View 4 Replies
View Related
Nov 23, 2015
We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:
Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">CSV (comma delimited)</Name>
    </OverrideNames>
   </Extension>
   <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">Excel (csv)</Name>
    </OverrideNames>
   </Extension>
Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.
View 6 Replies
View Related
Sep 21, 2015
I have a ssrs report having 2 tables in with 4 columns in each. When I go to export option in preview I can see all data coming in one excel sheet, But I am trying to get 2 tables in 2 different pages in Excel when I export.First page of excel comes with first table data with 4 columns and second page of excel comes with second table data with 4 columns .
View 2 Replies
View Related
Apr 16, 2015
I am working on reports in SSRS 2008 (not R2)... There are some reports with parameters that are hidden when the report is accessed through normal URL using ReportViewer.asx..The thing is that these hidden parameters need to be visible when the report is accessed using SSRS Report Manager.
View 3 Replies
View Related
Nov 18, 2007
Here is a situation in my company:
We have an Office SharePoint 2007 site, we developed a couple of web parts and added them to our site. We used SQL Server Reporting services 2005 as our reporting solution. The reports are hosted on the report center and when we need a report, we open it by sending a URL requesting the report, passing the report parameters in the URL query string. So the report is filtered based on the parameters passed from the web parts.
But since best practices say that you should host reports directly under SharePoint, by configuring the reporting services to run in the SharePoint integrated mode. We followed the steps and installed SharePointRS and we finally succeeded to publish the reports to a SharePoint folder, but we had a limitation: we are unable to pass the report parameters internally to the report hosted in SharePoint. If we passed them in the query string as the report center case, SharePoint neglects them totally.
So the question is: how can I pass parameters internally between a SharePoint web part and a SQL server 2005 reporting services report hosted in SharePoint?
View 1 Replies
View Related
Feb 16, 2007
We have a remote business client who has a proxy server setup and this translates into receiving a 407 proxy authentication blocking error when attempting to download the report builder application for deployment off the report server which is housed locally with us. The proxy server does not allow them to download application type files off another site. At the moment, they would not like to disable the proxy for these users to allow them to deploy the application on their client or add the report server as a trusted site. Is there a method by which Report Builder can be locally installed on the client pc instead of online only with it knowing the correct report server to access and then still launch correctly when the report builder button is pressed through report manager?
I am not a web application guru by any means and we have tried to replicate the oneclick deployment and what occurs in the local settings/apps/2.0 subdirectories, but the application still attempts to download/install again. If we attempt to just launch the local executable on the client, report builder assumes the client pc (localhost) is the report server to access. Is there a method which will succeed?
View 1 Replies
View Related
May 5, 2008
I am extracting RS reports through a WebService:
report = execService.Render("EXCEL", null, out extension, out mimeType, out encoding, out warnings, out streamIds);
Some works fine but some are generating the following SoapException:
Error during processing of €˜ReportParameterTime€™ report parameter. ---> Error during processing of €˜ReportParameterTime€™ report parameter. ---> Error during processing of €˜ReportParameterTime€™ report parameter.
I set required parameters to their default values. In this specific report I have two required parameters:
1: ReportParameterTime, String
2: ReportParameter1, String
I set them to their default values (ReportParameterTime to "[Czas].[Kalendarzowy].[Rok].&[2007]" - means
Time Calendar Year 2007) but I am getting the exception above. I set parameters using this code:
execService.SetExecutionParameters(parameterValues.ToArray(), CultureInfo.CurrentCulture.Name);
Where parameterValues is a List<ReportExecution2005.ParameterValue>.
When I am using Microsoft.Reporting.WinForms.ReportViewer the report is generated without
any problems. I can see one parameter to choose: "Kalendarzowy" (means adj. Calendar) and can
select years, the default value is "2007".
What am I doing wrong and how can I fix this error ? Does anybody know ?
Regards,
Daniel
View 15 Replies
View Related
Jun 29, 2015
I'm wondering how to print a SSRS report without bringing up a report viewer. Is there way to print(PDF format) the SSRS report in the web application(Â .net ) Â directly from the "print" button without bringing up a report viewer?
View 2 Replies
View Related
Sep 19, 2007
Hi,
I'm trying to deploy Reporting Services on an Internet-facing web server using a custom security extension (forms authentication). We will be putting Report Manager on the Web Server outside of the firewall, and Report Server on another machine inside the firewall. When testing the solution in a development environment with Report Manager and Report Server on the same box, the solution works fine.
When testing it using the Internet-facing environment described above, it almost works but there seems to be an issue with the authentication cookie that is generated. Here's what happens:
1. User enters credentials on UILogon.aspx and submits form.
2. In the code-behind, LogonUser() is called through a web service proxy and the authentication cookie is returned successfully. The code used here to set the cookie is the same code used in the Forms Authentication sample that ships with RS2005.
3. The user is redirected to Folder.aspx, and the request hangs for a while, and eventually kicks you back out to UILogon.aspx, as if it lost the authentication cookie. When doing a trace on the HTTP header, the cookie is still there.
Is there anything special I need to do to make the Report Server "see" the cookie when I have Report Manager and Report Server on different machines?
Any help would be much appreciated!
Mike
View 2 Replies
View Related
Jul 17, 2007
Hi,
I am unable to launch report builder from the report manager from a client machine. But I am able to launch from the localhost.
Even I am able to launch report builder by clicking reportbuilder.exe on the local machine.
Please help !!
OS - Windows XP professional
SQL Reporting Services 2005.
View 1 Replies
View Related
Dec 15, 2006
hi
I have installed SQL Server Reporting Service on window xp. everything working fine except one thing.
I have installed sql server with my a/c. my a/c have admin rights. when i giving http://dineshpatel/reports it is displaying page but Report Builder and other option are not displaying. I hope i don't have admin rights.
I have checked with local administrator login also but same problem.
what additional setting are require for admin rights?
Dinesh Patel
View 10 Replies
View Related
Mar 18, 2008
Hello everyone,
I created a custom assembly using C# to transform some binary data into text, and in this assembly I used one win32 dll developed by our customer to help me to do the tranformation.
The code I used to call the win32 dll is like below:
[DllImport("tdasuie.dll", EntryPoint = "AlrtLogConditionToText",
ExactSpelling = false, CharSet = CharSet.Auto, SetLastError = true)]
private static extern UInt32 AlrtLogConditionToText(Byte[] pbCondition, StringBuilder pszText, UInt32 dwSize);
I defined a C# method to call the above win32 method and return a string. Then in the report, I called this C# method to get the correct string.
In the report designer, the C# method in the custom assembly can return the correct string in the preview window. But after I deployed the report into the report server, the textbox will only display "#error" in the report manager web page.
Can anybody help me on it? Thanks a lot.
Danny
View 4 Replies
View Related
Oct 28, 2015
I have a report builder drill down report. I have row groups with totals.   It looks like the attached.  The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
= Switch ( Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
MID(Fields!protocol_id.Value,1,7)="THERAPY", Â Â Â Â Â Â Â Â Â Â
                     Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value),      Nothing)),
 MID(Fields!protocol_id.Value,1,7) = "GENERAL" and                          MID(Fields!program_id.Value,1,6)
= "INTAKE", Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Sum(IIF(Fields!status.Value = "CO",
[code]...
Is there any way to not display the expression in the Total columns unless the report is expanded?
View 2 Replies
View Related
