I get this error message:
Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.
The report model does have a perspective.
Anyone else seen this?
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
Hi,
I have created and deployed a data source (which uses "Credentials supplied by the user running the report") and a model which uses this data source.
Also I have built a report using Report Builder and all works well.
Now I wish to use Visual Studio to build a report, so I'm trying to create a data source from my computer which the report should use and I want it to use the model on the remote server. In the Report Wizard I am using "Report Server Model" and the following connection string:
Server="http://<remoteserver>/reportserver";datasource="/Models/MyModel"
IHowever I can't make the credentials to work. I've tried them all with no luck. What username/password must I use? Because so far nothing of what I have tried works.
Any help is highly appretiated.
Kind Regards,
Robert
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
I followed the tutorial http://msdn2.microsoft.com/en-us/library/ms167305.aspx with no problem but when I tried to preview the report the green circle just kept spinning and spinning.
I'm able to execute the command under the data tab with no problems. I'm able to view built in reports for SQL Server 2005. I've tried restarting Visual Studio and the entire PC but the report will still no generate.
Thoughts or ideas about what my problem is?
SQL Server 2005 SP1
Thanks,
Mike
I have a VS2005 Windows application that displays a reportviewer (SQL 2005) report in a tab control, then displays a second report in a second tab with reportviewer, and so on. The really strange thing is that I had this application working for about 3 months. I even had up to 30 reports in 30 tabs at one point. BUT, for some reason now all I get is the first report and the second, third, etc. tabs show a reportviewer with "Report being generated" that never displays the report. Even worse, another user is able to run this application and DOES get all the reports displayed. So what could have been updated on my PC that is causing this problem?
Thanks in advance.
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.
In Report Designer I am able to create a Model data source and see my entities/fields.
But when I run the report I get a "Failed to execute Query" message.
I know in the past this was an issue because Designer doesn't allow you to specify a perspective.
Does anyone know if this has been resolved?
I'm using SP2
Thank you,
Joe
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies View RelatedI have a report model that I am trying to deploy using report manager web service. A folder is created by the administrator to place all my SSRS reports. I deployed .rdl files using the "Upload file" function in "Report Manager". All the SSRS reports work fine but when I tried to upload .smdl file (report model file) using the Report Manager's "Upload file" function it gave me the following error message.
The permissions granted to user 'xxxxxxman' are insufficient for performing this operation. (rsAccessDenied)
Does this mean I only have permission to upload .rdl file but NOT .smdl file to my designated folder?
I'm having some difficulty linking a custome drilldown or clickthroug report to another report that I have already created in report builder. I followed a MSDN article found at http://msdn2.microsoft.com/en-us/library/ms345226.aspx but I cannot find the " Select a page area, select Drill-through reports" option in report manager. Does anyone have a better explanation on how to do this? I'm assuming that when you create the clickthrough report there has to some sore of filter on it that relates to the item of the clickthrough. I guess I'm I little confused, any help would be great. Thanks.
View 5 Replies View RelatedHello,
I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view.
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string?
Thanks!!
-Matt
Hi
I have a windows application. I am using following code right after setting the LocalReport ReportPath. Report rendering is too slow if use following line of code and Report viewer does not show spiny and the text that says €śReport is being generated.€?
Me.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
Me.ReportViewer1.ZoomMode = ZoomMode.Percent
Me.ReportViewer1.ZoomPercent = 80
Please advise how can make it fast and show spiny.
Thanks in advance.
I have created a report and deployed it successfully to a seperate machine with SSRS on. Both my dev machine and the SSRS machine are in the same workgroup. I can then Browse to the Report Manager though IE and click & run the report (The connection is using integrated security). The report requires a single parameter to be entered which once done works successfully. The report has a single table, an inbedded image and a few static fields in the report header.
If I however add a report viewer control to an aspx test form all is not well. As soon as the page opens I am prompted to enter the parameter which is good news as it shows there has been some communication with the SSRS metabase. I then click the view report button. Using Profiler I have proved that the report Stored Proc. is running with the correct parameter value. The report viewer however seems to hang and only displays "Report is being generated" without displaying the results. It does this even with a timeout set of 1000ms.
I don't think this is a security issue as both the Report Manager works and the test form prompts me for the parameter and effectively runs the report SP. I can only assume it is a problem with returning the report to the browser.
Does anyone have any insight?
Regards
Anthony Kay
Hi,
I have a report which doesn't work. It has a datatset based on a store procedure. The store-procedure take 10 to 50 second to execute, depending on the parameters.
But, I can't never see my report as i wait more than 10 minutes and it's never generated.
So, I have think that maybe it could be because of the matrix inside the report, so I have try to create another report which have the same dataset but with nothing inside, just a textbox, and it doesn't work too !!
So now, i'm very disapointed and I don't know what to do !
Someone can help me please ? It's very important.
Thank you.
Is it possible to reposition the "Report is being generated" graphic?
I have most of my reports set to 1000 px by 1000 px which forces the graphic off of the screen. I would like to be able to see it without having to scroll to it.
Also, is there any way to change the properties of that image? I would think a progress bar that actually showed how far along the report was before completion would be better.
Hello,
I develop some application using SQL Server Reporting Services 2005 to Russian customers. They usually don't know English well. That's why I really want to change phrase "Report is being generated" to Russian words.
How can I do it? I tried to fing this phrase in configuration files or registry but there wasn't success.
Thank you,
Hi,
I want to remove default animated gif report is being generated, how can i achieve it when i upload the rdl to share point, Need to remove actions and print option avialble when i upload my rdl to share point.
Regards,
Navin
Code Snippet
' DataSet element
writer.WriteStartElement("DataSets")
writer.WriteStartElement("DataSet")
writer.WriteAttributeString("Name", Nothing, "DataSet1")
' Query element
writer.WriteStartElement("Query")
writer.WriteElementString("DataSourceName", "DataSource1")
writer.WriteElementString("CommandType", "Text")
writer.WriteElementString("CommandText", m_commandText)
writer.WriteElementString("Timeout", "30")
writer.WriteEndElement() ' Query
writer.WriteStartElement("Parameters")
writer.WriteStartElement("Parameter")
writer.WriteAttributeString("Name", Nothing, "GlLayoutNo")
writer.WriteEndElement() ' Parameter
writer.WriteEndElement() ' Parameters
' Fields elements
writer.WriteStartElement("Fields")
Dim fieldName As String
For Each fieldName In m_fields
writer.WriteStartElement("Field")
writer.WriteAttributeString("Name", Nothing, fieldName)
writer.WriteElementString("DataField", Nothing, fieldName)
writer.WriteEndElement() ' Field
Next fieldName
' End previous elements
writer.WriteEndElement() ' Fields
writer.WriteEndElement() ' DataSet
writer.WriteEndElement() ' DataSets
In the above code, the XML parser does not understand the Parameters element. I've also tried ReportParameters as the name of the element. I don't seem to understand why, because in the SQL data tab, Parameters is a subelement of DataSet. Why am I getting this error and how do I programmatically specify that I want a report created on the fly to have parameters?
Deserialization failed: The element 'DataSet' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has invalid child element 'Parameters' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Fields http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Query http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:CaseSensitivity http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Collation http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:AccentSensitivity http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:KanatypeSensitivity http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:WidthSensitivity http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Filters ##other:*'. Line 21, position 8.
I'm trying to create a .htm file (a report from query results) on a webserver using SQL code.
TBL_LOGGEDIN_USERS_PERHOUR has just two columns. It gives the number of users logging in for hour.
HOURdatetime
NUMBER_OF_USERSsmallint
HOURNUMBER_OF_USERS
2006-12-28 15:00:00.000 15
2006-12-28 14:00:00.000 29
2006-12-28 13:00:00.000 30
2006-12-28 12:00:00.000 23
2006-12-28 11:00:00.000 23
2006-12-28 10:00:00.000 21
2006-12-28 09:00:00.000 31
2006-12-28 08:00:00.000 30
This is the actual code which generates one .htm file every 24 hours with the count of hourly logins. So the entries (as per the below logic) start at 12:00 AM everyday and end at 11:00 PM. The catch is - the entries come up fine upto 10:00 PM. The '11:00 PM entry' is missing in all the .htm files. I tried several options and later, I 'force added' an ELSE IF condition especially for the 11:00 PM case, indicated in the code below but it doesn't seem to help...
Also, I'm not really sure if this is a row limitation in the .htm file - like x no: of rows per .htm file.
Am I missing something here?.. Please advise. Let me know if I'm not clear. Thank you.
CREATE PROC CONDOR_CLIENT_LOGINS_PERHOUR
AS
BEGIN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[TBL_CUSTOM_LOGINSPERHOUR]')
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[TBL_CUSTOM_LOGINSPERHOUR]
CREATE TABLE [DBO].[TBL_CUSTOM_LOGINSPERHOUR] (
ROW1 NVARCHAR (30),
ROW2 NVARCHAR(30)
) ON [PRIMARY]
DECLARE @FLAG SMALLINT
DECLARE @REPFLAG SMALLINT
SET @FLAG = 0
SET @REPFLAG = 0
IF DATEPART(HH,GETDATE()) = 0
BEGIN
SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR1 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR = DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
SET @FLAG = 1
END
-- this is added for 11:00 PM entry START
ELSE IF DATEPART(HH,GETDATE()) = 23
BEGIN
SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR2 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
SET @FLAG = 2
END
-- the above is added for 11:00 PM entry END
ELSE
BEGIN
SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR3 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
END
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT 'HOUR', 'NUMBER OF CLIENT LOGINS'
UNION ALL
SELECT '' , ''
UNION ALL
SELECT '' , ''
IF @FLAG = 1
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR1
SET @REPFLAG = 1
END
ELSE IF @FLAG = 2
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR2
SET @REPFLAG = 2
END
ELSE
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR3
END
SELECT '' , ''
UNION ALL
SELECT '' , ''
DECLARE @REPPATH AS NVARCHAR(50)
SET @REPPATH = '\TKESTIIS5LOGINSPERHOUR' + REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 101),'/', '') + '.HTM'
EXEC SP_MAKEWEBTASK @REPPATH , 'SELECT * FROM TBL_CUSTOM_LOGINSPERHOUR' ,
@COLHEADERS = 0 , @RESULTSTITLE = 'Client Logins Per Hour Report',
@WEBPAGETITLE = 'Client Logins Per Hour Report' ,@LASTUPDATED = 1
DROP TABLE TBL_CUSTOM_LOGINSPERHOUR
IF @REPFLAG = 1
DROP TABLE #TEMP_LOGINSPERHOUR1
ELSE IF @REPFLAG = 2
DROP TABLE #TEMP_LOGINSPERHOUR2
ELSE
DROP TABLE #TEMP_LOGINSPERHOUR3
END
I never got an answer on this so I am reposting this issue. Also, please note that I have consistently reproduced this issue on multiple perspectives within and accross multiple cubes on 3 different SQL Server Instances. I have also had no problem with perspectives within Report Models that were not generated from an SSAS Cube.
Specifics:
=========
I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.
Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.
Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.
By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.
I am creating a SSRS report which would be executed by User manually through ReportServer URL.User would be generating the SSRS report for different Customer ID based on ad-hoc basis.
I am passing CustomerID as input parameter to the report. Is there any way to get the manually generated SSRS report name as 'Report_CustomerID_TodayDate.xls'.
E.g.If User is generating report for Customer ID 123 today then report name should be 'Report_123_07092013.xls'
Hi,
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Thanks,
S Suresh
what kind of expressions do you need to put around a date when defining a formula?
View 8 Replies View Relatedi need to get the result of two different queries into 2 different sheets of the same excel file while exporting a report to excel using reporting services..
somebody please help
Hi Guys,
I need some help and suggestion to rewrite one of my screens (using ASP.NET) which is using stored procedure. The processing on this screen is taking more than 3 minutes (which i know is totaly
unacceptable). I am making use of cursors within the stored procedure (SQL Server 2005). I really intend to get rid of cursors as they have their performance hit. I have been told to rewrite this screen
(or the stored procedure) so i need some help for SQL Gurus. Following are the details:
1. This is a Monthly Employee Attendance Report on a day by day basis for any given month (maximum 31 days in a month)
2. The values (for each day) have to be computed at runtime and not stored. e.g. Since an employee may have signed in/out several times in a day
3. There are around 500 employees data im dealing with
4. The user will select any given department and employee's data for the respective department has to be displayed for any given month
5. If the user selects [All Department], the entire 500 employees have to be displayed on the screen
6. This report will look like an excel report on the screen i.e. Employee's basic info and record of 31 days (maximum days in a month) are displayed in one row for each employee
7. This report involves are 7-8 tables. 7 tables are for employees basic info whereas one table has the attendance record
Kindly give me your suggestion on writing the SQL stored procedure. I cannot use any other option such as a real Excel Sheet or anything. I need suggestion on how to write this monthly report. By the
way, we dont intend to Cache the data since the report can be viewed at anytime of the day, so fresh data is required everytime. Also the data for 500 employees may be too much to be cached. Also in
the attendance table, we are dealing with approximately half a million attendance records.
Thanks and waiting for your suggestions...
I'm creating report model using named queries. Is it possible to specify parameters?
View 3 Replies View RelatedIs there an API or a way to consume a Report Model? I want to try and roll my own simple web-based / thin-client Report Builder without using the ClickOnce control.
Thanks,
--jason
Here is one scenario. There is category X which has subcategories A,B,C,D. A has 5 products and B has some 7 products, C 5 products and D has some 12 products. My problem is I need create a Report model where is if we select X
all the list of subcategories and their products should be displayed. I.e if we select X then
A
1
2
3
4
5
B
1
2
3
4
5
C and so on..........every thing should be displayed that are under category, i.e subcategories under that category and the products under each subcategory of that category.
Can anyone please help me out to create a Report Model to generate ad hoc reports
hi everyone,
I created a report model and deployed it. When I open the rpt builder on web I get the following error.
'The selected data source does not have and content available'
Seemed like it would be a easy issue, but I cannot retrieve any data in the rpt model. The datasource I'm using does work for rpts not associated with the rpt model. Any suggestions? It seems like I've missed a step. Thanks, Lisa
Hi All,
I'm a newbie to Report Model and not able to find some conceptual information, Can you guys answer following:
I€™ve
Employee [Name]
->Employee_Days [Day] (1-*)
->Employee_Day_Activities [Activity_Type, HoursSpent] (1-*)
schema and a generated model out of it. Now I€™ve few report requirements which will be developed based on Report Model. These Reports needs aggregate of All similar activities and the hrs spent and it'll be shown with every employee. Like Employee Xyz has spent 14 hrs total on Sick Activity, which will be shown as
Employee Sick Hrs Maternity Hrs WorkHrs
Xyz 14 0 20
Now i need to know the approach to develop it like i'v to ways in my mind
- create 3 custom fields(Sickhrs,Maternityhrs,Workhrs) in the Employee Entity(model), so that managers can create reports like above by simple drag and drop
- leave model as it is.. just create more descriptive fields in all the three table and let managers do the activity grouping etc to develop report like above..
Which approach should i follow? Moreover can you point me to some conceptual info like how report model works..etc..
regards
faraz
Hi,
I just wonder if anyone comes across this problem. When I generate a report model based on a cube, I got this error
The ID property for the Role 'xx' has a local name that exceeds the maximum length of 250 characters. (IDLocalNameLengthExceeded) Get Online Help
note: xx is my cube name.
It used to work fine before and I have changed anything since then so I don't know why it fails now. Please help!
Thanks in advance
Hi, it's my first time for using report model as the datasource of reporting service. But I don't know how to build the query string to get data from the report model.
Can anyone help me?
Thanks