I am facing some problem while grouping the fields..
I try to form one report where I get group by States then by city and then total count of states
like
State: A
city:1
No of ppls:
no of houses:
city:2
No of ppls:
no of houses:
city:3
No of ppls:
no of houses:
Total No of ppls in State A:
State: B
city:4
No of ppls:
no of houses:
city:5
No of ppls:
no of houses:
city:6
No of ppls:
no of houses:
Total No of ppls in State B:
'<Email> Â <Type>Personal</Type> Â <Code>1</Code> Â <Description>solange@nobre.com.br</Description> Â <LocalId>000000000000000000000000868000000001</LocalId> Â </Email>'
OUTPUT
'<Email> Â Â <Code>1</Code> Â <Description>solange@nobre.com.br</Description> Â <LocalId>000000000000000000000000868000000001</LocalId> Â Â <Type>Personal</Type> </Email>'
We have a designed a table report to show the hierarchy like "Locations -> Station -> Prod. category -> Product Name -> Product Model" and the report have some measures like Quantity in stock, Quantity sold.......
By default the report shows all the Locations with sum of all the measures, on expanding any location all the Stations are shown and on expanding Stations all the product names are shown and on expanding any product name all the product models are shown.
Now the requirement is, the same report should show the details of all the Product names in a single group, from all the locations and all the stations. And when i click on All stations link i should be shown the same report with all the stations under all locations.
i.e. the same report should have one row with the static text in columns like All Locations, All Stations, All Prod. Category, All Product Names and All Product Models. When the user click on All Product Names column the report should expand to show all the products in all locations and all stations.
I have looked for many examples but couldn't find such type of report, Is it possible to create such type of grouping using SQL server reporting services (MSSQL 2005). Pl. provide me a example to implement this type of requirement.
I have a question regarding a Language property on Report, Table and Cell levels. My reports must show monetary values in different formats depending on a currency symbol where the Client resides. For instance, money fields for USA, Canada, UK are shown as 123,456,789.00 and then "$" or "£" symbol; but European countries should have 123.456.789,00 format and a Euro symbol.
I have found that XXX.XXX.XXX,00 format corresponds to the Language property = "Italian". If I set the Language property = "Italian" on Report or Cell level at design time, the report shows the expected 123.456.789,00 format, no problem. (By the way, for some reason, on the Table level this property set does not work at all)
Unfortunately, I was not able to change the Language property to "Italian" on Report or Cell level on-fly using the following expression: =IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"English (United States)","Italian") For debugging, I even tried: =IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"Italian","Italian") But all numbers on the report are still shown in the 123,456,789.00 format regardless the Client's currency symbol.
I don't want to have 2 sets of my reports only because if the monetary format difference. And also I don't want to CAST the monetary value into a string and mask it myself with dots and commas.
I appreciate at advance any help or comment regarding the issue very much. This is a critical bug and it must be resolved ASAP.
Interactive sorting works just fine on my report until I set the execution property to render from snapshot. When viewing a snapshot, clicking on the column sort arrows will not sort the data I'm viewing. If I turn the snapshot feature off, then I get the interactive sort functionality back.
Is this by design or am I missing something fundamental here?
I am able to deploy and run reports from my screen at work, but some non-developers cannot run them. They can see the reports, but get the following error message when they click on the report:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors -------------------------- -----
- I assume the preceding error has something to do with credentials. I am using Windows security authentication for this. One user has worked through this and is now getting cannot run report query (one of the report queries in the report). Help would be appreciated.
I have a drill down report and would like to add interactive sort on two columns:- column 1 & column 2. These two columns should be in descending order. By adding this, when user's click on column 1 they will see the data based on the sorted order. Does anyone help me on this issue.
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC, case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC, case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC, case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end, case when @SortColumn = 'FileDataID' then FileDataID end, case when @SortColumn = 'Field2' then FileDesc when @SortColumn = 'VersionNotes' then VersionNotes end
case when @SortOrder = 'DESC' then DESC when @SortOrder = 'ASC' then ASC end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports, Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
I have an already published application running several MS SQL Server 2000 Reporting Services report. I need a way to find from either the reporting services log or the application server (IIS) logs or windows log to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend? How can I acheive this?
I have already got the IIS logs and it did not give the required info. I have looked into the Reporting Services logs but it does not provide the info either.
My issue is with converting multi-value parameters:
In Crystal Reports, you can set a parameter to accept multiple vales (Discrete, Range or Discrete and Range).
As an example:
I have a database table with a column called ID. I can create a parameter called param_id and set the options of the parameter to "Allow multiple range values".
With this setup, I can limit the result set of the report by comparing the param_id parameter to the ID column in the database. Because param_id is a multi-value range parameter, I can pass it the following data: 1 - 50 60 - 80 150 - 127
This will only return results within those ranges.
Does anyone know if SSRS provides this kind of functionality?
Is there a way to copy reports from other users "My reports" folder? I am logging onto the management studio reporting services using an administrative account and I am able to view the reports from all users' "My Reports" folder. But I am not able to export them as an rdl file. I am able to export reports from other common folders, but not from the "My Reports" folder.
The reason I need to do this is some of the users have created some reports in one environment and the reports are available in their respective "My Reports" folder. I need to move these reports to their corresponding "My Reports" folder in another environment.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Hi, After many nights without sleep I'm not seeing this? Can anyone help why I'm getting a ConnectionString Property not set error? thanks! Dim Sconn As StringDim DBCon As New Data.SqlClient.SqlConnectionSconn = ConfigurationManager.AppSettings("LocalSqlServer")DBCon = New SqlClient.SqlConnection(Sconn)Dim cmdCommand As New Data.SqlClient.SqlCommand 'Dont forget to instantiate a connection object cmdCommand.Connection = DBConDBCon.Open()
I would like to create table by T-SQLand need to specify DescriptionBut I can't find any sample to add Description property by T-SQL Additionally, I also would like modify Description property by T-SQL. What can I do ????? Please help me ......
Hello friends, I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query. Please let me know Thanks & RegardsGirish Nehte
I'm trying to see the properties of a database and I'm getting the following error, even when trying to connect as sa.
Property Owner is not available for Database '[EPro]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
I am use Reporting services 2000 SP2 and discover that the LineHeight property rendered correctly only in renderers for HTML output. PDF renderer, TIFF renderer and client side printing client do not render changes in this property at all.
Is it bug in Reporting services 2000?
Can someone try to change LineHeight property in SQL Server 2005 Reporting services and verify rendering output?
within a data flow task I have two OLE DB sources that I am attempting to put into a merge join.
When I connect them to the merge join transformation I get the following error:
The IsSorted property must be set to true on both sources of this transformation.
I understand what this means but I cannot see an IsSorted option in the properties of the OLE DB sources so I can't set it. Can you tell me where it is? for the time being I'm having to put a sort transform in to get it to work.
