In my Report using SSRS 2005, I am displaying data using table and this table has table footer in which I am calculating "average" using Avg() function.The values in table rows can be either floating numbers (for eg; -1,-99,5.005) or "N/A". I use the following function to calculate Average
=Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS")
where "Fields!Channel1.Value" are values in table rows.
Everything works fine if all the table rows are floating point numbers and not "N/A", but as soon as table rows values are "N/A" I encounter "#Error" in the table footer.
Can anyone figure out how to get this code working. Thanks in advance.
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17 test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused?
Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM');
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
HI, I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed for this i need a table and procedure to complete family tree Thanks @mbi
I am using LIKE operator for a query to get wild card searched data.
for example if i give AN, should get for %AN% only. But it is returning data for %NA% data too. I cross checked by giving simple data. Please help me on this.
I have a table in one database (source) and an equivalent table in another (destination) and I need to move from my source to my destination database.
When I move the data I need to do the equivalent of an updategram, so for instance compare primary keys and if it exists in both do an update, if it is in source but not destination do an insert, if it is in destination but not source do a delete.
Is there already a process for doing this, as it seems pretty fundamental but I've not really found anything that gives the result I need?
If not, is there a way of doing this with what is available within Data Flows or should I just go ahead and go through the pain of creating my own data flow destination object?
I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think
but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0
very strange..
doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case
i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...
more magically appear when hte same statement is ran in SSIS!!
I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here) the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring. I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID. FirstNextPreviousLast I am using a repeater to display the photo with the following sqlDataSource: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters> </asp:SqlDataSource> I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
I'm not seeing any built-in functionality for Report Builder to get Top N values. Am I missing something? Is there a way to do this? If not, is there a way to simulate this functionality?
There is a view on our server that has fields for project name and then 25 months, each row contains the descriptive name and the forecasted budget in each month. I need to create a chart showing the total budget for month and a running total for the year to date. In Excel this is easy with a Pivot Chart, how do I do this in SSRS 2005?
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
I have a doubt regarding use of navigation controls in a report and exporting the report. For example using a drilldown functionality in a report. After the report is generated one can export the report in whatever format he wants (PDF,TIFF, XML,...).
In this case, with the drill down functionality in the report, the details of the report are visible only when the plus sign is clicked. Suppose now the plus sign is not clicked and one exports the report in a PDF or TIFF format.
In the PDF file, the navigation or the drill down functionality is now not useful right ? There is no way to view the detail fields of the report now, using the PDF file right?
see article: Article ID: Q216700 . Within a trigger on SQL Server 6.5 Service Pack 5 or 5a, the IF UPDATE clause will evaluate to true for all columns when an INSERT is performed, even if there is no value specified and no default value exists for the column
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
We had a minor data loss snafu (nothing major, and easily recoverable with our backup strategy), but I don't seem to be able to use the ol' DBCC LOG trick to get insight about when the deletion occurred. Obviously, since the log was backed up and truncated at least once since the deletion, there's nothing in the log file to analyze. I've tried restoring to a temporary database, but hit a couple of snags. First of all, running DBCC LOG on a database that's in standby mode simply shows the logged activity of the recovery process, and not any of the activity that was in the original transaction logs. Furthermore, running DBCC LOG against a database that's in recovery fails pretty ungracefully with a severity 20 error (I obviously didn't care to experiment with that approach any further).
Is there any way I can peek into the original transactions contained within a series of full and log backups to get a little who/when information? Or is this something that only Lumigent can do? I don't mind really ugly hacks here, since I'm simply doing read-only business with the backups. I'm not scared of a hex editor if it comes down to it.
As so rightly stated in http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside6.mspx
"Currently, UDDTs don't support the notion of subtyping or inheritance, nor do they allow a DEFAULT value or CHECK constraint to be declared as part of the UDDT itself. These powerful object-oriented concepts will likely make their way into future versions of SQL Server. These limitations not withstanding, UDDT functionality is a dynamic and often underused feature of SQL Server."
There's no point bothering with using a UDDT in T-SQL if you can't apply constraints to the UDDT!
1 vote for the introduction of the mentioned features into T-SQL
I have a reporting server that needs to be utilized now but the person that set it up is no longer with the company. We have no idea what login he used to install the server and nobody was granted admin rights to the server. is there anything we can do to give someone admin access without having to reinstall (I don't think that is an option)??
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
I have a problem getting the full-text search service working on my SQL Express install.
I can see the "SQL Server FullText Search" service running in the SQL Server CM but when I run: select fulltextserviceproperty('isfulltextinstalled') it returns false.
Can anyone explain what I must of missed or point me in the right direction?
When i am calling the Report from ASP.NET getting the below error: An error has occurred during report processing. Query execution failed for data set 'dsOpenDefect'. For more information about this error navigate to the report server on the local server machine, or enable remote errors
SP:
@DefectImpact varchar(max) = '', @DefectNumber nvarchar(255) = '', @DefectApplication nvarchar(255) = '', @DefectBusinessFunction nvarchar(255) = '', @DefectRelease nvarchar(255) = '', @DefectStatus nvarchar(255) = '', @Owner nvarchar(255) = '' AS set concat_null_yields_null OFF Begin
if @DefectImpact <> '' or @DefectImpact IS NULL begin set @whereClause = ' where DefectImpact ' set @whereClause = @whereClause + ' = ''' + @DefectImpact + '''' end
set @sqlWhere =@sql + @WhereClause
PRINT @sqlWhere EXEC(@sqlWhere)
End
ASP.NET Code:
ReportParameter[] parm = new ReportParameter[7]; parm[0] = new ReportParameter("DefectImpact", txtValue3.Text.ToString()); parm[1] = new ReportParameter("DefectNumber", txtValue3.Text.ToString()); parm[2] = new ReportParameter("DefectApplication", txtValue3.Text.ToString()); parm[3] = new ReportParameter("DefectBusinessFunction", txtValue3.Text.ToString()); parm[4] = new ReportParameter("DefectRelease", txtValue3.Text.ToString()); parm[5] = new ReportParameter("DefectStatus", txtValue3.Text.ToString()); parm = new ReportParameter("Owner", txtValue3.Text.ToString());
I am creating a simple report in reporting services. It contains basic fields such as first_name, last_name, ID#, location.
Is there a way to create a search/find function that lets me input a field and it will find the results from any column?
I think I can figure out how to search by 1 single column but want the user to input one thing and search multiple fields.
For further clarification...if they know the last_name, but want the id#, they can search for last name and get all the columns for the particular search....same would work for, if they know the ID#, but want the last_name, they could search for ID# and get all columns in the result.
