How To Split One Report Into Many File Based On Volume.
Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,).
Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees.
Can anyone suggest a way to do this€¦
Regards,
Krishna
View Complete Forum Thread with Replies
Related Forum Messages:
Can I Split A Field Based On A Character?
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
View Replies !
Conditional Split Based On Conditions
Hi, I have the following table in MsAccess EmployeesA empId integer, empName varchar(60), empAge integer, empStatus char(1) - can be N,D or S - New, Deleted or Shifted and the following in Sql2005 EmployeesB Id smallint, Name varchar(60), Age int, Status char(1) - Bydefault 'N' I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now. If empStatus = N in EmployeesA, then insert a new record in EmployeesB If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D' If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row. How do I do it for each table each row in EmployeesA using a foreach file loop? Thanks, lolsron
View Replies !
Split One Row Into Multiple Rows Based On Time Elements
I'm dealing with a problem. The record information example DateTimeStart , DateTimeEnd , action , duration (seconds) 2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600 what i want is for every half hour between start and end a record 10.30 action1 11.00 action1 11.30 action1 how can i create this, i'm a little stuck on this
View Replies !
Retrieving Data From A DB Based On Output Of A Conditional Split
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need. Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing. Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command. How can I do this? Thank you! Nolan
View Replies !
Error During Installing Disc 2: The Wrong Volume Is In The Drive. Please Insert Volume SQLSRVRS Into Drive
This error occurs with the Disc 2 of SQL Server 2005 Standard Edition in a Windows 2000 Server with SP4 when the screen shows Configuring Components in the row WorkStation Components... The same Disc 2 has no problem in installing Windows 2003 Server with SP1. Additional info which may help: When the CD1 starts, it will give me a runtime error and if I choose to debug it, the following line is highlighted in D:splash.hta oShell.CurrentDirectory = commandLine.substr(1, commandLine.lastIndexOf("\")); if I choose not to debug, I can continue with the installation but then it will hit the wrong volume error eventually. Thanks for any help, Peter
View Replies !
Split The Existing MDF File Into Mutliple Files As A File Group?
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!! Anyways.. I heard that the general performance woul grow if i had them as "File Groups".. Is there anyway - to split the existing MDF file into Mutliple files as a File Group? Where should i start? Can someone please direct me..
View Replies !
Split Data File Into Multiple File Groups..!
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View Replies !
Report Builder Using Report Model Based On Linked Server (Excel) - Primary Key?
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 Replies !
Split In To Two Parts In Report Designer
After slicing the cubes for the report designer, I want to split the count (with dimension) into two parts based on a customized (fixed) parameter. For example, using the matrix table with 2 dimension, i want to use another sql or other methods to split the count even more into details. A table with numeric values stored in the cube. I want to split the range from <10, >10 ...etc. into two columns apart from slicing through the dimensions. How do I do that? Explicit detail is needed,thanks alot. I am using this for the cubes that i have designed. Initially i thought of separating the data at the inital stage of using the logical tables from SSAS project. However it would be better not to touch the cube ( changes in future) and amend this kind of report from the report designer. Thanks. Regards Alu
View Replies !
Split The File
my ssis package downloades the text file from Ftp. iT downloades for ex 5 files. I want to split the file to smaller file after downloades. If the size of file is more then 600 mB then I want to split it into 6 files. please suggest if any task in SSIS can perform this or any other way.
View Replies !
MS Access Detail Split Into Columns On Report
I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help. Thank you all! Timpy
View Replies !
Split Txt File Into Multiple
How can I split this incoming file into separate txts. I want to cut out each Header/detail row section into a new txt. What I mean by header/detail row: incoming txt file: http://www.webfound.net/split.txt basically want to cut out each section like this: http://www.webfound.net/what_to_cut.txt http://www.webfound.net/rows.jpg and a kicker...each new txt name must use a certain field (based on x numbers in header row) followed by another field whcih is the date form the header row. somethign like this: SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 59,4) == "HD" + SUBSTRING(InputFieldBigString,LEN(InputFieldBigString) - 1,8) == "HD" + .txt I need some hand holding here, it's my first time trying to do something so complicated in SSIS 2005. If I can first just get the txt split into multiple, that would be a big help.
View Replies !
Split Single File Over Two Tables.
I have an input file with fixed-width columns that I want to import into two tables.. 5 of the input columns go to 1 table and the remaining 15 go to another table. What's a good way to do this in SSIS? TIA, Barkingdog
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Report Based On Filtered Query OR Based On Custom Query
adp on SLQ7 What would be wise to do. I'm creating a report based on two inner joined tables and i've got a total sum field for each line in the report. (Price * Ordered) This results in the following sql statement: Code: sqlStr = "SELECT *, [Table1].Ordered * [Table1].Price AS LineTotal FROM [Table1] INNER JOIN [Table2] ON [Table1].RecieptNumber = [Table2].Number WHERE ([Table1].RecieptNumber = " & varNumber & ")" I think I have a few options now. 1. Leave the varNumber out of the query, and save it as a regular query. And now filter the report on varNumber. 2. Write the constructed query with createquerydef to eg. "TmpQueryForReport" And set the reports recordsource fixed to TmpQueryForReport. 3. Set the recordsource of the report to constructed sqlStr on Report_open() --------------------- Option 1, I got this working but when the database grows (and it will) this might get awfully slow. Option 2 I had this working before i switched to using MSSQL server 7.00 After the switch i thought, this might be a problem with giving db access rights cause the users might need write rights to the db. Option 3. Fast and easy??? Option 4 Stored procedures???
View Replies !
Can I Split A Long .sql File Into Multiple Files?
I have one really long .sql file I'm working on. It's actually a data conversion type script. It's gotten really cumbersome to work on as long as it is. I would like to split up various logical parts of script into their own .sql file.How can I have one file .bat, .sql or whatever call each .sql file in the order I specify? Hoping this is easy. Thanks
View Replies !
MSSQL DB File Getting Bigger, Can Split Or Reduce?
Hi.. We have a MSSQL application and the DB file (not the log file) seems getting bigger over this few year and right now you are running almost out of space. May I know how does the other company deal with this kind of situation? i am sure other company data is getting bigger as well and it has been longer time than ours. How to deal with it ?
View Replies !
Delete Blank Row In Flat File Destination From Conditional Split
Hello everybody I have one question about deleting blank row on flat file destination from conditional split. I create an SSIS package to filter data from Flat file source. On flat file source, it is Ragged right format and header row delimeter in {CR}{LF} the coulums are devided manulaay using markers. I use only 2 columns divided and send the source into conditional split task and the conditions are given to filter data, when the output from conditional split is placed on flat file destination, i notice blank rows on the output. I want to delete the blank rows so the result data can be displayed continuously in rows. anybody has any idea for this? I know the script task will work but hope to avoid to use script task. Thank you in advance for all the help.
View Replies !
Using 'IN' In A Procedure Based Report
We have a report that uses several data sets. In 3 data sets we are using multiselect drop downs to select criteria for the report to run. As long as we create the DataSet in SRS there is no problem and things work as expected. Our code looks something like this SELECT ID, NAME FROM PROPERTY WHERE (PROPERTY.AREA IN (@AREA)) If we select multiple values the report give multiple results However we have some very complex querys that use #temp tables. If we put these querys directly in the SRS dataset it wiill not allow us to use the fields because SRS can't read past the #temp tables, thus we need to use procedures. When we use procedures we want to still use "IN" statement in order to utilize the multiselect. But SRS is forcing us to chage the Int type to string, and then it is not returning the proper results if we select more then one value. Do we have to do something different to use IN statements in a procedure? Here is the code that will not run as a proc from SRS SELECT SITE.SITEID, SITE.PROPID, SITE.LOCATIONNAME, SITECONTROLDATA.R1PROGRAM, PROPERTY.PROPERTY, CLIENT.CLIENTID, CLIENT INTO [#TEMP1] FROM PROPERTY INNER JOIN SITE ON PROPERTY.PROPID = SITE.PROPID INNER JOIN SITECONTROLDATA ON SITE.SITEID = SITECONTROLDATA.SITEID INNER JOIN CLIENT ON PROPERTY.CLIENTID = CLIENT.CLIENTID WHERE (SITECONTROLDATA.CALC_DATE > GETDATE() - 8) AND (PROPERTY.AREA IN (@AREA)) AND (CLIENT.CLIENTID IN(@CLIENTID))
View Replies !
Subscription Based Report Via Email
I cant imagine this to be possible, but I currently have a report setup and a subscription that email the report to some users, I was curious to know if possible that if the report returns no records to not have the report sent?
View Replies !
Report Distribution Based On SQL Query In RDL
I need to create multiple reports with each report being sent to the specified user. For example, 3 Managers should each get their own report. A Stored Procedure receives a ManagerID parameter, so I need 3 instances of the report. I also need each report to be sent to its associated Manager: Mgr ID Manager Name 1 John Q Manager 2 Jane Q Manager 3 Jack O Lantern I set up a rss file to run the report and can run it multiple times, passing in a different ID value each time, but it will not be data driven because I must hard-code the parameters in the ReportParmaters object of the report for each execution. Is there a way, either by designing the report in VS2005 or on the Reporting Services server, to accomplish what I'm intending to do?
View Replies !
Distribute Report Based On Content
I am just getting started using subscriptions in SQL Reporting Services. I am using version 2000, on Enterprise Edition. Here is my situation. Can someone please let me know if this is do-able and perhaps point me in the right direction. I have a table with a series of 'jobs' or 'projects' in it. Each row has an employees name and email address in it. I currently have a report set up that reports information on all of the 'jobs' for each employee. It uses a simple selection list as a parameter. I would like to modify this so the report is automatically emailed to the employee on a scheduled basis. Employee A would receive a report that has all of the information for Jobs he is managing. I would also like this to be dynamic so if new employees are added into the project list the report does not need to be changed, or new supscriptions created. In my head it seems do-able, but I am not sure how to approach it. Any help would be great. thanks
View Replies !
Calculate Value Based On Other Values For Use In SRS Report
I am trying to create a report in SRS that will calculate a Composite Score based on the Sum of other scores and then that Sum is divided by 12. Here is a copy of the Query I have created to calculate the numbers I want to use, but when I create the expression in SRS the calculations don't come out correctly. The expression that I use in SRS is at the bottom of the Script. Thanks in Advance --6 Trait_03052008.sql --Pull test scores for 6 Trait out of Campus for SRS reports. SELECT s.lastname+','+' '+s.firstname AS Student, e.grade, sm.lastname+','+' '+sm.firstname AS Teacher, sc.name AS School, t.name AS Test, t.code, ts.rawscore, CASE WHEN t.name = '1 Ideas' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric)END AS IComp, CASE WHEN t.name = '2 Organization' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 3 AS Numeric) END AS OComp, CASE WHEN t.name = '3 Voice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS VComp, CASE WHEN t.name = '4 Word Choice' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore * 2 AS Numeric)END AS WComp, CASE WHEN t.name = '5 Sentence Fluency' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric) END AS SComp, CASE WHEN t.name = '6 Conventions' AND ts.rawscore IS NOT NULL THEN CAST(ts.rawscore AS Numeric)END AS CComp FROM testscore ts JOIN student s ON s.personid = ts.personid JOIN roster r ON r.personid = s.personid JOIN test t ON t.testid = ts.testid JOIN calendar ca ON ca.calendarid = s.calendarid JOIN enrollment e ON e.enrollmentid = s.enrollmentid and e.calendarid = ca.calendarid JOIN section se ON se.sectionid = r.sectionid JOIN course c ON c.courseid = se.courseid AND c.calendarid = ca.calendarid JOIN school sc ON sc.schoolid = s.schoolid JOIN staffmember sm ON sm.personid = se.teacherpersonid AND sm.schoolid = ca.schoolid WHERE t.code LIKE '6%' AND c.calendarid =50 AND c.homeroom = 1 GROUP BY s.lastName,s.firstName,e.grade,sm.lastName,sm.firstName,sc.name,t.name,t.code,ts.rawScore ORDER BY [Student], t.code, t.name DESC Expression being used in SRS to calculate the Composite Score. =SUM(Fields!IComp.Value)+(Fields!OComp.Value)+(Fields!VComp.Value)+(Fields!WComp.Value)+(Fields!SComp.Value)+(Fields!CComp.Value)/12
View Replies !
Monthly Based Report Parameter
Hi guys, I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months. Thanks in advance for any suggestion. Claudio
View Replies !
How To Disable A Report Parameter Based On Another?
I have a report that has 2 report parameters. The first paramter is a string type that allows null. It is supposed to allow the user to enter a memberId or just select all members (if the NULL is checked). The second report parameter is a multi valued selection that contains a list of country codes populated from a query. What I like to have is when the user decides to enter a memberid (uncheck the NULL) to have the country codes report parameters disabled. Is there a way to do that? Thanks, Elie
View Replies !
Report Based On Stored Procedure - ODBC
Can anyone tell me if this can be done? I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source. Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side. I connect by Database>ODBC and then choose my sp here. I get the error: "There are no fields in the file" All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data. I can post the exact sp but it is a pretty long IF THEN ELSE block. I checked the Seagate site and it said to convert the database driver to the native driver; I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client. Can someone tell me if there is a way around this to connect by ODBC using stored procedures. Thanks in Advance.
View Replies !
Report Model Based On Oracle And SQL 2005
I've successfully created a Report Model + Data Source View + Data Source based upon tables in a SQL Server 2005 database. And, I've also successfully created a different Report Model + Data Source View + Data Source based upon tables in an Oracle database. Thus, I know that my Visual Studio Report Model project can read data from the two databases. I now need one Report Model that is based upon a Data Source View that uses both the SQL Server 2005 and Oracle data sources. I need to allow my end users to use the Report Builder and build ad hoc reports based upon SQL Server 2005 tables joined to Oracle tables. Is it possible to create a Data Source View that has tables from an SQL Server 2005 instance and then add tables from an Oracle instance on a different server? When I create the Data Source View using the SQL Server 2005 as the first data source and then add the Oracle data source in the DataSource design view, I get an error when creating the Report Model that it can't query the oracle table. Thanks in Advance, JTR
View Replies !
Creating A Report Model Based On A View
in my report model project, i'm able to successfully create and deploy a report model based on a data source view that is in turn based on a table object in my data store. but if i try to create a report model that's based on a a data source view that is in turn based on a view in my data store, well then, the report model that gets created comes up blank - i.e. shows no objects. What gives? Is it not possible to create a report based on a view? that can't be right!
View Replies !
Report Builder Role Based Security
From Books Online Help: In the role-based security model that is implemented for Reporting Services, users who are assigned to the Content Manager role can create and edit reports in Report Builder. Local administrators are automatically assigned to this role. If you want other users to be able to work with a Report Builder report, you must create a role assignment for them that includes the default role "Report Consumer." Alternatively, you can create a custom role definition. As long as the customized role includes the "Consume reports" task, users who are assigned to that role will have sufficient permission to create and modify reports using Report Builder --------- However, when I try to give a contact those rights, the "Report Builder" link still does not show up for my users. Is there any other place where I can examine the security rights to see why the Report Builder permission is not granted to my users?
View Replies !
Creating Report Based On Multiple Databases?
My application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource. Is it possible that my report can be built on more than one databases? thanks in advance
View Replies !
How To Show Partial Report Based On User
Hi, I am new to SSRS. Is there a way to show part of a report based on User. Say we have a report consisting data for all department. When a department director view it, the report only display data for his/her department. Can this be achieved in SSRS? Thank you in advance for your help.
View Replies !
Report Based On Parent-child Dimension
Hello, I want to create a report based on an AdventureWordsAS parent-child dimension. I created a new report item. In the Data tab i created a new dataset using AnalysisServicesAS data source. I dragged and dropped into the designer [Organization].[Organizations] and [Measures].[Financial Reporting].[Amount] from the metadata pane. How can i configure a table control in the Layout tab, to create a drilldown report? How do i set the Group On and Parent Group fields if i have only one attribute? Thanks in advance
View Replies !
Display Report Based On User Role
I have created reports using SSRS 2005 and deployed in ReportServer. I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?
View Replies !
Summarized Report From One Table Based On Information From Other Tables
Hi, These are my tables auth: id, level, base info: aid, name store: aid, itemID aid is the ID that comes from the auth table. The final output that I plan to get is Name Number of Items where name comes from the info table, and number of items comes from the store table. Query something like: show the total number of items for all those users (in the fashion Name, Number of items)from the store table, for all those whose level=something and base=something in the auth table. For example, if there are 10 users whom match level=something and base=something in the auth table, What I want displayed is: ------------------------ Name Number of Items ------------------------ user1 10 user2 15 user3 20 .... user10 30 ----------------------- which if broken up is: select name from info where aid=X select count(*) from store where aid=X .. for all those whose base=something and level=something from auth. Thanks, Shashi
View Replies !
Sub-report Visibility By Expression With Two Conditions Based On Parameters
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.
View Replies !
How Can Display Items In The Report Based On This Dataset. (urgent)
Hi, I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion, if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth. If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using. Select laa.PlanId, laa.LoanId, ( 1-5 values are stored) los.Name From LoansAttriApplied laa Inner Join LoanOptions los on laa.LoanId = los.LoanId Where PlanId = @PlanId So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get can I display the data in A,B or 1,2,3 instead of 1 2 3 Can someone please give me some insight into this. Regards Karen
View Replies !
Report Builder Functionality In A Browser-based Scenario
I am planning to use SQL Reporting services in a new remote hosting centre and must provide ad-hoc reporting to a small number of users plus the access to running of previously prepared reports to a wider user base. User access must be thin client, browser based. The client workstations are locked down to a particular security configuration and there is no possibility of installing .Net Framework on them. Normally, only browser-based applications can be used at the desktop client. Apart from using Reporting Services (Citrix), what options do I have to provide users with the Report Builder in this scenario please? Are there any COTS packages that would provide this functionality? TonyB
View Replies !
How To Populate Parameter2 Based On Parameter1 Selection On Report
I have two parameters on report both will be populated in dropdownlist boxes. How can i trigger parameter2 based on parameter1 selection. but when i fill parameter2 i also need to pass the selected value of parameter1's data. Please i have done this on aspx form using vb.net but don't know here how to do that. haave difficulty in making a search on google, confused to form right words to search. Thank you all very much, fot the information.
View Replies !
Repost: Setting Up Caching For A Report Based On AS Cube
Good morning all, If anyone can help me with this, I would really appreciate it. We are working with a Windows 2003 box. Both the Reporting Services 2005 and Analysis Services 2005 are on the same box From Report Manager, I can run the report, Home > My Reports > BudgetActualsReport, with no problem. The problem occurs when I try to set up caching. Here is what I have tried, in detail First I go to Properties -> Execution and click the radio button for "Cache a temporary copy of the report. Expire copy of report after a number of minutes" for 30 minutes. I get the error: "Credentials used to run this report are not stored" So then I go to the Properties -> Datasource and add my username and password in the "Credentials stored securely in the report server" The datasource is configured as a custom datasource, with the string in the format: "Data Source=Server_Name;Initial Catalog=Cube_Name" When I try to run the report, I get error: "Cannot create a connection to data source 'DS_Name'. Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integrated Security unless connecting via http or https. " So then I go to the Properties ->Datasources and check the box next to "Use as Windows credentials when connecting to the data source" When i run the report, I get the error: "An error has occurred during report processing. Cannot impersonate user for data source 'DS_Name'. Logon failed. Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) " So then I go to the Properties ->Datasources and check the box next to "Impersonate the authenticated user after a connection has been made to the data source" When i run the report, I get the error: "An error has occurred during report processing. Cannot impersonate user for data source 'myDW_OLAP'. Logon failed. Logon failure: the user has not been granted the requested logon type at this computer. (Exception from HRESULT: 0x80070569)" I have also tried all the above with a SQL login and password. I get the error: Cannot create a connection to data source 'myDW_OLAP'. Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integrated Security unless connecting via http or https. So, can anyone point me to any resources that explain, in detail, how to set up caching when the datasource is an AS cube. My DBA doesn't know anything about AS, so I'm on my own. Thanks, Kathryn
View Replies !
How To Change Report Name Based On Multi Valued Parameter
Hi All, I'm having trouoble with changing my report name when I swtich to different parameter values. I use the following expression. IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total")) This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work. Is there any solution for this? Thanks
View Replies !
Coloring Matrix Report Based On RowNum And Column Num
Hi, I want to color matrix report based on the rowNumber and ColumnNumber.I have able to get the data but no luck in getting the color yet. I have wrritten function but I am not geeting correct rowNumber like in this case.Also to my surprise I can't pass rowNumner(Nothing) for my second parameter,it gives error. Current,Current intersection should get RowNumber as 1 Current and 30 intersection should get rownumber 2 Public Function GetBackColor(Byval ColumnName AS String,ByVal RowNum As Integer) AS String IF (ColumnName = "Current") Return Switch(RowNum=1,"Transparent",RowNum>1,"Silver") End If IF (ColumnName = "D30") Return Switch(RowNum=1,"Green",RowNum=2,"Transparent",RowNum>2,"Silver") End If IF (ColumnName = "D60") Return Switch(RowNum>0 AndAlso RowNum<3,"Green",RowNum=3,"Transparent",RowNum>3,"Silver") End If End Function Portfolio Status Current 30 60 90+ BK Non Perf BK Perf Collection Consumer Foreclosure Legal Payment Plan REO Current 402 52 1 1 1 30 22 21 32 1 1 60 3 3 9 17 1 5 1 13 1 Any suggestion will be appriciated -Thanks, Digs
View Replies !
Selecting Column Criteria Based On Report Parameter
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset? Sincerely appreciate all the help I can get. Thanks in advance.
View Replies !
Report Builder : Having More Than One Total Columns Based On Different Criteria
Hi, I would like to get some advises on how creating some kind of reports with Report builder Lets say i have these two tables with these columns Table Product ProductName Table ProductInstance Product ID CreationDate CompletedDate I would like to be able to create with report builder a report that looks this Start Date : 2008/10/01 - End Date : 2008/10/30 Total Total Total In Out OutStanding ProductName1 10 0 3 ProductName2 5 5 2 ProductName3 8 8 5 ProductName4 12 5 6 Total In : total number of product created between StartDate and EndDate Total Out : Total number of product completed between Start Date and End Date Total OutStanding : Total number of product that have not yet been completed. Any suggestion and advise on how to achieve this ?
View Replies !
Report Email Subscription Based On Snapshot Update
Hi Everyone, I want to receive some reports by email every time the datawarehouse refreshes its content. The data warehouse is being refreshed on a daily basis but the exact time varies between 9:30 am and 11:30 am. I have created the 4 reports in reporting services and set the execution property of each report to Render this report from a report execution snapshot and then created 4 subscriptions for email delivery based on processing option When the report content is refreshed In an Integration Services package I have 4 Script steps (VB.net) that perform an UpdateReportExececutionSnapshot for each of the reports. The problem is that these steps fail in a random order every time the package runs. Sometimes they all run fine , but then one or two fail with timeout error. So the emails are not being sent. I am currently using SQL Server 2005 SP2. If anyone has an idea on this issue, please let me know. Best Regards
View Replies !
Report Based On Optional Parameters From Stored Procedure
I have the following stored procedure: Code Block CREATE PROCEDURE udsp_td_queryowner @state varchar(10) = NULL, @businesstype varchar(20) = NULL, @size int = NULL, @sortorder varchar(20) = 'state' AS SELECT gl_t.state AS [State], gl_t.business_type AS [Business Type], gl_t.lowsize AS [Low Size], gl_t.highsize AS [High Size], e.InternetAddress AS [Owner] FROM gl_territory gl_t JOIN employee e ON gl_t.employeenumber = e.EmployeeNumber WHERE state = COALESCE(@state, state) AND business_type = COALESCE(@businesstype, business_type) AND COALESCE(@size, lowsize, highsize) between lowsize AND highsize ORDER BY CASE WHEN @sortorder = 'state' THEN gl_t.state WHEN @sortorder = 'business type' THEN gl_t.business_type WHEN @sortorder = 'owner' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName) END ASC, CASE WHEN @sortorder = 'low size' THEN gl_t.lowsize WHEN @sortorder = 'high size' THEN gl_t.highsize END ASC, CASE WHEN @sortorder = 'statedesc' THEN gl_t.state WHEN @sortorder = 'business typedesc' THEN gl_t.business_type WHEN @sortorder = 'ownerdesc' THEN RTRIM(e.FirstName) + ' ' + RTRIM(e.LastName) END DESC, CASE WHEN @sortorder = 'low sizedesc' THEN gl_t.lowsize WHEN @sortorder = 'high sizedesc' THEN gl_t.highsize END DESC What it allows me to do is enter in any number of the variables when running the stored procedure. For example, EXECUTE udsp_td_queryowner @state = 'IA' would give me everything in the table in the state of IA regardless of the other field values. Likewise, if I ran EXECUTE udsp_td_queryowner @state = 'KY', @size = 15 it would return the records that are in KY and the size of 15 is in the range of the low and high value. If I run the first example in Query Analyzer I get 53 records from the data I have. It returns every record that has IA as the state. I run the same thing in Reporting Services and all I get is 3 records. Just the 3 records for the state of IA where the business type is either null or blank (I can't tell.) If I allow all of the variables to accept Nulls then it returns the correct data. However, I would like to find a better alternative because when the report is run it returns all of the records in the table initially and if they user wants to enter in any parameters they have to toggle off the null box for the corresponding paramter. Any ideas?
View Replies !
|
TRACKER
