I am currently working with SQL 2005 Reporting Services and MS Visual studio 2005.
In my SQL server I have data about organizations, so that each organization's data is stored in an individual database.
In addition, the number of organizations/databases is variable. I know the number of organizations and the name of each one (that it agrees with the name of its database)
because there is a database (organizationsDB) with a table (organizationsTB) containing the list of the organizations.
So, It would be possible to create a report containing a summary of each organization? .... for example a table in which each row contains data of each organization.
I have loaded up the newest latests, greatest Visual Studio, SQL reporting etc. I have a need to pull in data from multiple sources. Currently I have DTS packages I have to run to dump information into 1 data source to report on. Has anybody figured out a way to have multipled data sources available when in the query builder?
I have got a quistion in regards to SQL Server Reporting Services 2005. We're currently using the SSRS in one of our applciations with 20+ reports. We need to deplaoy 2 instances of an applciation: Test and Production. These applcaitions are running off two different databases in SQL Server [DBName]_Test and [DBName]_Live. So far all of our reports have been written to use a shared datasource. This worked fine untill we had to deploy a Live instance of the application, where the shared datasource is overwritten with the live one.
Now we have manually created a new shared datasource called [DataSource]_Live. However, whenever we need to redeploy the reports from visual studio, we then have to go though each report and point it to a correct datasource. This is extremly manual process - and takes up a lot of time...
Surely there must be an easier way around this (without duplicating the reporting project in VS.NET / or having to modify the datasource fr each report manually each time)?
Is it possible to use a ReportViewer control (or something in .net) to change connection strings of a report's datasources? The connection string comes from a shared data source, so using an expression for the connection string in the actual report is out.
Essentially, I'm looking to tack on Analysis Services cube role(s) to the connection string to change data permissions. If there's another way to do this with a RS report, I'm all ears.
I've got a report model I am trying to move from a development server to a production server. I published it to my development RS, exported the SMDL file, then uploaded it to the production server. At that point, I tried assigning the data source to the model and got the error:
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user.
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
I have three types of specific reports that i have to create with the input parameters (range) either
1: By date (rdl 1)
2.By Month (rdl 2)
3.By Year (rdl 3)
Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).
I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.
Is it possible to dynamically refresh the report model of the report builder?
could it even be using code with any of the interfaces?
When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.
I have a report that grows horizontally as it contains a matrix. In the header section of the report, I have items such as Title and time stamp in text boxes. How can I make these items align in the center if the report grows horizontally, it looks kinda odd with report title not being in the center? Any input will be appreciated.
When I try to preview the report, I get the following error
An unexpected error occured while compling expressions. Native complier return value: '[BC32017] Comma, ')', or a valid expression continuation expected.'.
I have also tried it without the parameter,
="Data Source=MYSQLSERVER;Initial Catalog=ADV_1"
with the same result. When I use the exact same static DSN it works fine. Anyone have any idea what I might try to get it to work next?
One being a query which sums up claims for a group of members. Col A beign the memberID and B and C being amounts.
Now I set the output to Isosrted = true and set the output column A sorted property to 1.
The other OLE source is a table. Col A being member and b,c,d.. being name, address... I set the same properties for column a and I am trying to use a merge join.
The first two sources return 2245 rows and after the merge I end up with 648.
What would cause the merge to strip some records out?
I have several SSIS packages which I have setup using a datasource to connect to the database. I should be able to switch between databases by just changing the server and database on the datasource but for some reason the ssis package keeps on using the old database. I have the connection managers setup to reference the datasource and can see the connection manager pointing to the new database but when I run a task by right clicking on it and choosing execute task it calls the stored procedure in the old database. I know it is doing this because I renamed the stored procedure in the old database and the task errored out saying it could not find the stored procedure. Can someone please tell me what I am doing wrong?
Hi, I'm trying to create a report with dynamic columns. I mean that the user is able to select if he wants to see a double drill report with client group and product group, or agents with clients, or everything else he needs. I tried to insert some variables in the query that extracts data from report, and using that variables like parameters, but Reporting Services always get the parameter default value and not the correct value i give him. How can I solve my problem? There is another way to obtain dynamic reports?
i have a dataset which returns me a dynamic set of data ie. number of columns can be different....say 14 columns for some dates...and just 12 for some other...(pivot table is being used in the backend)...how can i present this in the table...also my column names are best suited to be the column headers in the table.....is it possible to create a table at runtime...??
For example: At the beginning I have 3 columns in the report, build by VS. After couple weeks, the same report needs to have more columns. And i don't want to use the VS to modify the layout manually, instead, when adding new columns, I can call a function to modify the rdl file add attributes into the xml file automatically. But after that which way I can go to publish the modified report into the report server, then when user clicks on that report again, there will be more columns show up.
is it possible to have a kind of dynamic subreport in a main report.
Let me illustrate this with an example:
Next to my table i would like to have a window (subreport) that changes content whenever i click on a different value inside my table. In the subreport i would like to display detail information on specific cells in my table.
I m working in Microsoft SQL Server Analysis Services Designer Version 9.00.1399.00 for creating reports .
Now, I m facing a problem to view a run time dialog box at time of my report. Means , In my report procedure , i have fired 2 delete queries after firing of these queries i wanna show a message box with Message "Some Records are Deleted". and one Ok Button !!
and after showing this dialog box i wanna view my report output !!
The procedure of my report is : ----------------------------------------------------------------------------------------- create procedure MyTemp as
-- Query1 delete from myTempTable1 where Id = 5
-- Query2 delete from myTempTable2 where Id = 10
-- After exectuing above two queries Query1 and Query2. -- here i want to view amessage dialog box with message "Some Records are Deleted"
select Id, name from myTempTable1,myTempTable2 -----------------------------------------------------------------------------------------
So, how can i do this !! I will change in my proceudre or report !! Please suggest me about solution of this problem .
If u dont have any solution regaring this then please suggest me where i will get the solution of this problem .
I need to set a Repeater.Datasource to one of three stored procedures depending on what button the user selects. How do I set it up please?<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ABCConnectionString %>"SelectCommand="???????????????" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="???????????????????" Name="?????????????" PropertyName="Text" Type="string" /></SelectParameters></asp:SqlDataSource> protected void btnKeyWordSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "KWSearch"; KWords = tbxKWordText.Text;}protected void btnCompanyNameSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "NAMESearch"; CoName = tbxCoNameText.Text;}protected void btnBrandNameSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "BRANDSearch"; Brand = tbxBrandText.Text;}
I have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.
Get this when selecting parameter value in Report Manager
Report is working fine in preview
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
Did one more test, changed the order of the parameters, is now going wrong on the other parameter, Year.
So, it is always on the first parameter.
Correction, I have put a multi-value parameter as first, I still have the problem, it is always on the first single value parameter
I have the same problem.
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
is there a way to bring in oracle and sql tables into the same report and join them together on common fields? also, if this is possible, i am trying to join them in the native UI and not the generic query designer mode.
i am hoping that through fully qualifying the tables as you bring them in, that this may be possible. any suggesions would be greatly appreciated...
I have to design a tabular report where the data in all the columns of the table comes from the cube, but for one column where the data comes from the ODS table.
Could anyone please let me know if it is possible to combine the results of the query from a cube with query from an ODS table and display together in a single report?
I'm running VB 2005 express edition installed from full MS download which included the SQL2005 express server V9.00.1116
Have used the Video tutorial (video 8 & 9) to create a database and have followed instructiions faithfully
Problem.
From the Data / Add New Datasource menu everything proceeds ok - that is a "dataset.xsd" file is created but then I get an error message
" Could not get type information for .........myfilename dataset "
and so the process is not completed properly and a datasource is not created.
I'm unsure what the type information is and have been unsuccesful turning up anything from the help files or forum so hoping someone can maybe tell me whats happening and how to fix this.
But If I change default parameter with everyone I need it always run correctly! The real problem is in execution mode, it not takes the parameter i give it danamically. I can see only the default columns I set by default. The thing I need to kwnow is: is this method correct or this problem can be solved in any other manner?
I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.
I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?
I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.
Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null. Add a filter to the dataset that contains the field to be searched. Set its Expression to: =IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "", Fields!LastName.Value, Nothing) , its operator to "=" and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")
