Hi,
In the reporting package I have a dimension "All Dates" under which there are "By Weeks" where week numbers are displayed like: "31/03/2013", "07/04/2013", "14/04/2013" etc. So instead of week number, dimension displays just first day of the week. Instead, in the report I would like to display "14", "15", "16", etc. How can I change the week format on the report side?
Thanks,
jl
There is a _week_of_year function in report studio using the ISO 8601 standard. Don't know if this works with a dimensional model (MDX) If this is a DMR model, then defining an attribute with the _week_of_year function would be the best solution (within the model)
_week_of_year is giving me an error of some kind. When setting an expression for Data Item it tells me "No errors." but when I placed the data item in the report and tried to run it, it fails: An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
???
The function expects a date datatype, so perhaps you are feeding it a string representing a date. Try to cast it to a date prior to using the week function..
Ok... I tried to do a data item (named 'Casted Week') with expression: cast([Week], date)
And then another data item with expression: _week_of_year([Casted Week]). And used this item on the report. Now different error: An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.
???
try:
_week_of_year(_make_timestamp (cast(substring([Datestring],7,4) as integer),cast(substring([Datestring],4,2) as integer),cast(substring([Datestring],1,2) as integer)))
Thank you for trying to help but still not working... It says:
Invalid coercion from 'level' to 'string' for '[Week]' in 'week_of_year(_make_timestamp(cast(substring([Week] from 7 for 4) as integer),cast(substring([Week] from 4 for 2) as integer),cast(substring([Week] from 1 for 2) as integer)))'.
???
Try the following.
Create a data item (Week Number) with the expression:
total(1 within set
periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))
)
Next, go to the week node in your crosstab. Change the source type under Text Source to Data Item Value. A new row will appear in the properties, Data Item Value. Set that to Week Number.
Example report XML based on the Sales and Marketing Cube in Cognos 10.2:
<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Month]</expression></dataItem><dataItem name="month number" aggregate="none" rollupAggregate="none"><expression>total(member(1) within set
periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))
)</expression></dataItem><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="month number"/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>
Paul, is was hoping you'd jump in with MDX based solution, but does this generate a weeknumber based on a date in string format?
Unfortunately no, this will count the number of weeks from the start of the year. The proper way to handle this would be to go to the transformer model and add the week number as an attribute (short name or description) to the week. Unfortunately if you're using the Transformer wizard to build the dimension, I don't think it's possible to add the week number. I may be wrong with that, and I'd welcome the input from a Transformer expert.
On the other hand, by not relying on the date, it makes it possible to handle any weird corporate calendars that might be used. Ever get into an argument about counting week numbers? Is week 1 the first full week of a year? Is it the Sunday of the week that contains Jan 1? This method puts the onus on the cube developer to get the weeks handled correctly.
_jl_ , are you using a transformer cube underneath, or is this a DMR model?
I'm not sure, but I guess it's a DMR-model based on this previous problem: http://www.cognoise.com/index.php/topic,21392.0.html. It's the same packet concerned here in this issue.
A quick and easy way to tell is by looking at the attributes of the levels. Do all of the standard levels in all of the dimensions have the same attributes, Short Name, Long Name, Category Code, Member Description? If so then it's a PowerCube. If not, click on tools->Show Generated SQL. Does the drop down say Native SQL or Native MDX? SQL will be DMR, MDX will be an OLAP source.
Ok. Yes, under "Week" there's:
Week - Short Name
Week - Long Name
Week - Category Code
Member Description
And in Tools -> Show Generated SQL/MDX it says "Native MDX" in dropdown menu.
That would definitely be a powercube. Try the suggestion that I made before with the periodsToDate.
The report runs but it doesn't work the way I wanted... Weeks are in the same from like "31/03/2013", "07/04/2013", "14/04/2013" and not 14,15,16.
???
Did you change the text source value of the crosstab node to data item value and use the new data item?
Yes I did. Tried it again today and guess I did something differently, cause now nothing shows up. It does the same thing with Sales and Marketing (cube) as well.
So what I did was:
1. Created a crosstab
2. Created a new Data Item (named 'Week Number') with the expression: "total(1 within set periodsToDate([Cube Name].[All Dates].[All Dates].[Year],currentMember([Cube Name].[All Dates].All Dates])))
3. Placed the created Data Item in the crosstab
4. Selected the node and changed Source type to "Data Item Value" and so also Data Item Value to "Week Number".
Tried to run...Result can be seen as attachment. :'(
With the Sales and Marketing (Cube) I tried to same thing but the expression was set as you said: "total(1 within set periodsToDate([sales_and_marketing].[Time].Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))"
What the heck. What do I do wrong here...?
I see the problem. The node should be the week set. Remove Week Number from the crosstab. Add Week to the rows. Click on the week node and change the text source there. You may also need to add a measure to the crosstab before this starts working, I'm not entirely sure why.
Did you try opening the example XML I posted? If you're on 10.1 you can easily downgrade the XML by changing xmlns="http://developer.cognos.com/schemas/report/9.0/" to xmlns="http://developer.cognos.com/schemas/report/8.0/"
With some work it should also be possible to downgrade the xml to 8.4 as well.
Still dont get it. Set week to the rows and it leaves the week information just empty.
Yes I'm on 10.1.1. and tried your XML but it gives me an error and wont run. Attachment...
That error simply indicates that you don't have the sales and marketing sample package installed. Simply open the query and adapt that to your cube.
Let's try a slightly different way.
First, reset your crosstab so you see the list of weeks. Run the report to make sure each row shows the week.
Next, go to the query and create a data item, called One, that contains the expression 1. This is creating a psuedo measure that we will use to count later.
Next create another data item, called Week Number, that containers the expression (changing to match your cube, of course) count([One] within set
periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))
)
Now go to the week node in your crosstab, and change the Source Type to Data Item Value, and select the Month Number from the drop down that appears in the row below.
It just doesn't seem to work. Now all the week numbers are shown as zeros.
???
Post the expression and report xml
Posted private message and got also an answer. That was fast. Working now. Thank you!
Please share the solution with all of us...
JL didn't want to share the report xml to the world, so he messaged it to me. The issue was simply that his time dimension contained two hierarchies, week and month, and the periodsToDate function was referencing the wrong one.
So it was
total(1 within set periodsToDate([sales_and_marketing].[Time].[Time - Months].[Year],currentMember([sales_and_marketing].[Time].[Time - Months])))
Since the crosstab node was referencing the Weeks hierarchy, currentMember returned the default member of the time-months hierarchy, which was the ALL member. Since the all member doesn't exist in the context of years, the set was empty and it would return 0 for each row. Simply switching the hierarchy reference to Time-Week was enough to fix it.
JL, the thing to remember with this solution is that the week numbers are based on the members in the week set feeding the crosstab node. So if you want to limit the weeks to the last four weeks, you would do that on the weeks set, not the week number data item.