Dynamically Generating SMDL (Report Model Definitions) With C#
Apr 20, 2006
My problem is this: How do I dynamically generate a Report Model Definition with c#?
Is there some sort of method I could call from the ReportingService2005 web service? Or some sort of APIs I could use?
If I didn't have a dynamic database structure, I would just create a Report Model Definition with BIS and then deploy the same model to each customer. However, our product creates additional tables in the database, depending on what data users wish to collect.
There are currently 2 solutions for this problem. First, I can manually create a Report Model Definition through the Buisness Intelligence Studio (BIS). However, I wish to be able to dynamically generate the report model without having to go through BIS. Second, I could use C# and manually write the XML of the SMDL. However this seems problematic.
I'm really hoping for some MS API that I'm missing out on here. Thanks for the help.
I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.
Is there a documented work around or will I need to set a primary key on each table?
Wasted a lot of time figuring out the possible cause of this. I was working in connected mode (with VSS), and when it tried to create a Report Model (after doing all its validations/checks), it had to check-out the project file, which is normal. But before doing so, it tries to check-out the .dsv file as well (data source view file from which report model is being generated). While attempting this, the visual studio crashes. I could never guess that this could be an issue. All the time I was trying to figure out if there was anything wrong with the data in my tables. So, for me, a simple solution worked - check-out the .dsv file before you start creating data model. I hope this may save time for others...
I am having a problem deploying or manually uploading smdl files to reporting services. I can upload anyother type of file without a problem. (dsv, ds, etc.) However, when i try to upload a smdl file, I get
"The permissions granted to user '<me>' are insufficient for performing this operation. (rsAccessDenied)"
I need to regenerate a large view each month which performs a union across a number of partitioned tables. (DB is SQL Server 7) The select statement for each table within the union is large and cannot be replaced with 'select *'. One possible approach is to obtain the current view definition from syscomments and append an additonal 'UNION select ... from ...' to the end. However I'm having difficulties getting the full textual definition back into variables with the stored procedure. Getting the view definition from the information schema returns a truncated version. Is there a way to get the whole of the description back (may be >8k)?
I have similar problems manipulating large text fields within the stored proc. If I try to build the entire view definition each period I hit the problem that EXEC cannot take a TEXT parameter (and a varchar isn't big enough).
There are some (messy) ways around this problem, but has anyone had a similar situation where large statements have have to built up before passing them to an EXEC statement and found a neat solution?
Hello all. I need to build a barcode and stuff it in a image control at runtime in SRS. I have the code at it works perfect for webforms etc but not in the actual SRS report itself. In SRS I put the code in the report properties code but it blows up on bitmap, graphics etc so I added the system.drawing and system.drawing.imaging namespace and it still fails with the same error. I'm certain there is a simple fix for this and would greatly appreciate anyones assistance in making this happen.
Error:
There is an error on line 15 of custom code: [BC30002] Type 'Bitmap' is not defined.
Dim ValidCodes As String = "17401644163811761164110012241220112416081604157214361244123014841260125416501628161417641652190218681836183018921844184217521734159013041112109414161128112216721576157014641422113414961478114219101678158217681762177418801862181418961890181819141602193013281292120011581068106214241412123212181076107415541616197815561146134012121182150812681266195619401938175817821974140013101118151215061960195415021518188619661724168016926379"
Dim Digit As Integer = 104
Dim i As Integer
For i = 1 To input.Length
Digit += (i * InStr(1, ValidInput, Mid(input, i, 1)))
I was wondering if there is an easy way to get a database schema out of a MS SQL 7 database indicating specs on each field (datatype,length,table it resides in, whether it allows nulls, default values, primary keys, foreign keys, etc.)
Does anyone have any SQL that will look at a DB and dynamically generate CREATE INDEX statements? I know I can use EM but I want to make this a scripted process and you can only generate CREATE INDEX statement if you script out the tables too.
When I attempt to generate a datasource model I get the following error messages: ------------------------------------------------ More than one item in the Entity 'Customer' has the name 'Customer Merge Custs'. Item names must be unique among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Customer' has the name 'Customer Merge Custs'. Field names must be unique within an Entity. (DuplicateFieldName)
More than one item in the Entity 'Pricing Service Layout Detail' has the name 'Pricing Service Extensions'. Item names must be unique among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Pricing Service Layout Detail' has the name 'Pricing Service Extensions'. Field names must be unique within an Entity. (DuplicateFieldName) ---------------------------------------------------
Examining any of the above tables in SQL Server Management Studio does not reveal any duplicate column names. In fact, 'Customer_Merge_Custs' does not appear to be a column in 'Customer' nor does 'Pricing_Service_Extensions' appear in 'Pricing_Service_Layout_Detail'.
As an experiment, deleting the table 'Pricing_Service_Extensions' and regenerating did make the two associated messages go away.
I try to generate ad hoc reporting model for analysis services cube in reporting services. in Management Studio I connected to reporting services, defined a data source to the analysis services database. But If i try to generate model i get the error: "While connection with a data source a mistake has appeared, or the query is invalid for the data source. (rsCannotPrepareQuery)(Report Services SOAP Proxy Source)"
I'm creating my first Report Model and I've managed to get through it, but if I select the "Update model statistics before generating" in the "Report Model Wizard", I get this error:
"Specified method is not supported"
(It would be a little less frustrating if it actually HAD specified the method <s>)
I have created a Time Series model and deployed it into SQL Server. I hope users can see forecast based on the up-to-date data residing in data source rather than the old ones used to train the model. In a addition, the interface provided for users is a .aspx.
Problems:
What ADO APIs should I exploit to dynamically process the model, perform the forecast and retrieve the results.
We are generating excel report using report viewer at run time but if excel report having more then 65000 record generating error Microsoft. Reporting Services.OnDemandReportRendering.ReportRenderingException: Excel Rendering Extension: Number of rows exceed.
The current way I have my asp.net 2.0 web app running reports is, based on an interface the user selects the criteria for a report. The .RDL file is created dynamically based on the user's selections. I then need to SOAP the dynamically created report to the report server and then the report runs fine. BUT it requires Adminstrative rights to do this. Can this be accomplished without giving the local machine admin rights. I am sorry if this question has been answered before but i have not been up here in a while.
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?
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:
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.
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?
Hi, I have craated an interface for generating the rdl file through c# (User will select some fields for group and some for details and report will be generated accordingly). I am generating the XML for the rdl according to the schema. And the report is running fine in my local system where i am using sqlexpress but is not working in production. The problem is coming with the datasource as follows.
An error has occurred during report processing. Cannot create a connection to data source 'SOP'. Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection
I am using Integrated Authentication, below is the datasource code generated by c#
When i am pasting the RDL xml to my BI project's Report it is giving preview properly, but after deployment it is not coming. I have tried with SQL Authentication also by removing the IntegratedSqcurity element and changin g the connection string to add "sa" userid, but still it doesn't run and gives the same error and if i refresh the report with the refresh button of report viewer, it shows some Wrong String Format error.
When i am editing the dataset in the report designer(BI Project), the connection string is not storing password and username info in the xml of the rdl, i read somewhere that it stores these values in VS2005 and reporting service database with some encryption and don't keep in the xml. So it is seeming to me that i am not sending these credentials to reporting service while deployment through my c# code. Below is my deployment codebyte[] byteRDL;System.Text.UTF8Encoding encoder = new UTF8Encoding();
byteRDL = encoder.GetBytes(reportDefination);
Property[] rsProperty = new Property[10];
//Property property = new Property();Warning[] warnings;
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.
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.
we have data base in ms access and reports in crystal reports 5 recently we converted our data base to sql sever 7 and reports to crystal reports 8. when i opend directly in cystal reports and given data base connecting through oledb i am getting reports but from the application it is in vb6 the report window is poping up and latter a error msg saying un able to open sql server when i checked up the connection it is ok help me anil
I am using ssrs and vs2005 to develop reports.The deployed reports are placed in the source directory(c:source) and i am using a asp.net page with a button on it to generate a report in .Pdf format and after generation this Pdf document will be placed in a destination folder(c: arget).
Can anyone help me out in solving this or atleast direct me as how to go on with it?
I have tried asking the same question in other forums. All i get is links
Please help me. I have the following requirement:
I have the following tables: Theater - TheaterId, TheaterName, Revenues,locationid, stateid State - StateId, StateName Location - LocationId, LocationName, StateId
I want to generate reports that will tell me the revenue generated for each theater in each location in a state. I want to run a batch process which will loop through the 3 tables and will passing the location and state id as parameters one by one. I want each report to be generated as a pdf and stored in a location. How do I do this?
I 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.
