hi,
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...??
Hi, I was wondering if there is a way to solve this issue.
I have the following MDX to retrieve specialised time related data from a cube
Code Block
SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[2007 Q1], [Time].[Quarter].[2007 Q1].lag(1), [Time].[Quarter].[2007 Q1].lag(2), [Time].[Quarter].[2007 Q1].lag(3), [Time].[Quarter].[2007 Q1].lag(4), ytd( [Time].[Quarter].[2007 Q1]), ytd( [Time].[Quarter].[2007 Q1].lag(4)),ytd([Time].[Quarter].[2007 Q1].parent.lag(1)),ytd([Time].[Quarter].[2007 Q1].parent.lag(2)) } } } ON COLUMNS ,
NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS
FROM [QMS]
WHERE ( [Overall].[Overall].&[Y] )
I can populate a table with the columns i need from the resulting dataset, however it is not dynamic. i.e.
The dataset field names come up as say,
Measures___Vacancy_Time_All_Time_2007_2007_Q1
Measures___Vacancy_Time_All_Time_2006_2006_Q4
Which i can place in the report...
But if I then want to parameterize [2007 Q1] like as follows
Code Block
="SELECT NON EMPTY { { { [Measures].[% Vacancy], [Measures].[Total Square Area], [Measures].[Deliveries], [Measures].[Net Absorption] } * { [Time].[Quarter].[" + Parameters!Quarter.Value + "], [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(1), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(2), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(3), [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "]), ytd( [Time].[Quarter].[" + Parameters!Quarter.Value + "].lag(4)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(1)),ytd([Time].[Quarter].[" + Parameters!Quarter.Value + "].parent.lag(2)) } } } ON COLUMNS ,NON EMPTY { DESCENDANTS( [Location].[All Location], [Location].[Market] ) } ON ROWS FROM [QMS] WHERE ( [Overall].[Overall].&[Y] )"
so that everything is then driven from a single selected Quarter value, the table report no longer gets populated, as it has hardcoded field values such as
=Fields!Measures___Vacancy_Time_All_Time_2007_2007_Q1.Value
and if the Quarter selected is [2006 Q1] for example, this field will not exist in the dataset.
Is there a way to accomplish this? I am using SSRS 2005 against SSAS 2000 cubes
Hi,
I have placed one table object to show my datarows in reporting services report.
Depending upon the Parameter i want to change the location of this table in report. Is it possible or any other way we can do this. please help on this.
The purpose is to show the table horizontally centered after hiding the visibility of some column depending upon the parameter.
Thanks in advance.
With regards,
Sathish Raja S
Hello all!
Does anybody know, if it is possilbe to dynamically change grouping in a table report?
I mean, is it possible to provide the enduser the capability to group data in a table on whatever column he would like?
The second question is related:
Is it possible to make a usersort on a grouped table, in such way, that ALL the rows would be sorted ascended regardless of the group they belong to? (For now the only way I see to do this is to give the enduser possibility to turn off grouping manually)
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.
Any tips on how to get this working?
Thanks
Mark Smith
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.
View 3 Replies View RelatedI 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.
Thanks
Regards
Alu
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.
All,
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.
Thanks.
I am creating a dynamic DSN in a report to pick which database to run a query against. I have a fairly simple expression,
="Data Source=MYSQLSERVER;Initial Catalog=ADV_" & Parameters!DBNum.Value
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?
R
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?
Hello,
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!
Hi:
I have a question of create a dynamic the report.
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.
thanks
Hello,
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.
Is this possible in SSRS 2005?
Greetings
V.
Hello Sir,
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 am using a Visual Studio 2005 for web application development, for Reporting service
I am using Sql Server Reporting Service and Report Builder 9.0.1399.0.
Is it possible to use the Report builder tools (Explorer, Fields and Design Report) in
an ASPX pages?.
Is it Possible to create the Reports dynamically in a Web Application using Report
Builder?
Thanks in Advance
Thanigaivel
Hi Friends,
Is it possible to give a name at run time to a report when we try to download it in any format.
Do we have any control over the report name for e.g. the report name can be passed at parameter value.
Thanks,
Novin
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?
View 2 Replies View RelatedI'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?
Thx
Helen
Hi,
I have report that needs to be scheduled hourly and the output is in excel. I want this to have a dynamic filename (Filename + datetime.xls).
How do i do that?
cherriesh
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 & "*")
voila!
Helen
Hi, I have a SRS report that I would like to a add an PDF file to be visibleprintable on the report. The data stored is the file location of the file.
I tried using an image; however it does not recognize the PDF files.
Please advise.
View 1 Replies View Related
Hi all,
Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -
Parameter 1 - 'Select an Application' - List of applications that are available
Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.
Is this possible to do in SSRS?
Thanks,
Brian
I'm trying to set up a parameter report from a OLAP cube. I need 3 dynamic parameters(ThisYear, ThisMonth, YESTERDAY).
THISYEAR: =CStr(DatePart("yyyy",Today()))
THISWEEK: =CSTR(DATEPART("ww",Today()))
YESTERDAY: =CSTR(Datepart("d", Today().AddDays(-1)))
So far so good. I want to send this report by email everyday, so I need to set these parameters as a default value. This is where I run into problems.
When I add any of these parameters I get the following error "The restrictions by the CONSTRAINED flag in the STRTOSET function were violated". This problem occur when I choose NON-queried under Report->Report Parameters, and set a default value for THISYEAR under DEFAULT VALUES
=CStr(DatePart("yyyy",Today()))
Hope someone found a way around this problem.
Thanks in advance!
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.
For example a report that would run on today's MM, yy (5/2007) would look something like this:
MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08
(Col 1 - Col 12 separated by commas)
How do I do this in a matrix report column?
I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.
Thanks for any ideas.
-Josh R.
Hello,
I have a report that uses a begin date and an end date as paremeters along with a user's email adddress to generate thier schedule for the coming week. I would like to schedule this report to run at the beginning of the week and email to each user based on thier email address. So the parameters will be system.date +1 and system.date+6 and user email. I have the users email addresses in a table so the 3rd parameter could be a "for each email in table users...". Is this posssible? Should it be in Report services or a scheduled Stored Procedure? Any help with syntax will be greatly appreciated!
Thanks,
Bob Myles
Is there a way to dynamically feed the Text of the Column titles into a report at run-time in Reporting Services?
In Access I could use DLookup to pull YTD Dates (e.g. "05/2008") from another view that kept track of the settings for the year being processed (Curr Year, Prior Year, Prior Year II, YTD I, YTD II, and etc. for each Year's data. YTD I and YTD II would read like '07/2008' and '09/2008'. The YTD II value could change as the next month's data became available ('10/2008'). As we switched from year to year (2007, 2008, etc.) all I had to do was update the [tbl Status Flags] table for the next year and all my many reports would reflect the correct headers for the periods in the data. The period could be switched back to prior years at any time by the user in order to view past data. When the year was changed the report headers would have to reflect that. The status flags table would give the setting for each year. Using hard coded titles was too confusing for the user and changing the titles manually was not feasible.
We have moved to SQL Server 2005 and Reporting Services 2005. Besides losing the ability to Change the Data at the top of every page as the Dept, VP Name, Director and etc. changed I also seem to have lost this dynamic column headers ability. I have found a cumbersome workaround for the data at the top of the page, but the only thing I have come up for the columns is to try to create queried parameters to feed those titles (they will be the same throught the report) and to fill those parameters with the queried defaults. I haven't tried it successfully yet, but so far it's looking like that is not how the defaults work.
Hopefully someone out there has a solution. I'd appreciate any help I can get.
Hi all,
I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.
I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.
I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)
Thanks in advance.
I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.
I get the error Fiels can not be used in query parameter expressions.
I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?
Thank You.
I have a report which runs for last 12 months data. Since this is going to be last 12 months the column headers change every month. How can we implement this with dynamic column headers in the dataset?
View 9 Replies View RelatedI am attempting to create a subscription that will send a report whose name would have been formatted to the following: ReportName_MMDDYYYY.
I know this can be done by creating a Windows File Share subscription type, but in this case, I would need to use E-Mail.
In this scenario, the user will receive the email and included, the file formatted as described above.
Is there a way to get it done ?