I am new to reporting services, working with RS2000. Roles and security have previously been set up.
Shared Datasources have been created. How is access granted to shared datasources?
Some users have access but we haven't been able to create new users with access to the shared datasources.
Thanks
Hi,
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)?
Thanks in advance,
Nick Goloborodko
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?
Thanks.
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?
View 4 Replies View RelatedI 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;}
Does anyone know of a tool or method that would allow me to join MS SQL and Oracle 7.3 tables for a query?
View 2 Replies View RelatedHello,
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.
Could you give me any ideas?
Thanks in advance!
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.
Dick Campbell
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...
thanks!
-dk
Hi,
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?
Any thoughts on this would be greatly appreciated
Thanks.
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 am scratching my head on this one.
Brand new at this !
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.
Thanks
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page.
I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources.
The datasource looks like this:
Team, Pld, W, D, L, F, A, Agg, Pts
my code for LeagueTableHome looks like this:
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I want then to show LeagueTableTotal in a GridView.
Can anybody help?
Hi all. Can you help me? I'm trying to build a custom component that recieves two datasources (like for instance the union all) . I first started by adding a new IDTSInput90 in the ProvideComponentProperties, but when I tried to use the component I got an error that has very helpful :
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Data Flow Task [Replica Transformation [1289]]: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputCollection90.get_Item(Object Index)
at MyCustomSSISComponent.SampleComponentComponent.ProvideComponentProperties()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
===================================
Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
This is my ProvideComponentProperties:
Code Snippet
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Staging Data";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSInput90 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Replica Data";
input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
// Add the output
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Replica Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// Add the error output
AddErrorOutput("StagingErrorOutput", input.ID, output.ExclusionGroup);
// Adds columns
AddXmlColumn();
IDTSOutputColumn90 column0 = ComponentMetaData.OutputCollection[1].OutputColumnCollection.New();
column0.Name = m_SyncStatusColumnName;
column0.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252);
IDTSOutputColumn90 column1 = ComponentMetaData.OutputCollection[2].OutputColumnCollection.New();
column1.Name = m_AS400ImportedDateColumnName;
column1.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0);
}
Since I'm new to SSIS I'm following Josh's SSIS Xmlify Data Flow Task sample.
Thanks.
I need to create a report with variable datasources. Any one knows the best way to do that?
When I try creating variables on the select statement of the dataset it errors out.
I I trayng something like this: select * from @server.@database.dbo.errorlog but It does not recognize the variables. Any suggestions??
Hello,
we use SQL Server 2005 Reporting Services with XML Datasources, but we have the following issue :
with a xml like this :
<Root>
<A>a</A>
<B>b</B>
<L>Item 1</L>
<L>Item 2</L>
<C>c</C>
</Root>
If we use the query Root, the resulting dataset is :
A B C
a b c
But with this query : Root/L, the dataset is :
L A B
Item 1 a b
Item 2 a b
The dataset doesn't find the "C" node.
Anyone have an idea ?
PS : I don't control the xml structure, so I can't displace the "C" node.
We are having our SSRS (Sql Server Reporting Services) Servers deployed in Domain1 to connect to the Domain1 data sources. Now, when we configure the SSRS datasources (which is in Domain1) to run under €œDomain2/User€? credentials , it€™s taking 60 minutes to render a report whereas when we configure the SSRS datasources to run under €œDomain1/User€? account it€™s taking only 5 seconds.
I would like to know €œWhy there is a latency difference while configuring the SSRS data sources with different domain credentials (Domain1Domain2)?€?
Any pointers to Support KB or any other related articles would be really great.
Hi everyone, I am using VS2005, just installed SP1, and the help -> about window reflects this. I have also installed the SQL Server Compact Edition SDK, but when i try to add a new data source connection (following the MANY tutorials on this), i do not see an option for SQL Server Compact Edition nor do i see an option for SQL Server Mobile. I am at my wits end and i just got started! Yikes... Any thoughts on how to proceed?
View 17 Replies View RelatedHi everybody,
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I hope somebody can help me. Thanks.
I can add more datasources and how can i frame my query based on the tables present in the various datasources?
View 5 Replies View RelatedIs 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.
Hi All,
I have created fact tables and dimension tables in datawarehouse database, and i created a olap cube from those tables.
I want to run SSIS Package which populates these fact and dimension tables from datasources.
Thanks in advance,
Archana
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:
View 12 Replies View RelatedAny fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?
I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.
There are no values within any of the tables that accounts for the order they are in.
If a package has 5 connections you need to go through the unsorted list 5 times to find them.
Sometimes you get lucky and they are in the first 20 or so.
I know I can write a script, just wondering if there is a fix for the sorting.
hi all,
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports.
i followed these steps
1)created a "Report server project" in Visual studio 2005
2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server"
3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services"
4)later i added one of the reports to "Reports" folder by selecting "Add existing item".
But in design mode of that report when i clicked "Preview" tab i am getting the error
"an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error
"Connection cant be made to database"
please help me in resolving this urgent issue....
Thanks in advance,
Archana Devi Papineni
Hi,
I have a shared drive mapped to the h: drive letter on my sql server and I want to create new databases on that drive. Is this at all
possible?
I try it and I couldn't see h: even if I typed it in.
Thanks
Hello
Maybe someone can suggest me some software which can be used for SQL 2005 shared hosting?
With database size limit, user number restriction and other features.
Thank you for information in advance
Hi,
I am trying to use shared variables in the emebedded code of a report. But
when i assign a value to the shared variable in the detail level and access
the same from footer it shows different value (0).
I declared an integer variable as a shared variable. And changed its value
in the detail level. Then tried to access its value in the table footer.
Public Shared testVal As Integer
Function testValFunc()
testVale = 25
End Function
In detail level i call Code.testValFunc()
In footer -> Code.testValFunc() gives 0
Any Idea??? Is it because the header/footer is computed first and then the detail?
-
HelpSeeker
Exit it in sqlserver notion of shared connection or dedicaced connection like there is exist in oracle ?
Exit it a part of memory (like PGA in oracle) who exist just for the connections ?
Else what are constitued the memory in sqlserver ?
Thanks
Ok, got an assignment from my boss.. Need shared storage for a 2 node cluster, with main focus on MSSQL DB Failover. We won't be storing alot of data, speed it not really an issue, just availability..
As far as I know I need 2 SCSI cards, 1 for each node. Node1 has to have ID7 and Node2 ID6 (for example)
this is it.. what's next? what's the best way to configure all this? Do I need a special box for all hard drives to be with certain controller?
I was thinking to link up like this
terminator
|
Node1
|
|
Disk
|
|
Node2
|
terminator
But I wanted to run some sort of RAID on disk, mirroring is fine.. Anyone can suggest me what parts I should get for this setup? my servers don't have 64bit PCI too
Hi,
I have noticed the the cubes that we have here use shared dimensions.
For almost all cubes(5-6) there are at least 4-5 common dimensions.
According to what I have been preached so far, the shared dimensions are so that you can reuse them. That is not what is practised here.
for example.
cube1 has somedim1, dim2_c1, dim3_c1...
cube2 has xyzdim1_c2,xyzdim2_c2,dim3_c2..
dim3_c1 and dim3_c2 are the same dimensions, one for each cube.
I don't know if I am missing something. Shouldn't the use the same dimensions? Could there be any reason for this.
pls. advice.
TIA
Hello,
Here's my problem :
I have several packages, one parents and several childs.
I want to define just one connexion for all theses packages, so I used a Data source. But I had several problem with that object.
So I'm looking for the best way to define one working database connexion for all my packages.
Can someone help me ?
Thanks in advance.