Im having an issue. There are two feeds which is suppose to populate a DB table every month. Feed 1 generates data every month but feed 2 doesn't get data every month as a result of which there is no value on that table. The last time feed 2 had received data and populated the corresponding table was on jan 2012. The problem now is if the table is not populated value wil b Null values in the tables. But instead of that if no value is received I should use the previous month"s value received by feed two as a result of which there will not be huge variance as oppose to putting NULL values. How can I use last moths data if feed 2 doesn't populate the tables. I know a case statement would be great but I just didn't know how to use it. Help would be highly appreciated.
The expression saved here is =IIF(IsNothing(Fields!End_Det.Value),0,Round(Fields!End_Det.Value))
Can something be done if there is no value present than go to the previous month and if there is no value in the previous month then go prior to another previous month? Please help
The expression saved here is =IIF(IsNothing(Fields!End_Det.Value),0,Round(Fields!End_Det.Value))
Can something be done if there is no value present than go to the previous month and if there is no value in the previous month then go prior to another previous month? Please help
you cant directly apply this logic at report expression level unless you bring previous month value as a separate column in your dataset. Are you currently having last month values also in dataset as separate columns?
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Thanks. Also no I haven't brought in previous month value as a separate column in my dataset. Because Im processing feb month report and there is no data from feed 2 and my superior wants me to pull the data from last month for feed 2 which will ultimately result in a close difference rather than a NULL values. How can I put that in a logic so that everytime there is no record from that feed the tables will get automatically populated with the last month's or previous months data.
Thanks. Also no I haven't brought in previous month value as a separate column in my dataset. Because Im processing feb month report and there is no data from feed 2 and my superior wants me to pull the data from last month for feed 2 which will ultimately result in a close difference rather than a NULL values. How can I put that in a logic so that everytime there is no record from that feed the tables will get automatically populated with the last month's or previous months data.
as suggested you need to include a logic in your query behind or procedure to include last month values
either put values in a temporary table and do an update on it to include last month value as a new column or use apply operator to return them along with same query
No actually there is a single SSIS package which populates records received from both these feeds. And this month when I ran that package it just populated results from feed 1 and feed 2 didn't have anything on it. Now the problem is that the data that populated the tables after the execution of this package has only populated tables from feed 1 and it shows no records inserted from feed 2. Now I have to use the tables which was populated by this single SSIS package to get the reports. But when I run this report it just shows me result of feed 1 and there are no records displayed for the other one as both the feeds populate 1 single table.Last month feed 2 when ran had populated tables and now I need to use last month records from feed 2 as this month's records as well.There is no difference in the query present on the dataset because when the record is there it populates it automatically and when there is no record it doesn't show that particular column in the report at all:(. Sorry I was not that clear on explaining my question. Let me know if it is still not clear?
No actually there is a single SSIS package which populates records received from both these feeds. And this month when I ran that package it just populated results from feed 1 and feed 2 didn't have anything on it. Now the problem is that the data that populated the tables after the execution of this package has only populated tables from feed 1 and it shows no records inserted from feed 2. Now I have to use the tables which was populated by this single SSIS package to get the reports. But when I run this report it just shows me result of feed 1 and there are no records displayed for the other one as both the feeds populate 1 single table.Last month feed 2 when ran had populated tables and now I need to use last month records from feed 2 as this month's records as well.There is no difference in the query present on the dataset because when the record is there it populates it automatically and when there is no record it doesn't show that particular column in the report at all:(. Sorry I was not that clear on explaining my question. Let me know if it is still not clear?
You're missing my point here regardless of whether you get values from feed 2 or not you've to bring values from last month also for feed 2. then in report you need to add an expression to check for nulls in current month values and in case its null take value from last month fields instead. In case where you have values coming from feed2 it will take it and will not look for last month values. But in any case the resultset has to have both values as separate column like
ValueCM ValueLM
where Cm,LM are Current Month,Last Month
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
I have one more issue. Could you please tell me a step by step guide of how I can deploy the report? I mean is there any way I can just add the reports I need in a separate folder? I mean I have bunch of rdl files that gets displayed on the solution explorer. Out of those I just need 5 reports and idont know the steps of how I can deploy these reports. Please this would be really helpful.
I have one more issue. Could you please tell me a step by step guide of how I can deploy the report? I mean is there any way I can just add the reports I need in a separate folder? I mean I have bunch of rdl files that gets displayed on the solution explorer. Out of those I just need 5 reports and idont know the steps of how I can deploy these reports. Please this would be really helpful.
you've property called TargetServerURL and TargetReportFolder available from Report Project properties. You need to set former to your report server url and latter to your required report folder. then right click your reports from solution explorer and select deploy. the reports will get deployed to required folder in reportserver. Then you can access the reports from report server using url
http://machinename/reports and going to relevant folder which you gave above. You should also be deploying datasources along with reports
Also you can do this only if your login is mapped to MyReports role in report server
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
I tried a lot but could not come with a query for the following. I have two tables. I would like you to please help me with a logic here. The first table dbo.Main_ax has records from the month of March and so does the second table dbo.subort_aw where the company prefixes the values for a year and currently its prefixed until Dec 2012. Now I would need to write a logic for dbo.subort such that the value remains the same for that quarter. Meaning if my quarter starts from March 2011, I would like to have the values of March, April and May 2011 same as the value of March as they are all different right now. So everything has to be unchanging for that quarter.No new employee number will be added for that period even if they join around April and all the new addition will be done in the next quarter.If dbo.main_ax will update accordingly.
Please help me with this one. I tried to explain to my best knowledge but let me know if i need to add any other info.
About SQL reporting services
Posted - 05/03/2012 : 11:34:45
