If I am totaling fields by groups of rows and I do so for every group do I need to use a stored procedure or cursor for this? I don't have a lot of experience with these areas but will give it a go based on what I find out.
I am trying to total the "error code" fields for each Branch. Of course, some don't have any, some have multiple errors. If a stored procedure is the only way, it will be a problem as our company's DBA has not given me permissions to run SPROCs. Is there a way to do this in a query?
I have been trying to figure out a subquery for this and it is not working.
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
I have a table named pop. This table has 2 fields, field codes with articles and other field with details of those articles. I intend to make a count to items that have more than 1 detail. In addition to this result is possible result for example:
Detailarticles 64 535 4111 3523 22207
the first line says I have 4 articles with 6 details.
which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?
I tried:
SELECT LoanNo, SUM(COUNT(LoanNo)) FROM DedupTest031504 GROUP BY LoanNo HAVING COUNT(LoanNo) > 1;
but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table: Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields: select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
SELECT a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count FROM accounts a left outer join location l on l.locationid=a.locationid left outer join providers e on e.[ProviderID]=a.attendingmdkey where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01' and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname order by a.AttendingMdKey
I have the following table structure.group1 group2 group1_result group2_result'One' 'Two' 3 2'One' 'Two' 3 1'One' 'Two' 2 5'One' 'Two' 4 1'One' 'Two' 0 5I need to sum up the number of times 'One' is greater than 'Two', andvice-versa. For example, the result I would like to achieve is asfollows.group1 group2 group1_total group2_total'One' 'Two' 3 2I'm using the following SQL statement, but I get 5 rows returned,giving me a '1' or '0' for each row.select group1, group2,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END),sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)FROM table1GROUP BY group1, group2Any help would be greatly appreciated.
For example in a table with this fields "field1, L1,L3,L100" field2 the count is 3
it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.
is this the best way to do it?
Select count(*) from Information_Schema.Columns Where Table_Name = @Table AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1
I have matrix report to display gender statistics based on hierachical geographic data e.g. Country 1 | region 1 | subregion 1 | No-of-males | no-of-females with drill through enabled
I want to have persentage near the number-of-gender as well as total population for a row, like this Country 1 | region 1 | subregion 1 | No-of-males (%-males) | no-of-females (%-females) | Total in the row
but I cannot find the way to do it.
Expression for data cell is =sum(Fields!no_of_person.Value)
Hi, I've looked in vain for an answer to this, and it seems like it should be simple. Some new fields have been added to a table, and I need to add them to the Report Model. When I go to the data source view, the new fields do not show up in the table. Is there an easy way to get the new fields to show up, or do I need to delete the table (and of course the relationships), and then add it back. Thanks for any help.
I am designing a report that creates a letter to send to a named individual. To accomodate different address lengths, additional fields have been added to the db. Is there a method by which if an address filed is empty, it does not display in the redered report and the fields below it are moved up to close the gap?
I have placed a textbox in the pageheader section of the report. In the textbox expression, I am trying to use a field . THis is an example of the expression in that textbox.
=First(Fields!ID.Value, "Jobstat") Howver it gives an err "THE Value expression for textbox referes toa field. Fields cannot be used in page headers or page footers"..Is there any other option that I have? I need this expression/field to be a part of page header, but not table header. Also, pl note that I have tried to place the field expression in a text box in the body of the report, and then refer to that text box in the page header or footer.However, when I do that, my expression does not appear on any pages of the report, only appears on the last page of the report.
Time columns go up to Dec 07. But I did not show all the columns.
The values are calculated as follows. Field value for Jan07 = (No of Loans pass due in Jan07 / Total No of loans disbursed in Jan07) Repayment due date is 5th of every month. if smbody does not pay on 5th, its considedred as pass due.
Source data tables look like this.
LoanTable(Disbursed date, userID, Amount, Status)
CreditTier table (Credit Tier, Rate) - Seems like no relationship with Loan table
we can identify pass due loans from status field in loan table. Status appears as 'Deliquency'.
I want to export records from the customer table then the final record needs to be a count of all records exported.I am always recieving an error - All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
SELECT CUSTTABLE.ACCOUNTNUM ,CUSTTABLE.NAME ,CUSTTABLE.BLOCKED ,CUSTTABLE.DATAAREAID FROM CUSTTABLE where blocked!='2' UNION SELECT COUNT(ACCOUNTNUM) AS TRAILER FROM CUSTTABLE WHERE blocked!='2';
Each different select statement works fine on its own but not when i combine them together with the union
I have a problem.... I have a TSQL procedure that calculates employee overtime based upon days 1-5 of week being "in-week", and days 6-7 oweek being week-end overtime. The resulting data is then mailed to dept managers. My problem: for some reason the calculated colums are being generated at a width of 40 char per column. I somehow need to reduce the width to 8-10 characters per colum (this will eliminate the wraping that I currently get in my e-mails)... Any help would be appreciated... Thanks..Tom
Is it possible to set up a report so that there's a column where the user can insert data of their own, which then gets saved to the underlying database?
What I'm thinking of is the ability to have a drop-down box at the end of each row of data in the report, whereby the user can select an option to specify the action they have taken in response to the data. Is this possible, and if so, how exactly would I achieve this?
hi friends we are looking at reports which have to be dynamically populated .. the problems faced are that the fields are so many that they dont fit horizontally in the same page ...so i have to make sure that only the fields permitted as per the report layout are shown on that page and the rest must be shown on the succeeding page.. what seems to be the solution ( maybe wrong ) that the somehow the way the report engine's methods can be overwritten but how? any other suggestions that are more straightforward are most welcome
I have a report (rdlc) in my WinForm project that the data are filtered in accordance with two dates: Initial and End. These two dates, the user inform in a Form of the project. Well, what I need I am to inform in the report these two dates. How that I make to pass these two dates of form for the report?
When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.
This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).
Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?
Perhaps I'm doing something wrong, or there is a workaround?
If anyone can shed any light in the issue, I'd greatly appreciate their comment.
When I'm building a report in report designer and get error message that says --error like --field textbox25--whats the easiest way to see all my report fields and quickly access them instead of clicking every field in a report?
I have a "report" that is more like a form with a great many fields on it that are not arranged like columns. The data is displayed in text boxes. My problem is that the "fields" or text boxes do not always form a nice single line where they connect - at least in the displayed format (HTML4.0), but actually do in printed format. It looks like the fields/textboxes may have a varying width, and possibly height.
Is there any way to make sure that the width and height are static?
I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far. I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial. So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field I also want to print the the same fields in the report total area. Any ideas ??
I have the following cursor that I am comparing 2 tables, theproduction table and a copy of the production table, I want results ofall address's that are like the address1 field...the problem is...myresults are giving me every field and if there is more than one, it isputting it in a grid....I only want to see results if they are 1 for the same address fieldthis is what I have so far....declare @address1 char(61),@city char(61)declare address_cursor CURSOR FORSELECT address1,city FROM test.dbo.testaddOPEN address_cursorfetch next from address_cursor into @address1,@citywhile @@fetch_status = 0BEGINselect * from testadd where @address1 like '%' + address1 + '%' and@city = cityFetch next from address_cursor into @address1,@cityPrintENDCLOSE address_cursorDEallocate address_cursor
I am trying to add some fields to my report (Using SQL Server 2005 Reporting Services, Visual Studio 2005).
As of now I can only use fields from one of my tables, or use functions within SQL DBMS. However, sometimes I wish to do more complicated things which can not be done (or at least I don't know how to do) withing SQL DBMS.
I wish to write a C# function (Web service) which takes a bunch of parameters and spits out some text. Is it possible to add a field to the report which calls this Web service, sends a bunch of parameters and displays the result of the function.
If not, Is there any way at all to manipulate some data using C# and display the result in the report.
When I've deployed a report model,in Report Builder I have 4 different entities at the same level,when I drag and drop the first field from one entity after I can't see the other three entities....What did I do wrong?? I don't understand ,if you could help me it would be great.
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.
I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.
This is all a generated query apart from the filter. If I change the @RegionParam back to its old constant value (which was 'Europe') then the fields appear again. I've set up the Query Parameters to cope with @RegionParam and also put a Report Parameter in the Layout page that uses it.
I'm lost as to why the fields don't appear with the parameter in there...
I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?
